Forty materialized aggregates raised the warehouse bill instead of cutting it — how do you decide which to keep?

## Why forty aggregates cost more than none Every materialized aggregate has a fixed recurring cost — refresh compute multiplied by refresh frequency, plus storage — and a variable return that exists only when queries actually hit it. Teams add aggregates one at a time in response to individual complaints, each defensible in isolation, and nobody ever measures the portfolio. The failure is structural: cost accrues automatically while return depends on continued use, so an unmanaged set of aggregates drifts toward net loss. ## Build the ledger first Before deleting anything, produce one row per aggregate with: - **Refresh cost per run** and **runs per day** — the recurring spend. - **Storage footprint** — usually the small term, but not on wide or fine-grained aggregates. - **Hits**: how many queries actually read it, from query history grouped by the relation scanned. Where the engine rewrites transparently, this is the number to look for in the plan or history, not the number of queries someone believes should be hitting it. - **Saving per hit**: bytes or compute the hit avoided, estimated by comparing against the equivalent fact-table scan. Return is hits times saving per hit. Anything where recurring cost exceeds return is a candidate, and the ledger converts an argument about opinions into a list sorted by money. ## The four categories you will find **Zero-hit aggregates.** Built for a dashboard that was retired, or superseded by another aggregate, or never eligible for rewrite in the first place because a filter column was missing. Delete them. In a portfolio of forty there are almost always a handful, and they are the cheapest win available. **Over-refreshed aggregates.** Refresh cadence set by habit rather than by the consumer's requirement. An aggregate refreshed every fifteen minutes to serve a report opened weekly is paying ninety-six times a day for one read. The fix is not deletion but matching cadence to the stated staleness SLA — and the act of asking each consumer what staleness they actually tolerate usually recovers more money than any deletion, because the honest answer is very often much looser than the current setting. **Near-duplicates.** Five aggregates over the same fact table at overlapping grains, each refreshing independently and each re-scanning the same source. Replace them with one at the coarsest grain from which all five queries can be re-aggregated, provided the measures are additive. One refresh instead of five, one thing to keep correct. Where grains genuinely conflict, consider a cascade — refresh a fine aggregate from the fact table, then derive coarser ones from it — which trades a dependency chain and some latency for a much smaller total scan. **Genuinely load-bearing aggregates.** High hit counts, large per-hit savings, cadence matched to need. Leave them alone and protect them: they are the reason the pattern exists. ## Question whether an aggregate was the right lever Some entries on the list should never have been aggregates. Before rebuilding them, check: - Would **partitioning or clustering the fact table** on the dashboard's filter column prune to a similar cost with nothing to maintain? - Is the query repeated identically often enough that a **result cache** answers it for free, making the aggregate redundant? - Is the workload's real problem **concurrency or isolation** rather than scan volume, in which case separating compute would fix it and an aggregate will not? An aggregate is a standing maintenance obligation. It should win against these alternatives, not be the reflex. ## Governance so it does not recur The cleanup is worthless if the portfolio regrows the same way. The durable fixes are organisational: - **Every aggregate has an owner and a declared consumer.** No owner, no aggregate. - **Every aggregate has a stated staleness SLA**, and cadence is derived from it rather than chosen. - **Expiry by default**: a quarterly review deletes anything with no hits since the last review, and the cost report goes to the owning team, not to a central platform budget where nobody feels it. - **A shared catalogue** so the next person finds the existing coarse aggregate instead of adding the forty-first. ## What to say in the interview Lead with measurement, not intuition: hits and refresh cost per aggregate. Then the sequence — delete the dead, re-cadence the over-refreshed, consolidate the overlapping, protect the load-bearing — and close with governance, because the portfolio grew to forty for reasons that will still be there next quarter.