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. 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.

The template

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:

Results on SAMPLE_DW

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

Compared with the import file

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
• Generating Child Tests using a Metadata Script
• Import File Example: Source to Target Reconciliation
• Metadata Script Example: Reconciliation by Segment