Skip to content
Sabbir HossainData Engineer
System Note 06 · 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 28K+ silently dropped 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. Usable history went from 1 month to 9+ months and the 28K+ dropped 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
28K+records recovered
9+months of usable history
Role periodData Engineer · June 2025 – Present
Outcome28K+ records recovered
A. Lineage graph (compact)
  • Internal REST API
  • Maximo (ERP)
  • Billing orders
  • 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 recovered—28K+
Usable history1 month9+ months
Duplicate hierarchy rows3.5Mremoved

Net improvement: usable history restored from 1 to 9+ months

C. Reconciliation checks (abridged)
CheckBeforeAfterStatus
Historical attribution (SCD Type 2)driftedresolved✓
Freshness & volume alerts—P10/P90 bands✓
D. Runbook excerpt (generalized)
  1. 01# Incident: rows multiplying on load
  2. 02# Runbook: dedup_recovery (generalized)
  3. 03
  4. 041. Confirm the duplication
  5. 05 - Count distinct vs total per key
  6. 06 - Sample the shape of the dupes
  7. 072. Find where it enters
  8. 08 - Trace lineage to the load step
  9. 09 - Diagnose the load-key mismatch
  10. 103. Re-key the load
  11. 11 - Upsert key vs primary index
  12. 12 - Make the write idempotent
  13. 134. Prove it before release
  14. 14 - Idempotency: run it twice
  15. 15 - Reconcile counts end to end
  16. 165. Stage the cutover
  17. 17 - Rehearse a reversible plan
  18. 18 - Monitor after go-live

Impact: Resolved a data quality incident across historical pipelines. Downstream metrics restored and validated.

Sabbir Hossain
← Back to Build Notes