Recognises inconsistent validity periods in a versioned table
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.
Can AI do this?
AI does this well Most of the tested AI models master this. 9 of 10 models master it.
| Model | Tier | Probability of mastery | Status |
|---|---|---|---|
| gemini-4-argon | Flagship | 100% | Mastered |
| gemini-3.1-pro-preview | Flagship | 99% | Mastered |
| gpt-6-astra | Flagship | 100% | Mastered |
| claude-fable-5-1 | Flagship | 100% | Mastered |
| gemini-3.8-flash | Mid tier | 100% | Mastered |
| gpt-6.1-sol | Mid tier | 100% | Mastered |
| claude-sonnet-5-5 | Mid tier | 100% | Mastered |
| gemini-3.5-flash-lite | Low cost | 40% | Not shown |
| gpt-6-luna | Low cost | 100% | Mastered |
| claude-haiku-5-5 | Low cost | 100% | Mastered |
Mastered means a probability of at least 90% that the model has this knobit, estimated from its graded answers. “Not shown” means the answers do not settle it either way. How the models are tested.
What it looks like in practice
Expected result: Overlaps, unintended gaps or mixed bounds, and the convention to apply
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)
Also known as: Overlapping, gapped or inconsistently bounded validity periods (physical model)
Related knobits
Builds on
Needed for
All 40 knobits of Design the physical data model.
Source: Review checklist 06-physical, row 23; DDL 2014 ch. 13; S&W 15.4