Recognises history kept without its constraints checked

K-705 · Skill knobit · Activity: Derive data quality rules · Competence: Data modelling

A table with validity periods or a current-row flag, on a platform where no key can express "no two versions of one key overlap" or "exactly one current row per key", and no DQ rule checks either. Point-in-time joins then return 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.

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 missing overlap and one-current checks

How it is assessed: Flags the deficient case and passes the acceptable one. Deficient: dim_account with valid_from/valid_to, unique on (account_identifier, valid_from) only; two overlapping versions load, and a payment on the overlap is counted twice Acceptable: Two set checks: no overlapping periods per account_identifier, exactly one is_current = TRUE per account_identifier; dimension: consistency, threshold 100 %

Also known as: History kept, its constraints not checked (data quality rule)

Related knobits

Builds on

• K-623 Recognises inconsistent validity periods in a versioned table
• K-658 Explains history strategies and validity-period conventions
• K-356 Explains rule enforcement mechanisms and platform enforcement

All 31 knobits of Derive data quality rules.

Source: Review checklist 07-dq-rules, row 5; DDL 2014 ch. 13; this skill (06-physical.md)