Reconciliation Key Finder

What it answers

Every keyed reconciliation rests on one assumption: the key identifies exactly one row, on both sides. Get the key wrong and the results are wrong in ways that look plausible. A key that is too narrow pairs duplicate rows at random and reports value mismatches that are not there. A key that is too wide (one that includes a balance or a load batch id) turns every changed value into a missing row plus an extra row.

The Reconciliation Key Finder is the template that settles the key before anything else runs. For each table marked for reconciliation, it tries every common way of choosing a key against the table's own data, and reports which choices actually produce a unique, never-null key. Its child test passes only when the key the reconciliation templates will use is good.

It ships in both versions of the Key-Based Reconciliation pack: the fields version reads the Reconciliation Role custom field, and the Tags version reads Column Tags such as Reconciliation Role: Key. Where it fits in the overall process is in Reconciling Source and Target with Key-Based Templates.

The strategies

Each strategy is a rule for picking key columns from the table's catalog entry:

Strategy 1 is the only one that decides pass or fail. The others are evidence: they show what a naming rule or the platform's metadata would give you, so you can decide.

Reading the result

Each child test returns one row per strategy:

The child passes when the In use row says UNIQUE. Any other status on that row fails the child, and the other six rows stay visible for you to choose from. Under the hood, the control side runs the same query on the same source data source, with the In use row's status set to UNIQUE, so the comparison fails on exactly one cell. The Key Finder never touches the target.

A worked example

STAGING.S_DIM_ACCOUNT_10 in the sample warehouse has 12,263 rows. Once the table is marked for reconciliation, and before any key is set, the Key Finder reports:

What the rows say:

• No key is defined yet. Snowflake records no primary keys, and no role is set, so rows 1 to 3 are empty.
• ACCOUNT_UNIQUE_KEY is unique. It is a hash the pipeline adds to every row. It is a valid key here only because the target carries the same value. Check that before relying on a surrogate.
• Strategy 5 is unique for the wrong reason. CLIENT_ID is a foreign key and ETL_INSERT_BATCH_ID is load metadata. The combination happens to be unique, but a key containing a batch id matches nothing on the other side.
• Strategy 7 finds nothing. Column names do not contain the staging table's full name, S_DIM_ACCOUNT_10. Name-based strategies depend entirely on naming habits.

The business key is SOURCE_CUSTOMER plus ACCOUNT_ID. ACCOUNT_ID alone is not enough: two source customers use the same account id once. After setting both columns' Reconciliation Role to Key and materializing again, rows 1 and 2 read ACCOUNT_ID, SOURCE_CUSTOMER, 0 duplicate key groups, UNIQUE, and the child passes.

Choosing among the candidates

• The key must mean the same row on both sides. Prefer columns that come from the source system over values the pipeline creates.
• Narrow is better, if unique. Add a column to the key only when the key is not unique without it.
• Composite keys are normal. Merged and multi-tenant data usually needs the source identifier in the key.
• Watch for load metadata in a candidate. Batch ids, load timestamps and file names make almost anything unique.
• Unique today is not unique forever. A key that is unique on a sample may not be on next month's data. Rerun the Key Finder after loads that change the shape of the data.
• No candidate at all? The table may genuinely have no row identity, for example an append-only event log with repeated events. Reconcile it with the count and total templates, and add a key column upstream if row-level checks matter.

Applying the choice

Set Reconciliation Role to Key on the chosen columns (in the Tags version, add the Column Tag Reconciliation Role: Key), using Bulk Edit on the table's Columns tab, a custom metadata Excel import, or a custom metadata script. All three are described in Reconciling Source and Target with Key-Based Templates, including why Bulk Edit with Add to Existing is the safe choice for tags. Then materialize the templates again: the child tests are written at materialization, so the new key reaches them only then.

Adding a strategy of your own

Each strategy is one block of the Key Finder's test SQL, joined to the others with UNION ALL. This is strategy 6:

{{!-- Strategy 6 Column name contains KEY: names containing KEY --}}
SELECT
    '6 Column name contains KEY' AS STRATEGY,
    '{{#list table.columns ', '~}}
        {{~#if (#contains this.name 'KEY')~}}
            {{name}}
        {{~/if~}}
    {{~else~}}
        (none)
    {{~/list}}' AS KEY_COLUMNS,
    COUNT(*) AS TOTAL_ROWS,
    (
        SELECT COUNT(*)
        FROM (
            SELECT
                {{#list table.columns ', '~}} {{!-- the picked columns; with none, the first column (the row then reads NO CANDIDATE) --}}
                    {{~#if (#contains this.name 'KEY')~}}
                        "{{name}}"
                    {{~/if~}}
                {{~else~}}
                    {{#list @root.table.columns ''~}}
                        {{~#if (eq this.sequence 1)~}}
                            "{{name}}"
                        {{~/if~}}
                    {{~/list}}
                {{~/list}}

            FROM "{{schema.name}}"."{{table.name}}"
            GROUP BY
                (the same list again)

            HAVING COUNT(*) > 1
        ) duplicates
    ) AS DUPLICATE_KEY_GROUPS,
    COALESCE(SUM(CASE WHEN
        {{#list table.columns ' OR '~}} {{!-- a null in any picked column --}}
            {{~#if (#contains this.name 'KEY')~}}
                "{{name}}" IS NULL
            {{~/if~}}
        {{~else~}}
            1 = 0
        {{~/list}}

    THEN 1 ELSE 0 END), 0) AS NULL_KEY_ROWS
FROM "{{schema.name}}"."{{table.name}}"

To add a strategy for your own convention, copy the block, give it the next number and a name, and change the condition in all four places (the key column list, the two lists in the duplicate count, and the null check), for example (#contains this.name '_NBR') for keys named *_NBR. The control data set's SQL wraps the test query, so add the same block there too. Save and materialize again.

Leave strategy 1 as it is. It mirrors the rule every other template uses, and the pass or fail depends on it.

Cost

Each strategy reads the table twice: one pass counts the rows and the null keys together, and one groups the key to count duplicates. The control side repeats every strategy, so one child test reads its table 28 times. On large tables, run the Key Finder when setting up, after schema changes and on demand, not in the nightly job.

Related

• Reconciling Source and Target with Key-Based Templates
• Key-Based Reconciliation Test Pack
• Reconciliation Catalog Custom Fields