milestone 11/12 Protect fact grain when joining versions

Join historical versions without multiplying fact rows.

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

The fact grain is one sales line. Joining a customer to every historical version multiplies rows and totals. Choose the current version for current-state reporting or effective version at event time for historical reporting. Validate row counts as well as measures; DISTINCT does not repair a wrong join.

SELECT h.country,SUM(f.sales) AS total_sales
FROM fact_sales f
JOIN dim_customer c ON c.customer_key=f.customer_key
JOIN customer_history h
  ON h.customer_id=c.customer_id
  AND h.valid_to IS NULL
GROUP BY h.country;

data for this milestone