Mixpanel to ClickHouse: How to Move Your Data
Move Mixpanel events into ClickHouse with Airbyte. Why incremental syncs return duplicates, how ReplacingMergeTree handles them, and when to use FINAL.
Moving Mixpanel to ClickHouse puts product event data somewhere it can be queried at speed and joined against everything else you own. It is a good pairing. It is also a pipeline where both ends have opinions about duplicate records, and neither of them behaves the way you would assume.
Mixpanel's incremental sync deliberately returns records you already have. ClickHouse deduplicates in the background rather than on write. Put those two facts together and you get a table that contains duplicates for a while and then does not, which is fine once you know, and deeply confusing when you do not.
Why Mixpanel sends you the same records twice
The Mixpanel export API filters by date, and only by date. There is no way to ask it for everything after 14:32 yesterday. So when an incremental sync resumes, it re-requests the entire day the last sync stopped in, and hands you every event from that day again.
That makes deduplication load-bearing rather than optional, which makes the primary key load-bearing too. Mixpanel recommends a composite of insert_id, event time, event name and distinct_id. Check that against your own data before accepting it, because some records lack an insert_id, and a primary key that is null on a subset of rows will not deduplicate those rows.
How ClickHouse handles it, and the word FINAL
The ClickHouse destination uses the ReplacingMergeTree table engine for deduplication, writing directly into typed columns rather than storing everything as JSON in a raw table. ReplacingMergeTree collapses duplicate rows during background merges, which happen when ClickHouse decides they should, not when your sync finishes.
So a query run shortly after a sync can legitimately return duplicates that a query run an hour later will not. To guarantee deduplicated results at query time, add the FINAL operator. It costs performance, so the usual pattern is a view with FINAL for correctness-critical queries and direct table access for exploratory work where a handful of duplicate events does not change the answer.
One more detail decides whether dedupe picks the right row. ClickHouse orders duplicates by the cursor column, and for that it wants a timestamp or numeric type. Give it a string cursor and it falls back to Airbyte's own extraction timestamp, which does not necessarily reflect the natural order of your source data. The sync logs will warn you, and it is worth reading them once rather than discovering the ordering was wrong months later.
Surviving 60 requests an hour
Mixpanel's rate limit is low, and the connector exposes several settings that exist specifically to work within it. The date slicing window defaults to 30 days per request and should be reduced if a large volume per window causes memory problems. The Engage stream page size defaults to 1,000 records per request, and raising it is the single biggest performance lever in this connector: at the default, a million profiles is measured in hours.
Two configuration values quietly reach backwards in time. The attribution window, five days by default, moves the effective start date back by that much. The export lookback window does the same in seconds for the export stream, to catch events recorded late. Both are worth setting deliberately, because changing the attribution window later triggers a re-sync from the shifted start date, and at 60 requests an hour that is not a small event. Set the project timezone and region correctly at the same time, since a wrong timezone silently misassigns events to days.
Frequently asked questions
Why does my ClickHouse table contain duplicate Mixpanel events?
Two reasons combine. Mixpanel re-sends the whole state date on each incremental sync, and ReplacingMergeTree removes duplicates during background merges rather than immediately. Query with FINAL if you need guaranteed deduplication now.
What primary key should I use for the export stream?
Mixpanel suggests insert_id together with event time, event name and distinct_id. Verify that insert_id is populated across your data first, because rows missing it will not deduplicate.
My Mixpanel sync is extremely slow. What do I change?
Raise the Engage stream page size above its 1,000 default, and stagger streams across the day rather than syncing them all at once. The underlying constraint is Mixpanel's 60 requests per hour.
Why did changing the attribution window trigger a huge sync?
The attribution window shifts the effective start date backwards, so widening it re-syncs everything from the new earlier date. Set it once, deliberately, at connection setup.
Get your Mixpanel data into ClickHouse
This is a fast destination fed by a slow source, and most of the work is accepting that. Choose a primary key that is actually populated, use a timestamp cursor so dedupe orders correctly, raise the Engage page size, and teach your analysts what FINAL does before they file a bug about duplicate events.
For ClickHouse on the other side of the pipeline, see ClickHouse to Databricks. For another rate-limited analytics source with a similar backfill problem, see AppsFlyer to BigQuery.
Integrate with 600+ apps using Airbyte
Move data from 600+ sources into warehouses, lakes, and beyond. Set up pipelines in minutes with pre-built connectors and the Connector Builder.