Data quality & reconciliation with exception reporting and no silent failure

Data quality & reconciliation with exception reporting and no silent failure

PURPOSE

Reconciles data sources using stable identifiers (Pay Number, driving licence, driver card, and driver qualification card numbers), producing exception reports and “no silent failure” checks.

WHEN TO USE

• TRIGGERS:
  • Reconcile these two data sources and produce an exceptions report with reasons.
  • Match names and payroll numbers across files and flag anything that does not join.
  • Build a ‘no silent failure’ check that stops the pipeline if counts do not match.
  • Create a weekly variance report for missing records, duplicates, and date gaps.
  • Design a data quality scorecard with thresholds and red flags.
• DO NOT USE WHEN…
  • You need open-ended fuzzy matching without acceptance criteria.
  • There are no stable identifiers in any source.

INPUTS

• REQUIRED:
  • At least two datasets (CSV/XLSX) with Pay Number and/or driver document numbers.
  • Which fields must match (e.g., Name, expiry date).
• OPTIONAL:
  • Normalization rules (case, spaces, punctuation).
  • Thresholds for gates/scorecard (max % missing, etc.).
• EXAMPLES:
  • Payroll export + compliance register
  • Two weekly exports from different systems

OUTPUTS

• Reconciliation plan (matching rules, normalization, join strategy).
• Exceptions report spec (CSV columns + reason codes) and variance checks.
• Optional artifacts: assets/exceptions-report-template.csv + references/matching-rules.md. Success = every record is categorized (matched/missing/duplicate/mismatch/invalid) with an explicit reason; pipelines stop on anomalies.

WORKFLOW

1. Confirm sources and key priority (Pay Number → Driver Card → Driving Licence → DQC).
2. Normalize columns:
   • trim spaces; standardize case; strip common punctuation for document numbers.
3. Validate keys:
   • flag blanks/invalid formats; identify duplicates per source.