DynamoDB to ClickHouse: How to Move Your Data

Moving DynamoDB into ClickHouse turns a schemaless key-value store into something you can aggregate across, at a fraction of the operational overhead of a full warehouse. DynamoDB is superb at fetching an item by its key and answers almost nothing else, which is precisely why this pipeline exists.

The interesting work happens before any data moves. DynamoDB tables have no schema and ClickHouse tables have typed columns, so something has to invent one, and that something is Airbyte's discovery phase sampling your table.

DynamoDB to ClickHouse at a glance:

Why move data from DynamoDB to ClickHouse?

Two situations account for most of these pipelines.

The first is analysis DynamoDB cannot do. Anything shaped like how many, grouped by, or joined to needs separate compute, and ClickHouse handles those aggregations quickly without the operational weight of a warehouse you also have to staff.

The second is powering something fast. If ClickHouse already sits behind an internal tool or a customer-facing dashboard, putting application data next to the event data already there means one engine answering both in milliseconds.

What do you need before you start?

Two credentials decisions and two that determine whether the tables are trustworthy:

AWS credentials, or better, an IAM role. Supply an access key and secret, or omit both and the connector falls back to the default credentials provider, picking up the role attached to the workload running it. On self-managed Airbyte the second option avoids long-lived keys in configuration.

A cursor attribute that satisfies the rules. Incremental sync needs a top-level attribute, not one nested inside a map, of string or integer type, in ISO 8601 or epoch format, and never blank on any item. Single-table designs that bury timestamps inside nested attributes cannot use them without restructuring.

Knowledge of your sparse attributes. Discovery samples your table, so an attribute appearing only on items outside that sample will not reach ClickHouse. Ask your application developers what optional attributes exist rather than trusting the discovered schema.

A ClickHouse instance and credentials. With permission to create tables in the target database. The destination writes through the native protocol into typed columns, which is the reason the schema question matters here more than on a lakehouse.

If your ClickHouse instance restricts inbound traffic by IP, add the Airbyte Cloud IP addresses to the allow list before you begin.

How do you build a DynamoDB to ClickHouse pipeline in Airbyte?

Step 1: Establish what the items actually contain

Sit down with whoever owns the table and list the attributes, including optional ones and any that changed type over the years. This takes twenty minutes and it is the only way to check the discovered schema against reality, because a schemaless store cannot tell you what it should contain.

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 either your access keys or nothing at all if you are using a role. Then review the discovered schema against your list from step 1, and refresh it after any deploy that adds attributes.

Step 3: Configure the ClickHouse destination

Click Destinations, then New Destination, and select ClickHouse, following adding a destination. Supply host, port, database and credentials. Deduplication uses the ReplacingMergeTree engine, and for it to order duplicates correctly your cursor should be a timestamp or numeric type rather than a string, which is worth checking given the cursor rules above.

Step 4: Create the connection and plan for deletes

Click Connections, then New connection, select your tables and a sync mode. The source is cursor-based rather than reading DynamoDB Streams, so deletions never reach ClickHouse and updates are only seen when they advance the cursor attribute. Decide now whether that matters.

Where deletions genuinely occur and you cannot add a soft-delete flag, a periodic full refresh is the remedy. On DynamoDB that is a full scan consuming read capacity, so schedule it deliberately rather than nightly out of habit.

How does the schema get invented?

The connector scans your table with a limit of 1,000 items. If the returned data exceeds 1MB it scans again with the same limit, repeating until it has at least 1,000 items. It then combines every top-level attribute it saw and infers each attribute's type from its value.

Two consequences follow. An attribute appearing only on items outside that sample will not be in the catalogue, so on a table where a small fraction of items carry an optional attribute, the sample may miss it entirely. And the connector assumes an attribute has the same type everywhere, which DynamoDB does not enforce.

That second point bites harder on a columnar destination than on a lakehouse. ClickHouse wants a typed column, so a field that is a number on older items and a string on newer ones has nowhere comfortable to land, and you find out at load time rather than when somebody queries it. Check the mixed-type case specifically before your first sync.

Why do duplicates appear and then disappear?

ReplacingMergeTree collapses duplicate rows during background merges, which happen when ClickHouse decides they should rather than when your sync finishes. So a query run shortly after a sync can legitimately return duplicates that the same query returns cleanly an hour later.

This is expected behaviour rather than a fault, and it catches out anyone who has not been told. To guarantee deduplicated results at query time, add the FINAL operator. It costs performance, so the usual pattern is a view using FINAL for correctness-critical queries and direct table access for exploratory work.

Combine that with the missing deletes above and there are two unrelated reasons a count here can differ from what DynamoDB holds. Knowing which you are looking at saves a great deal of time, and both are worth explaining to whoever queries these tables.

Frequently asked questions

Does Airbyte use DynamoDB Streams for change data capture?

No. The source is cursor-based. Deletions are not captured and updates are only seen if they advance the cursor attribute.

Why is an attribute missing from my table?

Discovery did not see it in the sampled items. Refresh the source schema, and ask your developers what optional attributes exist rather than trusting what was inferred.

Why does my ClickHouse table contain duplicate rows?

ReplacingMergeTree removes duplicates during background merges rather than immediately. Query with FINAL if you need guaranteed deduplication before those merges run.

Can I use a nested field as the cursor?

No. Cursor fields must be top level, string or integer, in ISO 8601 or epoch format, and never blank. Single-table designs that nest timestamps need a restructure or a full refresh.

Can I do this without writing code?

Yes. The setup above is entirely UI-driven. You will want ClickHouse views using FINAL where exact counts matter, which is a short piece of SQL.

Get your DynamoDB data into ClickHouse

Check the discovered schema against what your developers say the items contain, paying particular attention to attributes whose type has changed, since a columnar destination is less forgiving of that than a lakehouse. Pick a cursor that satisfies the rules, decide what a deletion should mean, and put exact counts behind a view using FINAL.

Airbyte's connector catalog includes 600+ pre-built connectors, so application data can sit beside the events that explain it. For a destination that tolerates schema surprises better, see DynamoDB to Databricks, and for a relational alternative, DynamoDB to PostgreSQL.

Start syncing now →