Data Quality, Reconciliation, And Observability

What's being tested

Interviewers are assessing your ability to engineer reliable, measurable systems that detect, triage, and (when appropriate) fix data disagreements between services. Expect to demonstrate end-to-end thinking: how you instrument code for observability, choose reconciliation algorithms that meet SLA/cost tradeoffs, and design alerting/runbooks so operators can act. Capital One cares because financial correctness, auditability, and recoverability are product-level requirements that must be enforced via sound engineering, not ad-hoc scripts.

Core knowledge

Reconciliation patterns: full-compare (materialize both sides and diff), incremental (per-window aggregates), and streaming (fingerprints/low-cardinality checks) each trade off cost vs latency and precision.
Fingerprinting / checksums: use deterministic aggregates (e.g., sum of (id, amount) and CRC64) to cheaply detect divergence; collisions probability must be quantified for chosen hash size.
Completeness and correctness metrics: define completeness = downstream_count / upstream_count, error_rate = failed/total, and latency lag = now - max(event_ts); pick absolute and relative thresholds for alerts.
Idempotency & deduplication: enforce idempotency keys at write boundaries; dedupe with unique constraints in `Postgres` or with de-dup buffers in streaming consumers; duplicates matter for counts and money.
Ordering & late-arriving data: handle out-of-order/late events with watermarks and configurable windows; document RPO for late arrivals and how they affect reconciliation windows.
Approximate algorithms for scale: use HyperLogLog for high-cardinality distinct counts, Bloom filter for membership tests, and reservoir sampling for representative checks when N > ~10M; full materialized diffs become impractical at high scale.
Observability primitives: emit counter/gauge/histogram metrics, structured logs, and distributed traces (OpenTelemetry); collect p95/p99 latencies and cardinality-aware tags to avoid tag explosion.
Monitoring & alerting design: prefer SLIs/ SLOs over raw thresholds; alert on symptom (e.g., sustained reconciliation failures, rising error_rate) and on signal (e.g., watermark lag > threshold). Route noisy alerts to paged vs low-priority channels.
Automated reconciliation vs human-in-loop: auto-repair (replay, patch) is ok for idempotent ops; for irreversible actions (financial transfers) prefer gated/manual remediation with audit logs.
State management & durability: store reconciliation state (last-checked watermark, diffs, retries) in a durable system (`Postgres`, object storage) with clear retention and eviction policies.
Sampling and testability: inject canary traffic, deterministic test events, and synthetic data to validate observability pipelines end-to-end; include schema-version checks in the pipeline.
Security & auditability: all reconciliation events and remediation actions need immutability and trace (who/when/what) for compliance.

Worked example — "Design a reconciliation service that compares transaction totals between two services"

First 30 seconds: clarify the data model (what is a transaction key and amount), volume (transactions per minute), required correctness (exact counts vs. ±0.1%), and acceptable latency for detection and recovery. Skeleton answer pillars: (1) ingestion: periodically read time-windowed aggregates from both sides (counts, sums, CRC), (2) compare: compute per-key diffs and a global checksum, (3) state & alerts: persist mismatches and emit metrics/alerts, (4) remediation: automated retry for transient failures, manual workflow for persistent mismatches. A key tradeoff: full record-level reconciliation is exact but expensive; choose fingerprinted-then-sample approach: if fingerprints mismatch, escalate to targeted full-compare for affected keys. Close by saying: if more time, add a reconciliation UI showing diff history, root-cause links to trace IDs, and automated backfill job templates with idempotent patch semantics.

A second angle — "Instrumenting real-time data freshness and completeness for a streaming pipeline"

Here the framing emphasizes streaming constraints: you must capture per-partition watermark lag, consumer offsets, and per-key completeness metrics. Emit watermark_lag and consumer_lag as gauges; expose counts of late events and reorders. Use OpenTelemetry for tracing an event through producers and consumers so you can link a reconciliation mismatch to a specific partition/offset. Cardinality pressure is acute: avoid per-event high-cardinality tags; instead aggregate per-service/partition and sample event-level traces only on error. Tradeoffs include sampling rate vs. ability to root-cause rare corruptions.

Common pitfalls

Pitfall: Confusing detection with resolution.
Many candidates propose only alerting on mismatches without a remediation plan; a robust design couples detection, triage metadata (example keys, traces), and safe automated remediation paths.

Pitfall: Relying solely on absolute deltas.
Absolute thresholds break across scales; prefer relative thresholds and percentiles (e.g., a 0.1% change or a statistical test) and distinguish transient blips from sustained drift.

Pitfall: Ignoring operational cost and cardinality.
Instrumenting everything with high-cardinality tags or tracing all events will balloon cost and noise; propose sampling, aggregation, and targeted tracing on anomalies.

Connections

Interviewers may pivot to adjacent topics like data engineering (ingestion guarantees, schema evolution, backfill orchestration) or SRE (SLO definition, alert fatigue reduction, oncall playbooks). Be ready to explain how reconciliation outputs feed runbooks and backfill jobs without designing the entire ingestion pipeline.