To match an event to the dimension state valid at transaction time, you either store the dimension surrogate key directly in the fact table during ETL ingestion or join dynamically using the entity natural key and an event timestamp between valid date ranges.
Surrogate key lookup at load time
The cleanest approach resolves the dimension surrogate key during pipeline ingestion. When loading fact_sales, the pipeline matches the natural customer_id and order_timestamp against the dimension version valid at that exact moment. The fact row then stores customer_sk. Downstream BI queries simply execute an equi-join:
SELECT d.customer_tier, SUM(f.amount) FROM fact_sales f JOIN dim_customer d ON f.customer_sk = d.customer_sk GROUP BY d.customer_tier;
A short look at the benefits:
- Equi-joins on integer surrogate keys run significantly faster than range joins.
- Business intelligence tools generate clean SQL without date filtering logic.
Point-in-time joins with valid ranges
When late-arriving data or dynamic views require joining at query time, match on the natural key using half-open intervals:
SELECT
f.order_id,
d.customer_name,
d.city
FROM fact_orders f
JOIN dim_customer d
ON f.customer_id = d.customer_id
AND f.order_time >= d.valid_from
AND f.order_time < d.valid_to;Always use half-open intervals [valid_from, valid_to) with strictly less-than on valid_to. Using BETWEEN causes double matches whenever an event occurs on the exact boundary timestamp where one version ends and the next begins.
As-was vs as-is reporting
The date-bounded join reconstructs historical as-was reality, showing the customer address at order time. For current as-is reporting, bypass date logic and join on f.customer_id = d.customer_id AND d.is_current = TRUE, which groups all historical activity under the customer current location.