milestone 3/6 Gold: dimensions, facts & quality gates

Build dimensions and facts with checks for trustworthy data.

Warehouse and Reporting · Profile raw CRM/sales records, build Silver and Gold tables in the disposable lab, explore the fact table, and publish repeatable reporting logic.

approach

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.

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;

data for this milestone