MongoDB to Amazon Redshift: How to Move Your Data

Moving MongoDB into Amazon Redshift takes the data your application actually runs on and puts it somewhere built for aggregation. MongoDB is excellent at fetching a document by key and poor at grouping a hundred million of them, which is why the questions analysts ask tend to arrive as requests for an export rather than as queries anybody runs directly.

This guide covers the managed path with Airbyte. Two things shape the build: a schemaless source has to be given a schema before a column store can do anything useful with it, and Redshift will not accept data without an S3 bucket in the middle.

MongoDB to Amazon Redshift at a glance:

Why move data from MongoDB to Amazon Redshift?

Two situations account for most of these pipelines.

The first is that the interesting data is in the application. Sessions, orders, events and profiles live in MongoDB because that is where the product writes them, and every analytical question about customer behaviour starts there. Answering those questions in MongoDB means aggregation pipelines competing with production traffic, which is a poor arrangement for both.

The second is consolidation in AWS, where Redshift sits alongside the S3 estate and the billing already in place. The pairing is least comfortable when documents genuinely vary: a column store wants stable columns, and a collection whose shape changes every quarter will keep forcing schema changes into a place designed to resist them. Where the shape really is unstable, MongoDB to Databricks absorbs that far more gracefully.

What do you need before you start?

Four things, and one of them is infrastructure people do not expect to need:

A replica set, and a user that can read from it. Change capture works through MongoDB's change streams, which exist only on a replica set, so a standalone instance rules out incremental capture entirely. The MongoDB source documentation covers the permissions involved.

A decision about schema mode. Schema-enforced produces typed columns from the documents it samples. Schemaless hands the whole document across as one field. Into a column store the first is almost always what you want, and choosing casually here undermines the entire reason for the pipeline.

An S3 bucket in the same region as the cluster. Staging is not an optimisation here, it is the only loading path. The bucket needs put, get, delete and list permissions, and it must sit in the cluster's region, so this is a small piece of AWS work to arrange before you begin rather than during.

A Redshift cluster and a user that can create two schemas. The pipeline writes your schema and a separate raw schema it manages itself. Grant accordingly, and tell anyone browsing the cluster that the second one is not theirs to tidy up.

If your cluster restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the relevant security group before you begin.

How do you build a MongoDB to Amazon Redshift pipeline in Airbyte?

Step 1: Prepare the staging bucket before touching Redshift

Create the bucket, confirm it is in the same region as the cluster, and grant put, get, delete and list to the credentials the pipeline will use. Cross-region staging either fails or quietly costs you in transfer charges, and the delete permission matters because staging files accumulate otherwise. Doing this first turns a confusing mid-configuration failure into five minutes of preparation.

Step 2: Configure the MongoDB source

Click Sources in the left navigation, then New Source, and select MongoDB, following adding a source. Supply the connection string, database and credentials, then choose schema-enforced mode and review what it discovered. That review is the moment to notice a field the sampling typed as a string because a handful of old documents disagree with the rest.

Step 3: Configure the Amazon Redshift destination

Click Destinations, then New Destination, and select Redshift, following adding a destination. Supply the cluster endpoint, database, credentials and the S3 bucket details. If you are connecting through an SSH tunnel, read the next section before assuming it covers everything, because it does not.

Step 4: Create the connection and pick a sync mode that suits change streams

Click Connections, then New connection, select your collections and a sync mode. Change capture handles deletes properly, which is the main reason to use it over a cursor. Bear in mind that change streams read from the oplog, so a pipeline paused longer than your oplog window cannot resume from where it stopped and needs a fresh snapshot.

Once data is landing, set sort and distribution keys on the tables people query. Redshift rewards that attention considerably, and nothing in the pipeline makes those choices for you.

What does schema-enforced mode actually change?

Whether Redshift receives columns or a single opaque field. In schemaless mode the connector passes each document across whole, which preserves everything faithfully and leaves a column store with nothing to work with: one field holding JSON, no types, no compression worth having, and every query parsing text at runtime.

Schema-enforced mode samples documents, works out what fields exist and what types they hold, and produces real columns. That is what makes the destination worth having, since columnar storage, compression and predicate pushdown all depend on knowing what a column contains. It is also, unavoidably, an inference drawn from a sample rather than a guarantee.

So treat the discovered schema as a draft to review. Fields present on only a fraction of documents may be missed, and a field holding numbers in recent documents and strings in older ones will be typed one way and surprise you with the other. Ask the application team what changed and when, and check those fields specifically, because a column store is far less forgiving of a type it did not expect than the database you are copying from.

Why does this pipeline need an S3 bucket at all?

Because Redshift loads properly through COPY from S3 and not through row-by-row inserts, and this destination offers no direct path. Data is written to your bucket as files, then loaded into the cluster. That is the right design for a column store, and it means the pipeline has two halves with different requirements rather than one connection to configure.

The consequence people miss concerns security. An SSH tunnel to a private cluster secures the SQL connection and nothing else, because staging traffic goes to S3 over HTTPS and never enters the tunnel. Teams sometimes set the tunnel up believing they have put the whole pipeline inside their private network, which is not what has happened, and the bucket deserves its own thought about encryption, access policy and who else can read it.

The second consequence is the raw schema. Alongside the tables you asked for, the pipeline maintains a schema of its own, which is not clutter and should not be dropped by a well-meaning administrator tidying up. Document both of these, the bucket and the extra schema, wherever your team records what exists in the account, because neither is obvious to somebody arriving later.

Frequently asked questions

Do I really need a replica set?

For change capture, yes. Change streams are a replica set feature, so a standalone instance cannot provide them and you lose reliable delete handling along with incremental sync.

Can I load into Redshift without S3?

No. Staging through S3 and loading with COPY is the only supported path, so the bucket is part of the architecture rather than an option you can decline.

Why are there two schemas in my cluster?

One is yours and one is a raw schema the pipeline manages. Leave the second alone, and make sure whoever audits the cluster knows what it is before they decide it looks unused.

Does an SSH tunnel secure the whole pipeline?

No, only the SQL connection to the cluster. Staging files travel to S3 over HTTPS outside the tunnel, so secure the bucket on its own terms rather than assuming the tunnel covers it.

Can I do this without writing code?

The pipeline, yes, though the bucket and its permissions are AWS configuration rather than Airbyte. Sort and distribution keys are SQL you will want to write once the tables are in use.

Get your MongoDB data into Amazon Redshift

Arrange the bucket first, in the cluster's region and with delete permission included, because staging is mandatory rather than optional. Choose schema-enforced mode and then actually review what it inferred, since a column store punishes a wrong type far harder than MongoDB ever did. Remember that an SSH tunnel leaves the staging path outside it, document the raw schema so nobody drops it, and set sort and distribution keys once you know what people query.

Airbyte's connector catalog includes 600+ pre-built connectors, so application databases can feed the warehouse without a bespoke export. For the same source into another warehouse, see MongoDB to Snowflake, and for another schemaless source into the same destination, Firebase Realtime Database to Amazon Redshift.

Start syncing now →