Proving Data Pipeline Correctness With Contract Tests and Shadow Runs

Why “it runs” is not correctness

Most pipeline failures during cutovers are not hard errors. They are silent shifts: a join key changes type, a default timezone flips, a dedup rule subtly differs between SQL and Python, or a late-arriving event falls outside a window. The pipeline completes, dashboards update, and only weeks later someone notices numbers drift.

To reduce that risk, treat correctness as something you prove before cutover. Two practical techniques work well together:

• Contract tests that lock down schema and semantic expectations at component boundaries.
• Shadow runs where the new implementation runs in parallel and you diff results (SQL vs Python, old vs new) until the mismatch rate is understood and driven to zero or explicitly accepted.

Start with contracts at the pipeline boundaries

A contract test is a small, deterministic check that asserts what must be true about a dataset leaving one step and entering the next. Unlike broad “data quality” checks, contracts are intentionally strict and tied to invariants your downstream logic depends on.

Define the contract surface

Pick the handoffs where a subtle change causes cascading errors:

• Raw ingestion to normalized tables
• Normalized tables to feature or mart tables
• Mart tables to downstream exports (reverse ETL, ML features, billing)

Contract tests that catch real cutover bugs

Useful contracts are both structural and semantic:

• Schema and types: required columns present, stable names, expected types (including nullability).
• Primary key uniqueness: no duplicates for the declared grain.
• Referential expectations: foreign keys exist or at least meet a threshold (for event streams, you may allow a small orphan rate).
• Monotonicity and ordering assumptions: event timestamps non-decreasing within a session, or watermark moves forward.
• Business invariants: amounts are non-negative, status transitions follow allowed states, currency codes are in an allowlist.
• Distributional guardrails: not full anomaly detection, but simple checks like “country is not 95% NULL” or “top values remain plausible.”

The goal is not to test everything. The goal is to encode the assumptions that your SQL and Python transformations quietly rely on.

Use shadow runs to prove the new pipeline matches the old

Contract tests tell you whether each step is producing a valid shape. Shadow runs tell you whether the new pipeline is producing the same answers as the old one. This is especially important when you are migrating logic from SQL to Python (or the reverse), changing a warehouse, or re-platforming an orchestrator.

Shadow run scope and constraints

Shadowing is easiest when you keep these constraints explicit:

• Read-only inputs: both old and new read identical sources or a snapshotted copy.
• Separate outputs: write new results to parallel tables (e.g., orders_mart_shadow) or a separate schema.
• Same time boundaries: the same watermark, lookback window, and timezone settings.
• Deterministic sampling: if you can’t run on full volume, sample by stable keys (hash of user_id) rather than “latest N rows.”

Diffing SQL and Python results the right way

A naive row-by-row diff is brittle. Instead, layer diffs so you can localize disagreement quickly.

• Row counts and grain checks: count rows per partition and assert uniqueness at the expected grain.
• Aggregate diffs: compare sums, averages, and distinct counts on key measures, segmented by dimensions (day, region, plan).
• Set diffs on keys: compare the set of primary keys produced by each pipeline (missing vs extra).
• Field-level diffs: for keys present in both, compare columns with type-aware tolerances (timestamps rounded to seconds, floats with epsilon, trimmed strings).

When results differ, don’t immediately assume one side is “wrong.” Most disagreements are explainable and fall into a small set of categories.

Common sources of mismatch

• NULL semantics: SQL three-valued logic vs Python boolean evaluation and default fills.
• Timezones and truncation: DATE_TRUNC rules, local vs UTC midnight, daylight saving edges.
• Dedup rules: “latest by timestamp” ties, stable ordering, and whether you include soft-deleted rows.
• Join behavior: implicit casts in SQL, join key normalization, many-to-many explosions.
• Lookback windows: late-arriving events and whether they are reprocessed. If you’re diagnosing attribution-style issues, the same concept appears in marketing data; see the practical pitfalls in lookback windows and attribution settings.

Implement a shadow run workflow that engineers can maintain

Shadowing only works if it is repeatable and visible. A minimal workflow usually includes:

• Snapshot or pin inputs (by partition or watermark) so comparisons are stable.
• Run old and new on the same schedule.
• Materialize comparison artifacts: mismatch tables, aggregate diff tables, and a run summary.
• Fail fast on contract breaks and alert on diff thresholds.
• Track diff burn-down: a backlog of mismatch causes with owners and expected resolution dates.

Where an engineering platform helps

Shadow runs involve real code (SQL and Python), multiple steps, and tight observability requirements. A code-first platform like windmill.dev can be a practical fit because it lets teams run and version scripts, compose them into DAG-style workflows, and attach logs, alerts, and schedules without building bespoke orchestration glue. In practice, that means the diff job (and its tolerances) lives alongside the transformation code it validates, and engineers can review changes with built-in diffs and Git-based collaboration.

Cutover criteria that are actually defensible

The end state is not “no diffs ever,” because some differences are improvements. The end state is a documented decision that the diffs are understood and acceptable.

Concrete criteria teams use:

• Zero contract violations for a full run window (often 2–4 weeks of daily runs).
• Key coverage: the set difference on primary keys is zero (or bounded by a known late-arrival policy).
• Aggregate tolerance: segment-level deltas are below a threshold (for example, <0.1% on revenue by day and region) with explicit exceptions.
• Backfill proof: the new pipeline can reproduce historical partitions, not just incremental data.
• Operational readiness: clear on-call signals, runbooks, and rerun/backfill procedures.

If your organization is working toward compliance evidence and needs traceability from controls to system behavior, the same idea of “provable correctness” shows up there too; see turning SOC 2 evidence notes into a control-to-system traceability matrix for a structured workflow.

Keep shadow diffing after cutover

After cutover, keep a reduced form of shadowing for a while:

• Run a weekly comparison on a small sampled slice.
• Keep contracts permanently, especially at boundaries that feed billing, finance, or customer-facing reporting.
• Gate high-risk changes (join logic, dedup rules, windowing) behind the same diff harness you used pre-cutover.

This turns a one-time migration exercise into an ongoing safety mechanism for a pipeline that will continue to evolve.