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 |
| Version | 2.0.0 |
| Time | 70 min |
| Scored checks | 17 |
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. |
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