What is an aggregate fact table in a dimensional model, and why keep the atomic fact table too?
answer
- one row per what, and how many fewer?
- same measures, different grain
- who rebuilds whom after a restatement?
- the detail layer is the source of truth
- derived summary, declared grain, atomic reference
basics
~20 sAn aggregate fact table stores the same measures as an atomic fact table, pre-summed to a coarser grain such as month-by-product. It is a derived performance copy; the atomic table stays as the source of truth for detail and rebuilds.
solid answer
~50 sAn **aggregate fact table** (or summary fact) carries the same measures as the atomic fact table but at a deliberately coarser grain — one row per product per store per month instead of one row per order line, say. A dashboard asking for monthly revenue by category then reads thousands of rows instead of billions, so it returns far faster. The key point is that an aggregate is **derived, never authoritative**. You keep the atomic fact table because it answers what the aggregate cannot: drilling to an individual order, filtering on an attribute the aggregate dropped, slicing at day grain. It is also what you rebuild the aggregate from when a load is restated, and the reference you reconcile against when numbers disagree. Aggregates are an optimization layered on a complete atomic model, never a substitute for one.
code
sql · 20 lines-- atomic fact: one row per order line
CREATE TABLE fact_sales (
date_key INTEGER NOT NULL,
product_key INTEGER NOT NULL,
store_key INTEGER NOT NULL,
order_number VARCHAR(20) NOT NULL, -- degenerate dimension
quantity INTEGER NOT NULL,
revenue_amount DECIMAL(18,2) NOT NULL
);
-- aggregate: one row per product per store per month
CREATE TABLE agg_sales_month_product_store (
month_key INTEGER NOT NULL,
product_key INTEGER NOT NULL,
store_key INTEGER NOT NULL,
quantity INTEGER NOT NULL,
revenue_amount DECIMAL(18,2) NOT NULL,
order_line_count INTEGER NOT NULL,
PRIMARY KEY (month_key, product_key, store_key)
);go deeper
Be ready to say in one sentence what an aggregate fact table is: the same measures, pre-summed to a coarser grain, with the detailed table kept alongside it. Name a concrete example such as daily order lines rolled up to product-by-month.
Explain the mechanics: declare the aggregate's grain, enforce it as a unique key, and build it by summing the atomic star rather than re-reading the source so the two cannot diverge.
Show judgment about which aggregates earn their keep — the row-count reduction, the family of queries served, and the permanent cost in load logic, tests and reconciliation that every summary table adds.
Own the policy: how many summary tables the platform supports, who may publish one, and how consumers are routed to them so the organization never ends up with several teams' rollups quietly reporting different revenue.
## What an aggregate fact table is A **fact table** in a dimensional model holds the numeric measurements of a business process — revenue, quantity, duration — plus foreign keys to the dimension tables that describe them (date, product, store, customer). The **grain** is the sentence stating what exactly one row means: "one row per order line" or "one row per product per store per month". An **aggregate fact table** is a second fact table over the same business process, built at a *coarser* grain by summing the atomic rows. If the atomic table is one row per order line per day, an aggregate might be one row per product per store per month, with `quantity` and `revenue_amount` summed inside each of those buckets. Nothing new is measured; the same numbers are simply pre-added. The payoff is row count. A retailer with a billion order lines a year may have only a few million product-store-month combinations. Any report whose result is already at or above the aggregate's grain can be answered by scanning the small table instead of the large one. ## The grain rule applies to aggregates too Design an aggregate exactly the way you design any fact table: declare the grain first, in one sentence, and make it a real uniqueness constraint. `(month_key, product_key, store_key)` should be unique. If two rows can share that key, the aggregate double-counts the moment anyone sums it, and the failure is silent — the report still renders, it is just wrong. A good habit is to write a standing test that the declared grain holds: ```sql SELECT month_key, product_key, store_key, COUNT(*) FROM agg_sales_month_product_store GROUP BY month_key, product_key, store_key HAVING COUNT(*) > 1; ``` ## Why the atomic fact table never goes away Three reasons, and interviewers want all three. **It answers questions the aggregate cannot.** The aggregate has thrown away dimensions and attributes: day, order number, customer, promotion. Any question below its grain, or filtered by a dropped attribute, has no answer in the summary. Aggregates serve a known family of queries; the atomic layer serves the unknown ones, which in analytics is most of them. **It is the rebuild source.** When a source system restates last quarter, or a bug in a transformation is fixed, you correct the atomic layer and regenerate the affected aggregate rows from it. If the aggregate were the only copy, an incorrect summarization would be unrecoverable — the detail needed to recompute it would not exist. **It is the reconciliation reference.** The only way to know an aggregate is still telling the truth is to compare it against the atomic table by period. Without a detailed layer there is nothing to compare against, and drift becomes undetectable. ## How the aggregate is built Always by summing the atomic table, never by re-reading the source system. Deriving from the source invites the two paths to diverge — a different filter, a different currency conversion, a different treatment of cancelled orders — and then the aggregate and the star disagree for reasons nobody can trace. ```sql INSERT INTO agg_sales_month_product_store SELECT d.month_key, f.product_key, f.store_key, SUM(f.quantity), SUM(f.revenue_amount), COUNT(*) FROM fact_sales f JOIN dim_date d ON d.date_key = f.date_key GROUP BY d.month_key, f.product_key, f.store_key; ``` ## Choosing which aggregates to build Build from observed query patterns, not guesses. The candidates are the grains your dashboards actually land on repeatedly — usually a date rollup (day to month) combined with a hierarchy rollup (product to category, store to region). An aggregate is worth its maintenance cost when it collapses a large row count into a small one and serves a whole family of reports rather than a single chart. A summary that only removes a modest fraction of the rows buys little and still has to be loaded, tested and reconciled forever. ## Aggregates should be invisible to the user The classic dimensional-design rule is that users and their SQL should not have to know which table they are hitting. If analysts must remember to write `agg_sales_month_product_store` for monthly questions and `fact_sales` for daily ones, half of them will use the wrong one and totals will disagree across the company. Routing belongs in the BI or semantic layer that sits above both tables, and it is only safe when the aggregate joins to conformed, shrunken versions of the same dimensions. ## What interviewers listen for That you say *derived*, that you declare a grain, that you keep the atomic layer, and that you build the summary from the star rather than from the source. Candidates who describe an aggregate as "a table we made because the dashboard was slow" and cannot say what one row means have not designed one.
- Should the aggregate be built from the atomic fact table or straight from the source system?From the atomic fact table. Deriving it from the source lets the two paths diverge — a different filter for cancelled orders, a different currency rule — and then the summary and the star disagree for reasons nobody can trace. Summing the star guarantees the aggregate is a true rollup of the numbers the detail layer already publishes.
- How do you decide an aggregate is not worth building?When it barely reduces the row count, when it serves one chart rather than a family of reports, or when the dimensions it would need at that grain do not exist yet. Every aggregate is permanent load logic, a permanent test surface and a permanent reconciliation obligation, so the saving must be large and recurring.
- What uniqueness guarantee should an aggregate fact table carry?Its declared grain must be a unique key — for a month-product-store aggregate, (month_key, product_key, store_key). Enforce it with a constraint or a standing test that groups by those columns and fails on any count above one. A duplicated grain row double-counts silently: the report still renders, it is simply wrong.
An aggregate is a printed monthly bank statement summary; the atomic fact table is the full transaction ledger. The summary is faster to read and always reproducible from the ledger — never the other way round.
saying these in an interview costs you the question
- Calling the aggregate the source of truth and dropping the detail
- Building the summary from the source system instead of the star
- Never declaring what one aggregate row means
- Assuming any question can be answered from the summary
- Treating an aggregate as a filtered subset rather than a coarser grain