How do you stop bad data from silently overwriting good data?
⚡ Pain Points We Tackled
A national leader in customized insurance, claims, and patient safety & risk solutions was losing trust in its own reporting. Reference data arrived from multiple operational systems of inconsistent reliability, and the ETL process had no way to distinguish a complete, trustworthy record from a thin one – so a detailed, verified attribute could be silently overwritten by a blank or low-confidence value from a weaker source. Duplicate and conflicting entries piled up in the warehouse, reporting errors followed downstream, and the nightly ETL run stretched past 30 minutes, delaying same-day analytics.
Our Approach
Azati designed a rules-based Survivorship Matrix, built directly into the Oracle SQL ETL logic, that decides – attribute by attribute – whether an incoming value should overwrite the existing record or be discarded in favor of the higher-priority source already in the warehouse. The decision logic runs inline as data loads, with no separate reconciliation stage.
Applied Methods and Practices
- Source Reliability Scoring: auditing every attribute from every upstream system and scoring it for reliability and completeness.
- Survivorship Matrix Design: rules-based logic embedded directly in the ETL layer to resolve conflicts at load time.
- Configurable Priority Weighting: rules that can be re-ranked as new sources come online, without touching the surrounding workflow.
- Pipeline Consolidation: removing redundant comparison and cleansing steps that had bloated the original ETL job.
Solution Features
- Inline Conflict Resolution: no manual reconciliation queue or nightly cleanup script – the warehouse never holds a degraded value even transiently.
- Configurable Rules Engine: priority logic adjustable as source reliability changes, without a re-engineering cycle.
- Faster, Leaner ETL: runtime cut from 30+ minutes to under 5, within the same infrastructure footprint.
- Audit-Ready Data Trail: a single, consistent view of policy and reference data defensible to auditors and regulators.