Reconciliation checks

threshold

Yes

Acceptable difference between source and target. By default, threshold = 0

filter

Yes

Configure a check filter, applied to both source and target.

Cannot be combined with source_filter or target_filter. See Filters.

source_filter

Yes

Filter applied to the source side only.

Combined with sources[].filter using AND. Cannot be combined with filter. See Filters.

target_filter

Yes

Filter applied to the target side only.

Cannot be combined with filter. See Filters.

qualifier

Yes

attributes

Yes

Aggregate diff

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

Example

Configuration keys

Key

Optional

Description

source

Name of the source to validate against

function

Aggregate function (avg, sum, min, max, avg_length, etc.)

source_column

Column to aggregate on the source side

target_column

Column to aggregate on the target side

source_column_expression

Yes

SQL expression applied to source_column before it is aggregated, evaluated on the source side. See Transform values before comparing.

target_column_expression

Yes

SQL expression applied to target_column before it is aggregated, evaluated on the target side.

threshold

Yes

Acceptable difference between source and target. By default, threshold = 0

filter

Yes

Filter applied to both source and target. Cannot be combined with source_filter or target_filter. See Filters.

source_filter

Yes

Filter applied to the source side only.

Combined with sources[].filter using AND. Cannot be combined with filter. See Filters.

target_filter

Yes

Filter applied to the target side only.

Cannot be combined with filter. See Filters.

name

Yes

qualifier

Yes

attributes

Yes

Duplicate diff

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

Example

Configuration keys

Key

Optional

Description

source

Name of the source to validate against

source_columns

List of column(s) to evaluate duplicates on, on the source side

target_columns

List of column(s) to evaluate duplicates on, on the target side, matched to source_columns by position

threshold

Yes

Acceptable difference between source and target. By default, threshold = 0

filter

Yes

Filter applied to both source and target. Cannot be combined with source_filter or target_filter. See Filters.

source_filter

Yes

Filter applied to the source side only.

Combined with sources[].filter using AND. Cannot be combined with filter. See Filters.

target_filter

Yes

Filter applied to the target side only.

Cannot be combined with filter. See Filters.

name

Yes

qualifier

Yes

attributes

Yes

Freshness diff

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

Example

Configuration keys

Key

Optional

Description

source

Name of the source to validate against

source_column

Timestamp column used to measure freshness on the source side

target_column

Timestamp column used to measure freshness on the target side

source_column_expression

Yes

SQL expression applied to source_column before its maximum is read, evaluated on the source side. See Transform values before comparing.

target_column_expression

Yes

SQL expression applied to target_column before its maximum is read, evaluated on the target side.

unit

Yes

Unit of time (hour, minute, day)

threshold

Yes

Acceptable difference between source and target. By default, threshold = 0

filter

Yes

Filter applied to both source and target. Cannot be combined with source_filter or target_filter. See Filters.

source_filter

Yes

Filter applied to the source side only.

Combined with sources[].filter using AND. Cannot be combined with filter. See Filters.

target_filter

Yes

Filter applied to the target side only.

Cannot be combined with filter. See Filters.

name

Yes

qualifier

Yes

attributes

Yes

Metric diff

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

Example

Configuration keys

Key

Optional

Description

source

Name of the source to validate against

source_expression

No*

SQL expression for source

target_expression

No*

SQL expression for target

source_query

No*

Full SQL query for source metric

target_query

No*

Full SQL query for target metric

threshold

Yes

Acceptable difference between source and target. By default, threshold = 0

filter

Yes

Filter applied to both source and target. Expression mode only. Cannot be combined with source_filter, target_filter, source_query or target_query — in query mode, put the condition in the query. See Filters.

source_filter

Yes

Filter applied to the source side only. Expression mode only.

Combined with sources[].filter using AND. Cannot be combined with filter or source_query. See Filters.

target_filter

Yes

Filter applied to the target side only. Expression mode only.

Cannot be combined with filter or target_query. See Filters.

name

Yes

qualifier

Yes

attributes

Yes

* 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.

Unlike the row-level custom queries on rows_diff, 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.

Rows diff reconciliation checks are not supported in Synapse.

Example

Configuration keys

Key

Optional

Description

source

Name of the source to validate against

source_key_columns

Key column(s) to align rows in source dataset

target_key_columns

Key column(s) to align rows in target dataset

source_columns

Yes

Columns to compare in the target dataset. If omitted, all columns are compared based on column order. The number of defined source columns must match the number of defined target columns

target_columns

Yes

Columns to compare in the target dataset. If omitted, all columns are compared based on column order. The number of defined target columns must match the number of defined source columns.

source_column_expressions

Yes

Map of column name to SQL expression, applied to the source side before the comparison.

