Reconciliation Source To Target

Why this matters

Reconciliation (Source to Target) proves that data moved or transformed is complete and accurate. As a Data Engineer, you will:

• Verify that daily warehouse loads match the raw source (counts and totals).
• Check CDC pipelines so inserts, updates, and deletes in source appear correctly downstream.
• Provide audit evidence for regulatory or financial reporting.
• Catch drift, late-arriving events, duplicate loads, and mapping errors before stakeholders are affected.

Concept explained simply

Reconciliation compares the source dataset to the target dataset after ingestion or transformation. You confirm:

• Completeness: every expected record made it to the target (no missing or extra rows).
• Accuracy: key fields and aggregated values are preserved (within allowed tolerances).
• Timeliness: the target reflects source changes within an agreed delay window.

Mental model

Think in three buckets after aligning by primary key and time window:

• Same: rows present in both source and target with matching values.
• Missing: rows in source not found in target (incompleteness).
• Extra: rows in target not found in source (duplicates or stale data).

Then check control totals (sums, counts) and field-level equality for accuracy. For CDC, also confirm deletes, effective dates, and late-arriving data handling.

Methods and patterns

• Partitioned counts and totals: compare COUNT(*) and SUM(amount) by date or batch_id.
• Set comparisons: source MINUS target (missing), target MINUS source (extra).
• Hash diffs: compute a stable hash of selected columns per primary key; compare hashes to find changed rows.
• Thresholds/tolerances: allow minor numeric differences from rounding or currency conversions (e.g., difference <= 0.01 per row).
• Watermarks/windows: reconcile only up to a safe point-in-time (e.g., source updated_at <= T) to handle late events.
• Referential checks: ensure foreign keys in facts match dimension surrogate keys (no orphans).
• Schema drift detection: compare expected columns, data types, and nullability.
• Idempotency checks: detect duplicate loads via batch_id, checksum files, or row-level uniqueness.

Worked examples

-- Source control totals
SELECT sale_date, COUNT(*) AS src_rows, SUM(amount) AS src_sum
FROM src.sales
WHERE sale_date = DATE '2025-12-01'
GROUP BY sale_date;

-- Target control totals
SELECT sale_date, COUNT(*) AS tgt_rows, SUM(amount) AS tgt_sum
FROM dw.fct_sales
WHERE sale_date = DATE '2025-12-01'
GROUP BY sale_date;

-- Missing rows: in source, not in target
SELECT s.order_id
FROM src.orders s
LEFT JOIN dw.fct_orders t ON t.order_id = s.order_id
WHERE t.order_id IS NULL;

-- Extra rows: in target, not in source (potential duplicates or stale)
SELECT t.order_id
FROM dw.fct_orders t
LEFT JOIN src.orders s ON s.order_id = t.order_id
WHERE s.order_id IS NULL;

-- Example using a stable concatenation; adjust function names to your SQL dialect
WITH src_h AS (
  SELECT order_id,
         MD5(CONCAT_WS('|', customer_id, status, CAST(amount AS VARCHAR), TO_CHAR(updated_at, 'YYYY-MM-DD"T"HH24:MI:SS'))) AS h
  FROM src.orders
),
 tgt_h AS (
  SELECT order_id,
         MD5(CONCAT_WS('|', customer_id, status, CAST(amount AS VARCHAR), TO_CHAR(updated_at, 'YYYY-MM-DD"T"HH24:MI:SS'))) AS h
  FROM dw.fct_orders
)
SELECT s.order_id
FROM src_h s
JOIN tgt_h t USING(order_id)
WHERE s.h <> t.h;

-- Choose watermark T (e.g., now - 2 hours)
WITH s AS (
  SELECT * FROM src.orders WHERE updated_at <= :T
),
 t AS (
  SELECT * FROM dw.fct_orders WHERE updated_at_source <= :T
)
SELECT COUNT(*) FROM s;
SELECT COUNT(*) FROM t;
-- Then perform set and hash checks within the same T window.

Who this is for

• Data Engineers building batch or streaming pipelines.
• Data Quality engineers implementing automated checks.

