lesson 82 Warehouse & reporting projects · advanced

Automated star-schema quality tests

Test relationships and data quality in a star schema.

the idea

A data quality test has a name, an expectation and a failure count. Zero failures means that expectation held, not that the data is perfect. Check unique line ids, valid quantities and both dimension relationships, then gate publication on the blocking tests.

Dataset: Retail

The tables the challenges use, with their first 6 rows. Under each column name: the kind of value it holds, and which table it links to.

worked example

Run these steps in order to see the idea before you write your own query. The step marked “the pattern” is what the challenges ask for. Running the example does not count toward progress.

1. 1

   Find duplicated line ids. Run this. line_id should be unique; groups with COUNT(*) > 1 mean the same sale line arrived twice in the batch.the pattern

   example.sql Run

   sql›

   SELECT line_id, COUNT(*) AS copies
   FROM dq_sales
   GROUP BY line_id
   HAVING COUNT(*) > 1
   LIMIT 10;

2. 2

   Find facts missing a customer dimension. customer_key values with no dim_customer row are orphans—those lines fail the relationship test before publish.

   step-2.sql Run

   sql›

   SELECT s.line_id, s.customer_key
   FROM dq_sales s
   LEFT JOIN dim_customer c ON c.customer_key = s.customer_key
   WHERE c.customer_key IS NULL
   LIMIT 10;

3. 3

   Shape one quality test query. Each named test returns one row: test name and failure count. Zero failures means that rule passed, not that the batch is perfect.

   step-3.sql Run

   sql›

   SELECT 'duplicate_line_ids' AS test, COUNT(*) AS failures
   FROM (
     SELECT line_id
     FROM dq_sales
     GROUP BY line_id
     HAVING COUNT(*) > 1
   );

common mistake

Failure counts can overlap on one row; their sum counts rule violations, not distinct bad rows.