A 47-second dashboard, answered from a few thousand rows

transcript

Marketing opens one dashboard. It takes 47 seconds. And the bill is 50,000 dollars a month.

Revenue by category, region and day. 90 days. 2 point 7 billion rows. 50 gigabytes shuffled across the cluster. 47 seconds, and a timeout. The pipeline works. The query layer doesn't.

No pre-aggregation. Every load adds up billions of rows from scratch. So add them up once. Ahead of time.

The raw fact table: about 11 billion rows. A daily rollup, over 1,000 category and region combinations: 365,000 rows. A monthly rollup: 12,000 rows.

Now route each query to the coarsest table that still has its detail. Last 90 days, by day? The daily rollup. Two years, by month? The monthly one. Specific transactions? Only then, the raw facts.

Daily revenue by category, opened 100 times a day? 50 milliseconds, not 45 seconds. And it's still exact. Rollups are sums, not samples.

But here's the catch. Don't build one rollup grouped by everything. Group by nearly every dimension, and almost nothing collapses. It's as big as the fact table.

Predictable, and queried over 10 times a day? Pre-aggregate it. Then answer from the smallest table that still knows enough.

Full breakdown in the lecture.