A 99.2% Scorecard and 11,400 Invalid Codes
Helix Pharma runs a patient-support data product on Databricks. Forty-two source feeds land in a bronze schema; 31 are loaded by IDMC mappings and 11 by Databricks notebooks written by a data science team. The monthly data-quality scorecard reported 99.2% conformance for drug codes, yet the finance reconciliation found 11,400 invalid NDC codes in the same period. The IDMC mappings do run data-quality rules, but rejected rows are written to a shadow table in a personal schema and nobody reviews it. Email and phone number masking is implemented as a view that only the CRM team uses; the analytics team queries the base table directly. Auditors have asked for evidence of who changed which rule, when, and what the impact was.
1. Diagnose how a 99.2% conformance score can coexist with 11,400 invalid codes in the same month. Give the checks you would run to prove which explanation is correct.
Read the worked answer
The scorecard likely measures a different population than the reconciliation did. Three gaps explain this pattern:
- Coverage. Only 31 of 42 feeds pass through IDMC; the 11 notebook feeds are outside the scorecard entirely. If invalid NDCs concentrate in notebook-fed sources, the scorecard never saw them.
- Timing. Rules run at mapping time on rows as loaded. Late-arriving corrections, reprocessed batches and direct table updates afterwards are never re-scored.
- Disposition. Rejects go to an unmonitored personal schema. A rule that writes failures aside without blocking or alerting turns a quality failure into a silent one, and the scorecard can end up recording "rule executed" rather than "data conformed".
Also question the definition. Conformance may be tested against an out-of-date reference table, or against a format pattern rather than a valid NDC list. 11,400 rows can pass a format check and still be invalid codes.
Checks to run: recompute the metric across all 42 feeds, against the silver table as it stood at month end, using the same reference-data version finance used. Compare against the published scorecard. Publish both numbers with their population, timestamp and reference version until they converge. The 0.8% is not the problem - the undefined denominator is.
2. Where should quality rules and masking be enforced so that the notebook path and the IDMC path behave identically for every consumer?
Read the worked answer
Principle: enforce once, at the table, not per pipeline. Anything implemented in a mapping is inherited only by consumers of that mapping's output; anything implemented in a view is inherited only by users of that view.
Masking. Move masking from the CRM view into Unity Catalog column masks on the base table's email and phone columns. Masks are applied at query time for every reader - notebooks, BI, ad hoc SQL - so the analytics team querying the base table gets the same result as the CRM team. Add row filters where jurisdiction restricts which patients a region may see. Then narrow SELECT on the base table to the service principals and engineering groups that genuinely need raw values, and check ownership: whoever owns the table is not masked, so ownership must not sit with an analyst group.
Quality. Pick one enforcement layer and reuse it:
- Delta CHECK constraints for invariants that must never be violated - null keys, valid ranges. These fail the write, which suits structural rules and not quarantine-style ones.
- Expectations in a pipeline framework for rules that should quarantine: write to a governed quarantine schema with alerting, never a personal one.
- IDMC mappings should call the same rule definitions, pushed down as SQL against the same reference tables, rather than re-implementing thresholds.
Notebook path. Move the 11 notebook feeds onto a shared rule library or a managed pipeline so both paths execute identical logic. A governance rule that only one tool enforces is a suggestion, not a control.
3. What evidence do you retain so an auditor can trace a rule change to its data impact without a week of manual reconstruction?
Read the worked answer
Auditors test whether you can show what a rule was, who changed it, when, and what happened when it fired - not whether a rule exists.
Retain:
- Rule definitions in version control, with rule ID, version, owner, effective date and the exact column or table targeted.
- Run-level evidence: run ID, job, timestamp, rule version, rows evaluated, rows failed, threshold and outcome. Persist these metrics into a governed Delta table rather than relying on job history that expires.
- Exception handling: who approved which exception, for which rows, until when, and what compensating control applied in the interim.
- Lineage from rule to column to downstream report, so an impact statement can be produced on demand.
- Access and change audit: Unity Catalog audit logs for grants and reads, IDMC run and admin logs for mapping and connection changes, both shipped to the SIEM with retention matched to policy.
Design point. Quarantine tables and quality metrics belong in governed schemas owned by a service principal. Personal-schema quarantine is exactly how the 11,400 rows went unseen.
Test it. Run a mock audit: pick one NDC rule and ask for its full history within one hour. If that takes a week, the evidence model is not finished - and the gap will be found by someone less friendly than the internal auditor.