C2 (3 points)
https://knobit.org/id/task/dq-rule-derivation/C2
Also as Turtle · JSON-LD · RDF/XML
| dcterms:license | https://creativecommons.org/licenses/by/4.0/ |
|---|---|
| rdfs:label | C2 (3 points) en |
| schema:hasPart | dq-rule-derivation C2a: closed and half-open validity periods in one mart reported |
| schema:hasPart | dq-rule-derivation C2b: overlap and gap checks written in each table's period convention |
| schema:hasPart | dq-rule-derivation C2c: balance fact referring to versioned customers by business key |
| schema:hasPart | dq-rule-derivation C2d: overwritten interest rate leaves a rule and a requirement uncheckable |
| schema:identifier | C2 |
| schema:position | 2 |
| schema:text | History: (a) 0.5 - the validity periods of the two versioned tables are reported as inconsistent: DIM_CUSTOMER.valid_to is the last valid day (closed), REF_FX_RATE.valid_to the first day of the next rate (half-open), so a join written for one returns two rows or none on the other; one convention is proposed for the mart; (b) 0.5 - BR-212 is checked on both tables, for overlaps and for gaps, each in its own table's convention (next valid_from = previous valid_to + 1 day on DIM_CUSTOMER, = previous valid_to on REF_FX_RATE); (c) 1 - FCT_BALANCE_DAILY.customer_id refers to the versioned DIM_CUSTOMER by business key alone: BR-209's check resolves the version valid on balance_date (exactly one), and a finding says that a join on customer_id returns one row per version - so AGG_CUSTOMER_COVERAGE, loaded through that join, multiplies a customer's balances by their number of versions - and proposes carrying the customer_key of the valid version; (d) 1 - DIM_DEPOSIT_ACCOUNT.interest_rate is overwritten by the merge and rate_changed_at and updated_at keep only the last change, so BR-210 cannot be checked and REQ-302 cannot be met: both are in not_checked and a physical finding asks for effective-dated rate history (and the notification date BR-210 needs). A check written over rate_changed_at fails this part. en |