Data Reconciliation and Operations Monitoring Dashboard

Two operational data sources represented the same business events differently. The task was to identify which valid source encounters had not reached the imported system, investigate evidence for likely causes and present the findings in a dashboard that an operations team could use.

• The source file was procedure-level.
• One encounter could contain multiple procedure rows.
• Names contained formatting variation.
• Dates appeared in multiple formats.
• The imported source contained duplicate encounter keys.
• A valid encounter key required patient, date and rendering provider.
• Invalid records needed to be separated from true missing encounters.

A reusable PostgreSQL view powered the dashboard, separating cleaning, validation and reconciliation logic so KPI and chart queries could read from a consistent surface.

• btrim
• lower
• regexp_replace
• Date parsing
• GROUP BY
• string_agg
• COUNT DISTINCT
• LEFT JOIN
• Evidence flags
• Separate KPI and chart queries

The pipeline converted 25,956 procedure-level rows into 6,612 valid encounters. Of those, 6,330 matched the imported data and 282 were missing, producing a 95.74% import rate.

The analysis found strong code-pathway evidence, including 203 missing encounters containing a NORCM marker and 205 containing at least one non-standard code marker. These were treated as evidence-based hypotheses rather than confirmed technical causes.

• Total encounter KPI
• Imported/matched KPI
• Missing KPI
• Import-rate KPI
• Root-cause or evidence-category chart
• Provider-level comparison
• Monthly trend
• Searchable 282-row exception table
• Data-quality indicators

• SQL-layer row-count checks
• Category totals reconciled to 282
• Provider totals reconciled to 6,612
• Monthly totals reconciled to 6,612
• First and last dates checked
• Dashboard table checked against query output

• No import/rejection logs
• No financial or claim-status information
• No proof of final production root cause
• Associations should not be presented as confirmed causality
• Public portfolio version must remain sanitised

• Formalise code eligibility and mapping rules
• Add pre-import validation
• Create a governed exception queue
• Retain detailed rejection logs
• Run automated daily reconciliation
• Add ownership and retry status
• Add alert thresholds
• Mask sensitive information
• Use role-based access

The public case study uses sanitised visuals and does not expose patient information, assessment files, private records or confidential source data.