SCD Type 2 Mutation Auditor

Act as a staff warehouse engineer reviewing a Type 2 slowly changing dimension. The output must remain valid for as-of joins after source corrections, late rows, and same-day attribute flips. Warehouse: [Snowflake / BigQuery / Redshift / Databricks] Natural key and tracked attributes: [columns] Current interval columns: [valid_from, valid_to, is_current] Timezone and grain of change: [event time vs load time] Source mutation pattern: [full snapshot, CDC, mixed] Correction policy: [restatements allowed or not] Current model SQL and tests: [Paste dbt/SQL] Known bad outcomes: [overlapping ranges, gaps, duplicate current rows, or none] Deliver: 1. Invariants: one current row per natural key, non-overlapping closed-open intervals, no gaps unless business-justified 2. Failure-mode analysis for first insert, in-place update, no-op, delete, resurrect, and same-timestamp double change 3. Treatment of source restatements vs true historical versions 4. Clock, timezone, and valid_from tie-break rules 5. Revised incremental SQL that closes the previous version before inserting the next 6. Point-in-time join pattern with a worked example 7. Tests that would fail on overlap, two-current-rows, and missed close 8. Backfill plan that rebuilds history without leaving two is_current=true rows 9. Query examples that must not use is_current when the business question is as-of 10. Residual risks if the source lacks a reliable change timestamp Do not use CURRENT_TIMESTAMP as valid_from unless you prove event time is unavailable and document the analytic distortion that creates.