Overview
These fields tell the Key-Based Reconciliation templates how to read your tables: which columns form a row's key, which are compared, which are left out, and which table each source table is reconciled against. Setting them is a catalog edit, not a template edit. Change a table's key here, materialize the templates again, and every reconciliation template uses the new key.
Where the fields fit in a reconciliation is in Reconciling Source and Target with Key-Based Templates.
Marketplace: Reconciliation - Catalog Custom Fields
The fields
All three sit in a Reconciliation section.
| Level | Field | Reference key | Type | Purpose |
|---|---|---|---|---|
| Column | Reconciliation Role | reconciliation_role |
Dropdown: Key, Value, Ignore | How the templates treat the column |
| Table | Reconciliation Target Schema | reconciliation_target_schema |
Short text | Schema of the table this one is reconciled against |
| Table | Reconciliation Target Table | reconciliation_target_table |
Short text | Name of the table this one is reconciled against. Setting it marks the table for reconciliation |
Reconciliation Role
| Value | Effect |
|---|---|
| Key | Part of the row's identity. Rows are matched on all Key columns together, so a key can span several columns |
| Value | Compared between the two sides |
| Ignore | Left out of every comparison. Use it for columns that differ by design, such as load timestamps and batch ids, and for a column the target does not have |
| (blank) | Key when the column is part of a declared primary key, otherwise Value |
The blank fallback means tables with declared primary keys work without any setup. SQL Server, PostgreSQL and Databricks ingest declared primary keys into the catalog. Snowflake does not, so on Snowflake set the role on the key columns.
Reconciliation Target Schema and Table
Reconciliation Target Table also marks a table for reconciliation. Every Key-Based Reconciliation template selects the tables where it is set, so set it on every table to reconcile, even when the target has the same name. For example, S_DIM_ACCOUNT_10 loading DIM_ACCOUNT gets DIM_ACCOUNT, and ORDERS loading ORDERS gets ORDERS. A table without it is not reconciled.
Set Reconciliation Target Schema when one table's target lives in a different schema from the template's Target Schema variable. When it is blank, the variable applies.
The fields are read from the source table's catalog entry. The target itself is queried through each template's control data source, the one mapped to Reconciliation Target when the pack was imported.
Import the fields
Import the fields before the Key-Based Reconciliation pack: its templates select tables through Reconciliation Target Table. You need the Global Custom Metadata Admin role, or Global Admin. Adding catalog custom fields needs Govern Edition. On QA Edition, use Key-Based Reconciliation (Tags) instead: the same templates, reading the same settings from the standard Table Tags and Column Tags fields, for example Reconciliation Role: Key. See Key-Based Reconciliation Test Pack.
- In Validatar, open Marketplace, find Reconciliation - Catalog Custom Fields, choose Get, then Import Directly. Validatar opens Settings > Catalog Custom Fields with an Import Custom Fields panel on the right.
- Open the Table level, pick Table in the panel's list, choose Import All once, then Save.
- Do the same for the Column level.
Importing from a downloaded file
Download the XML from the marketplace listing. On Settings > Catalog Custom Fields, choose Import, select the file, and follow steps 2 and 3.
Set the values
Three ways, from a handful of columns to a whole warehouse:
- Bulk Edit. On a table's Columns tab in the catalog, select the key columns, choose Bulk Edit, set Reconciliation Role to Key, and save. Repeat for Ignore.
- Excel import. On the data source's Custom Metadata page, generate the import file, fill in
schema.name,table.name,column.nameandreconciliation_role(or, for tables,schema.name,table.nameandreconciliation_target_table), and upload it. - Custom metadata script. A query source on the data source that returns the same columns from a rule, such as "every
*_IDcolumn named after its table is a key". It runs again whenever new tables arrive. Reconciling Source and Target with Key-Based Templates has a working example for both levels.
Dropdown values must be exactly Key, Value or Ignore. After any change, materialize the reconciliation templates again.