D8LooPFocus modeCODE WORKSPACE
SCD2 change boundaries
Detect changed dimension rows and compute close/open validity boundaries.
The SQL API will hydrate this workspace with the exercise-specific starter query and schema.
Sample input
dim_customer_history current rowsOnly is_current = 1 rows are candidates to close. Customer 101 is currently silver; 104 is currently gold.
| customer_id | tier | valid_from | valid_to | is_current |
|---|---|---|---|---|
| 101 | silver | 2025-01-01 | 9999-12-31 | 1 |
| 104 | gold | 2026-02-20 | 9999-12-31 | 1 |
| 105 | bronze | 2025-12-01 | 9999-12-31 | 1 |
customer_stage changed rowsThe staged tier for 101 and 104 changed; 105 did not.
| customer_id | tier | updated_at |
|---|---|---|
| 101 | gold | 2026-03-05 10:00:00 |
| 104 | platinum | 2026-03-05 12:30:00 |
| 105 | bronze | 2026-03-05 08:00:00 |
Expected output
Expected outputClose the old band on 2026-03-04 and open the new band on 2026-03-05 for changed customers.
| customer_id | old_tier | new_tier | old_valid_from | old_valid_to | new_valid_from | new_valid_to |
|---|---|---|---|---|---|---|
| 101 | silver | gold | 2025-01-01 | 2026-03-04 | 2026-03-05 | 9999-12-31 |
| 104 | gold | platinum | 2026-02-20 | 2026-03-04 | 2026-03-05 | 9999-12-31 |
Constraints
Load the live SQL exercise to inspect the full prompt, schema, starter query, and expected output.
Expected skills
SCD2, History, Validity windows
SQL
Loading...
AI evaluation
Run the SQL query to inspect preview rows.