milestone 6/12 Automated star-schema quality tests

Test relationships and data quality in a star schema.

CRM + ERP Integration · Follow extraction reconciliation, authoritative integration, date quarantine, quality gates, stable keys, replay-safe loads, and atomic promotion. Finish with a customer-grain capstone and field-level lineage.

approach

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.

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

data for this milestone