SQLHardofficialBenchmark eligiblepro
SCD Type 2 customer history
Source customers overwrite plan_tier and address. The warehouse must keep Type 2 history: close the old version, insert a new surrogate key, keep exactly one current row per natural key. Type 1 overwrite of history fails.
| Field | Detail |
|---|---|
| Creator | PipelineArena · expert |
| Version | 1.0.0 |
| Time | 60 min |
| Credits | 5 |
| Completion | 0% · 0.0 rating |
| Hidden tests | 4 |
Objective
dim_customer is Type 2: contiguous windows, one is_current per customer_nk, history preserved.
| # | Success condition |
|---|---|
| 1 | Each version has valid_from < valid_to |
| 2 | Adjacent versions abut (old valid_to = new valid_from) |
| 3 | Exactly one is_current per customer_nk |
| 4 | Prior plan_tier still queryable for the old window |
| 5 | Unchanged attributes do not spawn a new version |
| # | Visible instruction |
|---|---|
| 1 | Compare source_customers to current dim_customer. |
| 2 | On a Type 2 attribute change, expire the current row and insert a new surrogate. |
| 3 | Do not overwrite historical rows. |
Hidden tests score the final tables or files, not the exact syntax you used. Test implementation is never shown to participants.
Signed-in workspace: Open in app