Aggregating Multiple SCD-2 Attribute Timelines in BigQuery
Here’s another practical BigQuery SQL exercise 💡
Say you have an input SCD2-style table with [valid_from, valid_to) and key-value attributes. Now you want to determine which attributes were valid at the same time for a given grain (e.g. id).
To do this, we:
1️⃣ Build an anchor table of all change points (start and end dates), grouped by id.
2️⃣ Generate date ranges using LEAD() over the change points, so we know the next boundary.
3️⃣ Join back to the original table to find which rows were active within each [valid_from, valid_to) segment.
4️⃣ Aggregate the key-value pairs as ARRAY<STRUCT<key, value>> to preserve temporal context.
We can now see that, for example, for the period between [2023-01-05, 2023-01-08), for id = 2, B was true and A was false.
WITH anchor_dates AS (
SELECT id, valid_from AS valid_date FROM input_data
UNION DISTINCT
SELECT id, valid_to AS valid_date FROM input_data),
date_ranges AS (
SELECT
id,
valid_date AS valid_from,
LEAD(valid_date) OVER (PARTITION BY id ORDER BY valid_date) AS valid_to
FROM anchor_dates
)
SELECT
dr.id,
dr.valid_from,
dr.valid_to,
ARRAY_AGG(STRUCT(dt.key, dt.value)) AS attributes
FROM date_ranges dr
LEFT JOIN input_data dt ON dr.id = dt.id AND dr.valid_from < dt.valid_to AND dr.valid_to > dt.valid_from
WHERE dr.valid_to IS NOT NULL
GROUP BY dr.id, dr.valid_from, dr.valid_to
ORDER BY id, valid_from
Enjoyed this? Here are some related articles you might find useful: