A Green Data Quality Scorecard After 2,800 Misrouted Orders
Halbrook Outfitters runs a warehouse-fed fulfilment dashboard that 40 planners use daily. The data quality scorecard reports 99.2% completeness on 14 critical data elements and has been green for three quarters. Yet in Q3, 2,800 orders shipped to the wrong regional distribution centre because the warehouse attribute on the item master was blank; the ETL had been substituting the literal string 'UNASSIGNED' so the not-null check never fired. Separately, the accuracy dimension is measured by comparing the warehouse to the source ERP on the same nightly snapshot, which both derive from the same batch, so it reads 100% every night. The DQ lead wants to add 20 more rules to the scorecard and buy a cleansing tool. The VP of Supply Chain wants the number 'fixed' before the board deck goes out.
1. Diagnose why the scorecard stayed green. Name the distinct measurement failures and explain why each one is sufficient on its own to have hidden the defect.
Read the worked answer
Three independent measurement failures, each sufficient on its own:
- Default-value contamination. Completeness was implemented as
IS NOT NULL. The ETL substituted the literalUNASSIGNED, so the column was never null and the rule could never fail. A sentinel is still missing data. Completeness rules must carry a domain or allow-list check and treat known sentinels (UNASSIGNED,N/A,9999,1900-01-01) as null. The 2,800 misrouted orders were sitting inside a 99.2% green metric. - Circular validation. Accuracy compared the warehouse to the ERP on the same nightly snapshot. Both sides derive from one batch, so agreement is guaranteed unless the ETL itself corrupts the compare. That measures pipeline fidelity, not accuracy. An independent check needs a second path: staggered source reconciliation, manual sample audit, or comparison against an operational event such as a physical DC scan.
- No fitness-for-purpose measure. The dimensions reported were generic rather than tied to the decision planners make. There was no referential-integrity rule from item master to the distribution-centre dimension - precisely the rule that would have caught the failure.
Additional finding. A dashboard green for three quarters against an unreviewed rule set is itself a control weakness. No rule had ever been validated by injecting a known-bad record, which is the standard way to prove a rule can fail.
2. Given a fixed budget, how do you sequence remediation - rules, profiling, root cause, or tooling? And what do you tell the VP about the board deck?
Read the worked answer
Sequencing - cheapest and highest-yield first.
- Re-point the failing rules this week. Add sentinel checks and the referential-integrity rule to the 14 critical data elements. Cost is analyst hours, no licence. Re-run against six months of history to quantify exposure: how many item-master rows carried
UNASSIGNED, for how long, affecting which orders. - Profile before buying. Column profiling on the 14 elements reveals the actual distribution of sentinels and orphans. The proposed cleansing tool is likely solving a rule-definition problem; defer the purchase until profiling shows a volume that genuinely requires it.
- Replace the circular check with one independent reconciliation plus a quarterly manual audit of about 50 records. Do not add 20 generic rules; add rules mapped to known failure modes.
- Fix root cause, not the symptom. The sentinel was created by the ETL, so the durable fix is to fail the load or quarantine the row rather than substitute a string. Otherwise the tool cleans the same defect every night forever.
Board deck. Neither suppress nor simply restate. Show the corrected metric with a restated trend, state the definition change and which prior periods were restated, quantify the Q3 impact (2,800 orders), and give remediation dates with owners. A number that moves because the definition was fixed is defensible. A number that stays green after 2,800 wrong shipments is the one that becomes the story.