The Company Just 10x'd Its Data Volume Overnight — What Breaks First?

1. What breaks first, and why

Work through the pipeline in order rather than guessing:

• Ingestion: CDC on orders/order_items scales fine — logical replication throughput is generally far from saturated at 610K→6.1M change events/day; this isn't the bottleneck. Clickstream ingestion (55M→550M events/day, ~5.5GB→~55GB/day compressed) is also usually absorbable by object storage and a Kafka topic with more partitions, so raw landing survives, just at higher steady cost.
• The real first casualty is the intermediate sessionization step, which was already the single heaviest stage in the critical path at ~45–60 minutes for 55M events/day. Windowed operations over event data don't always scale linearly with a fixed-size compute allocation — at 550M events/day the same job either runs proportionally longer (7.5–10 hours, which alone blows the entire 7:00am SLA many times over) or fails outright on memory/shuffle limits if the warehouse's compute pool wasn't provisioned for the new scale. This is the stage to name specifically, not "the pipeline gets slower" in general — the case study's own numbers show this was already the longest single stage before any scale-up.
• Downstream of that, the marts build and BI cache warm are smaller in absolute time but also scale with fact-table size, compounding the delay rather than absorbing it.

2. Redesign for the bottleneck

Sessionization at this volume is a strong candidate to move off in-warehouse SQL windowing and onto a dedicated Spark job (the case study flags this as a lever held in reserve, not a default choice — 10x volume is exactly the trigger that promotes it from reserve to required). A Spark job gives control over partitioning, shuffle behavior and cluster sizing that a shared warehouse compute pool doesn't, and can be scaled horizontally by adding executors rather than hoping a single SQL engine's window-function implementation keeps pace. Alongside that: increase Kafka topic partition count for the clickstream topic so consumer parallelism scales with volume, and re-evaluate whether hourly clickstream landing needs to become more frequent (e.g., every 15 minutes) to keep individual landing batches a manageable size rather than one hourly batch 10x larger than before.

3. What still needs addressing even with linear scaling

Two things don't get fixed just by making every stage 10x faster:

• SLA slack disappears. The original design deliberately left 3–4 hours of slack in a 6-hour window. If every stage takes roughly 10x longer even after re-architecture (say the critical path goes from ~2 hours to ~3.5 hours after the Spark migration), the slack shrinks from 3–4 hours to under an hour — technically still inside the SLA, but far more fragile to any transient delay. The SLA and its slack need to be re-derived from the new critical path, not assumed to still hold because the deadline number (7:00am) didn't change.
• Cost scales with volume even after partition pruning and materialization are both already applied — those levers reduce the multiplier on top of volume, not the base volume itself. A 10x increase in daily landed bytes means a roughly 10x increase in the cost of scanning "the last 7 days" even with perfect partition pruning, because 7 days of data is itself 10x bigger. This is the point to explicitly separate: pruning and materialization are multiplicative savings on top of volume, and don't cancel out a 10x change in the volume itself — the cost conversation with finance needs to reflect that the baseline moved, not just that the levers are still in place.