Microsoft Dynamics 365 F&O and Snowflake integration

What moves between them

The primary flow runs from Microsoft Dynamics 365 F&O into Snowflake. ml-connector reads vendors, customers, purchase order headers and lines, vendor invoice headers and lines, GL main accounts, and posted journal entries from OData and writes them into the matching Snowflake tables. Posted GeneralJournalAccountEntries are read-only in D365, so they flow out for reporting and are never written back. JSON line items land in VARIANT columns. Reference data such as financial dimensions and main accounts is synced first so fact rows reference valid keys. Sync cadence is scheduled, and Business Events can trigger an incremental pull of a changed document between scheduled runs; reconciled output in Snowflake can optionally be read back into the pipeline.

How ml-connector handles it

ml-connector stores both credential sets encrypted. On the D365 side it requests an Entra ID client-credentials token scoped to the tenant-specific environment host and refreshes it when a call returns 401, accepting the full environment URL per customer since D365 has no shared base address. On the Snowflake side it self-signs a key pair JWT per request cycle and sends the KEYPAIR_JWT header. Reads are incremental by an OData filter on a modified timestamp, paging through @odata.nextLink, and writes use Snowflake MERGE keyed on the natural OData keys, including dataAreaId, so a re-run updates rather than duplicates rows. Each statement carries a requestId so a network retry is not double-executed, and JSON line items are inserted with PARSE_JSON into VARIANT columns. D365 returns HTTP 429 with Retry-After and Snowflake returns 429 without one, so the connector applies exponential backoff with jitter and caps D365 concurrency. Business Event stubs are deduplicated on ControlNumber and resolved to full records via OData; multi-company tenants require cross-company=true. Identifiers must use lowercase quoted column names to avoid Snowflake case folding, and the target warehouse needs AUTO_RESUME on so writes do not fail when it is suspended. Every record carries a full audit trail and can be replayed if a write fails.

A real-world example

A mid-sized manufacturer with roughly 600 employees runs Microsoft Dynamics 365 F&O for finance, procurement, and supply chain across several legal entities. The finance and analytics team needs spend, AP aging, and GL trend reporting, but running heavy BI queries directly against the live ERP slows the OData service and bumps into the service-protection limits during close. With D365 and Snowflake connected, vendors, purchase orders, vendor invoices, GL accounts, and posted journal entries are copied into Snowflake tables on a schedule, and the BI tools query the warehouse instead. Reports no longer compete with transactional users, multi-entity data is consolidated in one place, and analysts build models without exporting spreadsheets out of the ERP by hand.

What you can do

• •Copy Microsoft Dynamics 365 F&O vendors, customers, purchase orders, and vendor invoices into Snowflake tables on a schedule.
• •Land posted GL main accounts and journal entries in Snowflake for reporting, treating them as read-only in D365.
• •Write idempotently with Snowflake MERGE keyed on natural OData keys, including dataAreaId, so re-runs update instead of duplicate.
• •Bridge Entra ID client-credentials tokens on D365 and a self-signed key pair JWT on Snowflake, refreshed each cycle.
• •Run incremental pulls by OData timestamp filter and resolve Business Event stubs to full records with a callback to OData.

Questions

Which direction does data move between Microsoft Dynamics 365 F&O and Snowflake?

The primary flow is D365 into Snowflake. Vendors, purchase orders, vendor invoices, GL accounts, and posted journal entries are read from OData and written into Snowflake tables. Posted journal entries are read-only in D365, so they are never written back, though reconciled output computed in Snowflake can be read back into the pipeline if needed.

Does Snowflake receive invoices and GL accounts as native objects?

No. Snowflake is a data warehouse and has no built-in finance objects, so every target is a user-defined table. ml-connector maps each D365 OData entity to an agreed table schema and writes rows with the SQL API using MERGE, storing JSON line items in VARIANT columns. Reading the data back is done with SELECT statements against those same tables.

How does the integration detect changes without webhooks from Snowflake?

Snowflake does not push events to connector endpoints, so the connector drives both sides. It pulls changes from D365 on a schedule using an OData filter on a modified timestamp, and can act on D365 Business Events for near-real-time triggers by fetching the full record over OData. Writes into Snowflake are idempotent MERGE statements with a requestId, so a retry never double-executes.