In an SCD Type 2 load, why does closing the old row and inserting the new one take two steps?

## The shape of a Type 2 change When a tracked attribute changes for a dimension member, the dimension must end up with two rows for that member's business key: the previous version, now closed (its end date set to the change point and its current flag cleared), and a new version, open, carrying the new attribute values and the new surrogate key. That is two distinct row operations — an UPDATE and an INSERT — driven by one incoming source row. This is why a Type 2 load never reduces to a plain upsert. An upsert answers the question "does a row with this key already exist?" and then either updates it or inserts it. A Type 2 change needs *both* branches for the same key at the same time. ## The two-statement pattern The common, readable implementation stages the changed set first, then runs two writes over it: ```sql -- staged: one row per key whose tracked attributes changed create table stg_changed as select s.* from stg_customer_hashed s join dim_customer d on d.customer_id = s.customer_id and d.is_current = 1 where d.hashdiff <> s.hashdiff; ``` Then close, then insert. Order matters. If you insert first and close second, a close predicate written as "the current row for this key" now matches *two* rows and closes the version you just created. Writing the close first, and scoping it to rows that are currently open, avoids the ambiguity. If you must insert first, scope the close by surrogate key or by effective-from rather than by the current flag. Both statements must sit inside one transaction. Halfway through, the member has either two open rows (insert succeeded, close did not) or zero (close succeeded, insert did not). Every downstream query that filters on the current row then returns duplicates or drops the member entirely — and it does so silently, because nothing errors. ## The doubled-source pattern The single-statement variant makes the source carry both operations. For each changed key you emit two staging rows: one whose join key matches the existing open version (so the merge's matched arm closes it) and one whose join key deliberately matches nothing (so the not-matched arm inserts it). Typically the join is on the business key and the "insert" row nulls that key out, or the union carries an explicit action column. This is compact and runs as one atomic statement, which is its main attraction. The cost is readability: the trick is invisible to the next reader, and a mistake in how the two halves are keyed produces the same two-open-rows defect with no obvious cause. Many teams prefer the two-statement form precisely because a reviewer can see what each write does. ## New members and unchanged members A full load has three populations, and the pattern must handle all three: - **New key, no row in the dimension** — insert one open version, no close. This is the only case a plain upsert handles by itself. - **Existing key, tracked attributes changed** — close plus insert, as above. - **Existing key, nothing changed** — write nothing. Skipping this case is what makes the load rerun-safe; a load that inserts unconditionally piles up identical versions. A fourth population — a key that has disappeared from the source snapshot — is a policy decision rather than a mechanical one. A source snapshot that omits a member does not necessarily mean deletion (it may mean a filtered extract), so most loads either leave the version open, or close it and insert a version carrying a deleted flag, so that history still shows when the member stopped appearing. ## What interviewers are testing The question looks like statement trivia but is really about whether you have run a load in anger. The tells of experience are: naming the transaction boundary without being prompted; noticing that statement order interacts with the current-flag predicate; and separating the three populations explicitly rather than hoping one clever statement covers them. The tell of inexperience is describing the load as "upsert into the dimension", which produces a Type 1 overwrite and quietly destroys the history the design exists to keep.