Sabbir HossainData Engineer
System Note 04 · Enterprise Systems

Data Quality Recovery System

Bell Canada

SQLData QualityRoot Cause AnalysisETL
Live
What it is

The investigation and repair work that found why historical data was missing and wrong, then recovered it — 130,000+ records — and stopped it from happening again.

Why I built it

Reports only went back a month and quietly disagreed with the source systems, so nobody fully trusted the analytics.

What went wrong
  1. 01
    The 30-day blind spot
    Broke
    An external validator flagged ~28,000 mismatched records, and history only reached back about a month when it should have covered years.
    Why
    A staging load had a hardcoded 30-day lookback filter, silently trimming everything older than a month on every run.
    Fix
    Traced it to that one filter, then ran a full historical recast from January 2025 through an isolated off-hours flow so it would not crush the daily job. Coverage went from 1 month to 9+ months and 130,000+ records came back.
  2. 02
    3.5 million phantom rows
    Broke
    Joining events to the employee-hierarchy table multiplied rows — about 3.5 million duplicates — inflating every count built on top of it.
    Why
    The join flattened history with a MAX() subquery and keyed on a volatile mix of email and agent ID, so one event matched many hierarchy versions.
    Fix
    Switched to SCD Type 2 point-in-time joins (match the event to the hierarchy that was true on its date) with a stable composite key. The duplicates disappeared.
Outcome
130,000+corrected records
800%coverage expansion
Role periodJune 2025 – Present
Outcome130,000+ corrected records
A. Lineage graph (compact)
  • SmartPath (REST API)
  • Maximo (ERP)
  • IPACT (Billing)
  • LDAP (Directory)
  1. Ingest / Raw Landing
  2. Validate / Rules & Schemas
  3. Transforms / Standardize & Conform
  4. Canonical Trusted Dataset
↺ Feedback loop
B. Data diff — abridged
MetricBeforeAfter
Records corrected—130,000+
Analytical coverage1 month9+ months
Query latency (23M-row join)12 min2 min
Data incidents prevented—15+/mo
Debugging timebaseline−60%
Pipelines instrumented—12+

Net Improvement: 130,000+ records corrected

C. Reconciliation checks — abridged
CheckBeforeAfterStatus
Duplicate keys on retrypresent0✓
Historical attribution (SCD Type 2)driftedresolved✓
Count-by-stage proofmismatchreconciled✓
Idempotent re-runsnoneenforced✓
Event sequencingsequentialtwo-pass✓
Config-driven validationad hocautomated✓
D. RCA sequence — generalized
  1. 01DetectA count or latency mismatch trips an alert.
  2. 02ScopePin the affected sources, tables, and date ranges.
  3. 03Trace lineageFollow the data upstream to the first bad step.
  4. 04Repair / re-keyFix the transform or the load key at the root.
  5. 05BackfillReprocess history through an isolated flow.
  6. 06ReconcileProve counts match end to end.
  7. 07MonitorAdd a check so it cannot come back silently.

Impact — Resolved a data quality incident across historical pipelines. 130,000+ records corrected. Downstream metrics restored.

E. Data topology (map view)
  • SmartPath (REST API)
  • Maximo (ERP)
  • IPACT (Billing)
  • LDAP (Directory)
  1. Ingest Layer (Raw Landing)
  2. Transform & Standardize
  3. Schema & Rules Validation
  4. Backfill & Repair
  • Analytics (Dashboards)
  • Reporting (BI / SQL)
  • Ops Systems (Real-time)
Observability & monitoring across every stage
Sabbir Hossain

Evidence before confidence.

← Back to Build Notes