---
title: "Metadata Script Example: Source to Target Reconciliation"
slug: "metadata-script-example-source-to-target"
updated: 2026-10-20T15:21:51Z
published: 2026-10-20T15:21:51Z
canonical: "docs.validatar.com/metadata-script-example-source-to-target"
---

> ## 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: Source to Target Reconciliation

## What it checks

Whether a target object holds what its source says it should: the same row count, or the same total of an amount. Each child test compares one metric between one source and one target, using a source query and a target query.

The check is the same as in [Import File Example: Source to Target Reconciliation](/docs/import-file-example-source-to-target-reconciliation). What changes is how the two queries arrive. In the file, someone typed ten complete SQL statements into a spreadsheet. Here the script holds a compact mapping list (source, target, metric expression, optional filter) and assembles both statements from it. Adding a reconciliation means adding one line to the list.

Part of [Template Test Examples: Metadata Script](/docs/template-test-examples-metadata-script).

## The template

| Setting | Value |
| --- | --- |
| Name | Metadata Script Example - Source to Target Reconciliation |
| 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-source-to-target-reconciliation): test SQL `{{var.source_sql}}`, control SQL `{{var.target_sql}}`, numeric tolerance `{{var.tolerance}}`, and the same variables |

## The metadata script

`metadata-script-example-source-to-target.sql`:

```
-- One row per reconciliation. The mapping list lives in the script, and the script
-- assembles each child test's source and target queries from it.
WITH mappings AS (
    SELECT column1 AS source_schema, column2 AS source_table,
           column3 AS target_schema, column4 AS target_table,
           column5 AS metric, column6 AS source_expression, column7 AS target_expression,
           column8 AS row_filter, column9 AS tolerance
    FROM VALUES
        ('STAGING', 'S_DIM_CUSTOMER_30_FINAL', 'STAR_SCHEMA', 'DIM_CUSTOMER', 'row count', 'COUNT(*)', 'COUNT(*)', NULL, 0),
        ('STAGING', 'S_DIM_PRODUCT_30_FINAL', 'STAR_SCHEMA', 'DIM_PRODUCT', 'row count', 'COUNT(*)', 'COUNT(*)', NULL, 0),
        ('STAGING', 'S_FACT_SALES_30_FINAL', 'STAR_SCHEMA', 'FACT_SALES', 'row count', 'COUNT(*)', 'COUNT(*)', NULL, 0),
        ('STAGING', 'S_FACT_SALES_30_FINAL', 'STAR_SCHEMA', 'FACT_SALES', 'sales amount', 'ROUND(SUM(TRY_TO_DOUBLE(SALES_AMOUNT)), 2)', 'ROUND(SUM(TRY_TO_DOUBLE(SALES_AMOUNT)), 2)', NULL, 0.01),
        ('STAGING', 'S_FACT_PAYMENT_10', 'STAR_SCHEMA', 'FACT_PAYMENT', 'row count', 'COUNT(*)', 'COUNT(*)', NULL, 0),
        ('STAGING', 'S_FACT_PAYMENT_10', 'STAR_SCHEMA', 'FACT_PAYMENT', 'payment amount', 'ROUND(SUM(PAYMENT_AMOUNT), 2)', 'ROUND(SUM(PAYMENT_AMOUNT), 2)', NULL, 0.01),
        ('RAW', 'ACCOUNT', 'STAR_SCHEMA', 'DIM_ACCOUNT', 'row count', 'COUNT(*)', 'COUNT(*)', $$SOURCE_CUSTOMER = 'CUSTOMER_1001'$$, 0),
        ('RAW', 'POLICY', 'STAR_SCHEMA', 'DIM_POLICY', 'row count', 'COUNT(*)', 'COUNT(*)', NULL, 0),
        ('RAW', 'CLAIM', 'STAR_SCHEMA', 'DIM_CLAIM', 'row count', 'COUNT(*)', 'COUNT(*)', NULL, 0),
        ('STAGING', 'S_FACT_EXPENSE_10', 'STAR_SCHEMA', 'FACT_EXPENSE', 'row count', 'COUNT(*)', 'COUNT(*)', NULL, 0)
)
SELECT source_schema AS "schema.name",
       source_table  AS "table.name",
       target_table || ' ' || metric
           || IFF(row_filter IS NULL, '', ' (' || row_filter || ')') AS "test.key",
       metric        AS "var.metric",
       target_schema AS "var.target_schema",
       target_table  AS "var.target_table",
       'SELECT ' || source_expression || ' AS VALUE FROM ' || source_schema || '.' || source_table
           || IFF(row_filter IS NULL, '', ' WHERE ' || row_filter) AS "var.source_sql",
       'SELECT ' || target_expression || ' AS VALUE FROM ' || target_schema || '.' || target_table
           || IFF(row_filter IS NULL, '', ' WHERE ' || row_filter) AS "var.target_sql",
       TO_VARCHAR(tolerance) AS "var.tolerance"
FROM mappings
ORDER BY "test.key"
```

