Microsoft Dynamics 365 Business Central and Google BigQuery integration

What moves between them

The main flow runs from Microsoft Dynamics 365 Business Central into Google BigQuery. On each scheduled run, ml-connector reads the agreed Business Central entities, such as vendors, customers, purchase and sales invoices, general ledger entries, items, and dimensions, paging through the @odata.nextLink token and filtering on lastModifiedDateTime so only new and changed records are pulled. It writes those records into the customer-defined BigQuery tables that match each entity, using a streaming insert for incremental rows or a batch load job for larger pulls. Business Central is the system of record, so the connector does not write financial entries back from BigQuery into Business Central; where a flow needs a warehouse figure, it can run a BigQuery query and read the result. Cadence follows your reporting needs, from a nightly load to a refresh after month-end close.

How ml-connector handles it

ml-connector stores both credential sets encrypted. For Business Central it holds a Microsoft Entra ID client credentials token for the tenant and targets the company id on the configured environment name, since the environment is part of the URL, and it refreshes the token before it expires. For BigQuery 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, preserving the literal newlines in the PEM key so JWT signing does not break. Each Business Central entity is mapped onto a configurable dataset and table, and Business Central fields are typed to BigQuery columns, with new optional columns added safely while renames require a new table. Reads page through the @odata.nextLink token, stay under the 20,000 record limit, and use a lastModifiedDateTime filter to pull only changes since the last run. Writes carry an insertId for best-effort streaming dedup, and load and query jobs use a caller-supplied jobId so a retry returns the existing job rather than duplicating it. Because BigQuery jobs are asynchronous and send no events, the connector polls each job until its state is DONE, checks for an error result, and backs off with exponential delay. Business Central can push change subscriptions, but they carry no payload, so the connector still fetches the changed resource and renews the three-day subscriptions before they expire. Time-partitioned tables keep query and load costs low, and every record carries a full audit trail and can be replayed if a step fails.

A real-world example

A mid-sized distribution business with around 250 staff runs Microsoft Dynamics 365 Business Central for purchasing, sales, inventory, and finance, and reports across the company in Google BigQuery alongside web order and logistics data. Before the integration, an analyst exported vendor spend, AP and AR invoices, and GL balances from Business Central into spreadsheets each week and loaded them into BigQuery by hand, so the dashboards always trailed the books and reconciling the extracts ate hours. With Microsoft Dynamics 365 Business Central and Google BigQuery connected, vendors, invoices, general ledger entries, and items load into the warehouse tables automatically on a schedule, typed to the agreed columns and pulled incrementally by last-modified date. The dashboards refresh against current Business Central figures and the manual export-and-load step is gone.

What you can do

• •Load Business Central vendors, customers, purchase and sales invoices, general ledger entries, items, and dimensions into customer-defined Google BigQuery tables.
• •Map each Business Central entity onto a configurable BigQuery dataset and table, typing OData fields to warehouse columns.
• •Bridge the Business Central Microsoft Entra ID client credentials token and the Google BigQuery service account JWT, refreshing each before it expires.
• •Read Business Central incrementally with a lastModifiedDateTime filter and @odata.nextLink paging, staying under the 20,000 record per request limit.
• •Write with an insertId for streaming dedup and a stable jobId on load and query jobs, poll each BigQuery job to DONE, and keep a full audit trail on every record.

Questions

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

The main flow is Microsoft Dynamics 365 Business Central into Google BigQuery. Vendors, customers, purchase and sales invoices, general ledger entries, items, and dimensions are read from the Business Central API v2.0 and loaded into customer-defined BigQuery tables for reporting. Business Central is the system of record, so ml-connector does not write financial entries back from BigQuery; where a flow needs a warehouse figure, the connector can run a BigQuery query and read the result.

Does Google BigQuery hold the invoice or vendor tables, or does ml-connector create them?

BigQuery is a schema-flexible warehouse, not an ERP, so it has no native invoice or vendor objects. The customer defines the tables, such as vendors, invoices, and gl_entries, inside a dataset, and ml-connector accepts a configurable dataset and table per Business Central entity and maps the OData fields onto those columns. Adding new optional columns is safe, but renaming or removing a column requires a new table.

How does the integration detect changes if Google BigQuery sends no webhooks?

Google BigQuery sends no events at all, and Business Central change subscriptions carry no payload and expire every three days, so ml-connector polls Business Central on a schedule. It reads each entity with an OData lastModifiedDateTime filter and @odata.nextLink paging to pull only new and changed records, then writes to BigQuery and polls each load or query job until its state is DONE. Load and query jobs carry a caller-supplied jobId so a retry returns the existing job instead of duplicating it.