Pulling Amazon Redshift Data Into Microsoft Fabric With Data Factory

A fair number of the Australian businesses we work with are not sitting neatly inside one cloud. They have a warehouse in Amazon Redshift because a team stood it up three years ago and it works, but the reporting, the AI projects, and the rest of the analytics stack have drifted towards Microsoft. So now there is data in Redshift that people in Power BI and Fabric want to use, and the question lands on someone's desk: how do we actually get it across without building a fragile pile of scripts nobody wants to own?

That is the gap the Amazon Redshift connector in Microsoft Fabric Data Factory is meant to fill. Microsoft's connector overview documentation covers the supported capabilities. What I want to do here is talk about when you'd reach for it, where it does the job nicely, and the bits that catch people out, based on the cross-cloud pipelines we've built and, honestly, a few we've been called in to rescue.

Why anyone ends up doing this in the first place

Nobody wakes up wanting to shuttle data between AWS and Azure. It happens because of history. A retailer we worked with had years of transactional history in Redshift, and their finance and merchandising teams had moved onto Power BI and were quietly frustrated that the good historical data was locked in a system they had to file a ticket to get anything out of.

The instinct in that situation is often to rip Redshift out and consolidate everything into Fabric. Sometimes that is the right long-term call. But it is a big project, and in the meantime the business still needs the numbers. Bringing the Redshift data into Fabric with Data Factory lets you get value now, land the data in a lakehouse or warehouse where the rest of your stack can reach it, and make the "do we retire Redshift entirely" decision later with less pressure. That sequencing matters. I have watched teams try to do the big migration and the urgent reporting need at the same time and end up doing neither well.

What the connector actually does

At its core, the Amazon Redshift connector lets a Data Factory pipeline in Fabric connect to your Redshift cluster and read data out of it. You use it inside the copy activity to pull tables or query results and land them somewhere in Fabric, typically a lakehouse or a warehouse. It is a source connector, so think of it as the "get the data out of Redshift" half of a move-and-land pattern rather than a two-way sync.

A couple of things worth knowing up front. The connector supports connecting directly to Redshift, and it also supports an unload-to-S3 pattern where Redshift dumps the data to an S3 bucket first and Data Factory reads from there. That second option is not a nice-to-have. For anything beyond small tables, unloading to S3 and reading the files is dramatically faster and gentler on your Redshift cluster than pulling rows out over a direct query connection. Redshift is a columnar warehouse built for analytical scans, not for streaming millions of rows out through a client connection, and if you point a naive copy activity straight at a big table you will feel it.

If your Redshift cluster sits inside a private network, which any sensible production cluster does, you will need a path in. That usually means an on-premises data gateway or a virtual network setup so Fabric can actually reach the cluster. This is the single most common thing that works in a quick test against an open endpoint and then falls over the moment you point it at the properly secured production cluster. Plan the network path early, not on the day you go live.

Where it works well

For the bread-and-butter job of "we have tables in Redshift and we want them in Fabric on a schedule," the connector is solid and does not ask for much cleverness. You define the source, you define where it lands, you set a schedule or a trigger, and it runs. For a daily or hourly batch load of reasonably sized tables, this is genuinely straightforward, and that is high praise for a cross-cloud data movement tool. Cross-cloud is where these things usually get ugly.

It also fits neatly into the wider Fabric picture. Once your Redshift data is landed in a lakehouse, everything downstream in Fabric can use it: Power BI reporting, notebooks, further transformation, the lot. So you are not building a one-off bridge, you are feeding data into a platform your team already works in. For clients running a Microsoft-centric analytics stack, that is the real value, and it is a big part of what our Microsoft Fabric consultants spend their time on: getting external data sources landed cleanly so the rest of the stack just works.

The copy activity itself is mature. You get the usual controls around mapping columns, handling schema, and staging, and if you have used Data Factory for anything else the experience will feel familiar. There is not a steep separate learning curve just for Redshift.

Where it gets rough

Now the honest part. A few things are worth going in with your eyes open about.

First, egress. Moving data out of AWS costs money in data transfer fees, and if you set up a pipeline that re-pulls large tables in full every single run, you will get a line on your AWS bill that someone in finance will eventually ask about. This is not a flaw in the connector, it is physics of moving data between clouds, but the connector will happily let you do the expensive thing. Design for incremental loads where you can, pulling only what changed rather than the whole table each time. It takes more thought up front and it saves real money.

Second, the direct-query performance cliff I mentioned. It is easy to build a pipeline that works fine against a test table and then chokes when someone points it at the hundred-million-row fact table. If your volumes are meaningful, use the unload-to-S3 path. Do not find this out in production.

Third, type and data quirks. Redshift has its own handling of certain data types, character encodings, and edge cases around nulls and precision that do not always map cleanly to where you are landing the data in Fabric. Most of the time it is fine. But test with real data that includes your ugliest records, the ones with unusual characters or extreme values, rather than a clean sample. The mismatches that bite you are almost always in the data nobody thought to check.

Fourth, this is a read connector. If your actual need is two systems staying in sync, or writing back to Redshift, this is not that. It moves data out. Be clear about which problem you are solving, because "sync" and "load" are different jobs and people conflate them.

How I'd approach a real project

If a client came to us with Redshift data they wanted in Fabric, the shape of the work would be fairly predictable. Start by being clear about whether this is a permanent bridge or a stepping stone to retiring Redshift, because that changes how much you invest in the pipeline. Sort out the network path to the cluster before writing any pipeline logic, since that is the usual blocker. Use the unload-to-S3 pattern for anything sizeable. Build incremental loads rather than full refreshes wherever the source data supports it, to keep both runtime and egress costs sane. And test against genuinely messy data before calling it done.

None of that is exotic. It is the same discipline that separates a data pipeline that runs quietly for a year from one that generates a support ticket every fortnight. The tooling is good. The decisions around it are what determine whether you have built something maintainable. This is exactly the kind of cross-cloud plumbing our broader Microsoft Data Factory consulting work involves, and it is rarely the connector itself that makes or breaks a project.

The bigger question worth asking

One last thing, because it is the conversation I end up having most often. When a business asks us to move Redshift data into Fabric, the interesting question is usually not "how" but "why is the data in two places at all, and should it stay that way." Sometimes the honest answer is that Redshift is doing a job well and the bridge into Fabric is the right permanent architecture. Other times the bridge is quietly papering over a decision nobody wants to make about consolidating the analytics platform.

The connector gives you a clean way to get the data flowing either way, which is genuinely useful. Just do not let a working pipeline stop you from asking whether the two-warehouse setup is what you actually want long term. That is a strategy question as much as a technical one, and it is the sort of thing our data and analytics services exist to help with.

If you have Redshift data your Microsoft stack needs to reach, or you are weighing up whether to bridge it or consolidate it, get in touch and we will take a look at what you've got and what actually makes sense.