Reconciliation & Validation

Reconciliation vs validation

This is one of the three named topics on the loop, and it's the one candidates hand-wave most. "I'd add some data quality checks" is a fail. The interviewer wants to know exactly what you'd check, how you'd stop a bad load, and how you'd prove to a finance stakeholder that the warehouse agrees with the system of record. Two distinct disciplines sit underneath that, and conflating them is the first mistake:

• Validation asks "is this data well-formed and plausible on its own terms?" — no nulls in the primary key, amounts non-negative, status in an allowed set, row counts within a normal band. It's intrinsic. You can run it knowing nothing about any other system.
• Reconciliation asks "does this data agree with the source of truth?" — does sum(amount) in the warehouse match the payments provider's daily settlement total, row for row, penny for penny. It's relational. It needs a second, trusted reference to compare against.

A dataset can pass every validation test and still be wrong: dropped ten thousand rows on ingest, all of them individually valid. Only reconciliation catches that. Conversely, reconciliation totals can tie out while individual rows are garbage (two errors that cancel). You need both, and you need to say so out loud.

The six data-quality dimensions

When an interviewer asks "how would you think about data quality here?", a vocabulary beats a list of ad-hoc checks. The standard six dimensions give you a checklist you can walk without missing a class of defect:

Map each dimension to a concrete mechanism and you've turned "data quality" from a buzzword into an audit. Note that accuracy is the only one you generally can't test in isolation — it's where reconciliation lives. Timeliness ties directly into the freshness SLIs in Chapter 07.

Source-to-target reconciliation

Reconciliation is a comparison between two independent counts of the same thing. The design questions are: what do you compare, at what grain, how often, and with what tolerance.