Every key must be a column listed in source_key_columns or source_columns. Cannot be combined with source_query. See Transform values before comparing.

target_column_expressions

Yes

Map of column name to SQL expression, applied to the target side before the comparison.

Every key must be a column listed in target_key_columns or target_columns. Cannot be combined with target_query. See Transform values before comparing.

threshold

Yes

Acceptable difference between source and target.

Thresholds can be defined in two ways:

As the count of differing rows between source and target.
As the percentage of differing rows, relative to the number of tested rows in the source dataset.

By default, threshold = 0

filter

Yes

Filter applied to both source and target. Cannot be combined with source_filter, target_filter, source_query or target_query. See Filters.

source_filter

Yes

Filter applied to the source side only.

Combined with sources[].filter using AND. Cannot be combined with filter or with source_query. See Filters.

target_filter

Yes

Filter applied to the target side only.

Cannot be combined with filter or with target_query. See Filters.

source_query

Yes

Full SQL query that produces the source rows to compare, ending with the {{pagination}} marker. See Compare derived data with custom SQL queries.

target_query

Yes

Full SQL query that produces the target rows to compare, ending with the {{pagination}} marker. See Compare derived data with custom SQL queries.

name

Yes

qualifier

Yes

attributes

Yes

Diagnostics

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

Diagnostic

Meaning

diff_count / diff_percent

Total number of differing rows, and that number as a percentage of the tested source rows.

changed

Rows present on both sides whose compared columns differ.

exclusive_in_source

Rows whose key exists only in the source.

exclusive_in_target

Rows whose key exists only in the target.

source_check_rows_tested / target_check_rows_tested

Rows considered on each side after all filters are applied.

null_key_rows_source / null_key_rows_target

Rows excluded on each side because a key column was NULL. See NULL values in key columns.

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.

The legacy filter key still applies one condition to both sides and remains supported. It cannot be combined with the split keys. See 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 on the key column rather than relying on the rows diff check to surface it.

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.

Transform values before comparing

The two sides of a reconciliation rarely store the same fact in the same shape. A legacy source pads its text columns with trailing spaces where the migrated target does not; a source records a discount as 95 where the target records 0.95. Neither is a data problem, but a plain comparison reports every one of those rows as changed on a rows diff, and every one of those values as exclusive to its side on a reference diff.

source_column_expressions and target_column_expressions handle this one column at a time. Each is a map of column name to SQL expression, evaluated on that side's connection. Soda projects the expression aliased to the column name, so everything downstream — the comparison, the diagnostics, and the failed-row samples — sees the transformed value.

Both maps are available on the two row-level check types, rows_diff and reference_diff. The single-column check types — aggregate_diff and freshness_diff — take the singular form instead: source_column_expression and target_column_expression, one SQL expression per side with no column key, matching the column-level column_expression. duplicate_diff, row_count_diff, metric_diff and schema_diff take no expressions — a metric_diff side is already a full SQL expression.

Example: normalize whitespace, and rescale a percentage

On the source side Soda projects TRIM(name) AS name and discount_pct / 100 AS discount_pct in place of the stored columns; the target side is untouched. The two maps are independent — transform one side, the other, or both. When the same normalization is needed on both sides, declare it in both maps:

Example: normalize the values a reference diff compares

A reference diff takes the same two maps. It has no key columns, so an expression key must name a column listed in source_columns or target_columns — or in the columns shorthand, which sets both lists at once.

The SELECT DISTINCT that reads each side runs on the expression result, so 'abc ' in the source and 'ABC' in the target are one and the same value — which is the point — and codes that differ only in case or padding stop being reported as exclusive to a side.

What the transformed value reaches

Stage

Value used

Row alignment on the key columns (rows diff)

Expression result. The ORDER BY that pages and merges the side uses the expression, not the stored column.

Column comparison (changed, rows diff)

Expression result on each side.

Distinct-value comparison (reference diff)

Expression result. The SELECT DISTINCT and the ordering that pages each side both run on the expression, so two values the expression makes equal count as one. See Distinct values and NULL handling.

Diagnostics (diff_count, changed, exclusive_in_source, exclusive_in_target)

Expression result.

Failed-row samples in Soda Cloud

Expression result. The stored value is not reported alongside it — what you see is what was compared.

NULL exclusion — the key columns of a rows diff (null_key_rows_source / null_key_rows_target), the compared columns of a reference diff

Expression result. An expression that evaluates to NULL excludes its row exactly like a stored NULL does. See NULL values in key columns and Distinct values and NULL handling.

Row counts (source_check_rows_tested, target_check_rows_tested) and the metric-level check types

Unaffected. Expressions transform values, not row sets — only which rows a filter selects, and the NULL exclusion above, change how many rows are counted. Row count diff, aggregate diff and the other metric-level checks read the stored columns and take no expressions.

