A dashboard tile runs the same aggregate over a 2 TB fact table 900 times a day. An engineer proposes a materialised table refreshed hourly. The platform bills $5 per terabyte scanned - take that as given. Roughly does the materialisation pay for itself and which assumption dominates the error?
Show the full answer Hide the answer
The assumptions, stated
- The table holds 24 months of data, partitioned by date, and the tile filters the last 30 days - so roughly one twenty-fourth of the table, about 83 GB.
- It reads 4 of about 50 columns of similar width in a columnar format, so roughly 8% of those bytes: about 7 GB scanned per run.
- The materialised aggregate is small - a few hundred thousand rows, call it 40 MB.
- Refresh is incremental: each hourly run rebuilds only the current day's slice, so it scans roughly a thirtieth of the window, about 250 MB.
The arithmetic
On demand: 900 runs x 7 GB = 6.3 TB a day = about \(31 a day**, roughly **\)950 a month.
Materialised: refresh is 24 x 250 MB = 6 GB a day; the tile reads 900 x 40 MB = 36 GB a day. Total 42 GB a day = about \(0.21 a day**, roughly **\)6 a month.
The materialisation is about 150 times cheaper and pays for itself on the first day. Note what did the work: not the refresh being cheap, but the reads being 900 against a 40 MB object instead of 900 against 7 GB.
The decision rule this generalises to
Compare the refresh scan against the saved query scan:
materialise when (queries per interval) x (scan per query) > (scan per refresh)
Since a full rebuild scans roughly what one query scans, the rule collapses to something you can apply without arithmetic: if the aggregate is queried more often than it is refreshed, it pays. A tile queried 900 times a day against an hourly refresh is 37 queries per refresh - far inside the boundary. A tile viewed three times a day against an hourly refresh is on the wrong side of it, and the honest answer there is to refresh daily or not at all.
Which assumption dominates the error
How many distinct query shapes the one aggregate serves. Everything above assumes all 900 runs hit it. If each of six teams filters at a different grain, the aggregate fails to serve five of them and they fall through to the base table - so you pay the refresh and keep most of the scan. In production the hit rate, not the price list, is the variable to measure first.
Two secondary ones: a full rather than incremental refresh scans the whole 83 GB window every hour, 2 TB a day, which is still cheaper here but no longer dramatically so; and many platforms bill a minimum per query - at a 10 MB floor, 900 queries is 9 GB a day, which puts a floor under the saving but does not change the conclusion.
When this is the wrong answer
If the tile is queried rarely, if the aggregate's definition changes weekly, or if freshness must be sub-minute, materialisation adds a build to operate, a staleness window to explain and another object to invalidate. Below roughly one query per refresh interval, choose the on-demand scan - it is both cheaper and simpler - and prefer a result cache, which has no refresh to own, for the repeated-view case.