---
title: "Metadata Script Example: Change Over Time"
slug: "metadata-script-example-change-over-time"
updated: 2026-10-20T15:21:55Z
published: 2026-10-20T15:21:55Z
canonical: "docs.validatar.com/metadata-script-example-change-over-time"
---

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

# 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](/docs/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](/docs/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](/docs/import-file-example-change-over-time): 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](/docs/metadata-script-example-referential-integrity) 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.

## Related

- [Template Test Examples: Metadata Script](/docs/template-test-examples-metadata-script)
- [Generating Child Tests using a Metadata Script](/docs/generating-child-tests-using-a-metadata-script)
- [Import File Example: Change Over Time](/docs/import-file-example-change-over-time)
- [Metadata Script Example: Column Check Suite](/docs/metadata-script-example-column-check-suite)