milestone 3/12 Product validity with LEAD & half-open intervals

Define product validity using the next change date.

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

LEAD finds the next version start. Use [valid_from,valid_to) so a change date belongs to exactly one version; NULL end means current. This lab has unique starts per product. Latest-only output discards history; an as-of join needs all versions.

SELECT
  version_id,
  product_id,
  valid_from,
  LEAD(valid_from) OVER(PARTITION BY product_id ORDER BY valid_from,version_id) AS valid_to
FROM product_versions;

data for this milestone