Metadata Script 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. Here a segment is one source customer. The check is the same as in Import File Example: Reconciliation by Segment.

The file version lists every customer by hand, fifteen rows for five table pairs and three customers. This script lists the five table pairs once and reads the customers from the data. When a fourth customer starts sending files, the next materialization adds five child tests for it, one per table pair, and nobody edits anything.

This is the pattern Exercise 11 teaches: a CROSS JOIN between a list of objects and a list of values taken from the data.

Part of Template Test Examples: Metadata Script.

The template

The metadata script

metadata-script-example-reconciliation-by-segment.sql:

-- One child test per table pair and per source customer. The customer list is read from the
-- data, so a new customer gets its own children at the next materialization without anyone
-- editing a list.
WITH pairs AS (
    SELECT column1 AS source_table, column2 AS target_table
    FROM VALUES
        ('ACCOUNT', 'DIM_ACCOUNT'),
        ('POLICY', 'DIM_POLICY'),
        ('CLAIM', 'DIM_CLAIM'),
        ('PAYMENT', 'FACT_PAYMENT'),
        ('TRANSACTION', 'FACT_TRANSACTION')
),
customers AS (
    SELECT DISTINCT SOURCE_CUSTOMER AS segment_value
    FROM RAW.ACCOUNT
    WHERE SOURCE_CUSTOMER IS NOT NULL
)
SELECT 'RAW'             AS "schema.name",
       p.source_table    AS "table.name",
       p.target_table || ': SOURCE_CUSTOMER = ' || c.segment_value AS "test.key",
       'STAR_SCHEMA'     AS "var.target_schema",
       p.target_table    AS "var.target_table",
       'SOURCE_CUSTOMER' AS "var.segment_column",
       c.segment_value   AS "var.segment_value",
       '0'               AS "var.tolerance"
FROM pairs p
CROSS JOIN customers c
ORDER BY "table.name", "test.key"

How it works:

• pairs is the only list a person maintains: which raw table loads which star schema table.
• customers reads the distinct SOURCE_CUSTOMER values from RAW.ACCOUNT, the customer master. Nulls are filtered out, because a null would produce a child test that counts nothing.
• CROSS JOIN pairs every table with every customer: five pairs times three customers, fifteen rows.
• test.key is the target table plus the segment, for example DIM_CLAIM: SOURCE_CUSTOMER = CUSTOMER_1002. One source table appears three times, each with its own key.
• var.tolerance is the text '0' on every row. Return a per-pair value from pairs if some tables are allowed to differ.

What it returns

Fifteen rows, the same as the import file. The three rows for RAW.CLAIM:

Results on SAMPLE_DW

Verified 2026-10-19: 15 children, 12 pass, 3 fail, the same as the import file version.

DIM_CLAIM is short for every customer, by about 2% each time, which points at the claim load rather than at one customer's feed.

Compared with the import file

Adapting it

Segment by something else. Replace SOURCE_CUSTOMER in customers and in var.segment_column with your segment column, such as a region, a line of business, or a load date.

Take the segments from each source table. This script reads one customer list for every pair. If each table has its own set of values, build customers as a UNION of SELECT DISTINCT ... FROM each source, tagged with the source table, and join on it instead of crossing.

Limit the segments. Add a filter to customers, for example only the customers active in the last 30 days, so that dormant segments do not produce child tests.

Pitfall: an empty segment list deletes everything. If RAW.ACCOUNT is empty when a job materializes the template (mid-reload, for example), the script returns no rows. When Delete tests is selected, every child test of the template is then deleted. Materialize from a job only after the source has loaded, or clear Delete tests on that job step.

Pitfall: segment values in keys. A key is stored in lower case. Two segment values that differ only in letter case collide, and Preview & Validate reports Duplicate test key.

Related

• Template Test Examples: Metadata Script
• Generating Child Tests using a Metadata Script
• Import File Example: Reconciliation by Segment
• Exercise 11: Create a Source to Target Template Test Using a Metadata Script