A successful pipeline run is not a completeness guarantee

An orchestrator can report success even when the source delivered fewer records than expected, a filter excluded valid rows or a merge silently discarded duplicates. Runtime success proves that execution reached its completion criteria; it does not establish that the resulting dataset is complete and correct.

Reliable data platforms need explicit business-level reconciliation alongside technical monitoring.

1. Define the reconciliation grain

Choose the business unit at which completeness matters: transaction, deal leg, invoice, customer-day or another stable key. A raw row-count comparison can be misleading when source and target legitimately use different grains.

Document expected transformations, exclusions, deduplication rules and late-arriving data windows before setting thresholds.

2. Use multiple independent controls

Combine source and target counts with distinct business-key counts, null-key checks, financial or volume totals, minimum and maximum business dates, and freshness timestamps. A matching count can hide one missing record and one duplicated record.

For financial data, reconcile signed amounts and currency-specific totals rather than aggregating unlike currencies. For operational feeds, compare volumes within consistent units and time boundaries.

3. Make incremental loads observable

Capture a run identifier, source watermark, extraction start and end, ingestion timestamp, transformation version and target commit identifier. Persist these values in an audit table.

Distinguish event time, business date and processing time. A file received today may contain yesterday's transactions, and a late correction may update a historical partition.

4. Validate merges and deduplication

Test whether merge keys are stable and unique at the intended grain. Record inserted, updated, unchanged and rejected rows separately. If deduplication chooses the newest version, explicitly define the ordering columns and tie-breakers.

A common failure mode is to count only rows written, ignoring valid records rejected by an over-restrictive filter or a key collision.

5. Handle expected exceptions

Not every discrepancy warrants a production outage. Define tolerances where appropriate, but require documented justification. Financial reporting may need exact reconciliation; telemetry can tolerate bounded late arrival.

Maintain an exception register with affected dates, keys, source and target totals, reason, owner, remediation and closure evidence. Never silently reset a failed control to green.

6. Design for replay and month-end reporting

Daily snapshots and month-end snapshots may intentionally differ in cutoffs, as-of dates or exclusion policies. Document those differences and test them independently.

Use idempotent reprocessing so rerunning the same source batch does not double-count transactions. Preserve immutable evidence of published reporting periods where auditability requires it.

7. Turn controls into release gates

A production-ready pipeline should publish data only after required validations pass, or explicitly mark a dataset provisional when the business permits it. Alert on stale watermarks, missing partitions, duplicate keys and out-of-tolerance totals.

Review control failures with both engineering and data owners. The most useful quality metric is not how many tests passed, but whether material defects were detected before consumers relied on the data.

Practical reconciliation checklist

  • Identify the business grain and expected exclusions.
  • Capture counts, unique keys and control totals.
  • Track watermarks and late-arrival windows.
  • Audit merge and deduplication decisions.
  • Reconcile daily and month-end outputs separately.
  • Store exceptions with ownership and evidence.
  • Gate publication on material control failures.

Further reading

Microsoft's Azure data engineering documentation and the documentation for your chosen orchestration and storage platform provide implementation details. This article offers a general operational pattern; it does not assert specific Google Search Console keyword demand.

Worked example: reconcile a daily financial batch

Assume a source and curated table both contain business_date, transaction_id, currency and amount. Compare totals at the same business date and currency, after applying documented exclusions:

SELECT business_date, currency,
       COUNT(*) AS row_count,
       COUNT(DISTINCT transaction_id) AS distinct_transactions,
       SUM(amount) AS amount_total
FROM curated_transactions
WHERE business_date = '2026-10-30'
GROUP BY business_date, currency;

Run the equivalent query against the source after aligning grain and filters. Investigate any mismatch rather than masking it with a broad tolerance. A matching aggregate is necessary but insufficient: compare business-key sets in both directions, because offsetting omissions and duplicates can produce identical totals.

Audit-table design

Store run_id, dataset_name, business_date, source_watermark, target_version, source_count, target_count, source_total, target_total, validation_status, exception_count and checked_at_utc. Keep currency and unit in the control key. Define how late corrections are replayed and when a published snapshot becomes immutable.

Release-gate decision table

| Control result | Action | | --- | --- | | Exact count, key and control-total match | Publish with audit evidence | | Count matches but key sets differ | Quarantine and investigate | | Missing source partition or stale watermark | Hold publication and alert owner | | Documented allowable late arrival | Publish provisional data only if policy permits |

Related AIDataCenter reading

For the conceptual distinction between execution success and data completeness, see Successful Data Pipeline Run Does Not Prove Data Completeness. This article concentrates on implementing measurable controls and audit records.

Define a reconciliation contract

A reconciliation contract should specify the source dataset, curated target, business grain, relevant dates, currency or unit, permitted exclusions, late-arrival window, and owner. It should also describe whether source and target counts are expected to match one-to-one. A target aggregated to one row per account-month cannot be validated against raw transaction counts without a grain conversion.

Example contract:

| Property | Example definition | | --- | --- | | Business grain | One row per transaction ID and business date | | Partition | Business date in agreed reporting timezone | | Monetary control | Sum of signed net amount per currency | | Completeness | All eligible source business keys represented | | Duplicate rule | No repeated key at the target grain | | Late arrivals | Reconcile within an explicitly approved correction window | | Owner | Data engineering and business data steward |

These values are illustrative. A real contract must be approved by the relevant business owner.

SQL implementation: identify missing and extra keys

