A Unified Data Quality Management Framework for Hybrid OLTP-OLAP Architectures: Metrics, Enforcement, and Cross-System Consistency Guarantees

Abstract

Organizations increasingly operate hybrid architectures in which an online transaction processing (OLTP) system of record is continuously replicated into a separate online analytical processing (OLAP) store for reporting and analytics, typically through a change-data-capture (CDC) pipeline. Industry surveys report that data quality incidents in such pipelines are frequent and costly: one 2023 survey found monthly data-incident counts rising year over year, average incident resolution time reaching roughly fifteen hours, and over half of respondents estimating that at least a quarter of revenue was affected by data quality problems. Existing data quality tooling, exemplified by frameworks such as Deequ and Great Expectations, is built around per-record, single-system validation and is well suited to catching malformed records at the point of ingestion. It is structurally unable to catch a distinct and, we argue, under-addressed class of fault: defects that are invisible at the level of any single well-formed record and become visible only when OLTP and OLAP state are compared directly, or when an aggregate is checked against an independent recomputation. We present the Unified Data Quality Framework (UDQF), which operationalizes Wang and Strong's four-category data quality model as six concrete, measurable metrics, completeness, validity, uniqueness, consistency, timeliness, and accuracy, and instruments the first three with local per-record checks and the last three with a cross-system reconciliation pass. We build a real reference implementation, a PostgreSQL OLTP store and a DuckDB OLAP store connected by an outbox-style changelog, and evaluate it against 2,040 order-lifecycle events carrying 200 discretely injected faults across five fault classes plus one systemic rollup defect, comparing three conditions: a blind-sync baseline, local validation only, and the full UDQF pipeline. The baseline detects none of the injected faults (0 percent). Local validation alone detects all completeness, validity, and uniqueness faults but none of the three cross-system faults, yielding 60.0 percent overall detection (McNemar exact p less than 1e-35 against baseline on the locally checkable classes). The full UDQF pipeline additionally detects 37 of 40 consistency faults, 40 of 40 timeliness faults, and the systemic accuracy defect, reaching 98.5 percent overall detection (McNemar exact p less than 1e-23 against local-only validation on the cross-system classes) while adding no measurable per-event sync overhead and under 250 milliseconds of reconciliation cost for the full event stream. We also report an unplanned, honest finding: because local quarantine changes which records reach OLAP, it measurably perturbs the very accuracy metric that only reconciliation can check, indicating that these six quality dimensions are not fully independent in a hybrid architecture and should be monitored together rather than as isolated checks. We release the schema, fault-injection harness, detection code, and evaluation artifacts to support replication.