Import File Example: Change Over Time

What it checks

A metric on a table should look roughly like it did last time. Row counts do not halve overnight, total sales do not triple, distinct claim counts do not jump. This template computes one metric per row of the spreadsheet, compares it with the average of that child test's own previous runs, and fails when the change exceeds a percent threshold.

Both the metric and the sensitivity vary per table, and both live in the file. A dimension that changes slowly gets a 5% threshold over 10 runs. A fact table that grows every load gets 10% over 7. A count of distinct claims, noisier still, gets 15% over 5.

Part of Template Test Examples: Import File.

The template

Setting	Value
Name	Import File Example - Change Over Time
Level	Table
Generation source	Import File
Severity	Medium
Quality dimension	Consistency
Child test name	`Drift: {{table.name}} {{var.metric}}`
Child folder	Relative, `{{var.template_test_name}}`
Test data set	Script on the template's data source, single value, numeric (below)
Control data set	Previous single-value results: aggregate Average, window type Runs, window size `{{var.lookback_runs}}`, include prior versions
Value success criteria	Percent of control value, tolerance `{{var.tolerance}}`
Overall success	Value, tolerance 0
Test missing result action	Fail
Control missing result action	Pass. The first run has no history and must not fail for it

The test data set SQL:

SELECT {{var.metric_sql}} AS METRIC_VALUE
FROM {{schema.name}}.{{table.name}}

Variables declared on the template:

Reference key	Type	Purpose
`metric_sql`	Other	Aggregate expression to compute. Default `COUNT(*)`
`metric`	Other	Label for the child test name. Default `row count`
`lookback_runs`	Numeric	How many previous runs to average. Default 10
`tolerance`	Numeric	Percent change allowed against the baseline. Default 1

Nothing on the template is ticked From File. The threshold, the window, and the metric all travel as variables with matching var. columns, and the two settings reference them directly: Value Success Tolerance is {{var.tolerance}}, the control's window size is {{var.lookback_runs}}. Prefer this over the From File checkbox for any per-row value. One mechanism covers every field, and a variable has a default the template can fall back on.

The import file

import-file-example-change-over-time.xlsx, one sheet, header row plus 9 data rows.

Header	Meaning
`schema.name`, `table.name`	The catalogued table
`test.key`	Unique key. Needed because `FACT_SALES` and `FACT_PAYMENT` each have two metrics
`var.metric`	Label: `row count`, `total sales amount`, `distinct claim count`
`var.metric_sql`	The aggregate expression
`var.tolerance`	Percent change allowed against the baseline
`var.lookback_runs`	Runs averaged into the baseline

The rows:

Table	Metric expression	Threshold %	Lookback runs
`STAR_SCHEMA.DIM_CUSTOMER`	`COUNT(*)`	5	10
`STAR_SCHEMA.DIM_PRODUCT`	`COUNT(*)`	5	10
`STAR_SCHEMA.DIM_POLICY`	`COUNT(*)`	5	10
`STAR_SCHEMA.DIM_CLAIM`	`COUNT(*)`	5	10
`STAR_SCHEMA.FACT_SALES`	`COUNT(*)`	10	7
`STAR_SCHEMA.FACT_SALES`	`SUM(TRY_TO_DOUBLE(SALES_AMOUNT))`	10	7
`STAR_SCHEMA.FACT_PAYMENT`	`COUNT(*)`	10	7
`STAR_SCHEMA.FACT_PAYMENT`	`SUM(PAYMENT_AMOUNT)`	10	7
`STAR_SCHEMA.FACT_TRANSACTION`	`COUNT(DISTINCT CLAIM_ID)`	15	5

SALES_AMOUNT is stored as text in the sample warehouse, hence TRY_TO_DOUBLE. That is a Snowflake function; substitute your platform's safe cast.

What materializes

Nine child tests in a folder named after the template. The FACT_SALES amount child, for instance, carries this test script:

SELECT SUM(TRY_TO_DOUBLE(SALES_AMOUNT)) AS METRIC_VALUE
FROM STAR_SCHEMA.FACT_SALES

a value success tolerance of 10, and a control data set that averages the previous 7 runs. The DIM_CUSTOMER child carries tolerance 5 and window 10. Open both to see the per-row settings side by side.

Results on SAMPLE_DW

Verified 2026-09-11 over two consecutive runs.

Run	Behaviour
1	All 9 pass. No history exists, so the control is missing and the template's Control Missing Result Action of Pass applies. The run seeds the baseline
2	All 9 pass. Each metric is compared with the average of run 1, which it equals

Nothing loads into the sample warehouse between runs, so these children keep passing. To see one fail, edit a row's threshold to 0 and its metric expression to COUNT(*) + 1, upload, materialize, and run.

Adapting it

Choose metrics that move for the right reasons. Row count catches truncation and double loads. A sum catches a currency or unit error even when the row count is right. A distinct count catches a key that stopped being unique. Add one row per metric per table.

Tune per table. Slow dimensions: low threshold, long window. Growing facts: a threshold that covers normal daily growth over the window. If a table grows 3% a day and you average 7 runs, the latest value sits roughly 10% above the average even when nothing is wrong. Set the threshold with that arithmetic in mind, or switch the aggregate to Maximum.

Days or weeks instead of runs. Change the window type on the control data set. The window size variable still applies, now as a number of days or weeks.

Pitfall: control missing result action. Leave it at Pass. With Fail, every new child fails its first run, and so does every child after a re-materialization that resets its history unless include prior versions is on (it is, on this template).

Pitfall: percent of a zero baseline. A metric whose previous runs averaged 0 cannot be compared by percent. Use an absolute tolerance for metrics that are usually 0, or a different test.