Microsoft Dataverse to Amazon Redshift: How to Move Your Data
Move Microsoft Dataverse into Amazon Redshift with Airbyte. Why wide Dynamics entities time out on the default page size, and why a cluster handles them well.
Moving Microsoft Dataverse into Amazon Redshift brings the tables behind Dynamics 365 and Power Apps into a warehouse where they can be joined to everything else. Business applications hold the records; a cluster is where they meet finance and operations data.
This guide covers the managed path with Airbyte. Two things shape the build: Dynamics entities are unusually wide and that has practical consequences on the way out, and a columnar warehouse handles that width better than most destinations.
Microsoft Dataverse to Amazon Redshift at a glance:
Why move data from Microsoft Dataverse to Amazon Redshift?
Two situations account for most of these pipelines.
The first is joining business application data to the rest of the organisation. Opportunities, cases and accounts mean considerably more beside billing, delivery cost and support volume, and Dataverse has never seen any of those.
The second is reporting for people who already work in a cluster, without asking them to learn a new platform. If your work is heavy transformation rather than SQL reporting, Microsoft Dataverse to Databricks handles that shape with less table maintenance.
What do you need before you start?
Four things, and the first is a formatting detail that wastes afternoons:
Your environment URL, with nothing after the host. No trailing slash and no path, which is easy to get wrong when copying from a browser. The Microsoft Dataverse source documentation sets out the fields and the app registration.
An app registration, giving tenant, client and secret. Created in Entra rather than in Dataverse, so this usually involves somebody who administers your Microsoft tenant.
Change tracking enabled on the tables you want incrementally. It is a per-table setting in Power Apps, and without it a stream can only full refresh.
An S3 bucket in the cluster's region and a key plan. Loading goes through S3 staging, and Redshift's performance depends on sort and distribution keys the pipeline will not choose for you.
If your cluster restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list before you begin.
How do you build a Microsoft Dataverse to Amazon Redshift pipeline in Airbyte?
Step 1: Look at how wide your tables are
Open a few of the entities you intend to sync and count the columns, because a Dynamics table carrying two or three hundred attributes is ordinary and it changes how you configure this. Knowing which of your tables are the wide ones tells you where to expect trouble and which page size to start with, rather than discovering both from a timeout during the first sync.
Step 2: Configure the Microsoft Dataverse source
Click Sources in the left navigation, then New Source, and select Microsoft Dataverse, following adding a source. Supply the environment URL, tenant, client identifier and secret. Leave the maximum page size at its default of five thousand for now, knowing it is the dial to turn when a wide table misbehaves.
Step 3: Configure the Redshift destination
Click Destinations, then New Destination, and select Redshift, following adding a destination. Supply the cluster details, database, credentials and your S3 staging bucket. Keeping that bucket in the same region as the cluster avoids paying transfer costs on every sync.
Step 4: Create the connection and schedule maintenance
Click Connections, then New connection, select your tables and a sync mode. Deleted records arrive carrying only their identifier, so build a history table downstream if the contents of deleted opportunities matter. Then schedule vacuum and analyze, because Redshift does not maintain itself.
Watch for timeouts on the widest tables during the first week, since that is where this connector asks something of you.
Why do wide tables time out?
Because a page of records is measured in rows rather than in bytes, and Dataverse rows are not all the same size. The connector requests up to five thousand records per page by default, which is sensible for a narrow table and a great deal of data when each row carries hundreds of attributes.
Dynamics entities are famously wide. Years of customisation add fields nobody removes, and a core entity in a mature environment can carry several hundred columns, most of which no report has ever used. The documentation names the remedy directly: reduce the maximum page size when you encounter timeout errors on tables with wide rows.
So treat it as a per-environment adjustment rather than a fault. Start at the default, watch which tables struggle, and lower the page size until they complete, accepting that more pages means a longer sync. If only one or two entities are the problem, a separate source with its own page size for those tables keeps the rest running at full speed.
What does a cluster do with three hundred columns?
Rather well, which is a pleasant change. Redshift stores data by column, so a query selecting twelve fields from a three hundred column table reads twelve columns and ignores the rest. The width that made extraction awkward costs you very little once the data has landed, which is a genuine argument for this destination with this source.
That means you can keep the entity intact rather than trimming it to the fields somebody wants today, and the field nobody modelled remains available when a question arrives about it. Resist the urge to prune during the pipeline, because reinstating a column later means a resync you could have avoided.
What you should decide deliberately is the physical design. Sort by the date your reports filter on, usually created or modified, and choose a distribution key from the column your joins use, typically the account or contact reference that ties entities together. And schedule vacuum and analyze from the first week, because a cluster loaded regularly and never maintained gets slower in a way that looks like growth.
Frequently asked questions
A table times out during sync.
Reduce the maximum page size, which defaults to five thousand records. Wide rows make a page large, and that is the documented remedy.
The connection fails immediately.
Check the environment URL for a trailing slash or a path, since it should be the host alone. After that, check the app registration credentials.
Why is a table only offering full refresh?
Change tracking is not enabled on it. That is a per-table setting in Power Apps, and incremental sync depends on it entirely.
Should I trim columns before loading?
Usually not. Redshift reads only the columns a query selects, so keeping the whole entity costs little and saves a resync when somebody wants a field later.
Can I do this without writing code?
The pipeline, yes. Key design, the history table for deletions and the maintenance jobs are not, and they are what makes the cluster usable.
Get your Microsoft Dataverse data into Amazon Redshift
Check the width of your entities before configuring anything, because the page size defaults to five thousand records and Dynamics rows are large. Give the environment URL no trailing slash, and enable change tracking per table. Then keep the full entity rather than pruning it, since a columnar warehouse reads only what you select, and put the effort into sort and distribution keys plus scheduled maintenance.
Airbyte's connector catalog includes 600+ pre-built connectors, so business application data can be analysed beside everything it affects. For the same source into a warehouse with different controls, see Microsoft Dataverse to Snowflake, and for an enterprise database into the same destination, Oracle Database to Amazon Redshift.
Integrate with 700+ apps using Airbyte
Move data from 700+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.