Sage X3 and Snowflake integration

What moves between them

Purchase orders, supplier invoices, GL accounts, and GL entries flow from Sage X3 into Snowflake on a poll schedule you control. Each record is mapped to your Snowflake schema and inserted or updated in the target tables. Change detection uses Sage X3's updatedDate field to pull only records modified since the last sync, and Snowflake stores a watermark of the last-synced timestamp. GL entries are flattened and included in full so your finance and accounting teams can reconcile and analyze cost allocations against your ERP. Direction is ERP to warehouse; Snowflake is read-mostly and no financial entries are written back to Sage X3.

How ml-connector handles it

ml-connector stores your Sage X3 OAuth2 credentials encrypted and refreshes the bearer token when it expires in 5 minutes, keeping the connection alive across long polling runs. You provide the server URL and folder name for your Sage X3 instance, and ml-connector accepts either the REST api1 or GraphQL Xtrem endpoint based on your X3 version. On the Snowflake side, ml-connector stores your Key Pair credentials (the RSA private key encrypted) and generates a fresh JWT on each request, handling the account identifier and warehouse requirements. Schema mapping is configured per table, so Sage X3 supplier invoices can land in a different schema from GL accounts. Change detection starts with the updatedDate field on each Sage X3 record, so ml-connector only pulls records changed since the last sync, reducing payload and query load. If a Sage X3 record fails to insert (schema mismatch, missing dimension), ml-connector retries with exponential backoff and logs the failure so it can be reviewed and replayed. Analytical dimensions on GL entries vary per your X3 configuration, so the mappers are defined as conventions rather than hard-coded, and you can extend them as your GL structure evolves.

A real-world example

A mid-sized precision-parts manufacturer runs Sage X3 for procurement and finance across five plants and a shared warehouse. The finance team needs to analyze labor costs, scrap, and material variance by plant and cost center, but Sage X3 reporting is slow and spreadsheet exports lose detail. With Sage X3 and Snowflake connected, purchase orders, supplier invoices, and GL entries flow into Snowflake every 4 hours. Analysts query Snowflake directly instead of waiting for month-end close, and auditors can trace any invoice back to its GL posting and cost allocation. The finance team now closes the month 2 days earlier and runs analysis on live data instead of 2-week-old snapshots.

What you can do

• •Move purchase orders, supplier invoices, and GL entries from Sage X3 into Snowflake tables on a schedule you control.
• •Detect changes automatically using Sage X3 updatedDate fields, so only modified records are synced since the last run.
• •Map Sage X3 GL accounts, suppliers, and cost dimensions to your Snowflake schema without re-keying financial data.
• •Handle Sage X3 OAuth2 token refresh, Key Pair JWT generation, and instance-specific server URLs transparently.
• •Track every record with a full audit trail, including timestamps and retry history, for finance compliance and debugging.

Questions

How does ml-connector handle Sage X3 token expiry and Snowflake Key Pair authentication?

Sage X3 OAuth2 tokens expire in 5 minutes, so ml-connector refreshes the bearer token before each request. On the Snowflake side, ml-connector generates a new JWT from your encrypted RSA private key for each API call, and tracks token expiry to prevent outages. Both credential sets are stored encrypted in ml-connector's database.

Does the integration work with both REST api1 and GraphQL Xtrem endpoints?

Yes. ml-connector accepts either endpoint depending on your Sage X3 version. REST api1 works on V6 and later, while GraphQL Xtrem requires V12 or later and is the recommended path. You specify which endpoint your instance exposes, and ml-connector uses it for all polling queries.

What happens if a Sage X3 record fails to insert into Snowflake?

ml-connector logs the failure with the record data and the Snowflake error, and retries with exponential backoff. Every failed record is stored in the audit trail so you can review it, fix the schema mapping or source data, and replay it manually. This keeps your warehouse in sync without losing visibility into what went wrong.