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 |
| Version | 1.0.0 |
| Time | 60 min |
| Scored checks | 18 |
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. |
Scoring checks the leftover tables or files, replay safety, and seeded variations — not the exact syntax you used. Check implementation is never shown to participants.
Signed-in workspace: Open in app