Metadata Script Example: Change Over Time

What it checks

Whether a metric has drifted too far from its recent history. Each child test computes one metric on one table and compares it with the average of that metric over previous runs. The comparison is the same as in Import File Example: Change Over Time.

The file version watches nine metrics that someone chose. This script watches every table in STAR_SCHEMA and every amount column on its fact tables, read from INFORMATION_SCHEMA each time the template is materialized. It also sets the thresholds by rule instead of row by row: fact tables, which move more between loads, get a wider band and a shorter baseline than dimensions.

Part of Template Test Examples: Metadata Script.

The template

Setting	Value
Name	Metadata Script Example - Change Over Time
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: test SQL `SELECT {{var.metric_sql}} AS METRIC_VALUE FROM {{schema.name}}.{{table.name}}`, control set to the average of the previous `{{var.lookback_runs}}` runs, percent of control value `{{var.tolerance}}`, Control Missing Result Action Pass

The metadata script

metadata-script-example-change-over-time.sql:

-- Every STAR_SCHEMA table gets a row count, and every FLOAT amount column on a FACT_ table
-- gets a total, so a fact table produces several children. The thresholds follow the table
-- type: fact tables move more between loads than dimensions.
WITH metrics AS (
    SELECT table_schema, table_name, 'row count' AS metric, 'COUNT(*)' AS metric_sql
    FROM INFORMATION_SCHEMA.TABLES
    WHERE table_schema = 'STAR_SCHEMA' AND table_type = 'BASE TABLE'
    UNION ALL
    SELECT table_schema, table_name,
           'total ' || LOWER(REPLACE(column_name, '_', ' ')),
           'SUM(' || column_name || ')'
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE table_schema = 'STAR_SCHEMA'
      AND STARTSWITH(table_name, 'FACT_')
      AND data_type = 'FLOAT'
      AND ENDSWITH(column_name, '_AMOUNT')
)
SELECT table_schema AS "schema.name",
       table_name   AS "table.name",
       table_name || ' ' || metric AS "test.key",
       metric       AS "var.metric",
       metric_sql   AS "var.metric_sql",
       IFF(STARTSWITH(table_name, 'FACT_'), '10', '5')  AS "var.tolerance",
       IFF(STARTSWITH(table_name, 'FACT_'), '7', '10')  AS "var.lookback_runs"
FROM metrics
ORDER BY "test.key"

How it works:

The first half of metrics gives every base table in STAR_SCHEMA a row count.
The second half gives every FLOAT column ending in _AMOUNT on a FACT_ table a total, named after the column (total payment amount) and computed as SUM(PAYMENT_AMOUNT).
test.key is the table plus the metric name, so a fact table with a row count and a total has two keys and two child tests.
var.tolerance and var.lookback_runs come from the table name: 10% over 7 runs for fact tables, 5% over 10 runs for everything else.

What it returns

Seventeen rows: fourteen row counts and three totals.

Table	Metrics
`DIM_ACCOUNT`, `DIM_BUDGET`, `DIM_CLAIM`, `DIM_CUSTOMER`, `DIM_PAYER`, `DIM_POLICY`, `DIM_PRODUCT`, `DIM_PROVIDER`, `DIM_USER`	row count (5%, 10 runs)
`FACT_EXPENSE`	row count, total expense amount (10%, 7 runs)
`FACT_PAYMENT`	row count, total payment amount (10%, 7 runs)
`FACT_TRANSACTION`	row count, total transaction amount (10%, 7 runs)
`FACT_REVENUE`, `FACT_SALES`	row count (10%, 7 runs)

FACT_SALES gets no total. Its SALES_AMOUNT is stored as text and its TAX_AMOUNT as NUMBER, so the data_type = 'FLOAT' filter skips both. The import file version reaches its sales amount with TRY_TO_DOUBLE, an expression no rule in this script would write.

Results on SAMPLE_DW

Verified 2026-10-19: 17 children, all pass. The first run has no history, and Control Missing Result Action is set to Pass so that the first run seeds the baseline. From the second run on, a child test fails when its metric moves more than its tolerance away from the average of the previous runs.

Compared with the import file

	Import file	Metadata script
Which tables are watched	Seven, chosen by hand	Every base table in `STAR_SCHEMA`
Which metrics	Nine, including a distinct count and a text-to-number total	Row counts, plus totals of `FLOAT` amount columns
Thresholds	Typed per row	Set by table type
Child tests	9	17
A new table	Unwatched until someone adds a row	Watched from the next materialization

The two approaches suit different jobs. The file is the place for a handful of carefully chosen metrics. The script is broad coverage that nobody has to remember to extend.

Adapting it

Watch another schema. Change table_schema = 'STAR_SCHEMA' in both halves, or use IN (...).

Add metric types. Add a third UNION ALL block, for example a distinct count of every column ending in _ID on fact tables: 'distinct ' || LOWER(column_name) as the metric and 'COUNT(DISTINCT ' || column_name || ')' as its SQL.

Mix rules and exceptions. Join the result to a short list of per-table overrides, as the referential integrity script does, so that a known volatile table can have a wider band.

Pitfall: changing a metric's name re-keys it. The key includes the metric name. Rename total payment amount and the child test is deleted and recreated, losing the run history that forms its baseline.

Pitfall: views and new tables. The script reads base tables only. A table that exists in the warehouse but not yet in the Validatar catalog fails Preview & Validate with Invalid meta object. Ingest metadata after adding tables.