Reconciling Source and Target with Key-Based Templates

What this guide is for

Reconciliation answers one question: does the target hold what the source says it should? It sounds like one test. In practice it is four questions, and they have to be asked in order:

1. Can the two sides be compared at all? Same columns, a key that identifies one row, no null keys.
2. Is everything there? The same rows, counted overall, per segment and per key.
3. Is anything extra? Rows in the target that the source never sent.
4. Did anything change? Rows present on both sides whose values differ.

Asking them out of order produces noise. A full row compare on a table whose key is not unique reports thousands of mismatches that are really duplicate rows paired at random. A compare on a target that dropped a column fails with a SQL error instead of a finding. This guide walks through setting up the Key-Based Reconciliation pack so each question is asked at the right moment, and through reading the answers.

The pack works for any pair of tables whose column names match: staging and warehouse, an old platform and a new one, production and a restored copy, a nightly extract and its source. Keys can span several columns.

What the pack contains, template by template, is in Key-Based Reconciliation Test Pack. How to choose a key is in Reconciliation Key Finder.

How the templates work

Every template in the pack is a metadata-driven template test. It picks up the source tables you mark for reconciliation in the catalog, and materializes one child test per table. For each table, the template reads the column list from the catalog and sorts each column into one of three roles:

The template then writes the same SQL against the source table and the target table. The target is the table named by the source table's Reconciliation Target Table field, in the schema named by its Reconciliation Target Schema field, or by the template's Target Schema variable when that field is blank.

So the keys and targets are catalog data, not template code. Change a table's key in the catalog, re-materialize, and every template picks it up. The SQL never needs editing for a new table.

Which tables are reconciled

No template lists tables. Each one selects with a metadata filter on the catalog:

• Fields version: every table whose Reconciliation Target Table is set.
• Tags version: every table with the Table Tag Reconciliation Needed.

A table joins all fourteen templates as soon as it is marked, and the next materialization creates its child tests. A freshly imported pack selects nothing until you mark tables. Because the pack names no tables, it imports onto any data source without missing-metadata warnings.

Note: Child test SQL is written when you materialize. After you change a Reconciliation Role or Target value, materialize the templates again before running them, or the children keep the old key.

Two versions: fields or tags

Adding catalog custom fields needs Govern Edition. So the pack ships twice, with the same fourteen templates in each. They differ only in where they read roles and targets:

Choose the fields version on Govern Edition: dedicated fields are easier to read and to filter on, and they leave your tags alone. Choose the Tags version on QA Edition, or wherever adding catalog fields is not an option. On the sample warehouse the two produce identical results, template for template.

The rest of this guide says "set Reconciliation Role to Key". In the Tags version, read that as "add the Column Tag Reconciliation Role: Key".

Rules for the tags

• Opt in with Reconciliation Needed. Every template selects the tables that carry this Table Tag, spelled exactly so. A table without it is not reconciled, whatever other reconciliation tags it has. Validatar's tag filters match whole tags, which is why the opt-in is its own tag and not part of the target tag.
• Exact text. Reconciliation Role: followed by Key, Value or Ignore, with the capital letters and the single space after the colon as shown. A tag spelled any other way is not read.
• One role tag per column, one target tag of each kind per table. If a column carries more than one role tag, Ignore wins, then Key.
• Other tags are fine. A column tagged PII and Reconciliation Role: Key is a key column.
• No role tag means the fallback. Key when the column is part of a declared primary key, otherwise Value.

Before you start

• Edition. On Govern Edition, use the fields version with the Reconciliation Catalog Custom Fields. On QA Edition, use the Tags version. Neither version needs column roles on tables that declare a primary key: the declared key becomes the key and every other column a value. The tables still need marking (Step 2). SQL Server, PostgreSQL and Databricks ingest declared primary keys. Snowflake does not.
• Data sources. Each template runs its test side on a source data source and its control side on a target data source. The source data source's catalog must hold the source tables, because that is where the templates read columns, roles and targets. The target data source only has to reach the target tables, and it can be on a different platform. When one database holds both sides, use the same data source for both. The Key Finder runs both of its sides on the source.
• Metadata. Ingest metadata on the source data source so the catalog has the current columns.
• Permissions. Creating tests in the target project, and editing catalog metadata on the source data source to set roles.

