Microsoft Dataverse to BigQuery: How to Move Your Data
Replicate Microsoft Dataverse to BigQuery with Airbyte. Change tracking per table, the app registration step people miss, deletes, and status code lookups.
Replicating Microsoft Dataverse to BigQuery moves the tables behind Dynamics 365 and Power Apps into a warehouse where they can be analysed with SQL and joined against marketing, finance, and product data. Airbyte handles it with no code, and this is one of the better-equipped connectors in the catalogue: it uses genuine change tracking rather than a timestamp cursor, and it replicates deletes.
The prerequisite that catches people is not in Airbyte at all. Incremental sync only works on tables where change tracking has been enabled in Power Apps, and it is not on by default for every entity.
How incremental sync works here
The Microsoft Dataverse source uses Dataverse's change tracking feature rather than a cursor column. It sends requests with the change-tracking preference header, the API returns a delta link on the last page of results, and the connector stores that link as state to retrieve only what changed on the next run.
That is a stronger mechanism than a timestamp cursor, because it does not rely on the application updating a modified date, and it captures deletions. But it is entirely dependent on change tracking being switched on for the table, which lives in the Power Apps table settings.
Tables without change tracking support full refresh only. Audit each table you plan to replicate before you build, because full refresh on a large Dataverse entity is both slow and, in a warehouse where you pay per query, avoidably expensive.
One limitation on deletions matters for warehouse work in particular: deleted records carry only the ID. You learn which record went and when you found out, but not what it held. If you are building a slowly changing dimension or an audit history in BigQuery, capture the last known version before applying the deletion, because the connector cannot give it back to you.
Setting up access across two portals
In the Azure portal, open Microsoft Entra ID and create an app registration, noting the application client ID and directory tenant ID. Under certificates and secrets, create a client secret and copy its value immediately, since it is shown only once.
Then in the Power Platform admin centre, select your environment and add that app registration as an application user under users and permissions, with at least read access to the tables you want. This step is the one people skip, and its symptom is misleading: authentication succeeds and the sync finds nothing, which looks like a configuration error on the Airbyte side rather than a missing grant in Dataverse.
The connector uses version 9.2 of the Dataverse Web API and discovers your schema automatically from the entity definitions in your environment, so streams are not configured by hand.
What arrives in BigQuery
The type mapping is sensible. Strings and unique identifiers become strings, DateTime becomes a timestamp with timezone, Integer and BigInt become integers, Boolean stays boolean, and Money, Double, and Decimal become numbers. Status and State become integers, and anything unlisted defaults to string. Virtual fields are excluded from the schema entirely.
Two of those will shape your models. Status and State arriving as integers means your warehouse holds numeric codes rather than the labels users see in Dynamics, and the option set metadata carrying those labels is not synced by this connector. Build a lookup table in BigQuery, or every dashboard will show status 2 where a human expects Active. And Money mapping to a general number type is worth verifying against a known figure if you are reconciling revenue.
The BigQuery destination uses direct load and writes straight to final tables. Batched Standard Inserts is the default loading method and suits Dataverse volumes. Your service account needs BigQuery User and BigQuery Data Editor, and the dataset location must match the datasets you will join against, since BigQuery cannot query across physical locations and the location is fixed at creation.
Tables are partitioned daily on the extraction timestamp and clustered on that plus the primary keys. Since analysts will filter on a record's created or modified date rather than on when Airbyte read it, that default partitioning gives no pruning for the queries you will actually run. Model a table partitioned on the business date if query cost matters.
Frequently asked questions
Why is incremental sync unavailable for a table?
Change tracking is not enabled on it. Tables without change tracking support full refresh only. Enable it in the table settings in Power Apps and re-sync.
The connection tests fine but no records arrive.
The app registration has probably not been added as an application user in your Dataverse environment. Add it in the Power Platform admin centre with read access to the tables you need.
How do I get readable status values?
Maintain a lookup table in BigQuery. Status and State map to integers, and the option set labels are metadata this connector does not sync.
Can I keep a history of deleted records?
Only partially. Deleted records carry the ID alone, so retain the last known version in your own model before applying the deletion if the contents matter.
Get your Microsoft Dataverse data into BigQuery
Audit change tracking table by table, finish both halves of the app registration, build a status lookup, and model a table partitioned on the business date. For the same source elsewhere, see our guide to Microsoft Dataverse to Snowflake. For a relational source into the same warehouse, see PostgreSQL to BigQuery.
Integrate with 600+ apps using Airbyte
Move data from 600+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.