Sage X3 and Google BigQuery integration

What moves between them

Financial and operational records flow from Sage X3 into BigQuery. Suppliers, purchase orders, invoices, GL accounts, and GL entries are read from Sage X3 using its delta detection fields and inserted into BigQuery datasets and tables on a schedule you define. Each scheduled run discovers changes since the prior poll, and ml-connector loads only new or modified records to keep BigQuery aligned with Sage X3 without retransmitting the entire history.

How ml-connector handles it

ml-connector stores the Sage X3 OAuth2 credentials and the BigQuery service account key encrypted, and manages both the 5-minute Sage X3 token refresh cycle and the 1-hour BigQuery token lifecycle automatically. On each scheduled run, ml-connector polls Sage X3 using the Xtrem GraphQL API or REST api1 endpoint, filters records by updatedDate to find deltas, and streams them into the BigQuery tables you have configured. Because both systems are pull-only and Sage X3 tokens expire quickly, ml-connector detects token expiry, refreshes before it causes a request failure, and retries rate-limited or transient failures. Every record carries an audit trail and source timestamp so you can trace its origin and replay it if a downstream transformation fails.

A real-world example

A mid-sized food distributor runs Sage X3 on-premise for procurement, inventory, and financial accounting across three distribution centers. The finance team needs to analyze purchasing patterns, invoice aging, and supplier spend by product category. Before the integration, data was exported from Sage X3 monthly in CSV format and hand-loaded into a spreadsheet for analysis. Now suppliers, purchase orders, and invoices flow automatically from Sage X3 into BigQuery on a weekly schedule, and the finance team runs SQL queries to track supplier performance, invoice metrics, and procurement trends without manual export steps.

What you can do

• •Load suppliers, purchase orders, invoices, and GL entries from Sage X3 into BigQuery datasets on a schedule you control.
• •Detect changes in Sage X3 records using updatedDate fields and load only new or modified rows into BigQuery, avoiding duplicate data.
• •Authenticate Sage X3 with OAuth2 bearer tokens and BigQuery with a service account JWT, with automatic token refresh before expiry.
• •Store both credential sets encrypted and manage the 5-minute Sage X3 token and 1-hour BigQuery token lifecycles automatically.
• •Maintain a full audit trail on every record so you can trace its origin and replay it if a downstream analysis or ETL step fails.

Questions

How does ml-connector handle Sage X3 token expiry and BigQuery token refresh?

Sage X3 access tokens expire every 5 minutes and refresh tokens last 30 days. BigQuery service account tokens expire after 1 hour. ml-connector manages both lifecycles internally, detecting expiry and refreshing proactively before each request. This keeps the pipeline moving without interruption even when scheduled runs span multiple token lifecycles.

What happens if Sage X3 records have not changed since the last poll?

ml-connector uses updatedDate and modifiedDateTime fields on Sage X3 records to detect deltas. On each scheduled run it queries only for records changed since the prior poll timestamp, so unchanged records are not retransmitted to BigQuery. This reduces network traffic and BigQuery ingestion costs by loading only new or modified data.

Can I configure which Sage X3 entities and BigQuery tables to sync?

Yes. ml-connector lets you map Sage X3 entities like suppliers, invoices, and GL entries to specific BigQuery datasets and tables. You define the schedule (daily, weekly, etc.), the entity classes to poll, and the target table names. ml-connector handles the rest: delta detection, token refresh, retry logic, and audit trail.