Counting Billions of Events Correctly: Why the Fix Was Processing, Not Storage
A usage-counting pipeline outgrew MySQL, and moving to DynamoDB would not have saved it. The real bottleneck was round trips. Here is the OLAP redesign.
On this page
When a count decides who gets paid, a wrong count is a bug in somebody’s income. Canva has described exactly this problem for its creators program. The more interesting lesson is how long the team treated a processing problem as a storage problem.
Why this count has to be right
Payments to creators depend on how often their content is used. Overcount and the company overpays. Undercount and creators lose trust. So three requirements pull on the design at once:
| Requirement | What it demands |
|---|---|
| Accuracy | Every usage counted once, no more, no less |
| Scalability | Volume was doubling about every 18 months, into billions of uses a month |
| Operability | When data is wrong, someone must be able to fix it quickly and safely |
Design one: everything in MySQL
The first design was deliberately plain. Raw events landed in MySQL. A deduplication worker scanned them in order, tracking a pointer to the last record it had seen. An aggregation worker then scanned the deduplicated table and incremented counters. Every stage persisted its output so it could be reused.
It was easy to reason about, and for roughly the first two years it was the right call. Single-threaded sequential processing makes incident recovery straightforward: you know exactly where the pointer is.
The costs piled up as volume grew.
Design two (abandoned): DynamoDB
Moving raw events to DynamoDB relieved storage immediately, and the team considered moving everything. They stopped before the full rewrite, because the evaluation showed the same thing: it would not remove the need for frequent round trips during processing.
Before you migrate databases, ask what actually scales with load. If your pipeline does a lookup per record, a faster or bigger store gives you cheaper lookups, not fewer. What looks like a storage problem is often a processing-shape problem wearing a storage costume.
Design three: ELT into a warehouse
The redesign changed the shape of the work.
- Extract and load
Raw usage data from web and mobile is replicated into the warehouse (Snowflake) by a reliable pipeline run by the data platform team. This is the foundation, so its reliability is the first thing to get right.
- Reshape the raw rows
Source events arrive as JSON, which warehouses query inefficiently. Selected properties are projected into proper columns.
- Deduplicate and aggregate with set-based SQL
Transformations are defined as SQL models with a tool such as dbt, each building on the previous. One groups daily usage by day and template brand, for example. The database processes whole sets in memory instead of one record per call.
- Keep intermediates as views
Intermediate stages are materialised as views rather than persisted tables. Nothing stale sits between the source and the answer.
- Unload results to the services that need them
Finished counts go out through object storage and a queue to the operational databases, with tuned rate limits so those databases are never throttled.
The key architectural property is the warehouse’s separation of storage and compute: compute can be scaled up for a run and down afterwards, independently of how much data is stored.
What changed in practice
| Before | After |
|---|---|
| Aggregation took over a day | Minutes for billions of records, under an hour end to end |
| Fix incidents by editing rows | Re-run the pipeline end to end |
| Persisted outputs at every stage | Views recomputed from source |
| Thousands of lines of dedup and aggregation code | Simpler SQL, over 50 percent less stored data |
| Frequent incidents | Rare, roughly once a few months or less |
The re-run property is the quiet winner. When a pipeline is a pure function of its inputs, a bad result is repaired by fixing the cause and running it again, with no hand-editing of state.
The new bill
Nothing is free. The team took on:
- Transformation deployment. SQL transformation jobs run as their own service with their own deploy and compatibility concerns.
- Unloading. Moving results back to operational databases needs a reliable path (object storage plus a queue) and careful rate limits.
- More infrastructure to observe. Replication, warehouse and transformation service all need monitoring in one coherent view.
How to apply it to your own counting problem
Do
Start simple, and keep honest numbers on cost per record. When per-event round trips dominate, move to batch, set-based processing. Give events a stable dedup key, make every stage re-runnable, and put reconciliation checks on the output.
Don't
Swap databases to fix a pipeline that is slow because of its access pattern. Also avoid persisting every intermediate result “just in case”: each one is more state to verify and repair.
Takeaway
Billing-grade counting is a correctness problem first and a scale problem second, and scale arrives through the shape of the computation, not the brand of the database. Replace record-at-a-time processing with set-based batches, derive everything from the raw events, and make “run it again” the standard repair. That is what turns a day-long, hand-fixed pipeline into a boring one.
Frequently asked questions
Why did moving to a scalable NoSQL store not fix the pipeline?
Storage was only half the problem. The pipeline made at least one database round trip per event to deduplicate and aggregate, so work grew linearly with event count regardless of where rows lived. A faster store shortens each trip but leaves the trips. Batch computation in memory removes them.
What is the difference between ETL and ELT?
ETL transforms data before loading it into the warehouse. ELT loads raw data first and transforms it inside the warehouse using its compute, usually in SQL. ELT keeps the raw data available, makes transformations re-runnable, and suits warehouses that separate storage from compute.
Why use SQL views instead of storing intermediate tables?
Intermediate tables are state that can go stale, drift from the source or need manual repair. Views recompute from the source each run, so re-running the pipeline end to end fixes mistakes. The price is recomputation cost, which a scalable warehouse absorbs.
How do you deduplicate events at scale?
Give every event a stable identifier or a defined dedup key, then group by that key in a set-based query and keep one row per key by a deterministic rule. Doing it as one batch query beats checking each event against a table one at a time.
/* Comments */
Comments are offline right now — we reconnect automatically, nothing is lost.