Microsoft Dynamics 365 F&O and Google BigQuery integration

What moves between them

The flow runs from Microsoft Dynamics 365 F&O into Google BigQuery. ml-connector reads vendors, customers, purchase orders, vendor invoices, and posted general journal entries from the F&O OData API and loads them into the matching BigQuery tables, one entity per configured table. Each table is keyed on the F&O natural key, such as InvoiceNumber with VendorAccount, and partitioned on a date column like the invoice or transaction date so polling and reporting queries stay cheap. The cadence is a schedule you set, since neither system can push a usable event here. BigQuery is treated as a read destination for analytics, so ml-connector does not write records from the warehouse back into F&O.

How ml-connector handles it

ml-connector stores both credential sets encrypted. On the F&O side it requests an Entra ID bearer token with the client credentials flow, scoped to the tenant environment host, and refreshes it before the roughly one-hour expiry. On the BigQuery side it signs a JWT with the service account private key and exchanges it for an access token, preserving the literal newline characters in the PEM key so the signature stays valid. Because BigQuery sends no events and F&O Business Events carry only identifier stubs, the connector polls F&O on a schedule using server-driven paging and follows the @odata.nextLink cursor through large result sets. Each F&O entity maps to one BigQuery table, with OData fields aligned to the table schema and a partition column chosen for cheap incremental queries. To stay idempotent, ml-connector assigns a caller-supplied jobReference.jobId on each load job so a retried batch returns the existing job rather than duplicating rows, and sets an insertId per row on streaming writes for best-effort dedup. F&O returns HTTP 429 with a Retry-After header under service protection limits, so the connector honors that delay and backs off. Real gotchas it handles: the tenant-specific F&O host is a credential, not a constant; cross-company reads need cross-company=true with a dataAreaId filter; BigQuery streaming inserts and DML are unavailable on the free sandbox, so a billing-enabled project is required for writes; and adding a column to a BigQuery table is safe while renaming or dropping one is destructive.

A real-world example

A mid-sized distribution company with about 600 employees runs Microsoft Dynamics 365 F&O for procurement, accounts payable, and the general ledger across three legal entities. Finance and operations wanted a single analytics layer combining ERP spend with web and logistics data, but their only option was an analyst exporting OData results to spreadsheets every week, which was slow, error-prone, and always a few days stale. With Microsoft Dynamics 365 F&O and Google BigQuery connected, vendors, purchase orders, vendor invoices, and posted GL entries load into partitioned BigQuery tables on a nightly schedule across all three legal entities. The analytics team now builds spend and cash dashboards in SQL on fresh data, and the manual export step is gone.

What you can do

• •Load Microsoft Dynamics 365 F&O vendors, customers, purchase orders, vendor invoices, and posted GL entries into Google BigQuery tables.
• •Map each F&O OData entity to a configured BigQuery dataset and table with a date partition column for cheap queries.
• •Bridge Entra ID client credentials on the F&O side and a Google service account JWT on the BigQuery side.
• •Poll F&O on a schedule with @odata.nextLink paging and Retry-After backoff, since neither system pushes usable events.
• •Stay idempotent with a caller-supplied job ID on load jobs and an insertId on streaming rows to avoid duplicate loads.

Questions

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

The flow runs from Microsoft Dynamics 365 F&O into Google BigQuery. Vendors, customers, purchase orders, vendor invoices, and posted general journal entries are read over OData and loaded into BigQuery tables for analytics. BigQuery is a warehouse rather than a system of record, so ml-connector treats it as a read destination and does not write records back into F&O.

Does the integration use Business Events, or does it poll Dynamics 365 F&O?

It polls on a schedule. BigQuery sends no webhooks at all, and F&O Business Events deliver only lightweight identifier stubs that still require a follow-up OData read for the full record. Rather than depend on those stubs, ml-connector reads the F&O entities directly on the cadence you set, paging through results with the @odata.nextLink cursor and honoring the Retry-After header on any HTTP 429 response.

How are duplicate rows avoided when a load is retried into BigQuery?

ml-connector assigns a caller-supplied jobReference.jobId to each BigQuery load job, so if a retry submits the same job ID the API returns the existing job instead of creating a duplicate. For streaming writes it sets an insertId per row for best-effort deduplication within BigQuery's roughly one-minute window. Records are also keyed on the F&O natural key in the target table, which keeps repeated reads from producing conflicting rows.