Key-Based Reconciliation Test Pack

What this pack contains

Fourteen metadata-driven, table-level template tests that reconcile a source table against a target table whose column names match. Each template picks up every table you mark for reconciliation in the catalog, reads its columns, sorts them into Key, Value and Ignore, and writes the same query against both sides, so one template covers every marked table. Keys can span several columns.

How to set the pack up and work through a reconciliation is in Reconciling Source and Target with Key-Based Templates. This page is the reference: what each template checks, how it is built, and what it reports on the sample warehouse.

Two versions

The templates are the same, and the Tags version adds (Tags) to each template's name. Its child test names show the source table only, because the expression that reads the target from the tags is longer than a child test name allows. The target is in each child's description. Everything below applies to both versions: where it says "Reconciliation Role is Key", read "has the Column Tag Reconciliation Role: Key" for the Tags version.

Setup

Rung 1: can the sides be compared?

Rung 2: do the totals agree?

Rung 3: which keys?

Rung 4: which values?

Where to get it

Both versions are published to the Validatar marketplace:

• Key-Based Reconciliation, with the Reconciliation - Catalog Custom Fields item. Import both.
• Key-Based Reconciliation (Tags). Nothing else to import.

Prerequisites

• Validatar Cloud or Validatar Server with a template test license.
• For the fields version, Govern Edition to import the Reconciliation catalog custom fields. On QA Edition, use the Tags version.
• A source data source whose catalog holds the source tables, with metadata ingested. The templates read columns, roles and targets from this catalog, and run their test side here.
• A target data source that reaches the target tables. The templates run their control side here. It can be the same data source as the source, or one on another platform.
• To follow the sample selection, the Snowflake sample data warehouse (SAMPLE_DW), usually named Sample Data Source - Snowflake Data Warehouse on training and demo instances. Map it to both sides: its STAGING tables are the sources and its STAR_SCHEMA tables the targets.

Import

1. Fields version only: in Validatar, open Marketplace, find Reconciliation - Catalog Custom Fields, choose Get, then Import Directly, and import its section at the Table and Column levels as described in Reconciliation Catalog Custom Fields.

2. Find Key-Based Reconciliation, or Key-Based Reconciliation (Tags), and choose Import. Pick the target project; Validatar opens its import screen with the pack loaded.

3. Map the pack's two data sources:

   • Reconciliation Source - Sample Snowflake Data Warehouse to the data source that holds your source tables.
   • Reconciliation Target - Sample Snowflake Data Warehouse to the data source that reaches your target tables.

   Map both to the same data source when one database holds both sides, as it does for the sample.

4. Choose a target folder and commit.

The import creates the fourteen templates. None names a table: each selects with a metadata filter on the mark described in the next section, so it imports onto any data source without missing-metadata warnings and selects nothing until tables are marked. Each runs its test side on the data source you mapped Reconciliation Source to, and its control side on the one you mapped Reconciliation Target to. The Key Finder runs both sides on the source.

In the fields version the filter reads the Reconciliation Target Table field, so import Reconciliation - Catalog Custom Fields before the pack.

Importing from a downloaded file

Download the pack XML from the marketplace listing, then in the target project open Import, select the file, map the two data sources and commit.

Before the first run

The templates read the tables, keys and targets from the catalog, so set those first:

1. Mark each source table to reconcile by setting its Reconciliation Target Table, even when the target has the same name. For the sample: S_DIM_ACCOUNT_10 → DIM_ACCOUNT, S_DIM_CLAIM_10 → DIM_CLAIM, S_FACT_PAYMENT_10 → FACT_PAYMENT, S_FACT_EXPENSE_10 → FACT_EXPENSE, S_DIM_PROVIDER_10 → DIM_PROVIDER, S_DIM_POLICY_10 → DIM_POLICY.
2. Materialize the Reconciliation Key Finder and run it. Choose the keys from its results.
3. Set Reconciliation Role to Key on the key columns and to Ignore on load metadata. For the sample: Key on SOURCE_CUSTOMER and the table's *_ID column (ACCOUNT_ID, CLAIM_ID, and so on); Ignore on ETL_INSERT_TIMESTAMP, ETL_INSERT_MECHANISM and ETL_INSERT_BATCH_ID.
4. Materialize all the templates.

In the Tags version, steps 1 and 3 are tags: the Table Tags Reconciliation Needed and Reconciliation Target Table: DIM_ACCOUNT on S_DIM_ACCOUNT_10, the Column Tag Reconciliation Role: Key on its key columns, and Reconciliation Role: Ignore on the three load columns. Reconciliation Needed is the mark; the target tag can be left off when the target has the source's name.

The guide shows a custom metadata script that sets all of these for the sample tables in one run, for either version, and explains why a script that sets tags replaces the other tags on the objects it touches.

How the templates are built

The column roles

Inside each template, a loop over table.columns decides each column's role:

• Reconciliation Role is Ignore → left out.
• Reconciliation Role is Key or Value → that role.
• Reconciliation Role is blank → Key when the column is part of a declared primary key, otherwise Value.

The Explicit Values Only template differs in one place: a blank role means left out.

Every column at its own position

Validatar matches the two sides on column names, and the column list given to the comparison must number its columns uniquely, no higher than the number of columns the query returns. So every reconciliation query returns every column of the table in its catalog position: as itself, as an aggregate, or as a NULL placeholder marked Ignore. Row Count per Key on S_DIM_ACCOUNT_10 renders as:

SELECT
    COUNT(*) AS ROW_COUNT,
    NULL AS "ACCOUNT_UNIQUE_KEY", "ACCOUNT_ID", NULL AS "CLIENT_ID", NULL AS "ACCOUNT_OPEN_DATE", ... "SOURCE_CUSTOMER", NULL AS "SOURCE_FILE", NULL AS "CREATED_BY", NULL AS "ETL_INSERT_TIMESTAMP", NULL AS "ETL_INSERT_MECHANISM", NULL AS "ETL_INSERT_BATCH_ID"
    /* key ACCOUNT_ID */ /* key SOURCE_CUSTOMER */
FROM "STAGING"."S_DIM_ACCOUNT_10"
GROUP BY
    "ACCOUNT_ID", "SOURCE_CUSTOMER"

The same query runs against "STAR_SCHEMA"."DIM_ACCOUNT" on the target data source. Placeholders also mean an Ignore column never has to exist on the target: marking a dropped column Ignore is enough to compare the rest. The /* key ... */ line is a SQL comment listing the key columns. On a table with no key, that line becomes the column that raises the no-key error described below.

Reading the template SQL

Each template's SQL is written to be read in the template editor: one block per line, indented, with a comment that says what the block renders. This is the start of Row Count per Key's test SQL in the fields version:

SELECT
    COUNT(*) AS ROW_COUNT,
    {{#list table.columns ', '~}} {{!-- key columns as themselves, every other column as a NULL placeholder --}}
        {{~#if (eq this.cf.reconciliation_role 'Ignore')~}}
            NULL AS "{{name}}"
        {{~else~}}
            {{~#if (eq this.cf.reconciliation_role 'Key')~}}
                "{{name}}"
            {{~else~}}
                {{~#if (eq this.cf.reconciliation_role 'Value')~}}
                    NULL AS "{{name}}"
                {{~else~}}
                    {{~#if this.is_primary_key~}}
                        "{{name}}"
                    {{~else~}}
                        NULL AS "{{name}}"
                    {{~/if~}}
                {{~/if~}}
            {{~/if~}}
        {{~/if~}}
    {{~/list}}

    ...

Every template tests the roles in the same order: Ignore, then Key, then Value, and the primary-key fallback when the column has none of them. The ~ in a tag strips the whitespace next to it, so the template's indentation does not reach the child test, and {{!-- --}} comments never do. When you edit a template, keep two habits:

• Keep the comment on the same line as its {{#list}}. Handlebars removes a line that holds nothing but a block tag or a comment, indentation included.
• Keep the blank line after a {{~/list}}. The closing tag's line is removed with its newline, and the blank line puts the next clause back on a line of its own.

A long header comment at the top of each side's SQL states what that side returns and how roles and targets are read.

A table without a key

When a table has no key column, the templates do not compare it on nothing. Every template that uses the key raises an error that names the table:

No key columns on STAGING.S_DIM_PROVIDER_10: set Reconciliation Role to Key on its key columns, or declare a primary key

The Key Finder fails for that table with NO CANDIDATE on its In use row. Columns Align and Row Count per Segment do not use the key and run as usual. One case cannot raise the error: a row-level compare whose query returns no rows at all, such as an Incremental Full Row Compare with nothing in its window. With no rows there is nothing to compare, and that child passes.

Shared settings

Key Is Unique and Key Has No Nulls compare two numbers with the custom calculation testNumericValue + controlNumericValue == 0.

Variables

Results on SAMPLE_DW

Verified 2026-10-20 on Snowflake with the sample roles and targets above, the test side on one data source and the control side on a second data source over the same warehouse. The fields version and the Tags version give the same results:

Why:

• FACT_EXPENSE is missing 19,556 keys (65,959 staging rows, 46,403 target rows). Its MODIFIED_DATE runs to 2029, so recent-window checks see the gap too.
• DIM_PROVIDER is missing 679 keys (779 against 100). No provider row was modified in the last seven days, so the incremental compare has nothing to compare and passes.
• DIM_POLICY has no SOURCE_FILE on the target. Columns Align reports it, and the templates that select every compared column error on it. Marking SOURCE_FILE Ignore on the staging table makes them pass.

Customization

Your own tables. Mark them: nothing on the templates needs changing. To narrow one template, for example to one schema, add a Schema Name filter next to its mark filter. Filters combine with AND. Validatar tag filters match whole tags, and a column filter has no effect on a table-level template, so the mark has to be a table-level field or a whole tag.

Another platform. The SQL is written for Snowflake, SQL Server, PostgreSQL and Oracle, and proven on Snowflake. Set the platform variables in the table above. A variable applies to both sides, so when the source and target run on different platforms, pick values both accept or copy the template and edit one side. Row Fingerprint per Key needs Snowflake on both sides. The detail is under Platform notes in Reconciling Source and Target with Key-Based Templates.

Allowances. The templates compare exactly. To tolerate a known difference while it is fixed, copy the template and change its success criteria, or mark the column Ignore, rather than living with a failing test.

More failing rows. Raise Abort Processing After Failures on a template when you need every failing row, for example to build a reload list.

Troubleshooting

A template materializes no child tests. No table is marked. Set Reconciliation Target Table, or add the Table Tag Reconciliation Needed exactly as written, then materialize again.

Every child fails with NO CANDIDATE in the Key Finder. No key is set and the platform declares none. Set Reconciliation Role, then materialize again.

Roles were set but nothing changed. The children were materialized before the change. Materialize again.

A child errors with No key columns on .... That table has no key. Set one.

A child errors with invalid identifier. A compared column is missing on the target. Columns Align names it.

Text that looks identical fails. The comparison is case-sensitive and exact. See the Normalized Full Row Compare.

The guide has the full table of symptoms: Reconciling Source and Target with Key-Based Templates.

Related

• Reconciling Source and Target with Key-Based Templates
• Reconciliation Key Finder
• Reconciliation Catalog Custom Fields
• Generating Child Tests for Specific Metadata Objects