Import File Example: Source to Target Reconciliation

What it checks

Each child test runs one query against a source object and one query against its target, then compares the two numbers. The queries themselves live in the spreadsheet. That is the point of this example: the template holds no SQL of its own beyond {{var.source_sql}} and {{var.target_sql}}, so one template reconciles staging to dimension, raw to fact, a filtered subset, or a sum, depending on what each row says.

It answers the question every load pipeline raises: did everything that left the source arrive at the target, and does it still add up?

Part of Template Test Examples: Import File.

The template

Variables declared on the template:

Nothing on the template is ticked From File. Every value that changes per row, including the tolerance, is a variable with a matching var. column in the file. Variables render anywhere Handlebars is accepted, so {{var.tolerance}} sits directly in the Value Success Tolerance field.

The metadata links matter for coverage. The test data set links to {{schema.name}}.{{table.name}} with coverage on, so the source table counts as tested. The control data set links to {{var.target_schema}}.{{var.target_table}} with coverage off, so the target shows the relationship in the catalog without double counting.

The import file

import-file-example-source-to-target.xlsx, one sheet, header row plus 10 data rows.

The rows:

Two of the SQL cells, to show the shape:

-- var.source_sql, sales amount row
SELECT ROUND(SUM(TRY_TO_DOUBLE(SALES_AMOUNT)), 2) AS VALUE FROM STAGING.S_FACT_SALES_30_FINAL

-- var.target_sql, filtered account row
SELECT COUNT(*) AS VALUE FROM STAR_SCHEMA.DIM_ACCOUNT WHERE SOURCE_CUSTOMER = 'CUSTOMER_1001'

Both statements must return exactly one row with one numeric column. The column is configured on the template as VALUE, so alias it that way.

What materializes

Ten child tests in a folder named after the template. Names come from the child name pattern, so the folder reads:

S2T row count: S_DIM_CUSTOMER_30_FINAL -> DIM_CUSTOMER
S2T row count: S_DIM_PRODUCT_30_FINAL -> DIM_PRODUCT
S2T row count: S_FACT_SALES_30_FINAL -> FACT_SALES
S2T sales amount: S_FACT_SALES_30_FINAL -> FACT_SALES
...

Each child's test script is the row's var.source_sql verbatim and its control script is var.target_sql verbatim. Its value success tolerance is the row's number. Open a child to confirm; nothing in it still says {{.

Results on SAMPLE_DW

Verified 2026-09-11.

The two failures are real. DIM_CLAIM is missing 1,026 claims that exist in RAW.CLAIM, and FACT_EXPENSE holds fewer than three quarters of the staged expense rows. Whether that is a filter in the load or a defect is exactly the conversation this test exists to start.

Adapting it

Reconcile your own tables. Replace the rows. Keep schema.name and table.name pointing at objects in the catalog of the data source you mapped at import. The var. columns can name anything the data source can query, catalogued or not.

Compare across data sources. The control data source is the one per-row setting with no variable form. Tick From File on it and add a Target Data Source column naming the second data source per row. The target SQL then runs there.

Change what is compared. Because the SQL is in the file, a row can compare a distinct count, a max date, a hash of concatenated keys, or a grouped total collapsed to one number. The only constraint is one numeric value out of each side.

Tighten or loosen tolerance per row. The var.tolerance column is read per row. Use 0 for counts. Use a small absolute value for sums that involve floating point.

Pitfall: keys. If you add a second comparison for a table that already has a row, give it a distinct test.key. Without one, the upload fails with Duplicate test key.

Pitfall: blank variable cells. A blank var.source_sql cell renders as an empty script, and that child errors when it runs. The variable's default on the template is used only when the column is missing from the file entirely. Fill every SQL cell.

Related

• Template Test Examples: Import File
• Generating Child Tests from an Import File
• Import File Example: Referential Integrity Rule
• Import File Example: Change Over Time