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. A fact row is one sales line; its surrogate keys come from looking up the existing dimensions, not from numbering rows again. Quality gates reject lines with unknown keys, non-numeric or non-positive quantities and invalid dates, and the measure is recomputed by a declared rule (quantity × catalog price).

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

   Count sales with unknown products. Run this. Lines whose product_id does not match dim_product are rejects—inner joins to the dimension drop them unless you count them first.the pattern

   example.sql Run

   sql›

   SELECT COUNT(*) AS unknown_products
   FROM bronze_sales b
   LEFT JOIN dim_product p ON p.product_id = TRIM(b.product_id)
   WHERE p.product_key IS NULL;

2. 2

   Preview dimension keys for facts. Gold facts must reuse customer_key and product_key from these tables, not invent new ids from Bronze row numbers.

   step-2.sql Run

   sql›

   SELECT customer_key, customer_id, country
   FROM dim_customer
   LIMIT 10;

common mistake

An inner join to the dimensions is itself a quality gate: lines with unknown customers or products disappear. Count the rejects so the batch reconciles.