DynamoDB to Amazon Redshift: How to Move Your Data
Move DynamoDB into Amazon Redshift with Airbyte. Why the inferred schema misses sparse attributes, why deletions never arrive, and the strict cursor rules.
Moving DynamoDB into Amazon Redshift puts application data where it can be aggregated and joined. DynamoDB is excellent at retrieving an item by key at any scale, and almost useless for asking how many, grouped by, or compared with last quarter.
Two things decide whether the result is trustworthy. The schema is inferred from a sample rather than declared, so sparse attributes can be missed entirely, and the connector does not read DynamoDB Streams, so deletions never arrive.
DynamoDB to Amazon Redshift at a glance:
Why move data from DynamoDB to Amazon Redshift?
Two situations account for most of these pipelines.
The first is analysis DynamoDB cannot do. Aggregations, joins and ad hoc filters are what a warehouse is for, and running a table scan against DynamoDB to answer them is both slow and expensive in consumed capacity.
The second is staying inside one estate. If your warehouse, cost reports and other sources already run on AWS, bringing application data across puts everything in reach of one set of queries rather than two systems that never meet.
What do you need before you start?
The cursor requirement is strict, and the bucket is a prerequisite you cannot skip:
AWS credentials with read access to the table. Supply an access key and secret, or omit both to use the default credentials chain. The DynamoDB source documentation covers the fields and the region setting.
A cursor field meeting four conditions. It must be top-level rather than nested, a string or an integer, formatted as ISO 8601 or an epoch timestamp, and present on every item. A single blank value breaks incremental sync for the whole stream.
An S3 staging bucket and IAM permissions. The Redshift destination writes data to S3 as files with a manifest, then issues a COPY command. You need put, get, delete and list on the bucket, in the same region as the cluster.
A list of the attributes you actually need. Ideally from whoever writes to the table, because the inferred schema is a sample rather than a specification and will not tell you about an attribute present on a small fraction of items.
Note also that an SSH tunnel covers the SQL connection only. Staging traffic reaches S3 over HTTPS regardless, so a network design assuming everything passes through a bastion is wrong.
How do you build a DynamoDB to Amazon Redshift pipeline in Airbyte?
Step 1: Get the real attribute list from the application team
Ask what items in this table can contain, including attributes added later and present on only some records. The connector's inferred schema is a reasonable starting point and not an authority, and the gap between the two is where quiet data loss happens.
Step 2: Configure the DynamoDB source
Click Sources in the left navigation, then New Source, and select DynamoDB, following adding a source. Supply the region and credentials, then review the discovered schema against the list from step 1 rather than accepting it.
Step 3: Configure the Redshift destination
Click Destinations, then New Destination, and select Redshift, following adding a destination. Supply the cluster host, database, schema and credentials, along with your S3 bucket and IAM key. Staging files are removed after a successful COPY by default.
Step 4: Nominate a cursor and set your keys
Click Connections, then New connection, nominate your cursor field and choose an incremental sync mode. Set sort keys on whatever date column your queries filter by, and distribution on the partition key if you will join against it.
A full read consumes DynamoDB capacity, so schedule the initial load against a quiet period and check your provisioned throughput before starting it.
Why can the inferred schema be wrong?
Because DynamoDB does not have a schema to read, so the connector builds one by scanning at least a thousand items and recording what it finds. That is a sensible approach and a sample rather than a specification, which matters when items are not uniform.
An attribute present on a small fraction of items can be missed entirely, and an attribute holding a number on most items and a string on a few will be typed from whichever the sample saw. On a row-oriented warehouse with real column types, that second case is a genuine conversion problem rather than a cosmetic one.
So check the discovered schema against what the application team says the table holds, and ask specifically about attributes added partway through the table's life. Those are the ones most likely to be sparse and most likely to matter, because they usually represent a newer feature somebody wants to analyse.
What happens to deleted items?
Nothing, which is the point. The connector reads the table rather than DynamoDB Streams, so a deleted item simply stops being returned and the copy already in Redshift stays there indefinitely, with nothing marking it as gone.
That matters more than on a warehouse-to-warehouse pipeline, because DynamoDB usually holds live application state where items are genuinely removed. Sessions expire, carts are abandoned and cleaned up, records are deleted on request, and your warehouse counts all of them as current.
The better remedy is soft deletion in the application, marking items with a flag and updating the cursor field so the change is visible. Failing that, a periodic full refresh on the affected tables is the fallback, and it costs DynamoDB capacity each time you run it.
Frequently asked questions
Why is an attribute missing from my Redshift table?
The schema is inferred by sampling at least a thousand items, so a sparse attribute can be missed. Check the discovered schema against what the application team says items contain.
Are deletions captured?
No. DynamoDB Streams are not read, so deleted items stay in your warehouse. Use soft deletion in the application, or a periodic full refresh.
Why has incremental sync stopped working?
Check the cursor field. It must be top-level, a string or integer, in ISO 8601 or epoch format, and present on every item, because a single blank value breaks the stream.
Do I need an S3 bucket to load into Redshift?
Yes. The destination stages data in S3 and loads it with COPY, which is Redshift's recommended approach. Put the bucket in the same region as your cluster.
Can I do this without writing code?
Yes. The setup above is entirely UI-driven. The only code you might want is a soft-delete change in your application, which is a schema decision rather than pipeline work.
Get your DynamoDB data into Amazon Redshift
Check the inferred schema against what the application team says items contain, because sampling is not a specification. Settle the deletion question before anyone counts rows, nominate a cursor meeting all four conditions, and provision the staging bucket in your cluster's region.
Airbyte's connector catalog includes 700+ pre-built connectors, so application data can sit beside the commercial sources that explain it. For the same source in a lakehouse, see DynamoDB to Databricks, and for files already in object storage, Amazon S3 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.