Acumatica and Google BigQuery integration

What moves between them

The flow runs from Acumatica into Google BigQuery. On each scheduled run, ml-connector reads new and changed Acumatica records since the last high-water mark and writes them as rows into the matching BigQuery tables, such as vendors, invoices, purchase_orders, payments, and gl_entries. Direction is one way, ERP to warehouse, because BigQuery is a reporting sink rather than a system of record. Cadence is set per flow, from frequent intraday syncs to a nightly load, and the run records the LastModifiedDateTime watermark so the next run only pulls deltas.

How ml-connector handles it

ml-connector stores both credential sets encrypted. On the Acumatica side it accepts the full tenant URL and the endpoint version per customer, requests an OAuth token, and refreshes it when a call returns 401. On the BigQuery side it signs a JWT with the service account private key and exchanges it for an access token that it refreshes before the 3600 second expiry, and it preserves the literal newlines in the PEM key so signing does not break. Because BigQuery has no change events, the connector polls Acumatica on LastModifiedDateTime and pages with $top and $skip, unwrapping Acumatica field values that arrive as {value: ...}. Rows are written with insertAll and a stable insertId so a retry after a network error does not duplicate within the dedup window. Acumatica rate limits return HTTP 429 once the request count crosses the licensed threshold, so the connector backs off with jitter and retries. Schema is additive, so adding a column to a BigQuery table is safe, and every record carries a full audit trail and can be replayed if a write fails.

A real-world example

A mid-sized distribution company runs Acumatica for procurement, AP, and the general ledger across several warehouses, and its analytics team works in Google BigQuery. Before the integration, an analyst exported bills and GL journals from Acumatica to spreadsheets every week and hand-loaded them into BigQuery, which left dashboards a week stale and prone to copy-paste errors. With Acumatica and Google BigQuery connected, new bills, payments, and journal lines flow into the warehouse on a nightly schedule, allocated to the right tables. Spend and cash dashboards refresh on their own, and the manual export step is gone.

What you can do

• •Copy Acumatica vendors, AP bills, purchase orders, payments, and GL journals into Google BigQuery tables.
• •Run one-way ERP to warehouse syncs so BigQuery stays a clean reporting copy of Acumatica finance data.
• •Poll Acumatica on LastModifiedDateTime with offset pagination so each run loads only new and changed records.
• •Bridge Acumatica OAuth 2.0 and BigQuery service account JWT auth, refreshing both tokens before they expire.
• •Write rows with insertAll and a stable insertId, with retries and a full audit trail on every record.

Questions

Which direction does data move between Acumatica and Google BigQuery?

Data moves one way, from Acumatica into Google BigQuery. Acumatica is the system of record and BigQuery is the reporting warehouse, so ml-connector reads finance records from Acumatica and writes them as rows into BigQuery tables. It does not write data back into Acumatica from the warehouse.

Does BigQuery push changes back, or does the connector poll?

BigQuery sends no webhooks and has no outbound push, so the connector never waits for an event from it. Acumatica can emit Push Notifications, but the reliable pattern used here is polling Acumatica on a LastModifiedDateTime filter on a schedule you set. Each run stores the high-water mark so the next run pulls only deltas.

How are the two different authentication methods handled?

Acumatica uses OAuth 2.0 through its built-in identity server with a tenant-specific URL and a version-matched endpoint, while BigQuery uses a Google service account that signs a JWT and exchanges it for a one-hour access token. ml-connector stores both credential sets encrypted, refreshes each token before it expires, and preserves the literal newlines in the BigQuery private key so JWT signing works.