Import File Example: Reconciliation by Segment

What it checks

Whether a source table and its target hold the same number of rows, one segment at a time. A whole-table row count tells you that something went missing. Splitting the count by a segment column (a source customer, a region, a line of business) tells you where. If one customer's feed failed to load, only that customer's child test fails, and the other customers keep a clean history.

This is the template to start from when one catalogued table needs several child tests. Every row of the file names the same kind of object, a source table, but the file repeats each table once per segment value. test.key keeps those rows apart: the table appears three times, with three different keys, and produces three child tests.

Part of Template Test Examples: Import File. The same template, with a script that reads the segment values from the data instead of a file, is Metadata Script Example: Reconciliation by Segment.

The template

The test data set counts the source rows in one segment:

SELECT COUNT(*) AS ROW_COUNT
FROM {{schema.name}}.{{table.name}}
WHERE {{var.segment_column}} = '{{var.segment_value}}'

The control data set counts the target rows in the same segment:

SELECT COUNT(*) AS ROW_COUNT
FROM {{var.target_schema}}.{{var.target_table}}
WHERE {{var.segment_column}} = '{{var.segment_value}}'

Variables declared on the template:

The source table is the catalogued object, so it carries coverage through the metadata link {{schema.name}}.{{table.name}}. The target is linked through {{var.target_schema}}.{{var.target_table}} on the control data set, without counting as coverage.

The import file

import-file-example-reconciliation-by-segment.xlsx, one sheet, header row plus 15 data rows: five table pairs, each repeated for three source customers.

The table pairs:

The three rows for RAW.CLAIM:

Leave out test.key and these three rows would all key to raw.claim. Upload would then fail with Duplicate test key: raw.claim.

What materializes

Fifteen child tests, three per source table, in a folder named after the template. The child for claims from CUSTOMER_1002 gets this test script:

SELECT COUNT(*) AS ROW_COUNT
FROM RAW.CLAIM
WHERE SOURCE_CUSTOMER = 'CUSTOMER_1002'

and this control script:

SELECT COUNT(*) AS ROW_COUNT
FROM STAR_SCHEMA.DIM_CLAIM
WHERE SOURCE_CUSTOMER = 'CUSTOMER_1002'

Results on SAMPLE_DW

Verified 2026-10-19: 15 children, 12 pass, 3 fail.

The Source to Target Reconciliation example already shows that DIM_CLAIM is short of RAW.CLAIM overall. Split by customer, the shortfall turns out to hit every customer, at roughly the same rate (1.9% to 2.0%). That points at the claim load itself rather than at one customer's feed, which is the kind of conclusion a single whole-table count cannot give you.

Adapting it

Use your own segment column. Change var.segment_column on every row. The column must exist under the same name in both tables. If it is named differently on each side, add a target_segment_column variable and use it in the control SQL.

Segment by more than one column. Put a whole predicate in a variable instead of a column and a value: declare segment_filter and use WHERE {{var.segment_filter}} on both sides, with rows such as REGION = 'EAST' AND CHANNEL = 'WEB'.

Numeric segment values. The SQL quotes the value. For a numeric segment column, drop the quotes in both scripts, or cast the column to text in the WHERE clause.

Let the data decide the segments. The file has to list every customer by hand, so a new customer has no child tests until someone adds rows. The metadata script version reads the customer list from the data at each materialization instead.

Pitfall: a segment that exists on one side only. If a value appears in the target but never in the source, no file row names it and no child test covers it. Count the distinct segment values on both sides, or add a row per value you expect.

Related

• Template Test Examples: Import File
• Generating Child Tests from an Import File
• Import File Example: Source to Target Reconciliation
• Metadata Script Example: Reconciliation by Segment