lesson 82 Warehouse & reporting projects · advanced

Automated star-schema quality tests

Test relationships and data quality in a star schema.

the idea

A quality test has a named expectation and a failure count. Zero means that expectation passed, not that all data is perfect. Check unique line IDs, valid measures, and both dimension relationships. Gate promotion on declared blocking rules; do not silently repair financial data.

tables in this lesson

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

dq_sales5 columns · 5 rows

line_idinteger	customer_keyinteger	product_keyinteger	quantityinteger	salesreal
1	1	1	2	24
2	999	1	1	12
3	2	999	1	80
4	2	3	0	0
1	1	1	2	24

all 5 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

SELECT 'duplicate_line_ids' AS test,COUNT(*) AS failures
FROM (
  SELECT line_id
  FROM dq_sales
  GROUP BY line_id
  HAVING COUNT(*)>1
)
UNION ALL
SELECT 'orphan_customers',COUNT(*)
FROM dq_sales s
LEFT JOIN dim_customer c ON c.customer_key=s.customer_key
WHERE c.customer_key IS NULL
UNION ALL
SELECT 'orphan_products',COUNT(*)
FROM dq_sales s
LEFT JOIN dim_product p ON p.product_key=s.product_key
WHERE p.product_key IS NULL
UNION ALL
SELECT 'invalid_quantity',COUNT(*)
FROM dq_sales
WHERE quantity IS NULL OR quantity<=0

Watch for: Failure counts may overlap on one row; summed failures are rule violations, not distinct rejected rows.

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 dq_sales;
```

Execute the transformation. A quality test has a named expectation and a failure count. Zero means that expectation passed, not that all data is perfect. Check unique line IDs, valid measures, and both dimension relationships. Gate promotion on declared blocking rules; do not silently repair financial data.

step-2.sql

SELECT 'duplicate_line_ids' AS test,COUNT(*) AS failures
FROM (
  SELECT line_id
  FROM dq_sales
  GROUP BY line_id
  HAVING COUNT(*)>1
)
UNION ALL
SELECT 'orphan_customers',COUNT(*)
FROM dq_sales s
LEFT JOIN dim_customer c ON c.customer_key=s.customer_key
WHERE c.customer_key IS NULL
UNION ALL
SELECT 'orphan_products',COUNT(*)
FROM dq_sales s
LEFT JOIN dim_product p ON p.product_key=s.product_key
WHERE p.product_key IS NULL
UNION ALL
SELECT 'invalid_quantity',COUNT(*)
FROM dq_sales
WHERE quantity IS NULL OR quantity<=0

Test another question. Failure counts may overlap on one row; summed failures are rule violations, not distinct rejected rows.

step-3.sql

WITH checks AS(
  SELECT 'duplicate_line_ids' AS test,COUNT(*) AS failures
  FROM (
    SELECT line_id
    FROM dq_sales
    GROUP BY line_id
    HAVING COUNT(*)>1
  )
  UNION ALL
  SELECT 'orphan_customers',COUNT(*)
  FROM dq_sales s
  LEFT JOIN dim_customer c ON c.customer_key=s.customer_key
  WHERE c.customer_key IS NULL
  UNION ALL
  SELECT 'orphan_products',COUNT(*)
  FROM dq_sales s
  LEFT JOIN dim_product p ON p.product_key=s.product_key
  WHERE p.product_key IS NULL
  UNION ALL
  SELECT 'invalid_quantity',COUNT(*)
  FROM dq_sales
  WHERE quantity IS NULL OR quantity<=0
)
SELECT SUM(failures) AS blocking_failures,SUM(failures)=0 AS can_publish
FROM checks