The two dimension patterns every warehouse ends up with, written out properly: how each one is actually constructed, why LEAD and LAG do the whole job for Type 2, and why Type 1 needs a delete marker rather than a DELETE. Every table on this page is real output, not something I typed by hand.
Where this comes from. This is the approach I used on an insurance data warehouse, generalised. No employer, no product names, and the object names are neutral stand-ins. It follows on from the post about metadata-driven ELT: the change feed described below is what came out of the CDC layer there, and these two patterns are what consumed it.
That difference sounds small and it is not. It decides whether the question "what was this policy's excess in April" has an answer.
Neither pattern is built from a full snapshot of the source. Both are built from a change feed: one row per event, carrying the attributes as they were at that moment plus an operation code from the CDC layer.
Everything below runs against this. Two policies, eight events, deliberately awkward.
policy_reference cover_level excess broker op source_row_effective_date POL-100241 Standard 250 BRK-07 I 2026-01-04 09:12:00 POL-100241 Standard 250 BRK-07 U 2026-02-11 14:05:00 <- nothing tracked changed POL-100241 Premium 250 BRK-07 U 2026-03-02 08:41:00 POL-100241 Premium 500 BRK-12 U 2026-05-19 16:30:00 <- two attributes at once POL-100241 Premium 500 BRK-12 D 2026-06-08 11:02:00 <- deleted in source POL-100388 Standard 150 BRK-03 I 2026-01-20 10:30:00 POL-100388 Standard 150 BRK-03 D 2026-04-06 07:15:00 <- deleted POL-100388 Standard 150 BRK-19 I 2026-05-30 13:44:00 <- and then re-created
The three awkward cases are on purpose. A source system that touches a row without changing anything you care about. A hard delete. And a key that is deleted and then comes back, which is the case that quietly breaks most implementations.
The whole build is two window functions. LAG looks backwards to decide whether an event is worth recording at all. LEAD looks forwards to decide when each version stopped being true. Nothing else is needed, and in particular no cursor, no loop and no self join.
The trick that makes this clean is collapsing every tracked attribute into a single value, then comparing that one value with its predecessor. A hash is the obvious choice, and it means adding a tracked column later is a one-line change rather than a rewrite of the comparison.
WITH tagged AS (
SELECT
policy_reference,
cover_level,
excess_amount,
broker_code,
operation_code,
source_row_effective_date,
-- one value standing for the entire tracked state of the row.
-- deletes get their own sentinel so that a delete followed by an
-- identical re-create is still seen as a change.
CASE
WHEN operation_code = 'D' THEN '~DELETED~'
ELSE SHA2(CONCAT_WS('|', cover_level,
TO_VARCHAR(excess_amount),
broker_code))
END AS state_hash
FROM stg_policy
),
detected AS (
SELECT
t.*,
LAG(state_hash) OVER (
PARTITION BY policy_reference
ORDER BY source_row_effective_date
) AS prev_state_hash
FROM tagged t
),
kept AS (
SELECT *
FROM detected
WHERE prev_state_hash IS NULL -- the first event for this key
OR state_hash <> prev_state_hash -- or something actually moved
)
The sentinel matters more than it looks. Without '~DELETED~', a key that is deleted and re-created with identical attributes produces a delete event and then an insert whose hash equals the hash before the delete. The comparison sees no change, the insert is suppressed, and the dimension quietly claims the row never came back. That is the POL-100388 case in the sample data, and it is the reason it is in there.
Once you have only the events that represent a real change, the expiry of each version is simply the effective date of the next one. LEAD gives you that in a single pass.
bounded AS (
SELECT
k.*,
LEAD(source_row_effective_date) OVER (
PARTITION BY policy_reference
ORDER BY source_row_effective_date
) AS next_event_date,
LEAD(operation_code) OVER (
PARTITION BY policy_reference
ORDER BY source_row_effective_date
) AS next_operation_code
FROM kept k
)
SELECT
policy_reference,
cover_level,
excess_amount,
broker_code,
source_row_effective_date AS row_effective_date,
COALESCE(next_event_date,
'9999-12-31 23:59:59'::TIMESTAMP_NTZ) AS row_expiry_date,
next_event_date IS NULL AS is_current,
next_operation_code = 'D' AS closed_by_delete
FROM bounded
WHERE operation_code <> 'D' -- delete events close a version, they never open one
That last WHERE is the piece people get wrong. A delete is not a version of the row. It has no attributes worth storing. Its only job is to supply an expiry date to the version before it, which the LEAD has already done by the time the filter runs.
Run it against the sample feed and this is what comes out.
policy_reference cover excess broker row_effective_date row_expiry_date is_current closed_by_delete POL-100241 Standard 250 BRK-07 2026-01-04 09:12:00 2026-03-02 08:41:00 false false POL-100241 Premium 250 BRK-07 2026-03-02 08:41:00 2026-05-19 16:30:00 false false POL-100241 Premium 500 BRK-12 2026-05-19 16:30:00 2026-06-08 11:02:00 false true POL-100388 Standard 150 BRK-03 2026-01-20 10:30:00 2026-04-06 07:15:00 false true POL-100388 Standard 150 BRK-19 2026-05-30 13:44:00 9999-12-31 23:59:59 true false
Three things to notice. The February event produced no row, because nothing tracked changed. POL-100241 has no current version at all, because it was deleted and never came back. And POL-100388 has a hole in its history between April and May, which is correct, because during those weeks the policy genuinely did not exist.
A very common variant closes each version at the next effective date minus one second, so the windows do not touch. Do not do this. It is a bug waiting for a source system that starts recording milliseconds, and it hard-codes a grain into your data.
Set the expiry equal to the next effective date and make the join half open instead: greater than or equal to the start, strictly less than the end. Every instant belongs to exactly one version, with no gap and no overlap, at any precision.
SELECT p.policy_reference, p.cover_level, p.excess_amount, p.broker_code FROM dim_policy p WHERE :as_at_timestamp >= p.row_effective_date AND :as_at_timestamp < p.row_expiry_date
as at 2026-02-11 14:05:00 as at 2026-03-02 08:41:00 <- the exact boundary POL-100241 Standard 250 POL-100241 Premium 250 the new version wins, POL-100388 Standard 150 POL-100388 Standard 150 with no double count as at 2026-05-01 00:00:00 as at 2026-06-30 00:00:00 POL-100241 Premium 250 POL-100388 Standard 150 BRK-19 (POL-100388 absent, it was (POL-100241 absent, it was deleted on 6 April) deleted on 8 June)
The middle column is the test worth keeping. Query the exact instant of a change and you should get one row, the new one. If you get two, your intervals overlap and every measure joined through that dimension is inflated. If you get none, they have a gap.
Type 1 looks like the easy one and it has exactly one interesting problem: what to do when a row disappears from the source.
The wrong answer is DELETE. Delete the row and every fact that references it has a dangling key, you lose the ability to tell "this entity was removed" from "this entity was never loaded", and a re-run that misses the delete silently resurrects it. The right answer is to keep the row and flip a marker.
A merge that sees two source rows for the same target row will fail on Snowflake, which by default raises an error rather than picking one non-deterministically. That is a feature. Reduce the feed to the latest event per key before it reaches the merge.
WITH src AS (
SELECT
policy_reference,
cover_level,
excess_amount,
broker_code,
operation_code,
source_row_effective_date,
SHA2(CONCAT_WS('|', cover_level,
TO_VARCHAR(excess_amount),
broker_code)) AS attribute_hash
FROM stg_policy
QUALIFY ROW_NUMBER() OVER (
PARTITION BY policy_reference
ORDER BY source_row_effective_date DESC
) = 1
)
MERGE INTO dim_policy_current AS tgt
USING src
ON tgt.policy_reference = src.policy_reference
-- the row went away in the source. keep it, keep its attributes,
-- and record when and that it happened.
WHEN MATCHED AND src.operation_code = 'D'
AND tgt.is_deleted = FALSE
THEN UPDATE SET
tgt.is_deleted = TRUE,
tgt.deleted_datetime = src.source_row_effective_date,
tgt.dwh_updated_datetime = CURRENT_TIMESTAMP()
-- a normal change. note that this also clears the delete marker,
-- so a key that comes back from the dead needs no special handling.
WHEN MATCHED AND src.operation_code <> 'D'
AND (tgt.attribute_hash <> src.attribute_hash
OR tgt.is_deleted = TRUE)
THEN UPDATE SET
tgt.cover_level = src.cover_level,
tgt.excess_amount = src.excess_amount,
tgt.broker_code = src.broker_code,
tgt.attribute_hash = src.attribute_hash,
tgt.is_deleted = FALSE,
tgt.deleted_datetime = NULL,
tgt.source_last_changed = src.source_row_effective_date,
tgt.dwh_updated_datetime = CURRENT_TIMESTAMP()
WHEN NOT MATCHED AND src.operation_code <> 'D'
THEN INSERT (policy_reference, cover_level, excess_amount, broker_code,
attribute_hash, is_deleted, source_last_changed, dwh_updated_datetime)
VALUES (src.policy_reference, src.cover_level, src.excess_amount, src.broker_code,
src.attribute_hash, FALSE, src.source_row_effective_date, CURRENT_TIMESTAMP())
The attribute_hash comparison on the second branch is not an optimisation. It is what makes the load idempotent: re-running the same batch matches every row, finds every hash equal, and updates nothing, so dwh_updated_datetime stays where it was. Without that guard, every run rewrites every row and the audit column becomes a record of when the job last ran rather than when the data last changed.
Here is the same dimension after two consecutive runs of the real thing.
after the April run policy_reference cover excess broker is_deleted deleted_datetime dwh_updated POL-100241 Premium 250 BRK-07 false - 2026-05-01 02:00:00 POL-100388 Standard 150 BRK-03 true 2026-04-06 07:15:00 2026-05-01 02:00:00 after the June run policy_reference cover excess broker is_deleted deleted_datetime dwh_updated POL-100241 Premium 500 BRK-12 true 2026-06-08 11:02:00 2026-07-01 02:00:00 POL-100388 Standard 150 BRK-19 false - 2026-07-01 02:00:00 running the June batch a second time nothing changes. dwh_updated stays at 2026-07-01 02:00:00 on both rows.
POL-100388 goes out and comes back without a single line of code written for that case. It falls out of the ordinary update branch, because coming back from the dead is just a change like any other once the marker is part of the comparison.
Note also what Type 1 has lost. POL-100241 shows Premium and 500, and there is nothing in that table to say it was ever Standard or 250. That information exists only in the Type 2 version of the same dimension.
| Type 1 | Type 2 | |
|---|---|---|
| Rows per key | Exactly one | One per distinct state |
| Answers "what is it now" | Directly | With a current flag or an open interval |
| Answers "what was it in April" | No | Yes |
| Fact joins | On the business key | On the key valid at the fact's own date |
| Deletes | Marker on the single row | Close the open version, open nothing |
| Cost of a wrong hash | Missed updates | Missed or spurious versions, permanently |
| Reasonable default for | Reference data nobody backdates | Anything a report is ever restated against |
The honest rule I would give myself: if anybody will ever ask why last quarter's number moved, the dimension needs to be Type 2. Correcting a Type 1 dimension after the fact is not possible, because the information required to do it was thrown away at load time.
LEAD only sees the rows in its own window. If an event turns up out of order, you cannot just insert a version and carry on: the expiry date of the version before it is now wrong. The correct move is to rebuild the entire history for every affected key.
Watch what happens when one backdated event arrives for a key whose history was already complete.
a single late event lands: POL-100241 Premium 350 BRK-07 U 2026-04-14 10:00:00 POL-100241 Standard 250 BRK-07 2026-01-04 09:12 -> 2026-03-02 08:41 POL-100241 Premium 250 BRK-07 2026-03-02 08:41 -> 2026-04-14 10:00 <- expiry moved POL-100241 Premium 350 BRK-07 2026-04-14 10:00 -> 2026-05-19 16:30 <- new version POL-100241 Premium 500 BRK-12 2026-05-19 16:30 -> 2026-06-08 11:02
One inserted event, two rows changed. Any process that appends versions without re-deriving the neighbours will leave the March version claiming to be valid until May, and every point-in-time query in that six week window will return the wrong excess.
Two events on the same key at the same timestamp make both LAG and LEAD non-deterministic, so the same input can produce different dimensions on different runs. Add a tiebreaker to the ORDER BY and never rely on the timestamp alone.
ORDER BY source_row_effective_date, source_sequence_number, operation_code
If the source gives you no sequence number, a stable one derived from the file name and row position is better than nothing, because at least the result stops changing between runs.
It is tempting, since the operation is part of the event. It is also the fastest way to break idempotency: a delete followed by a re-insert of the identical row produces a different hash for the same business state, so the load creates a spurious version every time it replays. Hash the attributes. Handle the operation separately, as its own branch.
The marker is invisible to anything that does not filter on it. Every downstream view, extract and report has to decide explicitly whether it wants deleted rows, and the safe default is a view over the dimension that filters them out, so that using the raw table becomes the deliberate choice rather than the accidental one.
This is the Type 2 equivalent of the same trap. WHERE is_current = TRUE silently drops every entity that has been deleted, which is fine for "what do we hold today" and wrong for "how many policies did we cancel". Both questions are legitimate. The query has to say which one it is asking.
Compare a hash of the tracked attributes with its predecessor. Only a real change earns a version. Give deletes their own sentinel value.
Expiry is the next event's effective date, not that date minus a second. Join half open and every instant lands in exactly one version.
Flip a marker and keep the row. The un-delete then comes free, because clearing the marker is part of the ordinary update branch.
It is what makes a re-run a no-op, and it is what stops the audit column recording job runs instead of data changes.