Point-in-Time (As-Of) Joins Against an SCD2 Dimension

Level 5: Level 5: Advanced & Company-Specific SQL for DE Interviewsmedium28 minsnowflake-warehousepoint-in-time joinas-of joinSCD2 historyeffective_from/effective_to range predicatelate-arriving fact

Join a fact to the dimension version that was valid at event time using a half-open effective range, not the is_current row.

History is worthless if you only ever read the current row

L4 built a Type-2 (SCD2) dimension: every time a customer attribute changes, you close the old row by setting its effective_to and open a new one, so the table keeps the full history with effective_from and effective_to ranges and an is_current flag. The point of keeping that history is to answer "what was true then", and that is exactly what most people get wrong. They join on is_current = 1, which staples today's attribute onto every historical fact. An order placed while the customer was in the east region gets mislabeled west because that is where they live now.

The as-of (point-in-time) join

Join the fact to the dimension version whose validity window contains the event date:

JOIN dim_customer d
  ON d.customer_id = o.customer_id
 AND o.event_date >= d.effective_from
 AND o.event_date <  d.effective_to

The half-open range (>= on the low end, < on the high end) is deliberate: a change that takes effect on a date belongs to the new version, and no event ever matches two versions. The same predicate handles a late-arriving fact: an order that lands after its dimension already changed still joins to the version that was valid on its event date, not the newest one.

Interview nuance: get the boundary right or you double-count. If both ends were inclusive, an event on a change date would match the old and the new version at once, and the fact would fan out to two rows. Half-open ranges are how point-in-time joins stay one-to-one.

In the warehouse this differs. Snowflake, BigQuery, and DuckDB offer an ASOF JOIN keyword that finds the most recent dimension row at or before the event, so you do not write the range by hand. SQLite has no ASOF JOIN, so you express the same result with the half-open range predicate. The correctness idea, attach the version that was valid then and not the current one, is dialect-independent.

Sample data for this example

CREATE TABLE dim_customer (customer_id INTEGER, region TEXT, effective_from TEXT, effective_to TEXT, is_current INTEGER);
INSERT INTO dim_customer VALUES
  (1, 'east', '2026-01-01', '2026-03-01', 0),
  (1, 'west', '2026-03-01', '9999-12-31', 1),
  (2, 'north', '2026-01-01', '9999-12-31', 1);
CREATE TABLE fact_orders (order_id INTEGER, customer_id INTEGER, event_date TEXT, amount INTEGER);
INSERT INTO fact_orders VALUES
  (100, 1, '2026-02-15', 50), (101, 1, '2026-03-10', 80), (102, 2, '2026-02-20', 30), (103, 1, '2026-03-01', 40);

Worked example (SQL)

-- region_as_of is what was true then; region_current is is_current. Order 100 shows the gap.
SELECT o.order_id, o.event_date,
  asof.region AS region_as_of,
  cur.region  AS region_current
FROM fact_orders o
JOIN dim_customer asof
  ON asof.customer_id = o.customer_id AND o.event_date >= asof.effective_from AND o.event_date < asof.effective_to
JOIN dim_customer cur
  ON cur.customer_id = o.customer_id AND cur.is_current = 1
ORDER BY o.order_id;

Apply

Your turn

The task this lesson builds to.

Write a query that attaches to each order the customer region that was current on the order's event_date, as (order_id, region), over fact_orders(order_id, customer_id, event_date, amount) and the Type-2 dim_customer(customer_id, region, effective_from, effective_to, is_current).

Join to the dimension version whose validity window contains event_date using a half-open range (>= effective_from AND < effective_to), not the is_current row. Alias the columns exactly as named.

3 hints and 1 automated check are waiting in the workspace.

Practice

Make it stick

A second problem on the same idea, so it survives past today.

Write a query that returns total revenue bucketed by the region effective at event time versus by the customer's current region, as (region, revenue_as_was, revenue_current), over the same fact_orders and dim_customer tables.

revenue_as_was sums amount grouped by the as-of region (the half-open range join); revenue_current sums amount grouped by the customer's is_current region. A region with no revenue under one attribution shows 0. Alias the columns exactly as named.

3 hints and 1 automated check are waiting in the workspace.