• Row counts — cheapest signal. Count source rows by day vs target rows by day. Catches wholesale drops and duplications.
• Control totals / aggregate recon — sum a monetary or quantity column on both sides (sum(amount), count(distinct order_id)). Catches partial loss that row counts miss (right number of rows, wrong values).
• Checksums / hash-diff — hash each row's business columns, aggregate the hashes per partition (e.g. sum(hash) or a bit-xor), and compare. Detects any field-level drift without shipping the full dataset across systems — the workhorse for column-level correctness at scale.
• Financial reconciliation — the strict form: internal consistency (sum(debits) = sum(credits)) and external tie-out (warehouse settlement total = provider's reported total). Tolerance here is often exactly zero.

Sampling vs full, cadence, tolerance

Full reconciliation compares every partition; it's authoritative but can be expensive to pull from an OLTP source. Sampling (recon a random subset of days, or a statistical sample of keys) is a cheaper continuous signal with a periodic full recon as backstop. Cadence follows the money: financial pipelines reconcile every load and often re-reconcile a trailing window (late data mutates prior days — see Chapter 04); softer analytics can reconcile daily or weekly. Tolerance thresholds encode how exact the domain demands: revenue is usually a hard = 0 break; a distribution metric might tolerate ±0.5%. Set them explicitly per metric — an implicit tolerance of "looks about right" is not reconciliation.

Here's a recon query that compares source and target by day, computes absolute and relative variance, and flags anything outside tolerance. This is the kind of thing you'd whiteboard and then productionize as an audit table or a dbt test:

-- Source-to-target reconciliation for the orders pipeline, by event date.
-- Compares row counts AND a monetary control total, and flags breaks
-- that exceed a per-metric tolerance. Runs after each load; a non-empty
-- result set with status='BREAK' is the circuit-breaker signal.

with source as (
    select
        order_date,
        count(*)                          as src_rows,
        count(distinct order_id)          as src_orders,
        sum(amount_cents)                 as src_amount_cents
    from raw.orders_source          -- e.g. a Fivetran/CDC landing of the OLTP table
    where order_date >= current_date - interval '7 days'   -- trailing recon window
    group by order_date
),

target as (
    select
        order_date,
        count(*)                          as tgt_rows,
        count(distinct order_id)          as tgt_orders,
        sum(amount_cents)                 as tgt_amount_cents
    from analytics.fct_orders
    where order_date >= current_date - interval '7 days'
    group by order_date
),

recon as (
    select
        coalesce(s.order_date, t.order_date)                        as order_date,
        s.src_rows,   t.tgt_rows,
        s.src_amount_cents, t.tgt_amount_cents,
        coalesce(t.tgt_rows,0)         - coalesce(s.src_rows,0)     as row_diff,
        coalesce(t.tgt_amount_cents,0) - coalesce(s.src_amount_cents,0) as amount_diff_cents,
        -- relative variance guards against divide-by-zero on empty days
        case when coalesce(s.src_amount_cents,0) = 0 then 0
             else abs(coalesce(t.tgt_amount_cents,0) - s.src_amount_cents)
                  / s.src_amount_cents::float
        end                                                          as amount_rel_var
    from source s
    full outer join target t on s.order_date = t.order_date
)

select
    order_date,
    src_rows, tgt_rows, row_diff,
    src_amount_cents, tgt_amount_cents, amount_diff_cents, amount_rel_var,
    case
        when row_diff <> 0            then 'BREAK'   -- counts must match exactly
        when amount_diff_cents <> 0    then 'BREAK'  -- money: zero tolerance
        else 'OK'
    end as status
from recon
where row_diff <> 0 or amount_diff_cents <> 0    -- only surface breaks
order by order_date;

Break investigation

A break is the start of an investigation, not the end. The disciplined path: (1) localize — which day, which metric, which direction (target high ⇒ duplication; target low ⇒ drops)? (2) bisect the pipeline — re-run the same recon at each stage (raw vs staging vs mart) to find the layer where the variance appears. (3) classify — is it a genuine data loss, a late-arriving-data timing artifact (source moved after your snapshot), a dedup bug, or a definitional mismatch (source counts refunds, target doesn't)? Timing artifacts are the most common false alarm, which is exactly why you recon a trailing window and re-check rather than paging on a single-run diff.

Testing layers

Reconciliation is one layer. Around it sits a stack of tests, cheapest and most local first. Name the layers as a pyramid — it shows you know where each tool earns its place:

dbt tests come in two flavors. Generic tests are reusable and attach to columns in YAML; singular tests are a hand-written SQL query in tests/ that must return zero rows to pass. Packages extend the vocabulary: dbt_utils adds unique_combination_of_columns, recency, equal_rowcount; dbt_expectations ports Great Expectations' assertions (value ranges, distributions, regex, column types) into dbt. Here's a schema.yml showing generic tests plus dbt_expectations for shape, and a custom generic test you'd define once and reuse:

version: 2

models:
  - name: fct_orders
    description: One row per order at order grain.
    # A model-level contract: enforced at build time, not just tested after.
    config:
      contract:
        enforced: true
    columns:
      - name: order_id
        data_type: varchar
        constraints:
          - type: not_null
          - type: primary_key
        data_tests:
          - unique
          - not_null
      - name: amount_cents
        data_type: integer
        data_tests:
          - not_null
          # bespoke assertion from dbt_expectations: no negative money
          - dbt_expectations.expect_column_values_to_be_between:
              min_value: 0
              strictly: false
      - name: status
        data_type: varchar
        data_tests:
          - accepted_values:
              values: ['pending', 'paid', 'refunded', 'cancelled']
      - name: customer_id
        data_tests:
          # relationships = referential integrity to the dimension
          - relationships:
              to: ref('dim_customers')
              field: customer_id
    # model-level test: row volume shouldn't collapse or explode run-to-run
    data_tests:
      - dbt_expectations.expect_table_row_count_to_be_between:
          min_value: 1000
          max_value: 5000000
      # custom generic test defined below, reused here
      - reconciles_to_source:
          source_relation: source('oltp', 'orders')
          measure: amount_cents
          tolerance: 0

{# A custom generic dbt test. Returns rows only when target disagrees with #}
{# source beyond tolerance -- any returned row fails the test (and, wired  #}
{# into a build gate, blocks promotion). Reusable across any fact table.   #}

{% test reconciles_to_source(model, source_relation, measure, tolerance=0) %}

with target as (
    select sum({{ measure }}) as target_total
    from {{ model }}
),
source as (
    select sum({{ measure }}) as source_total
    from {{ source_relation }}
)
select
    source_total,
    target_total,
    abs(target_total - source_total) as variance
from target
cross join source
where abs(target_total - source_total) > {{ tolerance }}

{% endtest %}

dbt unit tests (GA in dbt 1.8+) are different in kind: they feed fixed mock input rows into a model's SQL and assert exact output rows, so you can test transformation logic — a tricky window function, a fiscal-week bucketing, a dedup rule — in CI without real data. Use them for the gnarly logic; use data tests for the runtime data. Outside dbt, Great Expectations (expectation suites + data docs), Soda (SodaCL checks, good for warehouse-native monitoring), and Deequ (Spark/Scala, constraint suggestion + verification at big-data scale) cover the same ideas for non-dbt or lakehouse stacks. Contract tests sit at the boundary and are the subject of the next section.

Data contracts

A data contract is an explicit, versioned agreement between a data producer and its consumers. It's the L4 move: instead of downstream teams reverse-engineering your table and silently breaking when you rename a column, the interface is declared and enforced. A real contract covers three things, not just schema:

• Schema — column names, types, nullability, the grain, the primary key. The part everyone remembers.
• Semantics — what the columns mean. Is amount gross or net? Cents or dollars? Does status='refunded' mean fully or partially? Allowed value sets, units, and definitions live here — this is where most "the data is wrong" incidents actually originate.
• SLA — freshness (updated by 6am daily), completeness (≥ 99.9% of source rows), availability, and a deprecation/change policy (N days' notice, versioned rollout).

The contract is only real if it's enforced in two places: at CI, so a producer's PR that breaks the schema fails before merge (dbt's contract: enforced: true does exactly this — the build errors if the model's output diverges from the declared columns/types); and at ingest, so a producer emitting malformed events is rejected or quarantined at the door rather than corrupting the warehouse. A schema registry (Confluent, or JSON-Schema/Protobuf checks) enforces the event side; dbt contracts and CI enforce the transform side.

Write-Audit-Publish & circuit breakers

Tests that run after you've already overwritten the production table are a smoke alarm that goes off after the house is ash. The fix is to make validation a gate, not a report. Write-Audit-Publish (WAP) is the canonical pattern:

1. Write — build the new data into a staging location (a temp/branch table, a separate partition, or an Iceberg/Delta branch) that consumers can't see.
2. Audit — run all validation and reconciliation checks against the staged data.
3. Publish — only if the audit passes, atomically swap the staged data into production (partition swap, ALTER TABLE ... SWAP, view repoint, or Iceberg branch fast-forward). If it fails, the swap never happens; production keeps serving the last-known-good data.

The publish step must be atomic — an all-or-nothing pointer flip — so consumers never see a half-written table. The check that decides whether to publish is the circuit breaker: a failed critical check halts promotion. Here it is as an Airflow flow; the shape is identical whether you orchestrate it in Airflow, Dagster, or a dbt build gate:

from airflow.decorators import dag, task
from airflow.exceptions import AirflowFailException
from datetime import datetime

@dag(schedule="@daily", start_date=datetime(2026, 1, 1), catchup=False)
def orders_wap():

    @task
    def write_staging(ds=None):
        # Build into an isolated staging table consumers cannot read.
        run_sql(f"""
            create or replace table analytics.stg_fct_orders as
            select * from transform.fct_orders_build where order_date = '{ds}'
        """)

    @task
    def audit(ds=None):
        # 1) intrinsic validation, 2) source-to-target reconciliation.
        breaks = run_sql(f"""
            with recon as ( {RECON_ORDERS_BY_DAY} )     -- the query from earlier
            select * from recon where status = 'BREAK'
        """)
        null_keys = run_sql(
            "select count(*) from analytics.stg_fct_orders where order_id is null"
        )[0][0]

        if breaks or null_keys > 0:
            # Circuit breaker: raise, so publish never runs. Prod stays on
            # last-known-good; page on-call with the specific break rows.
            raise AirflowFailException(
                f"AUDIT FAILED: {len(breaks)} recon breaks, {null_keys} null keys"
            )

    @task
    def publish(ds=None):
        # Atomic swap -- consumers flip from old to new in one transaction.
        run_sql("""
            alter table analytics.fct_orders swap with analytics.stg_fct_orders
        """)

    write_staging() >> audit() >> publish()

orders_wap()

The equivalent in dbt is dbt build (which interleaves tests with model runs) plus a staging schema and a swap, or dbt Cloud's WAP-style deferral; on a lakehouse you'd stage on an Iceberg/Delta branch and fast-forward only on green. The pattern is what matters: nothing reaches consumers until it has passed audit.

Anomaly detection & the alert-vs-block trap

Explicit tests catch defects you thought to write a rule for. Anomaly detection catches the ones you didn't — by learning the normal behavior of a dataset and flagging deviation across four axes (the observability pillars, expanded in Chapter 07):

• Volume — today's row count vs the trailing average / a control chart. Sudden 40% drop = upstream problem.
• Freshness — how long since the table last updated vs its normal cadence.
• Distribution — mean, null rate, cardinality, category mix drifting from history (a currency column that's suddenly 30% null).
• Schema drift — a column added, dropped, or retyped upstream without notice.

Managed tools (Monte Carlo, Bigeye, Metaplane, Soda) do this out of the box; you can roll your own with control charts (flag > 3σ from a trailing window) stored in an audit table. The judgment call is what to do when something fires — and this is where candidates walk into a trap:

dbt encodes this directly with test severity: error (block) vs severity: warn (surface but pass), and error_if/warn_if thresholds — the mechanism for the tiering above.

Quarantine, audit tables & dashboards

When validation finds bad rows mid-stream, you have a choice: fail the whole batch, or quarantine the offenders and let the good rows through. For high-volume ingestion, all-or-nothing is often too brittle — one malformed event shouldn't block a million good ones. The pattern is a dead-letter table (or dead-letter queue for streaming): route rows that fail validation into a side table with the failure reason and raw payload, publish the clean rows, and alert on the quarantine rate. Bad rows are preserved for investigation and replay after the upstream fix, rather than silently dropped.

-- Split a staged batch into clean rows (published) and bad rows
-- (dead-lettered with a reason) instead of failing the whole load.

insert into analytics.dead_letter_orders
select
    *,
    case
        when order_id is null            then 'null_order_id'
        when amount_cents < 0            then 'negative_amount'
        when status not in ('pending','paid','refunded','cancelled')
                                          then 'invalid_status'
    end                                   as reject_reason,
    current_timestamp                     as quarantined_at
from staging.orders_raw
where order_id is null
   or amount_cents < 0
   or status not in ('pending','paid','refunded','cancelled');

-- only the clean remainder proceeds to publish
insert into analytics.stg_fct_orders
select * from staging.orders_raw
where order_id is not null
  and amount_cents >= 0
  and status in ('pending','paid','refunded','cancelled');

Underpinning all of this are audit tables: a persistent log of every recon run — date, metric, source total, target total, variance, status, run id. They give you three things: an evidence trail a finance auditor can inspect, a time series to detect slow drift, and the raw data for reconciliation dashboards (green/red per feed per day, trending break rate, quarantine volume, freshness). A quarantine rate that's climbing over a week is a leading indicator of an upstream contract erosion you'd otherwise only catch when a total finally breaks. The dashboard is also your fastest answer to the stakeholder question that ends every one of these interviews: "how do you know the numbers are right?" — you point at the audit table.

The leveling ladder

Reconciliation & validation is a topic where the level gap is stark: L1 writes two dbt tests; L4 runs an org-wide contract-and-anomaly regime. Narrate the climb and you demonstrate three rungs in one answer.

This closes the "hard parts" — late data (04), incremental processing (05), and now correctness. Next, the machinery that keeps a pipeline standing once it's live: 07 · Reliability & Observability, where freshness SLAs, delivery semantics, and lineage turn these checks into an operable system.

✓ Check yourself

Answer from memory before opening each one.

1. An ingest bug drops ten thousand orders. Every dbt test on the table passes. Why, and which check would have caught it?

   Show answer

   Each surviving row is individually valid, and tests like not-null, unique and accepted values judge rows, not the set. Source-to-target reconciliation of counts and control totals catches missing or duplicated rows. Validation guards against malformed data and reconciliation against missing or duplicated data, so you need both. See reconciliation vs validation.

2. Row counts and the daily sum both tie out between source and warehouse, yet a customer says one order shows the wrong amount. How can that be, and what check would find it?

   Show answer

   Two errors can cancel in the total, such as one amount too high and another too low, and the counts say nothing about values. Hash each row's business columns, aggregate the hashes per partition, and compare them across systems. A mismatch localises drift to a partition, and the cost is far below shipping the data. See source-to-target reconciliation.

3. Run the recon query and one day shows a break with the target 1,204 rows and about $96,000 above the source. What does the direction suggest, and what do you do before paging anyone?

   Show answer

   Target high points to duplication, as target low would point to drops. Re-run the same comparison at raw, staging and mart to find the layer where the variance appears, then classify it: a dedup bug, a definition mismatch such as refunds, or a timing artefact from the source moving after your snapshot. Timing artefacts are the usual false alarm, so recheck a trailing window before paging. See break investigation.

4. The team runs its dbt tests after the load has overwritten the production table. What is wrong with that, and what is the smallest redesign that fixes it?

   Show answer

   A failure is reported only after consumers could already have read bad data, and rolling back is manual. Write to a staging table, audit it with the validation and reconciliation checks, and publish by an atomic swap only on a pass. On failure the swap never runs and production keeps serving the last good data. See Write-Audit-Publish.

5. Volume is three standard deviations above the trailing mean on Black Friday, and in the same run the primary key has duplicates. Which blocks the publish, and what goes wrong if you wire both the same way?

   Show answer

   The duplicate key blocks, because the corruption is certain and shipping it costs more than the delay. The volume spike alerts a human, because it is often a real event. Blocking on both halts production on legitimate peaks and trains people to bypass the gate; alerting on both lets corruption reach finance. Fix the severity per check in advance. See the alert-versus-block trap.

6. A producer team renames a column in a PR. Where should the break surface, and what happens if you have only post-load tests?

   Show answer

   In CI on the producer's PR, where an enforced contract fails the build, and at ingest, where a malformed event is rejected or quarantined. With only post-load tests, the break surfaces as a failed test or, worse, as a reconciliation break days later. A contract covers schema, semantics and an SLA, and the schema is the part that is easiest to enforce. See data contracts.

Takeaway

• Validation checks whether data is well formed; reconciliation checks whether the set agrees with the source of truth; you need both because each misses the other's failures.
• The six quality dimensions map to concrete mechanisms, and accuracy is the one that needs a second trusted source.
• Reconcile counts, control totals and per-partition hashes with an explicit tolerance per metric, zero for money, over a trailing window.
• Investigate a break by direction, by bisecting the layers, and by classifying it, and expect timing artefacts before assuming loss.
• Write-Audit-Publish makes checks a gate: stage, audit, and swap atomically only on a pass, so production keeps the last good data.
• Assign severity in advance: certain corruption blocks, plausibility signals alert.
• A data contract covers schema, semantics and SLA, and is enforced in CI and at ingest.
• Quarantine bad rows with a reason instead of failing a large batch, and keep audit tables as the evidence trail.

Gating bad batches is one part of an operable system; surviving failures, retries and on-call is the next. → Reliability & Observability

Further reading

• dbt: Add data tests to your DAG — dbt Labs docs. Generic and singular tests, severity, and error_if and warn_if thresholds.
• dbt: Model contracts — dbt Labs docs. What an enforced contract checks at build time, and what it does not.
• Iceberg: Branching and Tagging — Apache Iceberg docs. Branches as a staging area for write-audit-publish.
• Great Expectations documentation — Great Expectations. Expectation suites and validation results for non-dbt stacks.
• Open Data Contract Standard — Bitol, Linux Foundation AI & Data. A vendor-neutral format for schema, quality and SLA terms in a contract.