SQLHardofficialBenchmark eligiblepro
Late-arriving facts
An order from last month arrives today. dim_customer has already Type-2 changed. The fact must use the surrogate key that was current on the order date (Kimball late-arriving fact), not the current row.
| Field | Detail |
|---|---|
| Creator | PipelineArena · expert |
| Version | 2.0.0 |
| Time | 70 min |
| Credits | 6 |
| Completion | 0% · 0.0 rating |
| Hidden tests | 3 |
Objective
fact_orders.customer_sk matches the dim_customer version where valid_from <= order_date < valid_to.
| # | Success condition |
|---|---|
| 1 | Late fact uses event-time surrogate, not is_current |
| 2 | On-time facts still match current versions |
| 3 | Unknown natural keys get a documented inferred member, not a dropped fact |
| 4 | Re-run does not duplicate fact_orders.order_id |
| # | Visible instruction |
|---|---|
| 1 | Join dim_customer on natural key AND order_date inside the validity window. |
| 2 | Do not join only WHERE is_current. |
| 3 | If the member is missing, insert the inferred placeholder described in README.md. |
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