lesson 73 Warehouse & reporting projects · advanced

Gold: dimensions, facts & quality gates

Build dimensions and facts with checks for trustworthy data.

the idea

Gold presents business entities and measures. Dimensional surrogate keys come from the existing dimension lookup, not a new ROW_NUMBER on every load. One fact row is one sales line. For this exercise reject unknown keys, invalid dates, and nonpositive/nonnumeric quantities; recompute sales from quantity × catalog price.

tables in this lesson

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

bronze_sales6 columns · 13 rows

row_idinteger · key	customer_idtext	product_idtext	quantitytext	salestext	order_datetext
1	C001	P01	2	24	2025-01-01
2	C002	P03	1	80	2025-01-02
3	C001	P02	1	45	2025-01-15
4	C003	P04	2	240	2025-02-01
5	C004	P03	2	160	2025-02-05

showing 5 of 13 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

dim_product5 columns · 4 rows

product_keyinteger · key	product_idtext	nametext	categorytext	pricereal
1	P01	Notebook	Stationery	12
2	P02	Desk lamp	Home	45
3	P03	Keyboard	Tech	80
4	P04	Headphones	Tech	120

all 4 rows

example

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

example.sql

CREATE TEMP TABLE built_fact AS
SELECT
  b.row_id AS sale_key,
  c.customer_key,
  p.product_key,
  CAST(b.quantity AS INTEGER) AS quantity,
  CAST(b.quantity AS INTEGER)*p.price AS sales,
  b.order_date
FROM bronze_sales b
JOIN dim_customer c ON c.customer_id=TRIM(b.customer_id)
JOIN dim_product p ON p.product_id=TRIM(b.product_id)
WHERE TRIM(b.quantity)<>''
  AND TRIM(b.quantity) NOT GLOB '*[^0-9]*'
  AND CAST(b.quantity AS INTEGER)>0
  AND b.order_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
  AND date(b.order_date,'+0 days')=b.order_date;
SELECT *
FROM built_fact;

Watch for: Recomputed sales is a declared business rule here, not a universally valid correction to source accounting data.

build it step by step

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

1
Inspect Bronze sales before Gold. Gold maps cleaned keys into dimensions and facts. Read bronze_sales fields that will become fact grain and measures.
step-1.sql
sql›
```
SELECT row_id, customer_id, product_id, quantity, order_date
FROM bronze_sales
ORDER BY row_id
LIMIT 8;
```

Assemble a fact with dimension keys. Join Bronze rows to dimension surrogates, cast measures, and materialize the fact. Quality gates check counts and orphans.

step-2.sql

CREATE TEMP TABLE built_fact AS
SELECT
  b.row_id AS sale_key,
  c.customer_key,
  p.product_key,
  CAST(b.quantity AS INTEGER) AS quantity,
  CAST(b.quantity AS INTEGER)*p.price AS sales,
  b.order_date
FROM bronze_sales b
JOIN dim_customer c ON c.customer_id=TRIM(b.customer_id)
JOIN dim_product p ON p.product_id=TRIM(b.product_id)
WHERE TRIM(b.quantity)<>''
  AND TRIM(b.quantity) NOT GLOB '*[^0-9]*'
  AND CAST(b.quantity AS INTEGER)>0
  AND b.order_date GLOB '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
  AND date(b.order_date,'+0 days')=b.order_date;
SELECT *
FROM built_fact;