Problem Statement: Silent Corruption Is the Default in ETL
Frames the consistency checker as an independent verification plane between ETL execution and data consumption.
Problem statement
Design a data consistency checker that verifies, after every ETL run, that the destination faithfully represents the source: row counts reconcile, sampled and hashed data blocks match, constraints hold (no duplicate primary keys, foreign keys contained, values in domain), and any discrepancy triggers an automated alert or rollback. The system must also retain historical run statistics so slow drift — not just acute corruption — is detectable.
ETL pipelines fail quietly. A retry after a timeout can double-write a partition; a compaction race can drop late rows; a schema drift can null out a column; a timezone bug can shift a day boundary; a non-deterministic UDF can produce different output on re-run. Unlike a service API, a batch pipeline has no user staring at an error — the dashboard simply shows a plausible number. The checker is therefore the only component whose job is to ask: does the destination actually equal the source under the declared semantics?
Why this is a distinctive design problem
The checker sits between two moving systems and must compare them without being fooled by their motion. Source OLTP keeps accepting writes while the destination is loading; the warehouse may be compacting while the checker reads. A naive compare reads two live systems and reports phantom mismatches. The design therefore separates verification semantics (what equal means), isolation mechanics (which snapshots are compared), execution economics (how to verify billions of rows without doubling compute cost), and response policy (alert versus quarantine versus rollback). Each is an independent subsystem with its own consistency and latency requirements.
Public evidence versus design assumptions
Public tooling proves the category is operationally real. Amazon open-sourced Deequ, a Spark-based library that computes data-quality constraints at what the AWS blog describes as billions-of-rows scale inside Amazon data lakes. eBay open-sourced Apache Griffin, born from eBay's need to validate data moving between Hadoop and operational stores, supporting both batch and streaming consistency. Google ships TensorFlow Data Validation inside TFX, computing Beam-based statistics and schema anomaly detection over production ML data. Netflix popularized Apache Iceberg, whose snapshot isolation makes reproducible source/destination reads a first-class primitive. LinkedIn open-sourced ThirdEye for seasonal anomaly detection over metric time series. These are cited public descriptions; every numeric scale, SLO, or retention figure below that is not cited is an explicit design assumption for a fictional mature platform.
The four architectural planes
- Orchestration plane: run registry, commit events, check-suite compilation, scheduling, leases.
- Verification plane: distributed executors computing counts, samples, block hashes, constraint checks against pinned snapshots.
- Response plane: verdict aggregation, alerting, quarantine, rollback orchestration, reconciliation ledger.
- Learning plane: historical statistics, baselines, drift detection, dashboards, policy tuning.
A strong interview answer keeps these planes separate: the learning plane may lag, but the response plane must act on a verified verdict within seconds.
Key Highlights
- •ETL corruption is silent by default; the checker is the only component whose job is source-destination equivalence.
- •Verification semantics, snapshot isolation, execution economics, and response policy are four independent subsystems.
- •Deequ, Apache Griffin, TFDV, Iceberg, and ThirdEye prove the category with real published technology.
- •Comparing two live systems produces phantom mismatches; every comparison must pin explicit snapshots.
- •The learning plane may be eventually consistent; the response plane must be fast and authoritative.
Section Rescue Kit
Buzzwords to use:
Safe statements:
- "Before drawing components, I will define what equal means and which snapshots are compared."
- "The checker observes commits and reads pinned snapshots; it never mutates data directly."