DWH Interview Question 15 - What is a fact table? What are additive, semi-additive and non-additive measures?

Data Warehouse Interview Q&A · Question 15

In this post we are going to cover fact tables, and the three kinds of measures they hold: additive, semi-additive and non-additive.

A fact table stores the measurements of a business process, like sales or account balances, at one declared grain. Each row has keys that point to the dimensions, plus numeric measures. Fact tables are usually narrow but very long. They are the biggest tables in the warehouse.

The interesting part is that not every measure can be added up the same way. We will build two small fact tables and walk into the two classic traps: summing a balance across time, and averaging a percentage.

Question 15 of the Real DWH Question and Answers series on TechBrothersIT.

On this page
  1. The interview answer
  2. What is in a fact table
  3. Let's create some sample data
  4. Step 1: Additive - sum by any dimension
  5. Step 2: Semi-additive - summing across accounts works
  6. Step 3: Semi-additive - summing across time does not
  7. Step 4: Use the closing balance across time
  8. Step 5: Non-additive - never average a percentage
  9. Design tips
  10. Follow-up questions interviewers ask
  11. The full script

The interview answer

Say this first

A fact table stores the measurements of a business process at a declared grain: foreign keys to the dimensions plus numeric measures. Measures are additive (sum across every dimension, like sales amount), semi-additive (sum across some dimensions but not time, like a balance), or non-additive (never summed, like a percentage or unit price).

What is in a fact table

A fact table row has three kinds of columns:

  • Keys - point to the dimensions, like date_key. In our demo we show the product by name to keep it readable, but in a real fact table it would be a product key, next to keys for customer and store.
  • Measures - the numbers the business wants to analyze: quantity, unit price, amount and cost.
  • Degenerate dimension - sometimes an operational ID like the order number, kept on the fact with no dimension table of its own.

Let's create some sample data

We need two fact tables. fact_sales is a transaction fact with one row per product on an order line. fact_account_balance is a snapshot fact with one row per account per day.

Sample data — run this first
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS fact_account_balance;

-- transaction fact: one row per product on an order line
CREATE TABLE fact_sales (
  sale_id      INT PRIMARY KEY,
  date_key     INT,
  product      VARCHAR(50),
  order_number VARCHAR(10),
  quantity     INT,
  unit_price   DECIMAL(10,2),
  amount       DECIMAL(10,2),
  cost         DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
  (1, 20260105, 'Laptop Stand', 'SO-1001',  1, 35.00, 35.00, 20.00),
  (2, 20260105, 'USB-C Cable',  'SO-1001', 10,  9.00, 90.00, 30.00),
  (3, 20260202, 'Desk Lamp',    'SO-1003',  1, 30.00, 30.00, 24.00),
  (4, 20260202, 'Laptop Stand', 'SO-1003',  2, 35.00, 70.00, 40.00);

-- snapshot fact: one row per account per day
CREATE TABLE fact_account_balance (
  date_key INT,
  account  VARCHAR(20),
  balance  DECIMAL(10,2)
);
INSERT INTO fact_account_balance VALUES
  (20260301, 'Sara', 100.00), (20260301, 'Omar', 50.00),
  (20260302, 'Sara', 120.00), (20260302, 'Omar', 50.00),
  (20260303, 'Sara',  90.00), (20260303, 'Omar', 70.00);

Let's look at the sales fact.

Query
SELECT * FROM fact_sales ORDER BY 1;
Result4 rows · 8 columns
sale_iddate_keyproductorder_numberquantityunit_priceamountcost
120260105Laptop StandSO-1001135.0035.0020.00
220260105USB-C CableSO-1001109.0090.0030.00
320260202Desk LampSO-1003130.0030.0024.00
420260202Laptop StandSO-1003235.0070.0040.00

Four order lines. Notice line 2: 10 USB-C Cables for 90.00, a much bigger line than the others. That matters later.

Query
SELECT * FROM fact_account_balance ORDER BY 1;
Result6 rows · 3 columns
date_keyaccountbalance
20260301Sara100.00
20260301Omar50.00
20260302Sara120.00
20260302Omar50.00
20260303Sara90.00
20260303Omar70.00

Each customer's account balance at the end of each day, for three days in March.

Step 1: Additive - sum by any dimension

Additive measures are the easy ones. Let's sum the sales amount by product.

Query
SELECT product, SUM(amount) AS sales
FROM fact_sales
GROUP BY product
ORDER BY product;
Result3 rows · 2 columns
productsales
Desk Lamp30.00
Laptop Stand105.00
USB-C Cable90.00

Laptop Stand sold 105.00, USB-C Cable 90.00 and Desk Lamp 30.00. Amount adds up correctly by product, by date, by customer, by store, or all at once. Quantity and cost are additive too, and most transaction measures are.

Step 2: Semi-additive - summing across accounts works

Balances are semi-additive. Let's first sum them across accounts, for each day.

Query
SELECT date_key, SUM(balance) AS total_balance
FROM fact_account_balance
GROUP BY date_key
ORDER BY date_key;
Result3 rows · 2 columns
date_keytotal_balance
20260301150.00
20260302170.00
20260303160.00

The total money held was 150.00 on March 1, 170.00 on March 2 and 160.00 on March 3. That is correct: on any one day, the total is the sum of all balances.

Step 3: Semi-additive - summing across time does not

Now let's sum the same balances across days, per account.

Query
SELECT account, SUM(balance) AS wrong_total
FROM fact_account_balance
GROUP BY account
ORDER BY account;
Result2 rows · 2 columns
accountwrong_total
Omar170.00
Sara310.00

This says Sara has 310.00. She never had that much: her balance was 100.00, then 120.00, then 90.00. Adding the balances of three different days together is meaningless.

Step 4: Use the closing balance across time

Across time we take the last value (or an average), not a sum. Let's pick each account's balance on the latest date.

Query
SELECT account, balance AS closing_balance
FROM fact_account_balance
WHERE date_key = (SELECT MAX(date_key)
                  FROM fact_account_balance)
ORDER BY account;
Result2 rows · 2 columns
accountclosing_balance
Omar70.00
Sara90.00

At the end of the period Omar has 70.00 and Sara has 90.00. Balances, inventory levels and headcounts all behave this way.

Step 5: Non-additive - never average a percentage

Margin percent is a classic non-additive measure. Each sale has its own margin, and a dashboard might simply average those row percentages. Let's compare that with the true margin: total profit divided by total revenue.

Query
SELECT
  ROUND(AVG((amount - cost) / amount * 100), 1)
    AS avg_of_row_pct,
  ROUND((SUM(amount) - SUM(cost)) / SUM(amount) * 100, 1)
    AS true_margin_pct
FROM fact_sales;
Result1 row · 2 columns
avg_of_row_pcttrue_margin_pct
43.149.3

The average of the row percentages is 43.1, but the true margin is 49.3. The plain average treats every row the same and ignores that the USB-C line is much bigger than the others. The rule: store the additive parts (amount and cost) in the fact table and calculate the ratio at the very end. The same goes for unit prices and any other ratio.

Design tips

  • Prefer additive measures - if a measure is a ratio, store its numerator and denominator instead.
  • Declare one grain - every row must mean the same thing. Grain has its own post (question 17).
  • Know the fact table types - transaction, periodic snapshot, accumulating snapshot and factless facts are covered in questions 21 and 22.

Follow-up questions interviewers ask

Give an example of a semi-additive measure.

Account balance, inventory level, headcount: summable across accounts/products/departments, not across time.

How do you aggregate a semi-additive measure over time?

Closing (last) value, opening value, or an average over the period.

How do you handle a percentage measure?

Store the additive numerator and denominator in the fact and compute the ratio after aggregation.

The full script

Here is everything from this post in one script. Copy it and run it top to bottom.

Full script — copy and run top to bottom
-- ========================================================================
-- Data Warehouse Interview Q&A #15 - Fact tables and additive measures
-- 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_q15;  SET search_path TO dwh_q15;
-- ========================================================================

-- Step 0: create and load the sample data
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS fact_account_balance;

-- transaction fact: one row per product on an order line
CREATE TABLE fact_sales (
  sale_id      INT PRIMARY KEY,
  date_key     INT,
  product      VARCHAR(50),
  order_number VARCHAR(10),
  quantity     INT,
  unit_price   DECIMAL(10,2),
  amount       DECIMAL(10,2),
  cost         DECIMAL(10,2)
);
INSERT INTO fact_sales VALUES
  (1, 20260105, 'Laptop Stand', 'SO-1001',  1, 35.00, 35.00, 20.00),
  (2, 20260105, 'USB-C Cable',  'SO-1001', 10,  9.00, 90.00, 30.00),
  (3, 20260202, 'Desk Lamp',    'SO-1003',  1, 30.00, 30.00, 24.00),
  (4, 20260202, 'Laptop Stand', 'SO-1003',  2, 35.00, 70.00, 40.00);

-- snapshot fact: one row per account per day
CREATE TABLE fact_account_balance (
  date_key INT,
  account  VARCHAR(20),
  balance  DECIMAL(10,2)
);
INSERT INTO fact_account_balance VALUES
  (20260301, 'Sara', 100.00), (20260301, 'Omar', 50.00),
  (20260302, 'Sara', 120.00), (20260302, 'Omar', 50.00),
  (20260303, 'Sara',  90.00), (20260303, 'Omar', 70.00);


-- Step 1: Additive: amount can be summed by any dimension
SELECT product, SUM(amount) AS sales
FROM fact_sales
GROUP BY product
ORDER BY product;

-- Step 2: Semi-additive: balances CAN be summed across accounts (for one day)
SELECT date_key, SUM(balance) AS total_balance
FROM fact_account_balance
GROUP BY date_key
ORDER BY date_key;

-- Step 3: Semi-additive: balances can NOT be summed across time
SELECT account, SUM(balance) AS wrong_total
FROM fact_account_balance
GROUP BY account
ORDER BY account;

-- Step 4: Semi-additive done right: take the closing balance
SELECT account, balance AS closing_balance
FROM fact_account_balance
WHERE date_key = (SELECT MAX(date_key)
                  FROM fact_account_balance)
ORDER BY account;

-- Step 5: Non-additive: never average or sum a percentage
SELECT
  ROUND(AVG((amount - cost) / amount * 100), 1)
    AS avg_of_row_pct,
  ROUND((SUM(amount) - SUM(cost)) / SUM(amount) * 100, 1)
    AS true_margin_pct
FROM fact_sales;

Key points

  • Fact table = dimension keys + numeric measures at one grain
  • Additive: sum across every dimension (amount, quantity)
  • Semi-additive: not across time (balances, stock): use closing or average
  • Non-additive: ratios, percentages, unit prices: store the parts
  • Fact tables are narrow and very long
Next in this series

Question 16 — What is a dimension table? What is a dimension table, and what makes a good one?

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.

Subscribe to: Post Comments (Atom)