Which conditions let a materialized aggregate answer a warehouse query written against the fact table?

## What rewrite is Automatic query rewrite means the user writes a query against the fact table and the optimizer substitutes a pre-computed aggregate, transparently, because it can prove the substitution returns identical rows. It is attractive because consumers — BI tools, notebooks, ad-hoc analysts — need no knowledge of which aggregates exist, and you can add or drop aggregates without editing any query. The price is that eligibility is invisible: when the proof fails, nothing errors, the query just costs what it would have cost without the aggregate. ## The conditions the optimizer must satisfy **Grain subsumption.** The aggregate's grouping keys must be at least as coarse as the query's requirement, and the query's grain must be derivable from the aggregate's by further grouping. Day to month works because a day maps to exactly one month; month to day does not, because the aggregate has destroyed the information. Functional dependencies matter here: an engine can only exploit day-to-month if it knows the derivation, which usually means the query applies a recognised truncation to a column the aggregate stores. **Column availability for grouping and filtering.** Every column the query groups by must be present. Every column the query filters on must also be present, because the rows to keep have to be selectable *after* aggregation. This is the condition people forget: adding `WHERE device_type = 'mobile'` to a dashboard query silently disqualifies an aggregate that does not carry `device_type`, and the only symptom is that the bill goes up. **Predicate and join containment.** If the aggregate itself was defined with a filter — say only completed orders, or only the last 90 days — it can serve a query only when the query's predicate is at least as restrictive within that region. An aggregate over 90 days cannot answer a two-year question. Likewise, the joins the aggregate materialized must match or subsume the joins in the query; if the query joins a dimension the aggregate never touched, the engine can only rewrite when that join is provably lossless and the needed key is present. **Measure re-aggregatability.** The query's aggregate functions must be derivable by combining the stored measures — sums from sums, counts from counts, averages from a stored sum and count. A stored average or a stored distinct count blocks rewrite at any coarser grain. **Freshness.** The engine must decide the aggregate is current enough. Systems differ in policy: some refuse rewrite once the aggregate is behind its base tables at all, some accept a declared staleness tolerance, some transparently combine the aggregate with unmerged recent data. The important operational fact is that a large load can flip a query from cheap to expensive without anyone changing the query. ## Verifying rather than hoping Because every failure mode is silent, treat rewrite as something you measure: - Read the query plan and confirm the scanned relation is the aggregate, not the fact table. - Compare bytes scanned or compute time against the same query with the aggregate dropped or disabled. - Track hit counts per aggregate over time from query history; a rewrite that was working can stop working after a schema change, a new filter in a dashboard, or a refresh falling behind. ## Transparent rewrite versus pointing consumers at the rollup The alternative is to skip rewrite entirely and have the BI layer query the aggregate table by name. Compare honestly: - **Rewrite** keeps one logical model, survives adding and removing aggregates, and helps ad-hoc queries you never anticipated. But eligibility is fragile and invisible, and cost becomes hard to predict. - **Explicit rollup tables** give predictable, auditable cost and no surprises, at the price of consumers hard-coding a physical choice, and of every new question needing someone to know which table to hit. A common production compromise: define aggregates for rewrite where the engine supports it, but expose the important ones through named views so critical dashboards have a guaranteed path, and monitor hit rates so silent regressions surface as a cost alert rather than a quarterly invoice surprise.