Step 1: Import the fields and the pack

For the fields version:

1. In Validatar, open Marketplace, find Reconciliation - Catalog Custom Fields, choose Get, then Import Directly. On Settings > Catalog Custom Fields, import the panel's section at the Table level and at the Column level (Import All once at each, then Save). Reconciliation Catalog Custom Fields has the detail.
2. Back in Marketplace, find Key-Based Reconciliation and choose Import. Pick the project and map the pack's two data sources: Reconciliation Source to the data source that holds your source tables, and Reconciliation Target to the one that reaches your targets. Map both to the same data source when one database holds both sides. Choose a folder and commit.

For the Tags version, skip step 1, and in step 2 import Key-Based Reconciliation (Tags).

The templates arrive selecting nothing. Each one picks up the tables you mark in Step 2. To follow this guide on a training instance, mark the six SAMPLE_DW staging tables listed under What the sample shows.

Step 2: Mark the tables and point at the targets

Marking a source table is the whole selection. Every template picks it up at the next materialization.

• Fields version: open the source table in the catalog and set Reconciliation Target Table to the target's name, even when it matches the source's. Set Reconciliation Target Schema too when the target lives outside the template's Target Schema.
• Tags version: add the Table Tag Reconciliation Needed. Add Reconciliation Target Table: <name> when the target's name differs from the source's, and Reconciliation Target Schema: <name> when its schema differs from Target Schema.

In the sample, STAGING.S_DIM_ACCOUNT_10 loads STAR_SCHEMA.DIM_ACCOUNT. In the fields version its Reconciliation Target Table is DIM_ACCOUNT. In the Tags version it carries Reconciliation Needed and Reconciliation Target Table: DIM_ACCOUNT. To mark many tables at once, use the Bulk Edit, Excel import or script described for roles in Step 4.

Then, on each template's Test Definition tab:

1. Variables: set Target Schema to the schema the targets live in.
2. Control data source: the import set it to the data source you mapped Reconciliation Target to. Change it only to point one template somewhere else.

Leave Object Selection and Definition as it is: its filter is what reads the mark. To narrow one template further, add a filter beside it, for example on Schema Name. Filters combine with AND.

Step 3: Find the keys

A keyed comparison is only as good as its key. Before any other template, run the Reconciliation Key Finder. For every marked table, it scores each way of choosing a key on the table's own data: the Reconciliation Role field, the declared primary key, the first column, and column names containing ID, KEY or the table name. Each strategy reports the columns it picked, how many key values repeat, and how many rows have a null in the key.

The Key Finder child test passes when the key the other templates will use is unique and never null. Before any key is set, every child fails with NO CANDIDATE on its first row. That is the expected starting point: it means no key is defined yet.

Read the candidate rows and pick the key that identifies the same business row on both sides. Three rules cover most decisions:

• Prefer the business key over a load surrogate. A surrogate generated during the load (a sequence, a hash added by the pipeline) may not mean the same thing on both sides. Use it only when both sides carry the identical value.
• Expect composite keys. In multi-tenant and merged data, an id is often unique only within its source. In the sample, ACCOUNT_ID repeats once in DIM_ACCOUNT because two source customers use the same account id. The key is SOURCE_CUSTOMER plus ACCOUNT_ID.
• Do not trust a strategy because it says UNIQUE. On S_DIM_ACCOUNT_10, "column name contains ID" picks ACCOUNT_ID, CLIENT_ID and ETL_INSERT_BATCH_ID, and that combination is unique, but only because a load batch id is part of it. A key that includes load columns matches nothing on the target.

Reconciliation Key Finder covers the strategies, the result columns and how to add a strategy of your own.

Step 4: Set the roles

