skip to content

What must an aggregate fact table guarantee before a BI layer can silently substitute it?

level: seniorimportance: should knowfreq 35%

answer

  1. who decides which table answers the query?
  2. same metric name, same meaning?
  3. what if only one table has loaded today?
  4. dropped attributes decide eligibility
  5. conformed, derived, in sync, with fallback

basics

~20 s

The aggregate must expose identical measure definitions, join to shrunken conformed dimensions whose attribute values match the atomic star, and stay in sync with it. Queries needing a dropped attribute or a finer grain must fall back to the atomic table.

solid answer

~60 s

**Aggregate navigation** is the principle that users write one query against the business model and something above them chooses the cheapest table that can answer it. For that substitution to be safe rather than merely fast, the modeller owes four guarantees. 1. **Identical measure semantics** — `revenue_amount` must mean the same thing in both tables, same filters, same currency treatment, same handling of cancellations. If the aggregate quietly excludes returns, substitution changes the answer. 2. **Conformed shrunken dimensions** — the aggregate's month and category dimensions are rollups of the base ones, with the same attribute values and labels, so grouping and filtering behave identically. 3. **A declared, enforced grain** — so the router knows exactly which requests the table can serve, and duplicates cannot double-count. 4. **Synchronization** — the two tables reflect the same data as of the same load, otherwise the same dashboard gives two answers depending on routing. Anything the aggregate dropped — day grain, order number, customer — means those queries must transparently fall back to the atomic star.

code

sql · 17 lines
sql
-- the substitution test: same question, both tables, by period
WITH from_atomic AS (
  SELECT d.month_key, SUM(f.revenue_amount) AS revenue
  FROM   fact_sales f
  JOIN   dim_date d ON d.date_key = f.date_key
  GROUP  BY d.month_key
),
from_agg AS (
  SELECT month_key, SUM(revenue_amount) AS revenue
  FROM   agg_sales_month_product_store
  GROUP  BY month_key
)
SELECT a.month_key, a.revenue, g.revenue
FROM   from_atomic a
FULL   JOIN from_agg g ON g.month_key = a.month_key
WHERE  a.revenue IS DISTINCT FROM g.revenue;
-- any row returned means substitution is unsafe

go deeper

for a junior

Understand the goal: users should not have to know a summary table exists. Something above them picks the right table, and the answer must be the same either way.

for a middle

List the guarantees that make substitution safe — same measure definitions, conformed shrunken dimensions, an enforced grain, matching load state — and explain what the aggregate cannot answer.

for a senior

Demonstrate you would test the substitution with a standing comparison by period, and diagnose a dashboard whose total changes between refreshes as a routing-plus-staleness problem rather than a data error.

for a principal

Own the invisibility rule as policy: consumers query the business model, routing is a platform concern, and no team ships a summary table without conformance and a reconciliation test.

## The principle The design goal for summary tables is that **they are invisible**. An analyst asks for revenue by category by month; whichever table answers, the number is the same. Achieving that requires a routing layer — a BI tool's aggregate navigator, a semantic layer's aggregate awareness, or a view the team controls — plus a set of modelling guarantees without which routing is a correctness bug rather than an optimization. It matters because the alternative is worse than it sounds. If analysts choose tables by hand, some will use the summary for a question it cannot answer correctly, two dashboards will disagree, and trust in the warehouse erodes far faster than any query ever ran. ## Guarantee 1: the measures mean the same thing A measure name is a contract. If `revenue_amount` in the star includes returns as negative rows but the aggregate was built with `WHERE order_status = 'COMPLETED'`, then routing a query to the aggregate changes the answer — and nobody will notice, because both numbers are plausible. The defence is derivation: build the aggregate by summing the star with no additional filters. Every filter you feel tempted to add is a semantic difference, and if it is genuinely wanted it belongs in the atomic model where all consumers get it. ## Guarantee 2: conformed shrunken dimensions The aggregate must join to rollups of the *same* dimensions — a month dimension derived from the date dimension, a category dimension derived from the product dimension — carrying identical attribute values and labels. If they diverge, grouping by `category_name` returns different row sets in the two tables and drill-down from the aggregate to the detail loses or invents rows. This also fixes exactly which requests the aggregate can serve: **only** those whose grouping columns and filter columns all exist in the shrunken dimensions. A filter on `day_of_week` cannot be served by a month-grain aggregate at all, no matter how the query is phrased. ## Guarantee 3: a declared grain, enforced The routing layer needs a precise statement of the grain to decide eligibility, and the table needs a uniqueness constraint so the grain is true. A duplicate row in an aggregate double-counts, and the same query routed to the atomic table would not — so the disagreement appears intermittently, as a function of routing, which is the hardest class of bug to chase. ## Guarantee 4: synchronization If the star has loaded today's data and the aggregate has not, two runs of one dashboard can return different totals purely on routing. Either refresh both in the same transaction or pipeline step, or make the routing layer aware of the aggregate's watermark so it will not serve periods the aggregate has not caught up on. "Refresh the aggregate on its own schedule" is how a warehouse acquires a reputation for numbers that change when you press refresh. ## What the aggregate cannot serve Be explicit about the loss, because it is the whole point of the trade: - **Below its grain.** A month aggregate cannot answer a daily question. - **Dropped dimensions and attributes.** No customer key means no customer analysis; no `order_number` degenerate dimension means no order-level drill and no accurate order counts. - **Non-additive measures.** Distinct counts cannot be recovered by rolling the aggregate further. Each of these must route to the atomic table automatically. If the fallback is manual — "use the other table for that" — the invisibility principle has already failed. ## Where the routing lives Three common placements, all legitimate: an aggregate-aware semantic or BI layer that knows both tables; a database view that selects per request (fragile, but under your control); or, at the other extreme, the engine's own query rewrite over a materialized object. The modelling contract above is identical in all three — what differs is only who evaluates it. Interviewers care much more that you can state the contract than that you can name a product that implements it. ## Testing the substitution The practical safeguard is a standing test that runs the same logical question against both tables and asserts equality by period. It catches all four guarantees failing at once: a semantic drift, a dimension divergence, a duplicate row, a stale load. Without it, aggregate navigation is trust without evidence. ## What interviewers listen for The word *conformed*, the insistence that the aggregate is derived from the star with no extra filters, an explicit fallback story, and awareness that staleness is a correctness problem, not just a freshness one.

  • The aggregate is built with an extra filter excluding cancelled orders. Why is that dangerous?
    It makes the same measure name mean two things. A query routed to the aggregate returns a different revenue figure than the identical query on the star, and both look plausible so nobody investigates. If excluding cancellations is genuinely correct, apply it in the atomic model where every consumer gets it.
  • How do you handle a query that filters on an attribute the aggregate dropped?
    It must fall back to the atomic fact table automatically. Eligibility is decided by whether every grouping and filter column exists in the aggregate's shrunken dimensions at its grain; if one is missing, the aggregate cannot answer the question at all, and a manual 'use the other table' instruction means the routing has already failed.
  • Why is a stale aggregate a correctness problem rather than just a freshness one?
    Because two runs of the same dashboard can disagree depending on which table answered. A user who cannot reproduce a number stops trusting all of them. Refresh both in the same pipeline step, or make the router aware of the aggregate's watermark so it declines periods the aggregate has not caught up on.

saying these in an interview costs you the question

  • Adding filters to the aggregate that the star does not have
  • Telling analysts which table to query by hand
  • Refreshing the aggregate on an independent schedule
  • Assuming any monthly query can hit a monthly aggregate
  • Skipping the uniqueness constraint on the aggregate's grain

context

resolving links…