> For the complete documentation index, see [llms.txt](https://docs.soda.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.soda.io/reference/contract-language-reference/reconciliation-checks.md).

# Reconciliation checks

{% hint style="warning" %}
Reconciliation checks require having installed **the `soda-reconciliation` package using the** [**private PyPI**](#private-pypi-installation-flow) with an **Enterprise license**, unless you are using a runner.

Need access to the private PyPI? Please [contact us](mailto:support@soda.io).
{% endhint %}

Reconciliation checks validate that a **target dataset** matches one or more **source datasets**, ensuring that data remains consistent after migrations, in pipelines, or during synchronizations. They can be used for both **metric-level (aggregate) validation** and **row-level (record-by-record) validation**.

> Learn more about the use cases and performance considerations in the [Data reconciliation](/data-testing/data-reconciliation.md) page.

## Prerequisites

To use **reconciliation checks**, you must either:

* Run your contract with **Soda Runner**, which has reconciliation support built in, **or**
* Install the **Soda Reconciliation extension** locally:

{% code overflow="wrap" %}

```shellscript
pip install "soda-reconciliation" --pre -i "https://${SODA_API_KEY_ID}:${SODA_API_KEY_SECRET}@enterprise.pypi.cloud.soda.io" --extra-index-url=https://pypi.cloud.soda.io
```

{% endcode %}

> **Follow the** [**private PyPi installation flow**](/deployment-options/soda-python-libraries.md#private-pypi-installation-flow) to set up your environment and install the necessary Soda extensions.

## Example

#### Structure

* `dataset:` defines the **target dataset**.
* `reconciliation.sources:` defines the **source datasets** to compare against. Each source has a `name` and a `dataset`.
* `reconciliation.sources[].filter:` is an optional filter applied only to that source dataset.
* Each reconciliation `check` specifies which source it targets via the `source:` field.
* Each reconciliation `check` supports an additional **check-level filter** applied consistently to both source and target, layered on top of dataset-level filters. The row-level check types — [rows diff](#rows-diff) and `reference_diff` — can instead filter each side independently with `source_filter:` and `target_filter:`. See [Filters](#filters).
* Thresholds define acceptable differences.

> All Common Check Configurations (filters, thresholds, names, qualifiers, attributes) apply to reconciliation checks. Learn more about [Common Check Configurations](/reference/contract-language-reference.md#common-check-configurations).

```yaml
dataset: cloud_data_source/db/schema/dataset
filter: created_at >= CURRENT_DATE - INTERVAL '1 day'

reconciliation:
  sources:
    - name: on_prem
      dataset: on_prem_data_source/db/schema/dataset
      filter: created_at >= CURRENT_DATE - INTERVAL '1 day'
    - name: staging
      dataset: staging_data_source/db/schema/dataset
  checks:
    - row_count_diff:
        source: on_prem
        threshold:
          must_be_less_than: 1
    - row_count_diff:
        source: staging
    - aggregate_diff:
        source: on_prem
        function: avg
        column: employee_key
        filter: employee_key < 100
    - duplicate_diff:
        source: on_prem
        columns: [employee_key]
        threshold:
          must_be_less_than: 1
          metric: percent
    - freshness_diff:
        source: staging
        column: hire_date
        threshold:
          must_be: 0
          unit: hour
    - metric_diff:
        source: on_prem
        source_expression: SUM(employee_key + parent_employee_key)
        target_expression: SUM(employee_key + parent_employee_key)
        threshold:
          must_be_less_than: 100
          metric: percent
    - rows_diff:
        source: on_prem
        source_key_columns: [employee_key]
        target_key_columns: [employee_key]
        source_columns: [price, order_date]
        target_columns: [price, order_date]
        source_filter: src_status = 'A'
        target_filter: status = 'ACTIVE'
        threshold:
          must_be: 0
    - schema_diff:
        source: on_prem
```

## Source configuration

Each entry in the `sources:` list configures a source dataset to compare against the target.

<table><thead><tr><th width="160.199951171875">Key</th><th width="159.7999267578125">Required</th><th>Description</th></tr></thead><tbody><tr><td><code>name</code></td><td>Yes</td><td>Identifier for this source. Used in the <code>source:</code> field on checks. Must be unique across sources.</td></tr><tr><td><code>dataset</code></td><td>Yes</td><td>DQN path to the source dataset (e.g., <code>data_source/db/schema/table</code>). Must be unique across sources.</td></tr><tr><td><code>filter</code></td><td>No</td><td>SQL filter expression applied only to this source dataset.</td></tr><tr><td><code>default</code></td><td>No</td><td>Set to <code>true</code> to mark this as the unnamed default source. Used only when <a href="#migrating-from-single-source-to-multi-source">migrating from single-source</a>. Cannot be combined with <code>name</code>.</td></tr></tbody></table>

### Targeting checks to a source

Every check should specify which source it validates against using the `source:` field:

```yaml
checks:
  - row_count_diff:
      source: staging        # runs against the "staging" source
  - aggregate_diff:
      source: warehouse      # runs against the "warehouse" source
      function: sum
      column: amount
```

If a [default (unnamed) source](#migrating-from-single-source-to-multi-source) exists, checks without `source:` run against it. If no default source exists, every check **must** have an explicit `source:`.

### Validation rules

* All sources must have a `name` (unless using `default: true` for migration)
* `default: true` cannot be combined with `name`
* Source names must be unique
* Datasets must be unique across sources
* If no default source exists, every check must specify `source:`
* Source names are trimmed of whitespace; empty or whitespace-only names are rejected

***

## Filters

Reconciliation compares two datasets that usually live in two different systems, so a filter always applies to a **side** — source or target — never to "the comparison" as a whole. Filters can be declared at three levels, and every filter that applies to the same side is combined with `AND`.

### Filter application matrix

<table><thead><tr><th width="220">Where it is declared</th><th width="150">Key</th><th>Applies to</th></tr></thead><tbody><tr><td>Contract root</td><td><code>filter</code></td><td>The <strong>target</strong> dataset, for every check in the contract (reconciliation checks included). See <a href="/pages/yB0XMIIob1nyTOjyZraz#configure-a-filter-for-all-checks">Configure a filter for all checks</a>.</td></tr><tr><td><code>reconciliation.sources[]</code></td><td><code>filter</code></td><td>The <strong>source</strong> dataset of that entry, for every check bound to that source.</td></tr><tr><td>Check</td><td><code>filter</code></td><td><strong>Both</strong> sides of that check.</td></tr><tr><td>Check (<a href="#rows-diff"><code>rows_diff</code></a>, <code>reference_diff</code>)</td><td><code>source_filter</code></td><td>The <strong>source</strong> side of that check only.</td></tr><tr><td>Check (<a href="#rows-diff"><code>rows_diff</code></a>, <code>reference_diff</code>)</td><td><code>target_filter</code></td><td>The <strong>target</strong> side of that check only.</td></tr></tbody></table>

The effective filter for a side is the `AND` of every level that applies to it. For a row-level check bound to a source that declares its own filter, the source side is evaluated as `sources[].filter AND source_filter`.

The split form is available on the two row-level check types, `rows_diff` and `reference_diff`. The other reconciliation check types accept the both-sides `filter` only.

{% hint style="info" %}
In the past, the check-level `filter` on `reference_diff` was accepted but never applied. Now, **it is applied to both sides,** like it is on every other reconciliation check type. If you declared a `filter` on a `reference_diff` check, the compared row set changes: **verify that the filter still expresses what you intend on both sides,** or split it into `source_filter` and `target_filter`.
{% endhint %}

### Filter both sides with the same condition

Use the check-level `filter` when the same SQL condition is valid on both sides — which requires the column names and semantics to be identical in the source and the target dataset.

```yaml
reconciliation:
  sources:
    - name: warehouse
      dataset: wh_ds/public/orders_copy
  checks:
    - rows_diff:
        source: warehouse
        source_key_columns: [order_id]
        target_key_columns: [order_id]
        filter: status = 'ACTIVE'      # applied to source AND target
```

### Filter each side independently

When the two datasets do not share column names, encodings, or partitioning conventions — a common situation when reconciling a legacy system against its migrated copy — use `source_filter` and `target_filter` on a `rows_diff` or `reference_diff` check. Both keys are optional and independent: you can set one, the other, or both.

```yaml
reconciliation:
  sources:
    - name: legacy
      dataset: legacy_ds/erp/orders
  checks:
    - rows_diff:
        source: legacy
        source_key_columns: [order_id]
        target_key_columns: [order_id]
        source_filter: ORD_STATUS_CD = 'A'
        target_filter: status = 'ACTIVE'
```

### Rules

* The legacy `filter` key remains supported and unchanged. It is the both-sides shorthand.
* `filter` and the split form are **mutually exclusive**. Declaring `filter` together with `source_filter` and/or `target_filter` on the same check is a parse error.
* A filter and a [custom SQL query](#compare-derived-data-with-custom-sql-queries) on the **same side** of the same check is a parse error — put the condition in the query instead. `source_query` with `target_filter` is valid.
* The legacy `filter` combined with `source_query` or `target_query` is a parse error, because its both-sides promise cannot be kept. Use `source_filter`/`target_filter` instead.

{% hint style="info" %}
Filters are **not** part of a check's identity. Rewriting `filter: X` into `source_filter: X` plus `target_filter: X` keeps the same check in Soda Cloud, with its history intact.
{% endhint %}

***

## Row count diff

Compares the row count of the source and target datasets.

**Example**

```yaml
reconciliation:
  sources:
    - name: staging
      dataset: contracts-source/postgres/public/dim_employee_copy
  checks:
    - row_count_diff:
        source: staging
        threshold:
          must_be_less_than: 1
```

**Configuration keys**

<table><thead><tr><th width="159.60003662109375">Key</th><th width="160.20001220703125">Optional</th><th>Description</th></tr></thead><tbody><tr><td><code>source</code></td><td>No</td><td>Name of the source to validate against</td></tr><tr><td><code>name</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-names">Check names</a></td></tr><tr><td><code>threshold</code></td><td>Yes</td><td><p><a href="/pages/yB0XMIIob1nyTOjyZraz#thresholds">Thresholds</a></p><p>Acceptable difference between source and target.<br><br>By default, threshold = 0</p></td></tr><tr><td><code>filter</code></td><td>Yes</td><td><a href="/pages/6pS8St3coKJanI4KlnMI#check-level-filter">Configure a check filter</a>, applied to both source and target.</td></tr><tr><td><code>qualifier</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-qualifiers">Check qualifiers</a></td></tr><tr><td><code>attributes</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-attributes">Check attributes</a></td></tr></tbody></table>

***

## Aggregate diff

Compares the result of an aggregate function on a column between source and target.

**Example**

```yaml
reconciliation:
  sources:
    - name: staging
      dataset: contracts-source/postgres/public/dim_employee_copy
  checks:
    - aggregate_diff:
        source: staging
        function: avg
        column: employee_key
        filter: employee_key < 100
        threshold:
          must_be_less_than: 0.5
```

**Configuration keys**

<table><thead><tr><th width="160.20001220703125">Key</th><th width="160.20001220703125">Optional</th><th width="446.20001220703125">Description</th></tr></thead><tbody><tr><td><code>source</code></td><td>No</td><td>Name of the source to validate against</td></tr><tr><td><code>function</code></td><td>No</td><td>Aggregate function (<code>avg</code>, <code>sum</code>, <code>min</code>, <code>max</code>, <code>avg_length</code>, etc.)</td></tr><tr><td><code>column</code></td><td>Yes</td><td>Column to aggregate</td></tr><tr><td><code>threshold</code></td><td>Yes</td><td><p><a href="/pages/yB0XMIIob1nyTOjyZraz#thresholds">Thresholds</a></p><p>Acceptable difference between source and target.<br><br>By default, threshold = 0</p></td></tr><tr><td><code>filter</code></td><td>Yes</td><td><p><a href="/pages/6pS8St3coKJanI4KlnMI#check-level-filter">Configure a check filter</a></p><p>Filter applied to both source and target</p></td></tr><tr><td><code>name</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-names">Check names</a></td></tr><tr><td><code>qualifier</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-qualifiers">Check qualifiers</a></td></tr><tr><td><code>attributes</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-attributes">Check attributes</a></td></tr></tbody></table>

***

## Duplicate diff

Compares the number or percentage of duplicate rows based on one or more columns.

**Example**

```yaml
reconciliation:
  sources:
    - name: staging
      dataset: contracts-source/postgres/public/dim_employee_copy
  checks:
    - duplicate_diff:
        source: staging
        columns: [employee_key]
        threshold:
          must_be_less_than: 1
          metric: percent
```

**Configuration keys**

<table><thead><tr><th width="160.20001220703125">Key</th><th width="160.20001220703125">Optional</th><th>Description</th></tr></thead><tbody><tr><td><code>source</code></td><td>No</td><td>Name of the source to validate against</td></tr><tr><td><code>columns</code></td><td>No</td><td>List of column(s) to evaluate duplicates on</td></tr><tr><td><code>threshold</code></td><td>Yes</td><td><p><a href="/pages/yB0XMIIob1nyTOjyZraz#thresholds">Thresholds</a></p><p>Acceptable difference between source and target.<br><br>By default, threshold = 0</p></td></tr><tr><td><code>filter</code></td><td>Yes</td><td><p><a href="/pages/6pS8St3coKJanI4KlnMI#check-level-filter">Configure a check filter</a></p><p>Filter applied to both source and target.<br><br>Support both comparison of <code>metric:percent</code> and <code>metric:count</code><br></p></td></tr><tr><td><code>name</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-names">Check names</a></td></tr><tr><td><code>qualifier</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-qualifiers">Check qualifiers</a></td></tr><tr><td><code>attributes</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-attributes">Check attributes</a></td></tr></tbody></table>

***

## Freshness diff

Compares freshness (recency of the latest timestamp) between source and target.

**Example**

```yaml
reconciliation:
  sources:
    - name: staging
      dataset: contracts-source/postgres/public/dim_employee_copy
  checks:
    - freshness_diff:
        source: staging
        column: hire_date
        threshold:
          must_be_less_than: 1
          unit: hour
```

**Configuration keys**

<table><thead><tr><th width="160.20001220703125">Key</th><th width="160.20001220703125">Optional</th><th>Description</th></tr></thead><tbody><tr><td><code>source</code></td><td>No</td><td>Name of the source to validate against</td></tr><tr><td><code>column</code></td><td>Yes</td><td>Timestamp column used to measure freshness</td></tr><tr><td><code>unit</code></td><td>Yes</td><td>Unit of time (<code>hour</code>, <code>minute</code>, <code>day</code>)</td></tr><tr><td><code>threshold</code></td><td>Yes</td><td><p><a href="/pages/yB0XMIIob1nyTOjyZraz#thresholds">Thresholds</a></p><p>Acceptable difference between source and target.<br><br>By default, threshold = 0</p></td></tr><tr><td><code>filter</code></td><td>Yes</td><td><p><a href="/pages/6pS8St3coKJanI4KlnMI#check-level-filter">Configure a check filter</a></p><p>Filter applied to both source and target</p></td></tr><tr><td><code>name</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-names">Check names</a></td></tr><tr><td><code>qualifier</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-qualifiers">Check qualifiers</a></td></tr><tr><td><code>attributes</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-attributes">Check attributes</a></td></tr></tbody></table>

***

## Metric diff

Compares results of custom SQL expressions or queries across source and target.

**Example**

```yaml
reconciliation:
  sources:
    - name: staging
      dataset: contracts-source/postgres/public/dim_employee_copy
  checks:
    - metric_diff:
        source: staging
        source_expression: SUM(employee_key + parent_employee_key)
        target_expression: SUM(employee_key + parent_employee_key)
        threshold:
          must_be_less_than: 100
```

**Configuration keys**

<table><thead><tr><th width="185.79998779296875">Key</th><th width="159.4000244140625">Optional</th><th>Description</th></tr></thead><tbody><tr><td><code>source</code></td><td>No</td><td>Name of the source to validate against</td></tr><tr><td><code>source_expression</code></td><td>No*</td><td>SQL expression for source</td></tr><tr><td><code>target_expression</code></td><td>No*</td><td>SQL expression for target</td></tr><tr><td><code>source_query</code></td><td>No*</td><td>Full SQL query for source metric</td></tr><tr><td><code>target_query</code></td><td>No*</td><td>Full SQL query for target metric</td></tr><tr><td><code>threshold</code></td><td>Yes</td><td><p><a href="/pages/yB0XMIIob1nyTOjyZraz#thresholds">Thresholds</a></p><p>Acceptable difference between source and target.<br><br>By default, threshold = 0</p></td></tr><tr><td><code>filter</code></td><td>Yes</td><td><p><a href="/pages/6pS8St3coKJanI4KlnMI#check-level-filter">Configure a check filter</a></p><p>Filter applied to both source and target</p></td></tr><tr><td><code>name</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-names">Check names</a></td></tr><tr><td><code>qualifier</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-qualifiers">Check qualifiers</a></td></tr><tr><td><code>attributes</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-attributes">Check attributes</a></td></tr></tbody></table>

\* Either expression or query must be defined.

### Compare the result of a full SQL query

`source_expression` and `target_expression` are SQL fragments that Soda wraps in a query against the bound datasets. When the value you want to compare cannot be expressed that way — because it needs a join, a subquery, a `GROUP BY`, or a different table altogether — use `source_query` and `target_query` instead. Each query must return a **single row with a single value**.

```yaml
reconciliation:
  sources:
    - name: erp
      dataset: erp_ds/erp/orders
  checks:
    - metric_diff:
        source: erp
        source_query: |
          SELECT SUM(o.amount)
          FROM erp.orders o
          JOIN erp.customers c ON c.id = o.customer_id
          WHERE c.segment = 'RETAIL'
        target_query: |
          SELECT SUM(amount)
          FROM analytics.orders
          WHERE customer_segment = 'RETAIL'
        threshold:
          must_be_less_than: 100
          metric: percent
```

Unlike the row-level [custom queries on `rows_diff`](#compare-derived-data-with-custom-sql-queries), `metric_diff` queries are **both-or-neither**: define `source_query` and `target_query` together, or neither. They are executed once per verification and take no `{{pagination}}` marker.

Use `metric_diff` queries for **aggregate-level** comparisons over derived data, and `rows_diff` custom queries when you need to know *which* records differ.

***

## Rows diff

Compares rows between source and target based on keys, and checks specified columns for differences.

{% hint style="warning" %}
Rows diff reconciliation checks are **not supported in Synapse**.
{% endhint %}

**Example**

```yaml
reconciliation:
  sources:
    - name: staging
      dataset: contracts-source/postgres/public/dim_employee_copy
  checks:
    - rows_diff:
        source: staging
        source_key_columns: [employee_key]
        target_key_columns: [employee_key]
        source_columns: [price, order_date]
        target_columns: [price, order_date]
        threshold:
          must_be: 0
          metric: percent
```

**Configuration keys**

<table><thead><tr><th width="185.20001220703125">Key</th><th width="159.4000244140625">Optional</th><th>Description</th></tr></thead><tbody><tr><td><code>source</code></td><td>No</td><td>Name of the source to validate against</td></tr><tr><td><code>source_key_columns</code></td><td>No</td><td>Key column(s) to align rows in source dataset</td></tr><tr><td><code>target_key_columns</code></td><td>No</td><td>Key column(s) to align rows in target dataset</td></tr><tr><td><code>source_columns</code></td><td>Yes</td><td>Columns to compare in the target dataset. If omitted, all columns are compared based on column order.<br><br>The number of defined source columns must match the number of defined target columns</td></tr><tr><td><code>target_columns</code></td><td>Yes</td><td>Columns to compare in the target dataset. If omitted, all columns are compared based on column order.<br><br>The number of defined target columns must match the number of defined source columns.</td></tr><tr><td><code>threshold</code></td><td>Yes</td><td><p><a href="/pages/yB0XMIIob1nyTOjyZraz#thresholds">Thresholds</a></p><p>Acceptable difference between source and target.<br></p><p><strong>Thresholds</strong> can be defined in two ways:</p><ul><li>As the <strong>count of differing rows</strong> between source and target.</li><li>As the <strong>percentage of differing rows</strong>, relative to the number of tested rows in the <strong>source dataset</strong>.</li></ul><p>By default, threshold = 0</p></td></tr><tr><td><code>filter</code></td><td>Yes</td><td><p><a href="/pages/6pS8St3coKJanI4KlnMI#check-level-filter">Configure a check filter</a></p><p>Filter applied to both source and target.<br><br>Cannot be combined with <code>source_filter</code>, <code>target_filter</code>, <code>source_query</code> or <code>target_query</code>. See <a href="#filters">Filters</a>.</p></td></tr><tr><td><code>source_filter</code></td><td>Yes</td><td><p>Filter applied to the <strong>source</strong> side only.</p><p>Combined with <code>sources[].filter</code> using <code>AND</code>. Cannot be combined with <code>filter</code> or with <code>source_query</code>. See <a href="#filters">Filters</a>.</p></td></tr><tr><td><code>target_filter</code></td><td>Yes</td><td><p>Filter applied to the <strong>target</strong> side only.</p><p>Cannot be combined with <code>filter</code> or with <code>target_query</code>. See <a href="#filters">Filters</a>.</p></td></tr><tr><td><code>source_query</code></td><td>Yes</td><td>Full SQL query that produces the <strong>source</strong> rows to compare, ending with the <code>{{pagination}}</code> marker. See <a href="#compare-derived-data-with-custom-sql-queries">Compare derived data with custom SQL queries</a>.</td></tr><tr><td><code>target_query</code></td><td>Yes</td><td>Full SQL query that produces the <strong>target</strong> rows to compare, ending with the <code>{{pagination}}</code> marker. See <a href="#compare-derived-data-with-custom-sql-queries">Compare derived data with custom SQL queries</a>.</td></tr><tr><td><code>name</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-names">Check names</a></td></tr><tr><td><code>qualifier</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-qualifiers">Check qualifiers</a></td></tr><tr><td><code>attributes</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-attributes">Check attributes</a></td></tr></tbody></table>

**Diagnostics**

A rows diff check reports the following diagnostic metrics alongside its outcome.

<table><thead><tr><th width="260">Diagnostic</th><th>Meaning</th></tr></thead><tbody><tr><td><code>diff_count</code> / <code>diff_percent</code></td><td>Total number of differing rows, and that number as a percentage of the tested source rows.</td></tr><tr><td><code>changed</code></td><td>Rows present on both sides whose compared columns differ.</td></tr><tr><td><code>exclusive_in_source</code></td><td>Rows whose key exists only in the source.</td></tr><tr><td><code>exclusive_in_target</code></td><td>Rows whose key exists only in the target.</td></tr><tr><td><code>source_check_rows_tested</code> / <code>target_check_rows_tested</code></td><td>Rows considered on each side after all filters are applied.</td></tr><tr><td><code>null_key_rows_source</code> / <code>null_key_rows_target</code></td><td>Rows excluded on each side because a key column was <code>NULL</code>. See <a href="#null-values-in-key-columns">NULL values in key columns</a>.</td></tr></tbody></table>

### Filter the source and target separately

Use `source_filter` and `target_filter` when the same SQL condition is not valid on both sides — for example when the legacy source encodes a status as `'A'` and the migrated target spells it out. Both keys are optional and independent, and each is `AND`-combined with the filter declared on the source it is bound to.

```yaml
reconciliation:
  sources:
    - name: legacy
      dataset: legacy_ds/erp/orders
      filter: created_at >= CURRENT_DATE - INTERVAL '7 days'
  checks:
    - rows_diff:
        source: legacy
        source_key_columns: [order_id]
        target_key_columns: [order_id]
        source_filter: ORD_STATUS_CD = 'A'     # source side: sources[].filter AND this
        target_filter: status = 'ACTIVE'
        threshold:
          must_be: 0
```

The legacy `filter` key still applies one condition to both sides and remains supported. It cannot be combined with the split keys. See [Filters](#filters) for the full matrix and rules.

### NULL values in key columns

Rows diff aligns the two datasets on their key columns, and a `NULL` key cannot be aligned with anything. Rows with a `NULL` in any key column are therefore **excluded from the comparison on the side they occur on**, and counted in the `null_key_rows_source` and `null_key_rows_target` diagnostics. Soda logs a warning during the scan when this happens; the check still evaluates on the remaining rows.

Excluded rows are not counted as differences. If a `NULL` key on one side means a genuine data problem, cover it with a [missing check](/reference/contract-language-reference.md#missing-check) on the key column rather than relying on the rows diff check to surface it.

{% hint style="info" %}
Choose key columns that are unique and not nullable. A high `null_key_rows_source` or `null_key_rows_target` count means a large part of the dataset was never compared.
{% endhint %}

### Compare derived data with custom SQL queries

By default, Soda generates the paginated `SELECT` statements it runs against each side. When the data you need to compare is a **derivation** — a join, a normalization, an aggregation — you can supply the SQL for one or both sides with `source_query` and `target_query`.

{% hint style="warning" %}
**Views are the recommended path.** If you can create a view over the derived data and onboard it as a dataset, do that instead: views participate in discovery, profiling, and data standards, and can be reused by other contracts and checks. A query inside a check gets none of that. Use `source_query`/`target_query` when creating a view is not possible in your organization, or when the derivation genuinely belongs in the contract itself.
{% endhint %}

**Example: normalize a source to match the target's shape**

```yaml
reconciliation:
  sources:
    - name: normalized_source
      dataset: erp_ds/erp/orders          # anchor dataset for the check
  checks:
    - rows_diff:
        source: normalized_source
        source_key_columns: [order_id]
        target_key_columns: [order_id]
        source_columns: [order_id, amount, customer_name]   # key column listed first
        target_columns: [order_id, amount, customer_name]   # same order on both sides
        source_query: |
          SELECT o.order_id, o.amount, c.customer_name
          FROM erp.orders o
          JOIN erp.customers c ON c.id = o.customer_id
          {{pagination}}
        # target side: Soda generates the paginated select, as usual
        threshold:
          must_be: 0
```

**Per-side independence.** `source_query` alone, `target_query` alone, or both are all valid. The side without a query uses the query Soda generates. This differs from [`metric_diff`](#compare-the-result-of-a-full-sql-query), where the two query keys are both-or-neither.

**The `{{pagination}}` marker.** Row-level reconciliation streams both sides page by page, so Soda must be able to order and slice your query. Each custom query must contain the literal marker `{{pagination}}` **exactly once**, in **trailing position** (only whitespace may follow it). Soda replaces it with `ORDER BY <key columns> LIMIT <page size> OFFSET <offset>` in the dialect of that side, once per page. A query with no marker, more than one marker, or a marker that is not last is rejected when the contract is parsed.

**Requirements.** Custom queries hand you the SQL, and with it the responsibility for the properties Soda otherwise guarantees:

<table><thead><tr><th width="220">Requirement</th><th>Why</th></tr></thead><tbody><tr><td>The query is <strong>deterministic</strong></td><td>Pages are separate executions. A query whose result set changes between executions produces a meaningless diff.</td></tr><tr><td>Key columns are <strong>unique</strong> and define a <strong>total order</strong></td><td>They become the <code>ORDER BY</code> that makes paging stable, and the key on which the two sides are aligned.</td></tr><tr><td>The projection matches the other side</td><td><code>source_key_columns</code>, <code>target_key_columns</code>, <code>source_columns</code> and <code>target_columns</code> refer to the <strong>columns your query projects</strong>. The number and order of projected columns must match the other side; Soda validates this on the first page and fails the check with an explicit error rather than reporting a misaligned diff. A key column that is <strong>not</strong> listed in <code>source_columns</code>/<code>target_columns</code> is appended to the <strong>end</strong> of the projection Soda generates for that side, which is rarely where a custom query projects it — so list the key columns <strong>first</strong>, in both <code>*_columns</code> lists and in the <code>SELECT</code> of every custom query.</td></tr><tr><td>Data is <strong>quiescent during the scan</strong></td><td>Soda pages with <code>LIMIT</code>/<code>OFFSET</code> and no snapshot isolation. See <a href="#caveats-and-limitations">Caveats and limitations</a>.</td></tr></tbody></table>

**Cost.** The query is re-executed for every page, exactly like a non-materialized view. A source-side filter that trims the volume before the join is usually worth more than any query tuning.

**Grouped comparisons.** To reconcile aggregates row by row rather than as a single metric, write a `GROUP BY` query on each side and use the group-by columns as the key columns:

```yaml
reconciliation:
  sources:
    - name: erp
      dataset: erp_ds/erp/orders
  checks:
    - rows_diff:
        source: erp
        source_key_columns: [region, order_month]
        target_key_columns: [region, order_month]
        source_query: |
          SELECT region, DATE_TRUNC('month', order_date) AS order_month, SUM(amount) AS total
          FROM erp.orders
          GROUP BY region, DATE_TRUNC('month', order_date)
          {{pagination}}
        target_query: |
          SELECT region, order_month, total_amount AS total
          FROM analytics.orders_by_region_month
          {{pagination}}
```

{% hint style="info" %}
Queries, like filters, are **not** part of a check's identity. Editing a query keeps the check's history in Soda Cloud.
{% endhint %}

Filters and queries cannot both apply to the same side of the same check — put the condition inside the query. `source_query` with `target_filter` is valid. If the bound source declares `sources[].filter` and the check uses `source_query`, Soda logs a **warning** at parse time: that check's source side bypasses the source filter, while other checks on the same source keep using it.

### Limitations

* It is **not possible** to perform rows diff reconciliation with **datasets located in the same Snowflake data source**.\
  If your data source is Snowflake, and you wish to reconcile datasets within Snowflake, one of the datasets to reconcile must be:
  * in a different Snowflake instance, **or**
  * in a different data source type.
* If the source and target project a **different number of columns**, the check cannot align them and reports `NOT_EVALUATED`. Add a [schema diff](#schema-diff) check to the same source to see exactly which columns differ.

***

## Schema diff

Compares the **actual schema** of the source dataset with the **actual schema** of the target dataset: which columns exist on each side, and — optionally — whether their data types match. The check fails on any difference.

**Example**

```yaml
reconciliation:
  sources:
    - name: warehouse
      dataset: wh_ds/public/orders_copy
  checks:
    - schema_diff:
        source: warehouse
```

**Example with mapping and exclusions**

```yaml
reconciliation:
  sources:
    - name: legacy
      dataset: legacy_ds/erp/orders
  checks:
    - schema_diff:
        source: legacy
        column_mapping:            # renamed columns, source -> target
          ORD_ID: order_id
          ORD_AMT: amount
        ignore_columns: [etl_loaded_at, _ingestion_batch]
        types: true
```

**Configuration keys**

<table><thead><tr><th width="185">Key</th><th width="120">Optional</th><th>Description</th></tr></thead><tbody><tr><td><code>source</code></td><td>No</td><td>Name of the source to validate against</td></tr><tr><td><code>column_mapping</code></td><td>Yes</td><td>Map of source column name to target column name, for columns that were renamed. Mapped pairs are compared as if they had the same name.</td></tr><tr><td><code>ignore_columns</code></td><td>Yes</td><td>List of column names to leave out of the comparison entirely — for example technical columns that exist on one side by design.</td></tr><tr><td><code>types</code></td><td>Yes</td><td>Whether to compare data types as well as column names. Values are <code>true</code> or <code>false</code>. Default is <code>true</code>; set it to <code>false</code> for a name-only comparison.</td></tr><tr><td><code>name</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-names">Check names</a></td></tr><tr><td><code>qualifier</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-qualifiers">Check qualifiers</a></td></tr><tr><td><code>attributes</code></td><td>Yes</td><td><a href="/pages/yB0XMIIob1nyTOjyZraz#check-attributes">Check attributes</a></td></tr></tbody></table>

**Diagnostics**

<table><thead><tr><th width="220">Diagnostic</th><th>Contents</th></tr></thead><tbody><tr><td><code>missingInTarget</code></td><td>Columns present in the source and absent from the target. Each entry is an object: the column <code>name</code> and its <code>type</code>, as declared on the source side.</td></tr><tr><td><code>missingInSource</code></td><td>Columns present in the target and absent from the source. Each entry is an object: the column <code>name</code> and its <code>type</code>, as declared on the target side.</td></tr><tr><td><code>typeMismatch</code></td><td>Columns present on both sides whose data types are not equivalent. Each entry carries <code>sourceColumn</code> and <code>targetColumn</code> — they differ when <code>column_mapping</code> maps a renamed column — together with <code>sourceType</code> and <code>targetType</code>.</td></tr></tbody></table>

The check's measured value is the total number of differences, that is the three groups summed; the diagnostics carry no separate count.

Data types are compared on a **normalized** form, so that types which mean the same thing in two different systems — for example `VARCHAR(n)` and `TEXT` — are not reported as a mismatch. Column names are compared using each dialect's identifier normalization rules.

### Schema diff versus the schema check

Soda has two schema-related checks, and they answer different questions.

<table><thead><tr><th width="185">Check</th><th width="230">Compares</th><th>Use it to</th></tr></thead><tbody><tr><td><a href="/pages/yB0XMIIob1nyTOjyZraz#schema-check"><code>schema</code></a></td><td>The <strong>contract-declared</strong> columns against the <strong>actual</strong> columns of <strong>one</strong> dataset.</td><td>Detect drift away from the structure you have agreed on and written down.</td></tr><tr><td><code>schema_diff</code></td><td>The <strong>actual</strong> columns of the <strong>source</strong> dataset against the <strong>actual</strong> columns of the <strong>target</strong> dataset.</td><td>Detect that a migration, replication, or pipeline did not reproduce the structure of the system of record — regardless of what the contract declares.</td></tr></tbody></table>

They are complementary: a contract can declare its own structure with `schema`, and still assert with `schema_diff` that the upstream system it was copied from has not diverged.

### Migrating a v3 "compare schema" check

In SodaCL (v3), a reconciliation block could contain a bare `schema` check, which compared the source and target schemas. There is no v4 equivalent in the core `schema` check — that one compares the contract to a single dataset. Use `schema_diff` instead.

**SodaCL (v3)**

```yaml
reconciliation orders:
  datasets:
    source:
      datasource: legacy_ds
      dataset: orders
    target:
      datasource: wh_ds
      dataset: orders_copy
  checks:
    - schema
```

**Contract (v4)**

```yaml
dataset: wh_ds/public/orders_copy

reconciliation:
  sources:
    - name: legacy
      dataset: legacy_ds/erp/orders
  checks:
    - schema_diff:
        source: legacy
```

{% hint style="info" %}
Reconciliation checks are not translated by the [v3 to v4 migration](/reference/migrate-from-v3-to-v4.md) tooling; rewrite them by hand.
{% endhint %}

### Filters and queries do not apply

`filter`, `source_filter`, `target_filter`, `source_query` and `target_query` are **not** valid on a `schema_diff` check. A schema is dataset metadata, not a row set, so a row filter has no meaning here — declaring one is a parse error. To narrow the comparison, use `ignore_columns`.

***

## Caveats and limitations

### Data should be quiescent during a scan

Row-level reconciliation reads both sides in pages, using `LIMIT` and `OFFSET`. Each page is a **separate query execution**, and Soda does not hold a snapshot across them — no transaction spans the scan, on either side.

If rows are inserted, deleted, or re-ordered between pages, rows can be read twice or skipped, and the resulting diff will report differences that do not exist in either dataset. This applies to tables, to views, and to [custom SQL queries](#compare-derived-data-with-custom-sql-queries) alike.

**Recommendations**

* Schedule reconciliation scans in a window where the source and the target are not being written to — after a load completes, not during it.
* Where a quiet window does not exist, filter both sides to a **closed partition** (for example, "yesterday") so that the rows under comparison can no longer change.

### One dataset cannot back two sources

Every entry in `sources:` must reference a distinct dataset. Declaring the same dataset twice — for instance to compare two different filtered slices of it against the target — is rejected when the contract is parsed.

Per-check filters remove the main reason to want this: instead of two sources over the same dataset with different filters, declare one source and give each check its own [`source_filter`](#filter-the-source-and-target-separately).

### Filters and identity

Neither filters nor custom queries participate in check identity. You can retune a filter, split a `filter` into `source_filter` and `target_filter`, or edit a query without losing check history in Soda Cloud.

Check identity is derived from the check's **path**, which includes the name of the source the check is bound to. Renaming a source, or moving a check to a different source, therefore starts a new history — see [Migrating from single-source to multi-source](#migrating-from-single-source-to-multi-source).

***

## Migrating from single-source to multi-source

If you have an existing contract that uses the single-source `source:` syntax and want to add additional sources, you can migrate without losing check history in Soda Cloud.

Check identity in Soda Cloud is derived from the check's path. When you move from single-source to multi-source, checks that target the original source need to preserve their path so that Soda Cloud treats them as the same check and retains their historical results.

### How to migrate

Use the `default: true` flag on a source **without a name**. This unnamed default source preserves the original check path format, so Soda Cloud continues to recognize those checks as the same ones from before the migration.

**Before** (single source):

```yaml
reconciliation:
  source:
    dataset: on_prem_data_source/db/schema/dataset
    filter: created_at >= CURRENT_DATE - INTERVAL '1 day'
  checks:
    - row_count_diff:
    - aggregate_diff:
        function: avg
        column: employee_key
```

**After** (multi-source, preserving history):

```yaml
reconciliation:
  sources:
    - dataset: on_prem_data_source/db/schema/dataset
      filter: created_at >= CURRENT_DATE - INTERVAL '1 day'
      default: true
    - name: warehouse
      dataset: warehouse_data_source/db/schema/dataset
  checks:
    - row_count_diff:                    # runs against default source, same check identity
    - aggregate_diff:                    # runs against default source, same check identity
        function: avg
        column: employee_key
    - row_count_diff:
        source: warehouse                # new check against warehouse
```

### Key points

* The original source becomes an entry with `default: true` and no `name`. This preserves check paths and therefore history.
* Checks without a `source:` field continue to run against the default source.
* New sources get a `name`, and checks targeting them use `source: <name>`.
* `default: true` **cannot** be combined with `name`; the unnamed default is exclusively a migration mechanism.
* Only one source can be `default: true`.

***

<details>

<summary><strong>Legacy single-source syntax</strong></summary>

The original single-source syntax uses `source:` (singular) instead of `sources:` (plural). This syntax is still supported and is equivalent to defining a single unnamed default source.

```yaml
reconciliation:
  source:
    dataset: on_prem_data_source/db/schema/dataset
    filter: created_at >= CURRENT_DATE - INTERVAL '1 day'
  checks:
    - row_count_diff:
        threshold:
          must_be_less_than: 1
    - aggregate_diff:
        function: avg
        column: employee_key
```

With single-source syntax, checks do not need a `source:` field since there is only one source to target.

{% hint style="info" %}
If you are writing a new contract, use the `sources:` (plural) syntax instead. See [Migrating from single-source to multi-source](#migrating-from-single-source-to-multi-source) if you want to add additional sources to an existing single-source contract.
{% endhint %}

</details>

***

## Reconciliation checks with in-memory data sources (DuckDB, Spark)

Reconciliation checks can compare datasets that live in **different execution contexts**, such as:

* a dataset in a database (for example, PostgreSQL, Snowflake), and
* a dataset in an **in-memory data source** (for example, [DuckDB](/reference/data-source-reference-for-soda-core/duckdb.md) in-memory or a [Spark Dataframe](/reference/data-source-reference-for-soda-core/spark-dataframe.md)).

***

### Compare DuckDB datasets

To compare DuckDB datasets, you must pass **two different values** in `data_sources`:

1. The in-memory data source
2. The database data source

### Compare DuckDB to another data source

If you want to compare a DuckDB dataset to a different data source (for example, Postgres), you must pass DuckDB in `data_sources` and Data source config in `data_source_file_paths`:

1. The in-memory data source, passed directly as a `data_sources` object
2. The database data source, passed via a `data_source_file_paths` configuration file

#### Example: DuckDB (in-memory) vs PostgreSQL

The example below shows how to reconcile a DuckDB in-memory dataset with a PostgreSQL dataset using `verify_contract_locally`:

```python
result = verify_contract_locally(
    data_sources=[DuckDBDataSourceImpl.from_existing_cursor(cursor, name="snowflake")],
    data_source_file_paths=["../postgres_supabase.yaml"],
    contract_file_path="🐼.contract.yaml",
)
```

### Spark DataFrames

The same pattern applies when reconciling against **Spark DataFrames**:

* The Spark DataFrame is passed as an in-memory data source
* The database connection is provided via a YAML file in `data_source_file_paths`

***

{% hint style="info" %}
You are **not logged in to Soda** and are viewing the default public documentation. Learn more about [Licensing & documentation access](/reference/documentation-access-and-licensing.md).

If you do have a Soda license, make sure to **log in to Soda Cloud in this same browser**.
{% endhint %}


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.soda.io/reference/contract-language-reference/reconciliation-checks.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.