Mark the key columns Key. Then mark Ignore on every column that is expected to differ between the two sides: load timestamps, batch ids, the name of the file a row came from. Leave everything else blank, which makes it a value.

The Ignore step matters more than it looks. In the sample, S_DIM_CLAIM_10 and DIM_CLAIM hold identical claims, yet a full row compare that includes the three ETL_INSERT_* columns fails on every one of its 53,313 rows, because each layer stamps its own load time. With those columns marked Ignore, the same compare finds no differences.

There are three ways to set the fields.

Bulk Edit, for a few tables. Open the source table in the catalog, go to the Columns tab, select the key columns, choose Bulk Edit, set Reconciliation Role to Key and save. Do the same for the Ignore columns. The data source and schema Columns tabs offer the same Bulk Edit across tables.

Excel import, for many tables at once. On the source data source, open Custom Metadata, generate the import file, fill in one row per column (schema.name, table.name, column.name, reconciliation_role), and upload it. Role values must be exactly Key, Value or Ignore.

A custom metadata script, for rules you want to keep. When keys follow a naming convention, let a query set them, and run it again whenever new tables arrive. On the source data source, go to Custom Metadata > Metadata Sources > New Source > New Query Source, keep Same as primary, and put a query in the column-level script that returns schema.name, table.name, column.name and reconciliation_role. The query the sample uses, in Snowflake syntax:

-- Key:    SOURCE_CUSTOMER, and <ENTITY>_ID where the table is S_DIM_<ENTITY>_10 or S_FACT_<ENTITY>_10
-- Ignore: the ETL_INSERT_* load columns
-- Columns with no rule are not returned, so their role stays as it is.
SELECT "schema.name", "table.name", "column.name", "reconciliation_role"
FROM (
    SELECT c.table_schema AS "schema.name",
           c.table_name   AS "table.name",
           c.column_name  AS "column.name",
           CASE WHEN c.column_name = 'SOURCE_CUSTOMER'
                  OR c.column_name = REGEXP_REPLACE(c.table_name, '^S_(DIM|FACT)_(.+)_10$', '\\2') || '_ID' THEN 'Key'
                WHEN STARTSWITH(c.column_name, 'ETL_INSERT_') THEN 'Ignore'
           END AS "reconciliation_role"
    FROM INFORMATION_SCHEMA.COLUMNS c
    WHERE c.table_schema = 'STAGING'
) roles
WHERE "reconciliation_role" IS NOT NULL

The table-level script of the same source can set the targets the same way, returning schema.name, table.name and reconciliation_target_table. Setting the target also marks the table:

SELECT t.table_schema AS "schema.name",
       t.table_name   AS "table.name",
       REGEXP_REPLACE(t.table_name, '^S_(.+)_10$', '\\1') AS "reconciliation_target_table"
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.table_schema = 'STAGING'

Tick Ignore Missing Metadata Objects, use Preview Script to check the rows, then save and Refresh Now. Return only rows that have a value: a role that is not one of the three options makes Validatar reject the run.

Setting tags in the Tags version

The same three ways work, with one difference that matters: an import replaces a column's or table's whole tag list. A custom metadata script or an Excel import that sets Reconciliation Role: Key on a column removes every other tag that column had. In testing, a key column tagged Customer Identifier came out of the sample script with only Reconciliation Role: Key. Columns the import does not return keep their tags.

• Bulk Edit is the safe way to add a role tag to columns that already carry other tags. Choose Add to Existing rather than replacing.
• In an Excel import, a cell can hold several tags as a list, for example ["PII", "Reconciliation Role: Key"]. Include the tags a column already has.
• Use a script only on objects whose tags you own, or have it return the full tag list. For the Tags version, the column script returns column_tags with values such as 'Reconciliation Role: ' || 'Key'. The table script returns both table tags in one value, as a list:

SELECT t.table_schema AS "schema.name",
       t.table_name   AS "table.name",
       '["Reconciliation Needed", "Reconciliation Target Table: '
           || REGEXP_REPLACE(t.table_name, '^S_(.+)_10$', '\\1') || '"]' AS "table_tags"