How it works:

- **The mapping list** is a `VALUES` list with one line per reconciliation. Two lines name the same source and target (`S_FACT_SALES_30_FINAL` to `FACT_SALES`) with different metrics, so that pair produces two child tests.
- **`test.key`** is the target table plus the metric, plus the filter when there is one, the same key the import file uses. It is unique even when a pair appears twice.
- **`var.source_sql` and `var.target_sql`** are built by string concatenation: `SELECT <expression> AS VALUE FROM <schema>.<table>`, with `WHERE <filter>` when the line has a filter. The template then runs each one as written, because `{{var.x}}` is plain text substitution.
- **`var.tolerance`** is cast to text with `TO_VARCHAR`, so the value reaches the template exactly as the script formats it.
- **The filter on `RAW.ACCOUNT`** uses Snowflake's `$$` quoting, so the single quotes inside it need no escaping.

## What it returns

Ten rows, one per mapping line. The two rows for the `FACT_SALES` pair:

| `table.name` | `test.key` | `var.metric` | `var.source_sql` |
| --- | --- | --- | --- |
| S_FACT_SALES_30_FINAL | FACT_SALES row count | row count | `SELECT COUNT(*) AS VALUE FROM STAGING.S_FACT_SALES_30_FINAL` |
| S_FACT_SALES_30_FINAL | FACT_SALES sales amount | sales amount | `SELECT ROUND(SUM(TRY_TO_DOUBLE(SALES_AMOUNT)), 2) AS VALUE FROM STAGING.S_FACT_SALES_30_FINAL` |

## Results on SAMPLE_DW

Verified 2026-10-19: 10 children, 8 pass, 2 fail, the same as the import file version.

| Child | Result |
| --- | --- |
| S2T row count: CLAIM -> DIM_CLAIM | **Fail**: 54,339 source rows, 53,313 target rows |
| S2T row count: S_FACT_EXPENSE_10 -> FACT_EXPENSE | **Fail**: 65,959 source rows, 46,403 target rows |
| The other eight | Pass |

## Compared with the import file

|  | Import file | Metadata script |
| --- | --- | --- |
| Who writes the SQL | A person, once per row, in full | The script, from an expression and a filter |
| Adding a reconciliation | A new spreadsheet row with two SQL statements, then upload | A new line in the `VALUES` list |
| Child tests | 10 | The same 10, with the same keys |

## Adapting it

**Keep the mappings in a table.** If your team maintains source-to-target mappings in a table (for example, an ETL metadata table), replace the `VALUES` list with a query on it and choose that table's data source on the Metadata Script tab. New mappings then get child tests at the next materialization.

**Add a metric to every mapping.** `CROSS JOIN` the mapping list with a small list of metrics (row count, distinct key count, total amount), and build the expression from both.

**Different expressions per side.** The list carries a source expression and a target expression separately, for the case where the target stores the value under another name or type.

**Pitfall: the assembled SQL is not checked until it runs.** Preview & Validate checks the rows, not the SQL inside them. A typo in an expression shows up as a child test error. Run the template once after editing the list.

## 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: Source to Target Reconciliation](/docs/import-file-example-source-to-target-reconciliation)
- [Metadata Script Example: Reconciliation by Segment](/docs/metadata-script-example-reconciliation-by-segment)