Unlocking our data processing with ClickHouse

Where we started

Each platform we tracked had its own pipeline. Collected data landed in a document store, a monolithic script picked up each creator one at a time, ran every algorithm we had against it, and wrote the finished profile as an update into a search engine. The search engine was also, in practice, our data warehouse. Everything read from it. Everything wrote to it.

Then the numbers grew. Over a billion profiles. Millions of updates a day. The search engine spent more and more of its time absorbing writes instead of serving reads, and we started seeing the symptoms you would expect: CPU spikes, slow queries during ingestion windows, and data that could take days to reflect what was actually happening on a creator’s account.

Meanwhile, every new metric meant touching the monolith. Want to compute a new signal about creators? Open the script that already does forty other things, add your logic, test the whole thing, redeploy the whole thing, and hope nothing else broke.

The two bottlenecks were the same bottleneck

We spent a while treating these as two separate problems. The database was slow, so we tuned the database. The team was slow, so we tried to restructure the code.

Neither fixed much, because they were the same problem wearing two outfits.

• Upserts made the database slow.Every update to a profile had to find the existing record, lock it, rewrite it, and reindex it. Every update competed with every other update for the same rows. At millions a day, the database was doing an enormous amount of work just to resolve that contention.
• The monolith made the team fragile.Every algorithm lived in one codebase and ran in one process per platform. A change to one metric meant redeploying everything. A bug in one algorithm could stall the entire pipeline for that platform, and a bad deploy could take the whole thing down. Nobody wanted to be the person who touched the profile pipeline on a Friday.

Both problems came from the same root: a creator profile is a single record you update in place, by a single program. As long as that assumption held, the database had to serialize writes and the code had to be a monolith. You cannot have ten independent programs updating the same record without either coordinating them or corrupting the data.

So we questioned the assumption.

Why ClickHouse, and the one engine that made it work

ClickHouse is built for appending, not updating. You insert rows. You do not modify them. In the background, its merge-tree engines take the parts you have written and fold them together according to rules you choose. Updates as a concept mostly do not exist, and that turns out to be exactly what we needed.

The engine that made our design possible is CoalescingMergeTree. Its rule is simple: for each key, keep the newest non-null value of every column. That sounds like a small detail. It changes everything about how you can write data.

So one job inserts a row for a creator that contains only a detected language. Another inserts a row that contains only an engagement score. The collection pipeline inserts a row with the latest follower count and bio. None of these writers know about each other. None of them read the profile first. They each append what they own and move on.

ClickHouse merges those fragments into a single, current profile. No one writes an update. No one writes a join. The engine assembles the newest state of every column from whichever writer last provided it.

This changed two things at once:

• Nothing blocks anymore.In the old model every update to a profile competed with every other update for the same record, and the database spent its CPU resolving that contention. Now every writer just appends its own columns. Writes are cheap, they do not wait for each other, and adding another writer does not slow down the existing ones.
• The latest state of a profile is always there.The merge happens in the background, off the write path, so we get a current profile for every creator without the cost of rewriting the whole record every time one field changes. You ask for the profile, you get the newest value of every column, assembled from every source that contributed to it.

Each writer contributes what it owns. The engine assembles the profile. That one idea let us dismantle both the upsert model and the monolith at the same time.

The new shape

With that engine in place, the architecture almost designed itself.

Data flows through layers. Raw data lands first, exactly as collected, so we never lose anything and can always replay. From there it is cleaned into strictly typed tables: every field has a declared type, every record passes a contract before it is accepted. And from the cleaned tables it flows into a single unified profile table, one row per creator per platform, built on CoalescingMergeTree.

Every algorithm we used to run inside the monolith became its own datapoint. A datapoint reads what it needs from the unified profile table, computes one thing, and writes its result to its own small table. That table belongs to that datapoint alone.

ClickHouse materialized views do the rest. A materialized view in ClickHouse is not a cached query you refresh. It is an insert trigger: the moment rows land in a datapoint’s table, the view picks them up and inserts them into the unified profile table as partial rows. The datapoint never writes to the profile table directly and never knows the profile table exists. It writes its result, and the plumbing routes it home.

No joins. No locks. No coordination between jobs. Dozens of writers feeding one table, and the engine sorts it out.

Isolation is the unlock

The database change was what made the team change possible.

Each datapoint is now its own code, its own deployment, its own compute, and its own output table. It is a small, self-contained job. It reads the columns it needs, does one thing, writes one result, and knows nothing about any other datapoint. It does not import their code. It does not wait for their runs. It does not share their failures.

ClickHouse handles the assembly, so the datapoints do not have to.

This changes what failure means. In the monolith, a bug in one algorithm stalled the whole pipeline. Now a failed datapoint means one stale column for one cycle. Every other column keeps updating. The profile stays current everywhere except the one place that broke, and when the datapoint is fixed, its next run fills the gap.

It also changes what adding something means. A new metric is no longer a change to a monolith that forty other metrics depend on. It is one new job, written by one engineer, with its own table and its own materialized view. It ships when it is ready and touches nothing else.

The scariest part of adding a metric used to be everything around the metric. Now there is nothing around it.

The datapoints do share one thing: a common framework that handles reading from the profile table in parallel shards, batching results, and writing them out. An engineer writing a new datapoint writes the transformation and a short config. The framework handles the rest. That is the part that turned “add a metric” from a project into a task.

What this made possible

The old ceiling was write contention. Every update competed with every other update, so refreshing a profile more often meant slowing everything else down. Collecting more data made the platform worse, not better. That ceiling is gone.

Every metric runs across the full billion-plus profiles independently. Adding another one costs nothing to the ones already running, because there is nothing for them to contend over. When a datapoint needs to go faster, it gets more shards and more workers; the architecture does not change.

None of this came from a faster machine or a clever optimization. It came from dropping one assumption. Once a profile stopped being a single record owned by a single program, and became the merged result of many independent writers, everything else followed.

What this means for customers

Most of this post is about how the pipeline works. Here is what changed on the other side of the API:

• Fresher data.Each column updates on its own schedule instead of waiting for a full profile rewrite, so follower counts, public bios and computed scores reflect what is current, not what was current when the last batch finished.
• More datapoints.Adding a metric no longer slows down the ones already running, so the number of insights on a profile keeps growing without a trade-off elsewhere.
• Custom datapoints, fast.If you need something added to creator records that we do not compute today, it is one isolated job for one engineer. Requests that used to mean weeks of touching the monolith now ship in days.

More from engineering

All articles →

Data infrastructure

Rebuilding our pipeline for 13x throughput on Kafka and Prefect

Why one-at-a-time processing fell over, what replaced it, and how a 100k-creator batch went from 13 hours to one.

Nikola Krstev · 6 min read

Data infrastructure

Identity resolution: how we link a creator’s accounts across platforms

A weighted link graph, profile similarity and human review, combined to match accounts across 9 platforms at 98% precision.

Veljan Gjorgjiov · 10 min read