Prerequisites

• Comfort with SQL joins, aggregates, and windowing.
• Understanding of primary keys, foreign keys, and CDC basics.
• Familiarity with partitioning (by date, batch_id, etc.).

Learning path

1. Start with partitioned counts and sums checks.
2. Learn set comparisons (source minus target, and vice versa).
3. Add hash-diff checks for field-level accuracy.
4. Implement tolerances and watermarks for practical reliability.
5. Automate reports and alerts with clear, actionable outputs.

Step-by-step: a dependable reconciliation routine

1. Scope: choose table(s), partition(s), and time window.
2. Completeness: compare counts by partition and find missing/extra rows.
3. Accuracy: compare control totals and hash-diffs on critical columns.
4. Constraints: validate referential integrity and uniqueness.
5. Windows: apply watermark for late data; recheck after window closes.
6. Thresholds: define acceptable tolerances and alert conditions.
7. Report: produce a concise pass/fail summary with diagnostics.

Exercises

Do these hands-on tasks. Hints and solutions are provided. The same exercises appear below in the Exercises section of this page for easy copy/paste.

-- Control totals
WITH s AS (
  SELECT COUNT(*) AS c, SUM(amount) AS s
  FROM src.orders WHERE sale_date = DATE '2025-12-01'
), t AS (
  SELECT COUNT(*) AS c, SUM(amount) AS s
  FROM dw.fct_orders WHERE sale_date = DATE '2025-12-01'
)
SELECT s.c AS src_rows, t.c AS tgt_rows,
       s.s AS src_sum, t.s AS tgt_sum,
       ABS(s.s - t.s) AS abs_diff
FROM s, t;

-- Missing in target
SELECT s.order_id
FROM src.orders s
LEFT JOIN dw.fct_orders t ON t.order_id = s.order_id
WHERE s.sale_date = DATE '2025-12-01' AND t.order_id IS NULL;

-- Extra in target
SELECT t.order_id
FROM dw.fct_orders t
LEFT JOIN src.orders s ON s.order_id = t.order_id
WHERE t.sale_date = DATE '2025-12-01' AND s.order_id IS NULL;

WITH s AS (
  SELECT order_id,
         MD5(CONCAT_WS('|', customer_id, status, CAST(amount AS VARCHAR))) AS h
  FROM src.orders
), t AS (
  SELECT order_id,
         MD5(CONCAT_WS('|', customer_id, status, CAST(amount AS VARCHAR))) AS h
  FROM dw.fct_orders
)
SELECT s.order_id
FROM s
JOIN t USING(order_id)
WHERE s.h <> t.h;

Self-check checklist

• I compared counts and control totals for the intended partition/window.
• I checked both missing and extra keys with set comparisons.
• I validated field-level accuracy with a hash or explicit column comparisons.
• I applied a reasonable tolerance and documented it.
• I considered late-arriving data via a watermark.

Common mistakes and how to self-check

• Comparing unbounded data: always reconcile within a clear time window or partition.
• Ignoring null semantics: normalize nulls in hash inputs to avoid false diffs.
• Hashing PII directly: exclude sensitive fields or use safe derived values.
• Not reconciling deletes: ensure source deletes or end-dates are reflected downstream.
• One-sided checks: check both missing and extra keys.
• Overly strict tolerances: allow minor numeric differences from formatting or conversions.

Practical projects

• Build a daily reconciliation notebook that outputs pass/fail per table, partition, and totals.
• Create a hash-diff job for top 5 critical tables, with a mismatch report per column.
• Implement a CDC watermark reconciliation: run T-2h and T-24h checks and compare drift.

Next steps

• Automate reconciliation in your orchestration tool as a post-load step.
• Publish concise dashboards showing counts, totals, and mismatch trends.
• Document tolerances and SLAs for each pipeline.

Mini challenge

Your finance fact table shows matching counts but a total amount higher in target by 1.2%. List three targeted queries you would run to isolate the cause, and explain your expected findings. Keep your answer focused and actionable.

Quick test

Anyone can take the quick test for free. Sign in to save your progress and see your history.