Recognises a reference to a versioned table by business key alone
A foreign key or join to a table with versions that names the business key and not the version, or a code resolved through today's reference data. Each row multiplies by the number of versions, or past rows change meaning when the reference data does.
Can AI do this?
AI does this well Most of the tested AI models master this. 8 of 10 models master it.
| Model | Tier | Probability of mastery | Status |
|---|---|---|---|
| gemini-4-argon | Flagship | 100% | Mastered |
| gemini-3.1-pro-preview | Flagship | 100% | 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 | 99% | Mastered |
| claude-sonnet-5-5 | Mid tier | 100% | Mastered |
| gemini-3.5-flash-lite | Low cost | 39% | Not shown |
| gpt-6-luna | Low cost | 80% | Not shown |
| 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: The reference and the version key it needs
How it is assessed: Flags the deficient case and passes the acceptable one. Deficient: fact_payment.account_identifier joined to dim_account on account_identifier: one payment becomes three; customer_segment_code resolved through today's ref_customer_segment, so a 2025 report changes after the segment boundaries are widened Acceptable: The fact references the version key valid at the event date (account_key); reference tables carry validity, and the fact stores the code valid at the time
Also known as: Reference to a versioned table by business key alone (physical model)
Related knobits
Builds on
Needed for
All 40 knobits of Design the physical data model.
Source: Review checklist 06-physical, row 24; S&W 15.7, 16.6; DDL 2014 ch. 13