Amplitude to ClickHouse: How to Move Your Data

Moving Amplitude into ClickHouse is what teams do when the product analytics tool has become the bottleneck rather than the answer. Queries you cannot write, joins you cannot make, and a cost model tied to event volume rather than to how much analysis you actually do.

ClickHouse is a natural home for event data, so the destination side is easy. The friction is entirely on the way out of Amplitude, where the Export API caps each request and one setting decides whether your first sync completes at all. This guide covers the managed path with Airbyte.

Amplitude to ClickHouse at a glance:

Why move data from Amplitude to ClickHouse?

Two situations account for most of these pipelines.

The first is joining. Amplitude answers questions about product usage extremely well and cannot answer anything involving revenue, support cost or contract value, because it does not hold that data. Your own ClickHouse instance can hold all of it, and behavioural events queried next to commercial data is where the useful answers are.

The second is control over cost and query shape. Your own ClickHouse means your own retention, your own table engines, your own materialised views, and no limits other than the hardware you chose. If all you need is standard funnel and retention analysis, Amplitude already does that well and this pipeline earns you nothing.

What do you need before you start?

Short list, with two items that quietly decide whether the first sync succeeds:

An Amplitude API key and secret key. Both are found in your Amplitude project settings, and they are per project rather than per account, so a multi-project setup means a source per project. The Amplitude source documentation lists every field.

The correct data region. The setting defaults to the standard server, and if your Amplitude project sits in the EU data centre you must select the EU residency server instead. Getting this wrong does not produce a helpful message, it produces a connection that cannot find your data.

A deliberate start date. Leaving it blank replicates everything, which on a mature Amplitude project is an enormous first sync against an API with a hard size ceiling per request. Set it to the earliest date anyone will actually query.

A ClickHouse instance and credentials. With permission to create tables in the target database. The destination writes through ClickHouse's native protocol into typed columns rather than storing everything as JSON in a raw table.

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

Step 1: Estimate your daily event volume

Find out roughly how many events your project records per day, because that number determines your Request Time Range before you touch anything else. The Export API caps each export at 4 GB, and a high-traffic consumer product generates well past that in twenty-four hours. Knowing the figure turns the configuration into arithmetic rather than trial and error.

Step 2: Configure the Amplitude source

Click Sources in the left navigation, then New Source, and select Amplitude, following adding a source. Supply the API key, secret key, data region and start date, then set the Request Time Range, which controls how many hours of events each request covers and defaults to 24.

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 table engine, and for it to order duplicates correctly your cursor should be a timestamp or numeric type rather than a string, which on event data it naturally is.

Step 4: Create the connection and backfill carefully

Click Connections, then New connection, select your streams and an incremental sync mode where available. Run the backfill with a short Request Time Range even if steady state would tolerate the default, because the historical period is where the size ceiling bites and a failure six hours in costs you those six hours.

Once the backfill is complete you can raise the range for ongoing syncs. Two configuration values also reach backwards in time: the attribution window, five days by default, and the export lookback window in seconds. Both shift the effective start date, so set them deliberately rather than adjusting them later and triggering an unexpected re-sync.

Why does the Events stream fail or time out?

Because the Export API caps each export at 4 GB. Exceed it and the request errors outright. Get close to it and the request times out instead. Both failures have the same fix, which is reducing the Request Time Range so each request covers fewer hours.

For a low-volume product the 24 hour default is fine and you will never think about it. For a consumer app doing millions of events a day, a day of events is well past the ceiling and the first sync simply fails. Drop it to six hours, or one, and the same data moves in more, smaller requests.

The other streams come from the Dashboard REST API, which uses a cost-based budget with an hourly allowance, a burst allowance and a cap on concurrent requests. You do not manage that: the connector tracks what each request costs and throttles itself to stay inside the limits. Worth knowing so that a slow sync reads as expected behaviour rather than a fault.

How should you model event data in ClickHouse?

This is where the destination earns its place. Event data is exactly what ClickHouse is built for, so rather than treating the landed table as an endpoint, treat it as the raw layer and build materialised views for the aggregations your team actually queries: daily active users, funnel steps, retention cohorts.

Amplitude event properties are nested, and the connector preserves that structure. Extract the properties you query into typed columns in a view rather than making analysts navigate the raw structure every time, and keep the raw column so you can reprocess when the product team adds a property.

One behaviour to explain to your analysts before they report it as a bug. 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 return duplicates that the same query returns cleanly an hour later. Add the FINAL operator to guarantee deduplication at query time, and put it in the views where exact counts matter.

Frequently asked questions

Why does my Amplitude Events sync fail or time out?

The Export API caps each export at 4 GB and large requests can time out. Reduce the Request Time Range so each request covers fewer hours.

My Amplitude project is in the EU. What changes?

Set the data region to the EU residency server rather than leaving the standard server default, otherwise the connector queries the wrong data centre.

Why does my ClickHouse table contain duplicate events?

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

Do I need to manage Amplitude's rate limits myself?

No. The connector tracks the cost of each Dashboard API request and throttles automatically to stay inside the hourly and burst budgets.

Can I do this without writing code?

Yes. The setup above is entirely UI-driven. You will want ClickHouse views over the raw events, which is where your metric definitions and any use of FINAL belong.

Get your Amplitude data into ClickHouse

Estimate your event volume, set the Request Time Range against it rather than accepting the default, get the data region right, and run the backfill with a short range before raising it for steady state. Then build materialised views over the raw events and teach your analysts what FINAL does.

Airbyte's connector catalog includes 600+ pre-built connectors, so behavioural data can sit beside revenue and support sources in one engine. For the same source feeding a streaming platform, see Amplitude to Kafka, and for another product analytics source landing in ClickHouse, PostHog to ClickHouse.

Start syncing now →