Feeding history tables

Why does a Type 2 history table constrain every earlier decision in the pipeline?

A slowly changing dimension (SCD) is a table that describes things which change slowly, such as a customer's address. There are two common styles.

Type 1: overwrite the old value. Only the current state is kept. It is state-convergent.
Type 2: keep every version of the row, each with a validity interval valid_from and valid_to. It is path-dependent.

customer_id | address  | valid_from | valid_to
51          | Pune     | 2026-01-05 | 2026-03-02   <- closed when the change arrived
51          | Mumbai   | 2026-03-02 | NULL         <- the current row (open)

The modeling choice (history wanted) propagates backwards through every decision here and constrains all of them.

Polling CDC cannot feed Type 2 correctly. Aliasing destroys the intermediate states you are trying to record. If a customer moved twice between two polls, one version is lost.
The envelope must carry before-images, or you cannot find and close the previous version's validity interval.
Order must be preserved per key, or intervals overlap or leave gaps.
The merge is no longer a simple upsert. It is close the open row plus insert the new row: two operations that must be atomic. So a single MERGE is not enough, and SCD implementations use a multi-statement transaction or a two-pass write.

BEGIN;
UPDATE dim_customer SET valid_to = :change_ts
 WHERE customer_id = :id AND valid_to IS NULL;                -- close the old version
INSERT INTO dim_customer (customer_id, address, valid_from, valid_to)
VALUES (:id, :new_address, :change_ts, NULL);                 -- open the new version
COMMIT;

Type 1 tolerates everything above. Type 2 tolerates none of it. The downstream model's history requirement determines the minimum viable capture fidelity. This decision is made at the modeling layer and paid for at the capture layer, usually by different people, which is why the mismatch is so common.

In one line: path-dependent models require path-complete capture; you cannot bolt Type 2 onto a polling pipeline.

Case, resolvedConstructed case · Tariq at Quillpost

Duplicate orders and a 4,000-file rewrite

MERGE failed on duplicate matches, a corrected customer id left an order under two customers, a 1.5% change rewrote almost all 4,000 files, a per-city MAX view stayed wrong after a delete, and a Type 2 address table built from polling had gaps and overlaps. Where it started: Apply →

Duplicate matches. He de-duplicates the staged changes: one row per key, the highest log position. The "multiple matches" error disappears for the right reason, and the engine is no longer guessing.
The moved customer. The change event carries a before-image. He rewrites key changes as a delete of the old key plus an insert of the new key. The orphan copies are removed by a one-time cleanup using the before-images.
The slow merge. He cannot reduce the files touched, because updates hit orders of every age. He switches the table to merge-on-read (delete markers plus new rows) and schedules compaction at night. Write cost drops, and he accepts that compaction and slightly slower reads are the new bill.
The max view. MAX has no inverse. For the per-city maximum he keeps, per city, the top 5 order values in a small state table, and re-scans the city when more than 5 deletes hit the top. For the join view he decides not to maintain it incrementally at all: 46 GB of state for a view that is read once a day is a bad trade, so the view is recomputed nightly from the already up-to-date tables.
The address history. He moves the customer table to log-based capture with before-images, preserves order per key, and writes the "close the open row, insert the new row" logic as one transaction. The nonsense date ranges disappear. For old data captured by polling, he marks the history before the switch date as "approximate" in the documentation.