Microsoft Dynamics 365 Business Central and Snowflake integration

What moves between them

Data moves from Microsoft Dynamics 365 Business Central into Snowflake. ml-connector reads vendors, customers, purchase and sales invoices with their lines, general ledger entries, GL accounts, items, and dimensions from BC and writes each into its matching Snowflake table with a MERGE keyed on the BC record id or number. For entities BC supports as subscriptions, a change notification triggers a fetch and a load soon after the edit; purchase orders, which BC does not push, are pulled on a schedule using a lastModifiedDateTime filter. Snowflake is the reporting destination and is not an authoritative ERP, so ml-connector does not write finance records back into Business Central.

How ml-connector handles it

ml-connector stores both credential sets encrypted. For BC it runs the Entra ID client credentials grant against the tenant token URL, requesting the Business Central default scope, and builds the company-scoped base URL from the environment name. For Snowflake it signs an RS256 JWT from the stored RSA private key on each call and sends it as a bearer token with the KEYPAIR_JWT header, since the JWT is valid at most one hour. BC subscriptions are renewed before the three-day expiry by a cron job, and the validationToken handshake is answered on create and renew so the subscription stays active. Because a notification carries no payload, each one triggers a GET of the changed resource, then a MERGE into Snowflake. Invoice and order lines are stored in a VARIANT column using PARSE_JSON. Every Snowflake statement carries a requestId so a network retry cannot double-load, and BC throttling returns HTTP 429, which ml-connector backs off and retries with jitter. Each record carries an audit trail and can be replayed if a load fails. A common gotcha handled here is a suspended Snowflake warehouse, which fails queries unless auto-resume is on.

A real-world example

A mid-sized distribution company running Microsoft Dynamics 365 Business Central for purchasing, sales, and finance wants a single reporting layer in Snowflake that also holds web and shipping data. Before the integration, an analyst exported vendor lists, invoice registers, and trial balances from BC to CSV every week and loaded them by hand, so the dashboards were always a few days stale and month-end reconciliation meant re-pulling everything. With Business Central connected to Snowflake, vendors, invoices, GL entries, and dimensions land in warehouse tables continuously, keyed for clean upserts, so BI tools read current figures and finance reconciles against live ledger data instead of last week's export.

What you can do

• •Copy Business Central vendors, customers, invoices, GL accounts, general ledger entries, items, and dimensions into Snowflake tables.
• •Upsert every record with a MERGE keyed on the BC id or number, so scheduled reruns never create duplicate rows.
• •Follow Business Central change subscriptions for supported entities and poll purchase orders, which BC does not push.
• •Bridge the BC Entra ID client credentials grant and the Snowflake key-pair JWT on every request.
• •Store invoice and order lines as JSON in VARIANT columns, with retries and a full audit trail on each load.

Questions

Which direction does data move between Microsoft Dynamics 365 Business Central and Snowflake?

Data moves from Business Central into Snowflake. Vendors, customers, invoices, general ledger entries, accounts, items, and dimensions are read from BC and loaded into warehouse tables for reporting and reconciliation. Snowflake is the analytics destination and is not an ERP, so ml-connector does not write finance records back into Business Central.

Does Snowflake push changes back, or does the connector have to poll?

Snowflake does not push events to external connector endpoints; its only outbound notifications go to Slack, Teams, and PagerDuty for operational alerts. Because the flow runs from BC into Snowflake, this does not matter for the sync. Business Central is the source, and ml-connector reacts to BC change subscriptions and polls the entities BC does not push, such as purchase orders.

How are the two different authentication methods handled?

Business Central uses OAuth 2.0 client credentials from Microsoft Entra ID, so ml-connector exchanges the tenant ID, client ID, and secret for a token scoped to the BC API. Snowflake uses key-pair authentication, where a stored RSA private key signs a short-lived RS256 JWT sent as a bearer token with the KEYPAIR_JWT header. Both credential sets are stored encrypted and refreshed as each side requires.