---
title: "Metadata Script Example: Reconciliation by Segment"
slug: "metadata-script-example-reconciliation-by-segment"
updated: 2026-10-20T15:21:57Z
published: 2026-10-20T15:21:57Z
canonical: "docs.validatar.com/metadata-script-example-reconciliation-by-segment"
---

> ## Documentation Index
> Fetch the complete documentation index at: https://docs.validatar.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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](/docs/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](/docs/exercise-11-create-a-source-to-target-template-test-using-a-metadata-script) 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](/docs/template-test-examples-metadata-script).

## The template

| Setting | Value |
| --- | --- |
| Name | Metadata Script Example - Reconciliation by Segment |
| Level | Table |
| Generation source | Metadata Script |
| Script data source | The template's own data source (SAMPLE_DW) |
| Everything else | Identical to [the import file version](/docs/import-file-example-reconciliation-by-segment): source and target row counts filtered on `{{var.segment_column}} = '{{var.segment_value}}'`, numeric tolerance `{{var.tolerance}}`, and the same variables |

## 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`:

| `table.name` | `test.key` | `var.target_table` | `var.segment_value` |
| --- | --- | --- | --- |
| CLAIM | DIM_CLAIM: SOURCE_CUSTOMER = CUSTOMER_1001 | DIM_CLAIM | CUSTOMER_1001 |
| CLAIM | DIM_CLAIM: SOURCE_CUSTOMER = CUSTOMER_1002 | DIM_CLAIM | CUSTOMER_1002 |
| CLAIM | DIM_CLAIM: SOURCE_CUSTOMER = CUSTOMER_2001 | DIM_CLAIM | CUSTOMER_2001 |

## Results on SAMPLE_DW

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

| Pair | CUSTOMER_1001 | CUSTOMER_1002 | CUSTOMER_2001 |
| --- | --- | --- | --- |
| CLAIM -> DIM_CLAIM | **Fail**: 21,036 vs 20,644 | **Fail**: 22,576 vs 22,154 | **Fail**: 10,727 vs 10,515 |
| The other four pairs | Pass | Pass | Pass |

`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

|  | Import file | Metadata script |
| --- | --- | --- |
| The table pairs | Repeated on every row | Listed once |
| The customers | Typed on every row | Read from `RAW.ACCOUNT` at each materialization |
| A new customer | No child tests until someone adds five rows | Five child tests at the next materialization |
| A customer that stops sending data | Its rows stay until removed | Its child tests remain while it is still in `RAW.ACCOUNT` |
| Child tests | 15 | The same 15, with the same keys |

## 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](/docs/template-test-examples-metadata-script)
- [Generating Child Tests using a Metadata Script](/docs/generating-child-tests-using-a-metadata-script)
- [Import File Example: Reconciliation by Segment](/docs/import-file-example-reconciliation-by-segment)
- [Exercise 11: Create a Source to Target Template Test Using a Metadata Script](/docs/exercise-11-create-a-source-to-target-template-test-using-a-metadata-script)