The cases a working SCD2 load still gets wrong: a row that disappears from the source, a change that arrives after the change that follows it, and a fact whose dimension member does not exist yet.
A row that disappears.bronze.sap_kna1 is a daily full extract with no change timestamp, so deletion detection is 'present yesterday, absent today' — not the same statement as 'the customer was deleted'. The policy: close the open interval at :as_of and open nothing. History stays intact, the customer has no current version, and no deleted-flag column exists to forget.
Why
All three are found in production, because none of them occurs in test data anyone writes by hand. The deletion guard in particular is the difference between a bad morning and a bad quarter: closing most of a customer dimension is recoverable with time travel, and invisible until someone asks why revenue halved — by which time two more loads have run on top of it.
How
sqlDetected deletion — close, never delete
UPDATE gold.dim_customer d
SET _tech_valid_to = :as_of,
_tech_is_current = false
WHERE d._tech_is_current
-- the seeded fallback member is in no extract, ever. Without this line it
-- is the first row every deletion pass closes.
AND d.customer_sk <> 'UNKNOWN'
-- NOT EXISTS, not NOT IN: one NULL customer_sk in the staging table makes
-- NOT IN evaluate to NULL for every row, and the pass closes nothing.
AND NOT EXISTS (SELECT 1
FROM stg_customer s
WHERE s.customer_sk = d.customer_sk);
That pass belongs to the hand-written path, and to it alone — create_auto_cdc_from_snapshot_flow already closes an absent key itself, so running both closes the same interval twice at two different instants. On the declarative route the guard below moves in front of the snapshot's eligibility instead, and the queries here read the _tech_* view rather than the AUTO CDC target.
The guard in front of it matters more. A truncated extract — the SAP job died halfway, the file is 40 % of its usual size — otherwise closes 60 % of the dimension in one perfectly successful transaction.
sqlRefuse to apply deletions to an implausible extract
SELECT raise_error(
'sap_kna1 shrank by more than 10 % — refusing to apply deletions')
FROM (SELECT count(*) AS staged FROM stg_customer) s,
(SELECT count(*) AS live_rows FROM gold.dim_customer
WHERE _tech_is_current AND customer_sk <> 'UNKNOWN') t
WHERE s.staged < t.live_rows * 0.9;
-- the alias is live_rows, not current: CURRENT_DATE / CURRENT_USER and
-- friends make a bare 'current' a word you do not want to be arguing with.
A fact whose dimension member does not exist yet. A webshop order arrives for a customer SAP has not extracted. Because the key is a hash of the business key, the fact can always compute a customer_sk — that is the trap. A computed key with no matching dimension row is an orphan, an inner join drops it, and the revenue appears nowhere. Rule 7 chooses the visible failure instead:
sqlResolution with an explicit fallback — rule 7
SELECT l.order_id,
l.line_number,
coalesce(d.customer_sk, 'UNKNOWN') AS customer_sk,
o.order_ts
FROM silver.sales_order_line l
JOIN silver.sales_order o ON o.order_id = l.order_id
LEFT JOIN gold.dim_customer d
-- same recipe as make_sk(), or the join silently matches nothing
ON d.customer_sk = sha2(
concat_ws('||', coalesce(upper(trim(o.customer_id)), '~')), 256)
AND o.order_ts >= d._tech_valid_from
AND (o.order_ts < d._tech_valid_to OR d._tech_valid_to IS NULL);
The unknown member is a real row, seeded by the deployment in every dimension: customer_sk = 'UNKNOWN', _tech_valid_from far in the past, no end. Seeded by the load instead, it appears only after the first unresolvable fact, so the orphan test passes in dev and fails in a clean environment. It also turns an invisible defect into a number you can alert on:
sqlThe late-arrival monitor is one query
SELECT date_sk,
count(*) AS unresolved_lines,
sum(amount_eur) AS revenue_at_risk
FROM gold.fct_order_line
WHERE customer_sk = 'UNKNOWN'
AND date_sk >= current_date() - INTERVAL 7 DAYS
GROUP BY date_sk
ORDER BY date_sk;
Doing it
Seed the UNKNOWN member in the same deployment script that creates the dimension
Exclude the UNKNOWN member explicitly from anything that compares the dimension to a source extractThe deletion pass, the shrink guard, the row-count reconciliation. It is in none of them, so every one of those comparisons is wrong by one row in the direction that matters.
Put the shrink guard in front of every full-extract deletion pass, threshold in configuration
Alert when the UNKNOWN count crosses zero, not a thresholdThe day it starts rising is the cheapest day to fix the source.
Add the late-arrival and deletion cases to the fixture factoryOtherwise they will never be exercised.
Embedding it
Have them break it: truncate a fixture extract to 40 % and run the load against dwh_testThe dimension collapses, they recover it with time travel, and the guard gets written without you asking.
Ask what a GDPR erasure request does to an SCD2 dimension, and let the silence runThe answer is in the classification route; the discomfort is the teaching.
Put the team, not you, on the UNKNOWN alertWhoever gets paged makes sure the repair job exists.
Defaults
Detected deletions close the interval. No rows deleted, no flag column added
One mechanism closes intervals — the hand-written pass or the snapshot flow, never both
A shrink guard in front of every deletion pass driven by a full extract
One seeded UNKNOWN member per dimension, deployed as code, referenced by rule 7
Facts resolve as of order_ts, never as of load time
Gotchas
A truncated full extract applied as deletionsEvery job goes green, most of the dimension is closed, and the alarm comes from a human two days and two loads later.
Running the hand-written deletion pass on top of an AUTO CDC targetThe snapshot flow has already closed the absent key at the extract's sequence value; this pass closes it again at :as_of. Two closures, two different boundaries, and the no-overlap assertion fires on rows nobody edited.
A deletion pass that does not exclude the UNKNOWN memberIt is in no extract by definition, so the very first run closes it: rule 7's fallback has no current version, the orphan lines resolve to a row that is no longer valid at their order_ts, and the revenue you deliberately made visible goes back to being invisible. Nothing fails; the UNKNOWN monitor simply reads zero and looks like good news.
NOT IN against a staging column that can be NULLOne NULL customer_sk and the predicate is NULL for every row, so the deletion pass closes nothing at all — permanently, silently, and in the direction that looks like the system working. NOT EXISTS has no such behaviour.
Deleting the row instead of closing the intervalThat customer's history is gone, last year's revenue joins to nothing, and the restore is a time-travel exercise under pressure.
NULL instead of the UNKNOWN memberAn inner join deletes the revenue from the report; a left join produces a NULL group that BI renders as a blank label and readers take for a rounding line.
Creating the unknown member from the load rather than the deploymentThe orphan test then fails on release day, in the clean environment, for a reason nobody can reproduce locally.
Assuming unresolved facts fix themselvesfct_order_line carries no business key, so 'we will re-resolve later' means a permanent UNKNOWN bucket in the revenue report.