In this post we are going to cover aggregate tables: what they are, when you should build one, and the one problem every aggregate has, which is going stale when late data arrives.
An aggregate table stores pre-summarized fact data at a coarser grain. Instead of one row per product sold, it keeps one row per day per category, with the total already added up. Queries that only need that level of detail read the small table instead of the big one.
We will build an aggregate from a small sales fact, answer a dashboard question from it, break it with a late sale, and then fix it with a refresh that is safe to run twice.
Question 23 of the Real DWH Question and Answers series on TechBrothersIT.
- The interview answer
- Why aggregates exist
- Let's create some sample data
- Step 1: Build the aggregate from the atomic fact
- Step 2: Answer the dashboard question from the aggregate
- Step 3: A late sale arrives
- Step 4: Refresh the affected day
- Materialized views and design tips
- Follow-up questions interviewers ask
- The full script
The interview answer
An aggregate table stores pre-summarized fact data at a coarser grain, like sales per day per category instead of per order line, so frequent queries read far fewer rows. It is derived from the atomic fact, refreshed by the pipeline, and must stay consistent with it. Materialized views can automate it.
Why aggregates exist
Picture the sales dashboard. Every few minutes someone opens it, and it asks the same question: sales per day per category. Against the atomic fact table, every one of those queries scans every sale, maybe billions of rows, only to produce the same few thousand numbers.
An aggregate does that work once, after each load, and stores the result. The dashboard then reads the small table instead.
- Atomic fact - one row per product sold, can be billions of rows.
- Aggregate - one row per day per category, thousands of rows.
- Golden rule - an aggregate is a faster route to the same numbers, never a different truth.
Let's create some sample data
We need the atomic fact fact_sales, with one row per product sold, and an empty aggregate table agg_sales_daily_category that we will fill from it.
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS agg_sales_daily_category;
-- the atomic fact: one row per product sold
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
sale_date DATE,
category VARCHAR(50),
product VARCHAR(50),
amount DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
(1, '2026-01-05', 'Accessories', 'Laptop Stand', 35.00),
(2, '2026-01-05', 'Accessories', 'USB-C Cable', 18.00),
(3, '2026-01-05', 'Accessories', 'USB-C Cable', 9.00),
(4, '2026-01-05', 'Electronics', 'Wireless Mouse', 25.00),
(5, '2026-01-05', 'Home', 'Desk Lamp', 30.00),
(6, '2026-01-06', 'Accessories', 'Laptop Stand', 70.00),
(7, '2026-01-06', 'Electronics', 'Wireless Mouse', 25.00),
(8, '2026-01-06', 'Electronics', 'Keyboard', 50.00),
(9, '2026-01-06', 'Home', 'Desk Lamp', 30.00),
(10, '2026-01-06', 'Home', 'Desk Lamp', 30.00);
-- the aggregate: one row per day per category
CREATE TABLE agg_sales_daily_category (
sale_date DATE,
category VARCHAR(50),
sales DECIMAL(12,2),
sales_count INT,
PRIMARY KEY (sale_date, category)
);Let's look at the atomic fact.
SELECT * FROM fact_sales ORDER BY 1;| sale_id | sale_date | category | product | amount |
|---|---|---|---|---|
| 1 | 2026-01-05 | Accessories | Laptop Stand | 35.00 |
| 2 | 2026-01-05 | Accessories | USB-C Cable | 18.00 |
| 3 | 2026-01-05 | Accessories | USB-C Cable | 9.00 |
| 4 | 2026-01-05 | Electronics | Wireless Mouse | 25.00 |
| 5 | 2026-01-05 | Home | Desk Lamp | 30.00 |
| 6 | 2026-01-06 | Accessories | Laptop Stand | 70.00 |
| 7 | 2026-01-06 | Electronics | Wireless Mouse | 25.00 |
| 8 | 2026-01-06 | Electronics | Keyboard | 50.00 |
| 9 | 2026-01-06 | Home | Desk Lamp | 30.00 |
| 10 | 2026-01-06 | Home | Desk Lamp | 30.00 |
Ten sales over two days, January 5 and 6, across three categories: Accessories, Electronics and Home.
Step 1: Build the aggregate from the atomic fact
Now let's fill the aggregate. It is a simple GROUP BY on sale date and category, storing the total amount and the number of sales.
INSERT INTO agg_sales_daily_category
SELECT sale_date, category,
SUM(amount), COUNT(*)
FROM fact_sales
GROUP BY sale_date, category;Now let's check the result.
SELECT * FROM agg_sales_daily_category ORDER BY sale_date, category;| sale_date | category | sales | sales_count |
|---|---|---|---|
| 2026-01-05 | Accessories | 62.00 | 3 |
| 2026-01-05 | Electronics | 25.00 | 1 |
| 2026-01-05 | Home | 30.00 | 1 |
| 2026-01-06 | Accessories | 70.00 | 1 |
| 2026-01-06 | Electronics | 75.00 | 2 |
| 2026-01-06 | Home | 60.00 | 2 |
Ten detail rows became six summary rows. In a real warehouse, billions become thousands. Look at Accessories on January 5: three sales adding up to 62.00. We keep sales_count next to sales on purpose, so averages can still be calculated correctly at a higher level.
Step 2: Answer the dashboard question from the aggregate
Now let's answer the dashboard question, total sales by category, reading only the aggregate.
SELECT category, SUM(sales) AS sales
FROM agg_sales_daily_category
GROUP BY category
ORDER BY category;| category | sales |
|---|---|
| Accessories | 132.00 |
| Electronics | 100.00 |
| Home | 90.00 |
Accessories 132.00, Electronics 100.00 and Home 90.00. This is exactly what we would get from the fact table, but we read six rows instead of ten.
Step 3: A late sale arrives
Here is the catch. A late sale for January 6 arrives in the fact table after the aggregate was built. Let's insert it, then compare the fact table with the aggregate category by category.
INSERT INTO fact_sales VALUES
(11, '2026-01-06', 'Home', 'Desk Lamp', 30.00);Now let's check the result.
SELECT f.category, SUM(f.amount) AS fact_sales,
(SELECT SUM(a.sales) FROM agg_sales_daily_category a
WHERE a.category = f.category) AS agg_sales
FROM fact_sales f
GROUP BY f.category
ORDER BY f.category;| category | fact_sales | agg_sales |
|---|---|---|
| Accessories | 132.00 | 132.00 |
| Electronics | 100.00 | 100.00 |
| Home | 120.00 | 90.00 |
Accessories and Electronics still match. Home does not: the fact table says 120.00, the aggregate still says 90.00. The dashboard is now wrong and nothing raised an error. That is what a stale aggregate looks like.
Step 4: Refresh the affected day
The fix is to rebuild only the part of the aggregate that changed. We delete January 6 from the aggregate and insert it again from the fact table.
DELETE FROM agg_sales_daily_category
WHERE sale_date = '2026-01-06';
INSERT INTO agg_sales_daily_category
SELECT sale_date, category, SUM(amount), COUNT(*)
FROM fact_sales
WHERE sale_date = '2026-01-06'
GROUP BY sale_date, category;Now let's check the result.
SELECT f.category, SUM(f.amount) AS fact_sales,
(SELECT SUM(a.sales) FROM agg_sales_daily_category a
WHERE a.category = f.category) AS agg_sales
FROM fact_sales f
GROUP BY f.category
ORDER BY f.category;| category | fact_sales | agg_sales |
|---|---|---|
| Accessories | 132.00 | 132.00 |
| Electronics | 100.00 | 100.00 |
| Home | 120.00 | 120.00 |
All three categories agree again, and Home is 120.00 in both. Because the refresh is a delete followed by an insert, running it twice gives the same result. That property is called idempotent, and it keeps the refresh safe to re-run after a failure.
Materialized views and design tips
Do you always have to build this yourself? Not always. A materialized view is a query whose result the database stores and refreshes for you. Some cloud warehouses refresh them automatically and can even use them to answer queries written against the base table. The idea is the same: a stored summary that must match the detail.
- Keep the atomic fact - aggregates are derived, never the only copy.
- Only for frequent, slow patterns - don't pre-aggregate everything.
- Store counts with sums - so averages stay correct.
- Clear names - like
agg_sales_daily_category.
You can always roll detail up into a summary, but you can never drill down from a summary back to the detail.
Follow-up questions interviewers ask
Aggregate table vs materialized view?
Same idea; a materialized view is managed (and possibly auto-refreshed) by the database, an aggregate table by your pipeline.
Why store a count next to the sum?
So averages can be computed correctly at higher levels (sum of sums / sum of counts).
Should aggregates replace the atomic fact?
No. Detail can be rolled up, never recovered from a summary.
The full script
Here is everything from this post in one script. Copy it and run it top to bottom.
-- ========================================================================
-- Data Warehouse Interview Q&A #23 - Aggregate tables
-- TechBrothersIT. Sample data is invented for teaching.
-- Standard SQL, tested on PostgreSQL 16. Runs top to bottom in one go.
-- Run it in an empty database or schema: other videos reuse table names
-- like dim_customer, and their leftover tables can block the DROPs below.
-- PostgreSQL example: CREATE SCHEMA dwh_q23; SET search_path TO dwh_q23;
-- ========================================================================
-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS agg_sales_daily_category;
-- the atomic fact: one row per product sold
CREATE TABLE fact_sales (
sale_id INT PRIMARY KEY,
sale_date DATE,
category VARCHAR(50),
product VARCHAR(50),
amount DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
(1, '2026-01-05', 'Accessories', 'Laptop Stand', 35.00),
(2, '2026-01-05', 'Accessories', 'USB-C Cable', 18.00),
(3, '2026-01-05', 'Accessories', 'USB-C Cable', 9.00),
(4, '2026-01-05', 'Electronics', 'Wireless Mouse', 25.00),
(5, '2026-01-05', 'Home', 'Desk Lamp', 30.00),
(6, '2026-01-06', 'Accessories', 'Laptop Stand', 70.00),
(7, '2026-01-06', 'Electronics', 'Wireless Mouse', 25.00),
(8, '2026-01-06', 'Electronics', 'Keyboard', 50.00),
(9, '2026-01-06', 'Home', 'Desk Lamp', 30.00),
(10, '2026-01-06', 'Home', 'Desk Lamp', 30.00);
-- the aggregate: one row per day per category
CREATE TABLE agg_sales_daily_category (
sale_date DATE,
category VARCHAR(50),
sales DECIMAL(12,2),
sales_count INT,
PRIMARY KEY (sale_date, category)
);
-- Step 1: Build the aggregate from the atomic fact
INSERT INTO agg_sales_daily_category
SELECT sale_date, category,
SUM(amount), COUNT(*)
FROM fact_sales
GROUP BY sale_date, category;
-- check the result
SELECT * FROM agg_sales_daily_category ORDER BY sale_date, category;
-- Step 2: The dashboard question, answered from the aggregate
SELECT category, SUM(sales) AS sales
FROM agg_sales_daily_category
GROUP BY category
ORDER BY category;
-- Step 3: A late sale arrives in the fact table
INSERT INTO fact_sales VALUES
(11, '2026-01-06', 'Home', 'Desk Lamp', 30.00);
-- check the result
SELECT f.category, SUM(f.amount) AS fact_sales,
(SELECT SUM(a.sales) FROM agg_sales_daily_category a
WHERE a.category = f.category) AS agg_sales
FROM fact_sales f
GROUP BY f.category
ORDER BY f.category;
-- Step 4: Refresh the affected day: delete and re-insert
DELETE FROM agg_sales_daily_category
WHERE sale_date = '2026-01-06';
INSERT INTO agg_sales_daily_category
SELECT sale_date, category, SUM(amount), COUNT(*)
FROM fact_sales
WHERE sale_date = '2026-01-06'
GROUP BY sale_date, category;
-- check the result
SELECT f.category, SUM(f.amount) AS fact_sales,
(SELECT SUM(a.sales) FROM agg_sales_daily_category a
WHERE a.category = f.category) AS agg_sales
FROM fact_sales f
GROUP BY f.category
ORDER BY f.category;
Key points
- Pre-summarized facts at a coarser grain
- Frequent queries read far fewer rows
- Always derived from the atomic fact; same numbers, faster
- Late data makes them stale: refresh what changed
- Materialized views can automate the refresh
Question 24 — Conformed dimensions and the bus matrix What is a conformed dimension, and what is the bus matrix?
Every query and every result on this page was run on PostgreSQL 16 using the script above. The SQL is standard and runs on most databases with small changes (noted in the script). All tables and data are invented demo data for teaching.
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.