SQL · medium · Reported at Airbnb

fact_orders has an order_ts. dim_customer is SCD Type 2 with valid_from/valid_to. Join each order to the customer attributes that were current when the order was placed.

Asked in Data Engineer interviews, in the SQL round.

Short answer

A range join, not an equality join: ON o.customer_id = d.customer_id AND o.order_ts >= d.valid_from AND o.order_ts < d.valid_to. The half-open interval is what stops an order matching two versions on a boundary. Joining on the customer key alone, or filtering d.is_current = true, silently reports today's attributes for historical orders, the classic SCD mistake, and the reason last year's revenue-by-segment number quietly changes.

Sample data

These tables are loaded into an in-browser engine on this page, so you can run your own answer against them. No account needed.

fact_orders

dim_customer

How to answer it

Definition

A Type 2 dimension keeps one row per version of a customer, each valid for a window of time. Joining on the key alone would multiply every order by the number of versions, so the join has to include the time.

Syntax

FROM  fact_orders o
JOIN  dim_customer d
  ON  d.customer_id = o.customer_id
  AND o.order_ts >= d.valid_from
  AND o.order_ts <  d.valid_to        -- half open: >= start, < end

Example

SELECT o.order_id, o.order_ts, d.customer_id, d.segment, d.country
FROM   fact_orders o
JOIN   dim_customer d
  ON   d.customer_id = o.customer_id
 AND   o.order_ts >= d.valid_from
 AND   o.order_ts <  d.valid_to;

Why half open

version  valid_from   valid_to     segment
      1  2025-01-01   2025-03-01   Standard
      2  2025-03-01   9999-12-31   Premium

An order at exactly 2025-03-01 00:00:00 matches version 2 only. Had both ends been inclusive, that order would match both versions and be counted twice. Every boundary instant belongs to exactly one version, and that is the property which keeps the fact row count unchanged.

Key points

• This is a range join, not an equality join. Say that phrase; it is what is being tested.
• Half-open intervals, >= valid_from and < valid_to, so boundaries cannot double count.
• The open version needs a real far-future valid_to such as 9999-12-31, not NULL, or it drops out of the comparison.
• Verify by comparing the fact row count before and after. It must be identical.
• Joining on is_current instead answers a different question: it restates history as if today's attributes always applied.

FAQ

• What if valid_to is NULL for the current row? Either COALESCE(valid_to, TIMESTAMP '9999-12-31') or an OR valid_to IS NULL branch. The sentinel is cleaner.
• Is a range join expensive? Yes, more than an equality join. Partitioning or clustering the dimension on the key helps.
• What if an order has no matching version? A LEFT JOIN and a check for NULLs finds the gaps in the dimension's coverage.

The 30-second answer

It is a range join rather than an equality join. I match on the customer key and also require the order timestamp to fall inside the version's validity window: greater than or equal to valid_from, and strictly less than valid_to.

The half-open interval is the part I would emphasise. If both ends were inclusive, an order landing exactly on a version boundary would match two rows and be counted twice, which shows up as revenue that quietly does not tie out. I would also make sure the current version carries a far-future valid_to rather than NULL, and I would check the fact row count before and after, because it must not change.

In the interview

• What they are checking: that you know an SCD2 join is on the time range, and that you handle the boundary.
• Common mistake: joining on the key and taking MAX(valid_from), which picks the newest version rather than the one current at order time.

Practice this for real

• Check your resume against a Data Engineer job description, free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews, two free, no card