ClickHouse to Convex: How to Move Your Data

Moving ClickHouse into Convex puts analytical results where an application can read them. ClickHouse will aggregate hundreds of millions of rows in a second, which is not the same as being a good backend for a web app, and Convex is built for exactly the reactive reads that an analytical database handles awkwardly.

This guide covers the managed path with Airbyte. Two things shape the build: Convex is a beta destination with rules about what a table may be called, and what lands there is the current state of your data rather than its history.

ClickHouse to Convex at a glance:

Why move data from ClickHouse to Convex?

Two situations account for most of these pipelines.

The first is showing computed figures inside a product. Usage totals on a customer dashboard, a leaderboard, a score that took a heavy aggregation to produce: the calculation belongs in ClickHouse and the serving belongs somewhere reactive. Running that aggregation on every page load is the alternative, and it is a bad one.

The second is giving an application team data without giving them a database to operate. The pairing is wrong if the application needs to query that data flexibly or keep a history of it, because what arrives is a snapshot shaped by whatever you sent. Where the app wants a general-purpose store it can query properly, ClickHouse to PostgreSQL is a more conventional and more forgiving answer.

What do you need before you start?

Four things, and the first is a design decision rather than a credential:

A clear idea of what the application needs. Not the table, the answer. Convex should receive the aggregated result your interface displays, not the raw rows behind it, and working out which is which before you build saves discovering it through poor performance.

A ClickHouse user and a view that produces the result. Read access is enough, and a view holding the aggregation keeps the pipeline simple and the logic in one place.

A Convex deployment and its credentials. Check your intended table names against the rules first: letters, digits and underscores only, and no leading underscore. The Convex destination documentation sets out the configuration.

Tolerance for a beta destination. Convex is marked beta here, which means verifying behaviour rather than assuming it and being prepared for details to change. Fine for an internal dashboard, worth a conversation before it underpins something customers pay for.

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 ClickHouse to Convex pipeline in Airbyte?

Step 1: Aggregate in ClickHouse so Convex receives an answer

Write the view that produces exactly what your interface shows, at the grain it shows it. One row per customer per day beats a million raw events, and the difference is not merely tidiness: large payloads are where this destination struggles. ClickHouse is extremely good at this reduction, so use it, and treat everything downstream as a delivery problem rather than a computation one.

Step 2: Configure the ClickHouse source

Click Sources in the left navigation, then New Source, and select ClickHouse, following adding a source. Supply the host, port, database and credentials, then select your view rather than the underlying tables. Choose a cursor that moves reliably, which on an aggregated view usually means the date or period the row describes.

Step 3: Configure the Convex destination

Click Destinations, then New Destination, and select Convex, following adding a destination. Supply the deployment URL and access key. Name the tables in plain letters, digits and underscores, and do not start one with an underscore, since the rule is enforced rather than advisory and a rejected name stops the sync.

Step 4: Create the connection and avoid large full refreshes

Click Connections, then New connection, select your stream and a sync mode. Incremental with deduplication suits this pipeline, both because it is lighter and because current state is what an application wants. Full refresh works on small tables and degrades noticeably as they grow, so if yours is heading that way, reduce what you send rather than accepting slower syncs.

Set the schedule to match how fresh the figures need to look. A dashboard showing yesterday's totals does not need a five-minute sync, and each run costs you at both ends.

What should you actually send to Convex?

The smallest thing that answers the question. The instinct on any pipeline is to move the table and decide later, and here that instinct is expensive, because full refresh performance degrades as the volume grows and there is no clever configuration that rescues an oversized payload.

This is a happy constraint given what sits on the other end. ClickHouse exists to turn enormous row counts into small result sets quickly, so pushing the aggregation upstream plays to its strengths and keeps the pipeline light. A view producing a few thousand rows per sync will behave well indefinitely; one producing a few million will not.

It also settles where your logic lives. If the aggregation is in a ClickHouse view, one team owns the definition and changing it is a single statement. If it is in application code reading raw rows from Convex, the definition is scattered and the payload is large, which is the worst of both. Decide this at the start, because moving it later means rewriting both ends.

Why does Convex hold a snapshot rather than a history?

Because incremental deduplication maintains current state by design. Each record is updated in place rather than appended beside its predecessor, so what you have at any moment is how things are, with no trace of how they were. That is the right behaviour for serving an application and the wrong assumption for anything analytical.

The import-only nature of the destination compounds this. Convex cannot be used as a source here, so data that exists only in Convex cannot be pulled back out through the same route. Between the two facts, the practical rule is that Convex holds a view of your data rather than a copy of it, and ClickHouse remains the system of record.

So keep history where history belongs. If the application needs to show a trend, send the trend as rows from ClickHouse rather than expecting Convex to accumulate one. And make sure whoever builds against these tables understands the arrangement, since a developer who assumes yesterday's values are still there will write something that quietly stops working the first time a record changes.

Frequently asked questions

Why was my table name rejected?

Convex allows letters, digits and underscores only, and will not accept a leading underscore. Rename before the first sync rather than after something downstream refers to the old name.

Can I sync data back out of Convex?

Not through Airbyte, since Convex is available as a destination only. Treat it as a read-side copy and keep the authoritative version in ClickHouse.

Why are my syncs getting slower?

Large full refreshes degrade on this destination. Aggregate further upstream so each sync carries less, or move to incremental deduplication if you have not already.

Will Convex keep the previous values?

No. Deduplicated incremental syncs maintain current state, so records are updated rather than accumulated. Send history explicitly as rows if the application needs it.

Can I do this without writing code?

The pipeline, yes. The ClickHouse view that decides what gets sent is SQL, and it is the part that determines whether this arrangement works well or badly.

Get your ClickHouse data into Convex

Aggregate in ClickHouse and send the answer rather than the data, because large payloads are exactly what this destination handles least well. Check table names against the rules before the first sync. Remember that Convex is a beta destination holding current state, one way, with ClickHouse remaining the system of record, and make sure the application developers reading these tables know that too.

Airbyte's connector catalog includes 600+ pre-built connectors, so analytical results can reach the products that display them. For the same source into a warehouse, see ClickHouse to Snowflake, and for another database into the same destination, PostgreSQL to Convex.

Start syncing now →