lesson 87 Warehouse & reporting projects · advanced

Protect fact grain when joining versions

Join historical versions without multiplying fact rows.

the idea

The fact grain is one sales line. Joining a customer to every historical version multiplies rows and totals. Choose the current version for current-state reporting or effective version at event time for historical reporting. Validate row counts as well as measures; DISTINCT does not repair a wrong join.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

customer_history5 columns · 3 rows

version_keyinteger · key	customer_idtext	countrytext	valid_fromtext	valid_totext
101	C001	United States	2024-01-01	2025-02-01
102	C001	Canada	2025-02-01	NULL
103	C002	United Kingdom	2024-01-01	NULL

all 3 rows

dim_customer4 columns · 5 rows

customer_keyinteger · key	customer_idtext	nametext	countrytext
1	C001	Alice Nguyen	United States
2	C002	Ben	United Kingdom
3	C003	Cara	United Kingdom
4	C004	Dev	India
5	C005	Elena	Canada

all 5 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

SELECT h.country,SUM(f.sales) AS total_sales
FROM fact_sales f
JOIN dim_customer c ON c.customer_key=f.customer_key
JOIN customer_history h
  ON h.customer_id=c.customer_id
  AND h.valid_to IS NULL
GROUP BY h.country;

Watch for: History only covers C001/C002 in this teaching table; these reports intentionally cover matching customers, not every customer.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Understand the source contract. Fictional extracts, load counts, invalid dates, versioned dimensions, refresh staging and a data catalog. The foundation project is latest-state; separate history and incremental labs demonstrate extensions.
step-1.sql
sql›
```
SELECT *
FROM customer_history;
```
2
Execute the transformation. The fact grain is one sales line. Joining a customer to every historical version multiplies rows and totals. Choose the current version for current-state reporting or effective version at event time for historical reporting. Validate row counts as well as measures; DISTINCT does not repair a wrong join.
step-2.sql
sql›
```
SELECT h.country,SUM(f.sales) AS total_sales
FROM fact_sales f
JOIN dim_customer c ON c.customer_key=f.customer_key
JOIN customer_history h
  ON h.customer_id=c.customer_id
  AND h.valid_to IS NULL
GROUP BY h.country;
```

Test another question. History only covers C001/C002 in this teaching table; these reports intentionally cover matching customers, not every customer.

step-3.sql

SELECT h.country,SUM(f.sales) AS total_sales
FROM fact_sales f
JOIN dim_customer c ON c.customer_key=f.customer_key
JOIN customer_history h
  ON h.customer_id=c.customer_id
  AND f.order_date>=h.valid_from
  AND (f.order_date<h.valid_to OR h.valid_to IS NULL)
GROUP BY h.country