Start by comparing key sets after normalizing types and applying exactly the same eligibility rules. This example assumes transaction_id is unique within a business_date and uses SQL Server syntax.

WITH source_keys AS (
    SELECT business_date, transaction_id
    FROM dbo.source_transactions
    WHERE business_date = '2026-10-30'
      AND is_eligible = 1
),
target_keys AS (
    SELECT business_date, transaction_id
    FROM dbo.curated_transactions
    WHERE business_date = '2026-10-30'
)
SELECT 'missing_in_target' AS exception_type,
       s.business_date, s.transaction_id
FROM source_keys AS s
WHERE NOT EXISTS (
    SELECT 1 FROM target_keys AS t
    WHERE t.business_date = s.business_date
      AND t.transaction_id = s.transaction_id
)
UNION ALL
SELECT 'extra_in_target',
       t.business_date, t.transaction_id
FROM target_keys AS t
WHERE NOT EXISTS (
    SELECT 1 FROM source_keys AS s
    WHERE s.business_date = t.business_date
      AND s.transaction_id = t.transaction_id
);

This query detects key-set differences but does not detect multiple occurrences of a key. Add a duplicate check:

SELECT business_date, transaction_id,
       COUNT(*) AS duplicate_count
FROM dbo.curated_transactions
WHERE business_date = '2026-10-30'
GROUP BY business_date, transaction_id
HAVING COUNT(*) > 1;

If the real grain includes a deal leg, event type, or version, those fields must be part of the key. Otherwise a valid multi-leg transaction could be incorrectly flagged.

Reconcile values, not just existence

After comparing keys, compare amounts or other business measures. For financial data, group by currency; for physical volumes, group by measurement unit. Use a defined decimal precision and agreed rounding policy. Do not sum USD and CAD into a single control total without an approved FX conversion policy.

A robust control suite distinguishes:

  • Completeness: Are expected keys present?
  • Uniqueness: Are there unintended duplicates?
  • Accuracy: Do values agree at the approved grain?
  • Timeliness: Is the source watermark sufficiently recent?
  • Validity: Do fields satisfy business constraints?
  • Consistency: Do related datasets reconcile across layers?

A count match cannot prove accuracy. Similarly, an amount-total match can conceal offsetting errors.

PySpark example: compare grouped controls

The following example is illustrative and assumes source and target DataFrames already contain normalized business_date, currency, and amount columns.

from pyspark.sql import functions as F

def control_totals(df):
    return (
        df.groupBy("business_date", "currency")
          .agg(
              F.count("*").alias("row_count"),
              F.sum(F.col("amount").cast("decimal(20,4)"))
               .alias("amount_total")
          )
    )

src = control_totals(source_df).alias("s")
tgt = control_totals(target_df).alias("t")

comparison = (
    src.join(tgt, ["business_date", "currency"], "full_outer")
       .select(
           "business_date", "currency",
           F.col("s.row_count").alias("source_count"),
           F.col("t.row_count").alias("target_count"),
           F.col("s.amount_total").alias("source_amount"),
           F.col("t.amount_total").alias("target_amount"),
       )
)

A production implementation must explicitly flag null-side rows and compare decimals using the agreed tolerance. It should also validate key sets, duplicates, source eligibility, and batch watermarks. The example is a starting point, not a complete release gate.

Audit table and exception lifecycle

An audit record should be immutable or otherwise protected from silent alteration. Store the run identifier, input watermarks, source version, target version, control definition version, checked date, status, counts, totals, and exception reference. Avoid putting sensitive transaction details in unrestricted operational logs.

Each discrepancy needs an owner and a disposition: source defect, mapping error, legitimate late arrival, duplicate, rejected record, or accepted documented exception. Retain evidence of investigation and approval. A rerun should reference the original failure and demonstrate that the correction resolved it.

Snapshot and incremental-load pitfalls

Incremental pipelines frequently process a time window different from the business date. If a correction arrives today for a transaction from last month, reconciling only today's processing partition will miss the historical change.

For daily and monthly snapshots, explicitly define cutoffs and whether snapshots are replaceable or immutable. Compare the correct as-of business date and reporting period, not merely the latest ingestion timestamp. Use stable merge keys and deterministic tie-breakers for multiple source versions.

For financial month-end, agree whether reconciliation must be exact before publication and how post-close adjustments are handled. A provisional reporting layer should be visibly labeled and should never be mistaken for a certified close.

Failure scenario: the green run with missing records

Suppose a pipeline completes successfully and writes 9,950 rows, while the source has 10,000 eligible business keys. The orchestrator reports success because every task executed. Reconciliation finds 50 missing keys.

The correct response is to inspect source extraction filters, watermark boundaries, rejected records, merge predicates, and deduplication logic. Reprocessing should be idempotent, and the team should rerun the key-set and control-total checks before marking the dataset ready.

These numbers are hypothetical; they illustrate the difference between runtime health and business completeness.

Production release checklist

  1. Confirm the business grain and eligible source population.
  2. Capture source and target counts and distinct keys.
  3. Compare business-key sets in both directions.
  4. Reconcile monetary or volume totals within consistent units.
  5. Validate duplicate keys, nulls, watermarks, and late arrivals.
  6. Record exceptions with owners and disposition.
  7. Apply the approved publish, provisional, or hold policy.
  8. Persist audit evidence and verify replay behavior.

Authoritative references

Examples should be adapted to your platform, schema, reporting calendar, permissions, and business controls before production use.