Stage 6: Design the physical data model
Knobit of Data modelling · K-623 · Skill
Recognises inconsistent validity periods in a versioned table
What it means
In a table with validity periods, two versions of one key valid at the same time (contradiction), consecutive identical versions (circumlocution), gaps nobody meant, or closed intervals in one table and half-open in another. A point-in-time join then returns two rows or none.
How it is assessed
Flags the deficient case and passes the acceptable one. Deficient: `dim_account` versions 2026-01-01..2026-06-30 and 2026-06-01..9999-12-31: a payment on 2026-06-15 joins both and is counted twice; two rows with `is_current = true` Acceptable: Half-open intervals `[valid_from, valid_to)` project-wide; a unique constraint on natural key plus `valid_from`; DQ rules for no overlap and exactly one current row per key (`PHY-015`, `PHY-016`)
Builds on
- Explains history strategies and validity-period conventionsK-658 · Knowledge