milestone 8/12 Latest-state reporting versus SCD Type 2

Compare current-state reporting with preserved history.

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 foundational project keeps latest state. Type 1 overwrites attributes; Type 2 retains effective versions with distinct keys. Decide whether a report means current classification or classification at event time. Here customer_history uses nonoverlapping half-open validity ranges; NULL end means current.

SELECT customer_id,country
FROM customer_history
WHERE valid_to IS NULL;

data for this milestone