Acumatica and Snowflake integration

What moves between them

Data moves from Acumatica into Snowflake. ml-connector pulls Acumatica vendors, AP bills, purchase orders, AP payments, GL accounts, and journal transactions, then writes each into its matching Snowflake table. Pulls run on the schedule you set, filtered by LastModifiedDateTime so only records changed since the last run are fetched, and the high-water mark is stored after each run. Each load is a MERGE keyed on the Acumatica record identifier, so a record updated in the ERP updates the existing warehouse row instead of creating a duplicate. Snowflake is treated as a reporting destination, so ml-connector does not write financial entries back into Acumatica.

How ml-connector handles it

ml-connector stores both credential sets encrypted. On the Acumatica side it requests an OAuth2 token against the tenant identity endpoint and calls the Default endpoint at the exact version the customer runs, since the version is part of the URL and a mismatch returns 404. On the Snowflake side it self-signs a key-pair JWT for the service account on each call, because those JWTs expire within an hour. Acumatica wraps every field value in a value object and pages with $top and $skip, so the connector reads pages until a batch is short and unwraps each field before mapping it to a warehouse column. Acumatica bill and PO line arrays and journal detail lines are written into VARIANT columns as JSON so the line detail survives in Snowflake. Because neither system pushes reliable events to a connector, both sides are polled: Acumatica by LastModifiedDateTime watermark, Snowflake by request rather than callback. Each Snowflake statement carries a requestId and is a MERGE, so a retry after a network failure neither double-loads nor duplicates rows. Acumatica returns HTTP 429 once the per-minute request count crosses its license threshold and Snowflake returns 429 under load, so ml-connector backs off with jitter and retries on both, and every record carries a full audit trail and can be replayed if a load fails.

A real-world example

A mid-sized distribution company runs Acumatica for procurement, accounts payable, and the general ledger, and its analysts work in Snowflake alongside sales and inventory data already landed there. Before the integration, finance exported bills, PO, and GL balances from Acumatica to spreadsheets each week and someone hand-loaded them into Snowflake, so dashboards were always days behind and spend analysis could not join payables to the warehouse data. With Acumatica and Snowflake connected, changed vendors, bills, purchase orders, payments, and GL activity flow into Snowflake tables on a nightly schedule, keyed so updates land cleanly. Analysts query a current copy of the ledger, and the manual export and load step is gone.

What you can do

• •Replicate Acumatica vendors, bills, purchase orders, payments, and GL accounts into Snowflake tables.
• •Load Acumatica journal transactions and document line detail into Snowflake, with line arrays stored as JSON in VARIANT columns.
• •Pull only records changed since the last run using an Acumatica LastModifiedDateTime watermark.
• •Bridge Acumatica OAuth2 on the version-locked tenant URL and a self-signed key-pair JWT for the Snowflake service account.
• •Write each load as an idempotent MERGE with a Snowflake requestId, so retries never duplicate rows.

Questions

Which direction does data move between Acumatica and Snowflake?

Data moves from Acumatica into Snowflake. Acumatica is the source of record for finance data and Snowflake is the reporting destination, so vendors, bills, purchase orders, payments, GL accounts, and journals are read from Acumatica and written into warehouse tables. ml-connector does not write financial entries back into Acumatica.

Does this integration use webhooks or polling?

It uses polling on both sides. Acumatica Push Notifications exist but are secured by a shared-secret header and are not guaranteed, and Snowflake does not push events to external connectors at all. ml-connector reads changed Acumatica records by a LastModifiedDateTime watermark on the schedule you set, and the high-water mark is stored after each run.

How does the connector avoid duplicate rows in Snowflake when a sync reruns?

Every write is a Snowflake MERGE keyed on the Acumatica record identifier, so an existing warehouse row is updated rather than inserted again. Each statement also carries a requestId, which Snowflake uses to deduplicate retried calls after a network failure. Together these make reruns and retries safe without creating duplicate records.