Loadingone moment…
Sign in to run and submit your work
Reading is open to everyone. Running code and saving drafts need an account so your work is yours and comes back on your next visit.
or
CODE WORKSPACE
The customer dimension needs history: one row per version of a customer, with the dates that version was true between. The source sends a change event whenever any column moves, including columns nobody tracks, and it occasionally sends two events with the same effective date because a batch job corrected itself.
Write build_scd2(changes, key_field, tracked_fields). Return the history rows.
Function to write
build_scd2(changes: list[dict], key_field: str, tracked_fields: list[str]) -> list[dict]One row per version with the tracked fields, valid_from, valid_to and is_current, ordered by key then start.
How to approach it
Reduce the events to one state per instant first, then collapse the states that repeat.
Sample cases
+ 2 held back until you submit
a year of customer changes
A change that changes nothing, two changes on the same date, a delete, and a recreation after it.
Input
Argument 1
[
{
'customer_id': 'c1',
'effective_at': '2026-01-01',
'seq': 1,
'op': 'upsert',
'tier': 'silver',
'region': 'EMEA'
},
{
'customer_id': 'c1',
'effective_at': '2026-02-01',
'seq': 2,
'op': 'upsert',
'tier': 'silver',
'region': 'EMEA'
},
{
'customer_id': 'c1',
'effective_at': '2026-03-01',
'seq': 3,
'op': 'upsert',
'tier': 'gold',
'region': 'EMEA'
},
{
'customer_id': 'c1',
'effective_at': '2026-03-01',
'seq': 4,
'op': 'upsert',
'tier': 'platinum',
'region': 'EMEA'
},
{
'customer_id': 'c2',
'effective_at': '2026-01-15',
'seq': 1,
'op': 'upsert',
'tier': 'gold',
'region': 'APAC'
},
{
'customer_id': 'c2',
'effective_at': '2026-02-15',
'seq': 2,
'op': 'delete'
},
{
'customer_id': 'c2',
'effective_at': '2026-04-01',
'seq': 3,
'op': 'upsert',
'tier': 'gold',
'region': 'APAC'
}
]Argument 2
'customer_id'Argument 3
[
'tier',
'region'
]Returns
| customer_id | valid_from | valid_to | is_current | tier | region |
|---|---|---|---|---|---|
| c1 | 2026-01-01 | 2026-03-01 | False | silver | EMEA |
| c1 | 2026-03-01 | None | True | platinum | EMEA |
| c2 | 2026-01-15 | 2026-02-15 | False | gold | APAC |
| c2 | 2026-04-01 | None | True | gold | APAC |
no changes
An empty feed produces no history rather than one open interval.
Input
Argument 1
[] (empty list)
Argument 2
'customer_id'Argument 3
[
'tier'
]Returns
[] (empty list)
a single change
One change is one open interval, current, with no end date.
Input
Argument 1
| customer_id | effective_at | seq | op | tier |
|---|---|---|---|---|
| c9 | 2026-01-01 | 1 | upsert | free |
Argument 2
'customer_id'Argument 3
[
'tier'
]Returns
| customer_id | valid_from | valid_to | is_current | tier |
|---|---|---|---|---|
| c9 | 2026-01-01 | None | True | free |
Constraints
effective_at, then by seq.seq counts. A version that was true for zero time was never true.valid_from is the effective date of the change that created the version. valid_to is the effective date of the next version, and is exclusive.valid_to of None and is_current true. Every other version has is_current false.op of delete ends the current version and starts nothing. A later upsert starts a new version, so a key can have a gap in its history.valid_from, valid_to and is_current. Order by key, then by valid_from.Worked example
c1 has four changes and only three of them matter. The change on 2026-02-01 sets the tier to silver when it is already silver — the source emitted it because some untracked column moved — so it starts no version. Writing a row for it would show the customer changing on a day they did not, and every count of 'customers who changed tier this month' would be wrong.
The two changes on 2026-03-01 are the harder case. Taken in order they produce a gold version valid from 2026-03-01 to 2026-03-01 — an interval of zero length, which no as-of query will ever return, and which will nonetheless be counted by anything that counts rows. Only the last change at an instant survives, so the customer goes silver to platinum and gold never existed.
c2 is deleted on 2026-02-15 and recreated on 2026-04-01. That is a genuine gap: between those dates the customer did not exist, and an as-of join at 2026-03-01 must return nothing rather than the version either side of it.
What this tests
That history is a sequence of states, not a log of events. The three ways to get it wrong — writing a row per event, allowing zero-length intervals, and closing a delete into a new version — all produce a table that looks right and answers as-of questions incorrectly.
build_scd2(changes: list[dict], key_field: str, tracked_fields: list[str]) -> list[dict]Submit for review to find out what your query gets right, what it gets wrong, and how it compares with the best working query for this exercise.
This scenario runs a full workspace — editor, canvas and results side by side. It needs a laptop or desktop to be usable. Open this page on a bigger screen to start building.