Module 7: Building and running a warehouse · Lesson 7.4 · about 30 min
Testing a warehouse: counts, keys and orphans
A warehouse test is a query that returns the rows that break a rule; no rows means pass. Write the five tests every star needs (unique keys, no null keys, no orphans, accepted values, reconciled totals), catch four planted bugs in a Northbay star, fix them and watch every test pass.
Module 7 · about 30 minutes · read first: 7.3. You’ll be able to write the five standard warehouse tests in SQL, collect them in one results view, find planted bugs with them, and know which test catches which kind of mistake.
In everyday terms: a till check at closing time. You count the cash against the receipts. If they match, nothing to do; if not, you go and find the mistake.
1. Why this matters
A dashboard that shows the wrong number is worse than no dashboard: people make decisions on it. Most warehouse errors come from a few causes (duplicate keys, missing keys, values nobody expected, rows lost or doubled in a join), and each can be caught by a short SQL query run after every load. Teams that use dbt, Great Expectations or hand-written checks all test the same few things. Being able to list them, and write them, is a standard interview question.
- Unique test
- No key appears more than once.
- Not-null test
- A required column is never NULL.
- Relationships test
- Every fact key matches a dimension row.
- Orphan
- A fact row whose key matches no dimension row.
- Accepted values
- A column holds only allowed values.
- Reconciliation
- Totals in the warehouse match the source.
- Physical meaning
- Tests do not change any table. Each one reads the tables and returns the rows that break a rule.
“If the row count looks right the data must be right.” A join that drops and duplicates the same number of rows still gives the right count. Test keys, nulls, orphans and totals separately.
2. A test is a query for bad rows
The convention: a test returns the rows that break a rule. No rows means the test passes. When it fails, the result is the list of rows to investigate. The five tests every star needs:
| Test | Rule | Catches |
|---|---|---|
| Unique | each dimension key appears once | duplicate loads, bad joins in the dimension build, fan-out later |
| Not null | fact foreign keys are never NULL | guests or missing lookups with no unknown member |
| Relationships (orphans) | every fact key exists in its dimension | dimension rows deleted or filtered out |
| Accepted values | a column holds only the expected values | typos, new codes, stray spaces |
| Reconciliation | counts and totals match the source | rows lost or doubled anywhere in the pipeline |
3. Build a star with four planted bugs
This build looks fine at a glance. It has four bugs, each a real mistake people make. Run it, then we test.
-- noviz
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
CREATE TABLE dim_product AS
SELECT product_id AS product_key, product_name, unit_price FROM products;
INSERT INTO dim_product -- bug 1: part of the load ran twice
SELECT product_id, product_name, unit_price FROM products WHERE product_id = 28;
CREATE TABLE dim_customer AS
SELECT customer_id AS customer_key,
first_name || ' ' || last_name AS customer_name,
city, -- bug 2: city not trimmed
COALESCE(loyalty_tier, 'None') AS loyalty_tier
FROM customers
WHERE joined_on <= '2025-12-01'; -- bug 3: a test filter left in
CREATE TABLE fact_sales AS
SELECT o.order_id, i.product_id AS product_key,
o.customer_id AS customer_key, -- bug 4: guests get NULL, no -1 member
ROUND(i.quantity * i.unit_price * (1 - i.discount), 2) AS sales_amount
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'completed';
SELECT (SELECT COUNT(*) FROM dim_product) AS products, (SELECT COUNT(*) FROM dim_customer) AS customers,
COUNT(*) AS fact_rows FROM fact_sales
62 products (there should be 61), 593 customers (there should be 603 plus an Unknown member) and 7,112 fact rows. Nothing errored. Only tests will tell you something is wrong.
4. The five tests, one at a time
Unique. Every dimension key once:
-- try
SELECT product_key, COUNT(*) AS copies
FROM dim_product
GROUP BY product_key
HAVING COUNT(*) > 1
Product 28 is there twice. Any fact row for coffee beans will be doubled when joined to dim_product.
Not null. Fact foreign keys should never be NULL; guests belong to the -1 member:
-- try
SELECT COUNT(*) AS null_customer_keys
FROM fact_sales
WHERE customer_key IS NULL
845 fact rows have no customer key. A report that inner-joins to dim_customer drops all of them without a word.
Relationships (orphans). Every non-null fact key must exist in the dimension. The anti-join from SQL Lesson 4.4:
-- try
SELECT f.customer_key, COUNT(*) AS orphan_rows
FROM fact_sales f
LEFT JOIN dim_customer d ON d.customer_key = f.customer_key
WHERE f.customer_key IS NOT NULL AND d.customer_key IS NULL
GROUP BY f.customer_key
ORDER BY orphan_rows DESC
10 customers, with 97 fact rows between them, have no dimension row: the leftover filter threw away everyone who joined after 1 December 2025.
Accepted values. A column should hold only the values you expect. Here, city names must have no spaces at the ends, and tiers must be one of four:
-- try
SELECT 'city has spaces' AS problem, COUNT(*) AS bad_rows FROM dim_customer WHERE city <> trim(city)
UNION ALL
SELECT 'unknown tier', COUNT(*) FROM dim_customer
WHERE loyalty_tier NOT IN ('Bronze', 'Silver', 'Gold', 'None')
The tier test passes; the city test fails. A slicer in Power BI would show “Winnipeg” and “Winnipeg ” as two cities.
Reconciliation. The fact must match the source, and joins must not change the row count:
-- try
SELECT (SELECT COUNT(*) FROM order_items i JOIN orders o ON o.order_id = i.order_id
WHERE o.status = 'completed') AS source_lines,
(SELECT COUNT(*) FROM fact_sales) AS fact_lines,
(SELECT COUNT(*) FROM fact_sales f JOIN dim_product p ON p.product_key = f.product_key) AS after_product_join
The fact matches the source (7,112), but joining dim_product gives 7,287: the duplicate product 28 doubled its 175 lines. That is fan-out, and it is why “count before and after every join” is a habit worth keeping (SQL Lesson 4.5).
5. All tests in one view
Collect every test in one view that returns a test name, a failure count and PASS or FAIL. Run it after every load; a scheduler can stop the pipeline if any row says FAIL.
-- noviz
DROP VIEW IF EXISTS test_results;
CREATE VIEW test_results AS
WITH t AS (
SELECT 'unique dim_product.product_key' AS test,
(SELECT COUNT(*) FROM (SELECT product_key FROM dim_product GROUP BY product_key HAVING COUNT(*) > 1)) AS failures
UNION ALL SELECT 'unique dim_customer.customer_key',
(SELECT COUNT(*) FROM (SELECT customer_key FROM dim_customer GROUP BY customer_key HAVING COUNT(*) > 1))
UNION ALL SELECT 'not null fact_sales.customer_key',
(SELECT COUNT(*) FROM fact_sales WHERE customer_key IS NULL)
UNION ALL SELECT 'orphans fact_sales -> dim_customer',
(SELECT COUNT(*) FROM fact_sales f LEFT JOIN dim_customer d ON d.customer_key = f.customer_key
WHERE f.customer_key IS NOT NULL AND d.customer_key IS NULL)
UNION ALL SELECT 'orphans fact_sales -> dim_product',
(SELECT COUNT(*) FROM fact_sales f LEFT JOIN dim_product d ON d.product_key = f.product_key
WHERE d.product_key IS NULL)
UNION ALL SELECT 'accepted dim_customer.city trimmed',
(SELECT COUNT(*) FROM dim_customer WHERE city <> trim(city))
UNION ALL SELECT 'reconcile fact rows = source lines',
ABS((SELECT COUNT(*) FROM fact_sales) - (SELECT COUNT(*) FROM order_items i JOIN orders o
ON o.order_id = i.order_id WHERE o.status = 'completed'))
UNION ALL SELECT 'reconcile no fan-out on dim_product',
(SELECT COUNT(*) FROM fact_sales f JOIN dim_product p ON p.product_key = f.product_key)
- (SELECT COUNT(*) FROM fact_sales)
)
SELECT test, failures, CASE WHEN failures = 0 THEN 'PASS' ELSE 'FAIL' END AS result FROM t;
SELECT * FROM test_results
Five FAILs and three PASSes. Bug 1 shows up twice, as a duplicate key and as fan-out, and every other bug is caught by its own test.
Maths and principle
Principle: each test checks a property of a function. A dimension key must be a key: the map from key to row is a function only if each key appears once (unique). A foreign key must land in the dimension’s key set (referential integrity). A join of facts to a dimension preserves the fact count exactly when every fact key matches exactly one dimension row.
Equation: for a fact F and dimension D joined on k, |F ⋈ D| = Σ over fact rows f of (number of rows in D with key f.k). Each term is 1 when k is unique and present, 0 for an orphan, 2 or more for a duplicate. So |F ⋈ D| = |F| − orphans + extra copies. Here 7,112 − 0 + 175 = 7,287.
Analogy: a cloakroom. Each ticket number must be on one coat only (unique), every ticket handed out must have a coat (no orphans), and the count of coats returned should equal the tickets handed in (reconciliation).
Try it: a fact has 1,000 rows, 20 are orphans, and one dimension key appears three times, used by 10 fact rows. How many rows after an inner join? (1,000 − 20 + 2 × 10 = 1,000.) The count matches, yet two bugs hide. That is why you run each test separately, not only the count.
Watch it happen, one step at a time (any live box below has a Show how it runs button that animates the real query):
| sale | product_key | amount |
|---|---|---|
| 1 | 10 | 40 |
| 2 | 11 | 30 |
| 3 | 99 | 20 |
| 4 | NULL | 10 |
| product_key |
|---|
| 10 |
| 11 |
GROUP BY product_key HAVING COUNT(*) > 1 on the dimension: no rows, pass.6. Worked example: fix the bugs, re-run the tests
Fix all four bugs in the build: de-duplicate products, trim cities, drop the leftover filter, and add the -1 Unknown member for guests.
-- noviz
DROP TABLE IF EXISTS fact_sales;
DROP TABLE IF EXISTS dim_product;
DROP TABLE IF EXISTS dim_customer;
CREATE TABLE dim_product AS
SELECT product_id AS product_key, product_name, unit_price FROM products;
CREATE TABLE dim_customer AS
SELECT -1 AS customer_key, 'Guest' AS customer_name, 'Unknown' AS city, 'Unknown' AS loyalty_tier
UNION ALL
SELECT customer_id, first_name || ' ' || last_name, trim(city), COALESCE(loyalty_tier, 'None')
FROM customers;
CREATE TABLE fact_sales AS
SELECT o.order_id, i.product_id AS product_key,
COALESCE(o.customer_id, -1) AS customer_key,
ROUND(i.quantity * i.unit_price * (1 - i.discount), 2) AS sales_amount
FROM orders o JOIN order_items i ON i.order_id = o.order_id
WHERE o.status = 'completed';
SELECT * FROM test_results
- dim_product now has 61 rows; the unique test passes and the fan-out check is 0.
- dim_customer has 604 rows (603 plus Unknown); no orphans.
- The 845 guest lines now point at -1, so the not-null test passes.
- The accepted-values test on cities passes.
All eight tests say PASS. Because test_results is a view, the same tests ran against the new tables without rewriting anything.
In dbt the same tests are one line each in a YAML file (unique, not_null, relationships, accepted_values), and dbt writes these queries for you. The ETL course sets that up in Module 5.
7. Practice
-- exercise: Write a not-null test for fact_sales.product_key: return the number of rows where it is NULL.
-- hint: COUNT(*) ... WHERE product_key IS NULL.
SELECT COUNT(*) AS null_product_keys
FROM fact_sales
WHERE customer_key = -1
-- answer:
SELECT COUNT(*) AS null_product_keys
FROM fact_sales
WHERE product_key IS NULL
-- exercise: Accepted values with a trap: return every store whose province is not one of ON, MB, AB or BC. The starting query looks right but returns nothing. Make NULL provinces count as failures too.
-- hint: province NOT IN (...) is NULL, not true, when province is NULL, so the row is skipped. Wrap it: COALESCE(province, '?') NOT IN (...).
SELECT store_id, store_name, province
FROM stores
WHERE province NOT IN ('ON', 'MB', 'AB', 'BC')
-- answer:
SELECT store_id, store_name, province
FROM stores
WHERE COALESCE(province, '?') NOT IN ('ON', 'MB', 'AB', 'BC')
-- exercise: Reconcile money, not just rows: return the total sales in fact_sales and the total completed line value in the source (quantity × unit_price × (1 − discount), rounded per line), side by side.
-- hint: Two scalar subqueries; round each line with ROUND(..., 2) before summing, as the fact does.
SELECT ROUND(SUM(sales_amount), 2) AS fact_sales
FROM fact_sales
-- answer:
SELECT (SELECT ROUND(SUM(sales_amount), 2) FROM fact_sales) AS fact_sales,
(SELECT ROUND(SUM(ROUND(i.quantity * i.unit_price * (1 - i.discount), 2)), 2)
FROM order_items i JOIN orders o ON o.order_id = i.order_id
WHERE o.status = 'completed') AS source_sales
-- exercise: Find dimension rows no fact uses: list customer keys in dim_customer with no rows in fact_sales.
-- hint: LEFT JOIN dim_customer to fact_sales and keep rows where the fact side is NULL.
SELECT d.customer_key
FROM dim_customer d
-- answer:
SELECT d.customer_key, d.customer_name
FROM dim_customer d
LEFT JOIN fact_sales f ON f.customer_key = d.customer_key
WHERE f.customer_key IS NULL
- A test is a query that returns bad rows.
- The five basics: unique, not null, relationships, accepted values, reconciliation.
- Each test catches a different kind of mistake.
- Put all tests in one results view and run it after every load.
New words: data test, unique test, not-null test, relationships test, orphan, referential integrity, accepted values, reconciliation.
Check yourself
Sources
- dbt Labs documentation, Data tests https://docs.getdbt.com/docs/build/data-tests
- The Kimball Group, Dimensional Modeling Techniques https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/
- Kimball, R. and Ross, M. (2013), The Data Warehouse Toolkit, 3rd edition, Wiley, chapter 19 (ETL quality screens)
Your ticks stay in this browser. Save them as a file on the course home.
Updated 2026-11-22. Northbay Retail is a made-up company; its data is generated in your browser. Queries run on your device and nothing you type is sent anywhere.