FROM INFORMATION_SCHEMA.TABLES t
WHERE t.table_schema = 'STAGING'

Whichever way you choose, materialize the templates again, then run the Key Finder. Every child should now pass.

Step 5: Run the reconciliation in order

Run the templates as a ladder. Each rung assumes the one below it passed, and each is cheaper than the one above it.

Two reasons to keep the order:

• Rung 1 failures masquerade as data failures higher up. A missing target column becomes a SQL error on every row-level template. A duplicate key becomes hundreds of value mismatches, because a keyed comparison pairs duplicate rows in sorted order and cannot tell which belongs with which.
• Cost grows with every rung. The totals touch two numbers per table. The full row compare moves every column of every row from both sides. On large tables, run rungs 1 to 3 on every load and the row compares less often, or incrementally.

What the sample shows

On a training instance with the six sample tables marked and the roles from Step 4, the pack finds every real defect in the sample warehouse. Verified 2026-10-20:

The policy table is the ladder in miniature. Columns Align names the problem in one line, and that one line explains the errors in five other templates.

Reading the failures

Every reconciliation template stops reading after 100 failing rows (Abort Processing After Failures). A failing child therefore shows at most 100 rows. Use the count templates for the full size of a problem, and raise the limit on the template when you need every row.

Running it every day

• Put the cheap rungs in the load job. Columns Align, Key Is Unique, Key Coverage Summary and Row Count per Segment add little to a load and catch most failures. Run them right after the target is loaded.
• Run the row compares on a schedule that fits their cost. Daily with the Incremental Full Row Compare over the last day or week, and a full compare weekly or before a release.
• Materialize when the catalog changes. Newly marked tables, new columns and new roles reach the children only through materialization. Add a materialize step ahead of the runs in the job, or materialize after each metadata change.
• Route failures to the people who fix them. The Jira integration opens one issue per failing child and comments on it while it keeps failing.
• Keep the history. A reconciliation that passed yesterday and fails today is the most useful thing the pack produces. Retain results long enough to compare.

Best practices

• Keys are decisions. Record them once. Put them in the Reconciliation Role field, not in template copies. A custom metadata script that sets them from a rule is easier to review than hundreds of hand edits.
• Rerun the Key Finder after schema changes. A new column, a merged source or a changed load can make yesterday's key non-unique.
• Never compare load metadata. Load times, batch ids and file names differ by design. Mark them Ignore once, at the start.
• Reconcile at the grain the target stores. If the target aggregates the source, compare totals per key at the target's grain, not rows.
• Fix structure before data. Rung 1 failures first, always.
• Make every allowance explicit. If a known difference is acceptable while it is fixed, record it (an Ignore role, a template copy with a tolerance) rather than learning to ignore a red test.
• Expect exact comparisons. Values are compared exactly and text is case-sensitive. Normalize deliberately, with the Normalized Full Row Compare, when the two sides are meant to differ in format.
• Prove a migration on a sample first. Select a handful of tables, get every rung green, then widen the selection.

Platform notes

The templates are proven on Snowflake and written to run on SQL Server, PostgreSQL and Oracle too. Every identifier is double-quoted, numbers are cast to DECIMAL instead of rounded with a platform function, and every query reads from a table. The few settings that differ by platform are variables:

• A variable applies to both sides. When the source and target run on different platforms, pick a value both accept, or copy the template and edit one side's SQL. Columns Align across platforms usually needs the copy, because each side reads its own metadata view.
• Names match exactly. Quoted identifiers are case-sensitive. The catalog records names as the database stores them (PostgreSQL folds unquoted names to lower case, Oracle and Snowflake to upper case), so this only matters when the source and target spell a name differently.
• Row Fingerprint per Key is Snowflake-only. It uses Snowflake's HASH, which other platforms lack, and which returns different values for the same data held in different types. Both sides must be Snowflake.

Related

• Key-Based Reconciliation Test Pack
• Reconciliation Key Finder
• Reconciliation Catalog Custom Fields
• Template Tests
• Understanding Child Test Materialization