Rules

Every key of an expression map must be a column that side actually compares: on a rows_diff check, one listed in source_key_columns or source_columns (and target_key_columns or target_columns for the target map); on a reference_diff check, which has no key columns, one listed in source_columns or target_columns. A key that names anything else is a parse error rather than a silently ignored expression.
A side that uses column expressions must define its source_columns/target_columns list explicitly — on a reference_diff check, the columns shorthand counts as defining both. When the lists are omitted, Soda compares all columns by position (SELECT *), and an expression cannot be applied to that projection — this combination is a parse error.
A blank expression is a parse error.
A per-column expression and a custom SQL query on the same side of the same check is a parse error — a query already projects whatever SQL you want, so bake the expression into it. source_column_expressions together with target_query is valid. The rule concerns rows_diff alone: a reference_diff check takes no custom queries, so its expression maps have nothing to conflict with.
Both maps are rejected on a schema diff, which compares column metadata rather than values. See Filters and queries do not apply.
Expressions and filters compose freely: the filter selects which rows are read, from the stored values; the expression transforms the values that are then compared.
An expression is a raw SQL fragment evaluated on that side's connection, in that side's dialect — the same trust model as filters and custom queries.

On a rows diff, an expression on a key column can collapse distinct keys. TRIM(order_ref) maps 'A1 ' and 'A1' onto the same key. Rows diff aligns the two sides with an ordered merge that expects keys to be unique: rows sharing a key are paired in the order they stream, and any surplus copy on one side is reported as exclusive to that side. Collapsed keys land in that existing duplicate-key behaviour unchanged, so verify that the key is still unique after the expression. A reference diff has no such constraint — it compares sets of distinct values, so an expression that maps two values onto one simply makes them one value in the compared set.

Cost. The expression is evaluated by the database for every row read. An expression that lands in the ORDER BY — a key column on a rows diff, any compared column on a reference diff — can stop the database from using an index on that column; on a large dataset that, rather than the transformation itself, is what you will feel.

Expressions, like filters and queries, are not part of a check's identity. Adding, editing, or removing one keeps the check's history in Soda Cloud.

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.

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.

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

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, 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:

Requirement

Why

The query is deterministic

Pages are separate executions. A query whose result set changes between executions produces a meaningless diff.

Key columns are unique and define a total order

They become the ORDER BY that makes paging stable, and the key on which the two sides are aligned.

The projection matches the other side

source_key_columns, target_key_columns, source_columns and target_columns refer to the columns your query projects. 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 not listed in source_columns/target_columns is appended to the end of the projection Soda generates for that side, which is rarely where a custom query projects it — so list the key columns first, in both *_columns lists and in the SELECT of every custom query.

Data is quiescent during the scan

Soda pages with LIMIT/OFFSET and no snapshot isolation. See Caveats and limitations.

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:

Queries, like filters, are not part of a check's identity. Editing a query keeps the check's history in Soda Cloud.

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 check to the same source to see exactly which columns differ.

Reference diff

Verifies that the values of one or more columns also exist on the other side of the comparison — referential integrity across two data sources. Use it for foreign keys, code tables, and dimension keys that must stay aligned with a system of record that lives in a different database.

Reference diff compares sets of distinct values, not rows: it needs no key columns, and duplicates on either side collapse before the comparison. Where rows diff answers are these two datasets the same, row by row, reference diff answers does every value here have a counterpart there.

Example

Every product_code in the target dataset must also exist in the master product_codes dataset. Codes that exist only in the master dataset are reported, but are not violations in this direction.

Configuration keys

Key

Optional

Description

source

Name of the source to validate against

columns

Yes

Shorthand for the columns to compare when both sides name them identically. Equivalent to setting source_columns and target_columns to the same list.

source_columns

Yes

Columns whose values are read on the source side. If omitted, all columns of the source dataset are used, in schema order.

target_columns

Yes

Columns whose values are read on the target side, matched to source_columns by position. If omitted, all columns of the target dataset are used, in schema order. The number of target columns must match the number of source columns.

source_column_expressions

Yes

Map of column name to SQL expression, applied to the source side before the comparison.

Every key must be a column listed in source_columns, or in the columns shorthand; that list must be declared explicitly. See Transform values before comparing.

target_column_expressions

Yes

Map of column name to SQL expression, applied to the target side before the comparison.

Every key must be a column listed in target_columns, or in the columns shorthand; that list must be declared explicitly. See Transform values before comparing.

direction

Yes

Which side must contain the values of the other: target_must_exist_in_source (default), source_must_exist_in_target, or both. See Direction.

threshold

Yes

Acceptable number of values that violate the direction, or that number as a percentage of the tested source rows (metric: percent). By default, threshold = 0

