Mixpanel to ClickHouse: How to Move Your Data

Moving Mixpanel into ClickHouse gives you event data in an engine built for exactly this shape of query, at a fraction of the operational overhead of a full warehouse. Mixpanel answers product questions well and knows nothing about revenue, support cost or contract value.

This guide covers the managed path with Airbyte. Two things shape the build and neither is about ClickHouse: Mixpanel's rate limits are low enough to be the defining constraint, and incremental syncs return duplicates by design.

Mixpanel to ClickHouse at a glance:

Why move data from Mixpanel to ClickHouse?

Two situations account for most of these pipelines.

The first is joining. Product usage next to revenue, support load and contract value is where the useful answers are, and ClickHouse handles that at a fraction of the operational overhead of a full warehouse while being faster at the aggregations this data invites.

The second is powering something fast. If you already run ClickHouse behind an internal tool or a customer-facing dashboard, putting event data next to what is already there means one engine serving both. If your questions are funnels and retention curves, Mixpanel answers those well already.

What do you need before you start?

Several small settings here have larger consequences than their labels suggest:

A Mixpanel service account and project identifier. Along with your project region, which is either US or EU. Getting the region wrong produces a connection that cannot find your data. The Mixpanel source documentation lists every field.

Your project timezone. Found in your Mixpanel project settings, and the connector defaults to US/Pacific. If your project is set to anything else and you leave the default, events land on the wrong day and your daily figures will not match the Mixpanel interface.

An attribution window you intend to keep. The default is five days. Treat this as a decision rather than something to adjust later, because changing it moves the start date backwards and triggers a re-sync from that earlier point, which on this source is expensive.

A ClickHouse instance and credentials. With permission to create tables in the target database. The destination writes through the 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 a Mixpanel to ClickHouse pipeline in Airbyte?

Step 1: Settle timezone and attribution window

Look up your project timezone in Mixpanel and agree an attribution window with whoever analyses this data. Both are cheap to set correctly now and expensive to change later, the timezone because it silently shifts every daily figure and the attribution window because altering it forces a re-sync across a low rate limit.

Step 2: Configure the Mixpanel source

Click Sources in the left navigation, then New Source, and select Mixpanel, following adding a source. Supply the service account secret, project identifier, region, timezone, attribution window and start date. Leave select properties by default enabled so new properties on events and profiles are captured automatically.

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: Use a deduplicating sync mode

Click Connections, then New connection, select your streams and choose a deduplicating incremental sync mode. That is not a preference on this connector, because incremental syncs return duplicated records for the state date, and deduplication is what stops those accumulating.

Choose your primary key deliberately too. Mixpanel recommends a combination of insert identifier, event time, event name and distinct identifier, while noting that some rows may lack an insert identifier for certain users.

Why do duplicates arrive, and then appear again?

There are two separate mechanisms here and it is worth keeping them apart. On the source side, Mixpanel's API filter is granular to whole days only, so the connector can only ask for data from the state date onwards rather than the exact moment it stopped. Everything already collected for that day comes back.

On the destination side, ClickHouse's ReplacingMergeTree collapses duplicate rows during background merges rather than on write. So a query run shortly after a sync can legitimately return duplicates that the same query returns cleanly an hour later, which is expected behaviour rather than a fault.

Together they mean you should use a deduplicating sync mode and add the FINAL operator where exact counts matter. The usual pattern is a view using FINAL for correctness-critical queries and direct table access for exploratory work, and telling your analysts which is which before somebody reports duplicate events as a bug.

Why are the rate limits the defining constraint?

Because sixty queries per hour is genuinely low, and it applies to both of Mixpanel's API families. The Query API, behind cohorts, engage, funnels and annotations, allows five concurrent queries and sixty per hour. The Raw Data Export API behind the export stream allows more concurrency and is still capped at sixty per hour.

So a backfill across a long period takes as long as that ceiling allows, whatever else you tune. The date slicing window, which defaults to thirty days per request, is the lever that matters most: a smaller window means more requests against a fixed hourly budget, a larger one means fewer requests each doing more work, at the cost of memory.

Be conservative with your start date, spread heavy streams across separate connections on different schedules, and treat a slow first sync as arithmetic rather than a fault. The one thing not to do is change the attribution window casually, since that quietly moves the start date back and makes you pay the backfill again.

Frequently asked questions

Why are there duplicate events in my tables?

Two reasons, both expected. Mixpanel re-returns records for the state date because its filter is day-granular, and ReplacingMergeTree only collapses duplicates during background merges. Use a deduplicating sync mode and query with FINAL where counts matter.

Why is my sync so slow?

Mixpanel allows sixty queries per hour on both its API families. Large date windows simply take time, so narrow the start date and avoid syncing all streams simultaneously.

Why do my daily numbers not match Mixpanel?

Most likely the project timezone. The connector defaults to US/Pacific, so set it to whatever your Mixpanel project uses or events land on the wrong day.

Can I change the attribution window later?

You can, and it moves the start date backwards and re-syncs from that point. Given the rate limits, treat it as a decision to make once rather than a dial to adjust.

Can I do this without writing code?

Yes. The setup above is entirely UI-driven. You will want ClickHouse views using FINAL for correctness-critical queries, which is a short piece of SQL.

Get your Mixpanel data into ClickHouse

Set the project timezone and attribution window correctly the first time, use a deduplicating sync mode because duplicates are expected rather than exceptional, be conservative with the start date given sixty queries an hour, and teach your analysts what FINAL does before they report duplicates as a bug.

Airbyte's connector catalog includes 600+ pre-built connectors, so product events can sit beside the revenue and support data that explain them. For the same source in a warehouse, see Mixpanel to BigQuery, and for another product analytics source landing in ClickHouse, PostHog to ClickHouse.

Start syncing now →