---
title: "Import File Example: Source to Target Reconciliation"
slug: "import-file-example-source-to-target-reconciliation"
updated: 2026-09-21T15:20:42Z
published: 2026-09-21T15:20:42Z
canonical: "docs.validatar.com/import-file-example-source-to-target-reconciliation"
---

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

# 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](/docs/template-test-examples-import-file).

## The template

| Setting | Value |
| --- | --- |
| Name | Import File Example - Source to Target Reconciliation |
| Level | Table |
| Generation source | Import File |
| Severity | High |
| Quality dimension | Completeness |
| Child test name | `S2T {{var.metric}}: {{table.name}} -> {{var.target_table}}` |
| Child folder | Relative, `{{var.template_test_name}}` |
| Test data set | Script on the template's data source, single value, numeric. SQL: `{{var.source_sql}}` |
| Control data set | Script on the same data source, single value, numeric. SQL: `{{var.target_sql}}` |
| Value success criteria | Numeric tolerance, tolerance `{{var.tolerance}}` |
| Overall success | Value, tolerance 0 |
| Missing result actions | Fail on both sides |

Variables declared on the template:

| Reference key | Type | Purpose |
| --- | --- | --- |
| `source_sql` | Other | Complete SQL statement returning one numeric column named `VALUE` |
| `target_sql` | Other | Same, for the target |
| `target_schema` | Schema | Used in the control data set's metadata link and the child description |
| `target_table` | Table | Used in the child test name, description, and the control metadata link |
| `metric` | Other | Human label for the comparison, used in the child name |
| `tolerance` | Numeric | Allowed difference between the two values. Default 0 |

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.

| Header | Meaning |
| --- | --- |
| `schema.name`, `table.name` | The catalogued **source** object. This is the object the child test is attached to |
| `test.key` | Unique key. Needed here because `S_FACT_SALES_30_FINAL` and `S_FACT_PAYMENT_10` each appear twice, once for row count and once for an amount |
| `var.metric` | `row count`, `sales amount`, `payment amount` |
| `var.target_schema`, `var.target_table` | The target object |
| `var.source_sql`, `var.target_sql` | The two statements to run |
| `var.tolerance` | Allowed numeric difference. `0` for counts, `0.01` for rounded sums |

The rows:

| Source | Target | Metric | Tolerance |
| --- | --- | --- | --- |
| `STAGING.S_DIM_CUSTOMER_30_FINAL` | `STAR_SCHEMA.DIM_CUSTOMER` | row count | 0 |
| `STAGING.S_DIM_PRODUCT_30_FINAL` | `STAR_SCHEMA.DIM_PRODUCT` | row count | 0 |
| `STAGING.S_FACT_SALES_30_FINAL` | `STAR_SCHEMA.FACT_SALES` | row count | 0 |
| `STAGING.S_FACT_SALES_30_FINAL` | `STAR_SCHEMA.FACT_SALES` | sales amount | 0.01 |
| `STAGING.S_FACT_PAYMENT_10` | `STAR_SCHEMA.FACT_PAYMENT` | row count | 0 |
| `STAGING.S_FACT_PAYMENT_10` | `STAR_SCHEMA.FACT_PAYMENT` | payment amount | 0.01 |
| `RAW.ACCOUNT` where `SOURCE_CUSTOMER = 'CUSTOMER_1001'` | `STAR_SCHEMA.DIM_ACCOUNT`, same filter | row count | 0 |
| `RAW.POLICY` | `STAR_SCHEMA.DIM_POLICY` | row count | 0 |
| `RAW.CLAIM` | `STAR_SCHEMA.DIM_CLAIM` | row count | 0 |
| `STAGING.S_FACT_EXPENSE_10` | `STAR_SCHEMA.FACT_EXPENSE` | row count | 0 |

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.

| Child | Source value | Target value | Result |
| --- | --- | --- | --- |
| DIM_CUSTOMER row count | 3,240 | 3,240 | Pass |
| DIM_PRODUCT row count | 10,814 | 10,814 | Pass |
| FACT_SALES row count | 1,274,235 | 1,274,235 | Pass |
| FACT_SALES sales amount | 962,882,520.31 | 962,882,520.31 | Pass |
| FACT_PAYMENT row count | 49,619 | 49,619 | Pass |
| FACT_PAYMENT payment amount | 14,905,728.34 | 14,905,728.34 | Pass |
| DIM_ACCOUNT row count (CUSTOMER_1001) | equal | equal | Pass |
| DIM_POLICY row count | 11,672 | 11,672 | Pass |
| DIM_CLAIM row count | 54,339 | 53,313 | **Fail** |
| FACT_EXPENSE row count | 65,959 | 46,403 | **Fail** |

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](/docs/template-test-examples-import-file)
- [Generating Child Tests from an Import File](/docs/generating-child-tests-from-an-import-file)
- [Import File Example: Referential Integrity Rule](/docs/import-file-example-referential-integrity-rule)
- [Import File Example: Change Over Time](/docs/import-file-example-change-over-time)