filter

Yes

Filter applied to both source and target. Cannot be combined with source_filter or target_filter. See Filters.

source_filter

Yes

Filter applied to the source side only.

Combined with sources[].filter using AND. Cannot be combined with filter. See Filters.

target_filter

Yes

Filter applied to the target side only.

Cannot be combined with filter. See Filters.

page_size

Yes

Number of rows read per page from each side. Default is 100000.

batch_size

Yes

Number of rows fetched per round trip within a page. Default is 1.

name

Yes

qualifier

Yes

attributes

Yes

source_query and target_query are not available on a reference diff check; they are specific to rows diff. That is also why the two expression maps carry no query-exclusivity rule here: on this check type there is no query to bake an expression into.

Diagnostics

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

Diagnostic

Meaning

diff_count / diff_percent

Number of values that violate the direction, and that number as a percentage of the tested source rows.

exclusive_in_source

Distinct values found on the source side only.

exclusive_in_target

Distinct values found on the target side only.

source_check_rows_tested / target_check_rows_tested

Rows counted on each side after all filters are applied.

source_dataset_rows_tested / target_dataset_rows_tested

Rows in each dataset, before the check's own filters.

diff_percent divides a count of distinct values by a count of rows (source_check_rows_tested), whatever the direction. On a source with many duplicates or many NULLs in the compared columns, the percentage can therefore be larger than it intuitively should be. When the source side has no rows at all, the percentage cannot be computed and Soda reports an error. Prefer metric: count when the two sides differ a lot in cardinality.

Direction

direction decides which side is allowed to hold values that the other one does not have.

Value

The check passes when

Counted as differences

target_must_exist_in_source (default)

Every value in the target also exists in the source.

Values found only in the target (exclusive_in_target).

source_must_exist_in_target

Every value in the source also exists in the target.

Values found only in the source (exclusive_in_source).

both

The two sides hold exactly the same set of values.

Both counts, summed.

Any other value is reported as an error when the contract is parsed.

Both exclusive_in_source and exclusive_in_target are always reported, whatever the direction — only the ones the direction forbids are added to diff_count. A passing check therefore still shows how far the two sides have drifted apart in the tolerated direction.

Distinct values and NULL handling

Both sides are read with a SELECT DISTINCT over the compared columns, ordered by those columns and streamed page by page. Two things follow from that.

Duplicates do not matter. A value that occurs a thousand times on one side and once on the other is a match. To compare how often values occur, use duplicate diff, and to compare the rows themselves, rows diff.
Rows with a NULL in any compared column are excluded on the side where they occur: Soda adds an IS NOT NULL condition on every compared column to both queries. A NULL references nothing, so it is neither reported as missing on the other side nor counted as a difference.

Filters are applied inside that same SELECT DISTINCT — the WHERE runs before de-duplication — so filtering a side never changes what "distinct" means for it. The source side is evaluated as sources[].filter AND source_filter; see Filters.

A per-column expression does change it: the SELECT DISTINCT, the ordering, and the IS NOT NULL condition all run on the expression result rather than on the stored column. Values the expression makes equal collapse into one, and an expression that evaluates to NULL drops its row exactly like a stored NULL does.

Unlike rows diff, reference diff does not report how many rows the NULL exclusion removed. When a NULL in a compared column is itself a problem, cover it with a missing check on that column.

Compare several columns

Listing more than one column compares the tuple of their values, matched by position: the first source column against the first target column, and so on. The names may differ between the two sides, the number of columns may not.

A check whose two sides list a different number of columns, or whose columns cannot be resolved from the dataset schema, reports NOT_EVALUATED with an explicit error. So does a comparison between values that cannot be ordered against each other — an integer key on one side and a string key on the other, for example. Align the column types when that happens, casting them in a view over one side if the underlying tables cannot be changed.

Reference diff versus the reference check

Soda has two ways to assert referential integrity, and they operate at different scopes.

Check

Compares

Use it to

valid_reference_data (on the invalid check)

A column against a reference dataset in the same data source.

Enforce a foreign key inside one database, as part of the column's validity rules.

reference_diff

The distinct values of one or more columns against a dataset in another data source.

Verify that a copied, migrated, or synchronized dataset still references the same keys as the system of record it came from.

Reference diff reads both sides in pages, exactly like rows diff, so the quiescence caveat applies to it as well.

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

Example with mapping and exclusions

Configuration keys

Key

Optional

Description

source

Name of the source to validate against

column_mapping

Yes

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.

ignore_columns

Yes

List of column names to leave out of the comparison entirely — for example technical columns that exist on one side by design.

types

Yes

Whether to compare data types as well as column names. Values are true or false. Default is true; set it to false for a name-only comparison.

name

Yes

qualifier

Yes

attributes

Yes