Stored Procedure
Analyzer
Automatic data quality profiling of Snowflake tables via point-in-time snapshots and statistics.
Goal
When onboarding a new data source you want to quickly know what's in it: how many nulls, which ranges, how unique are the values? The Analyzer builds a systematic quality profile by capturing tables as snapshots and automatically calculating statistics over them — without writing manual queries.
By comparing snapshots over time you can also detect changes in data quality: are there suddenly more nulls? Has a column exceeded its expected range?
What to expect
The Analyzer consists of three stored procedures that you call in sequence:
create_pit()
Creates a point-in-time snapshot of a source table by cloning it with a timestamp in the name (e.g. CUSTOMERS_20240412_143000).
register_pits()
Registers new snapshots in a statistics register. Retrieves table and column metadata and links the timestamp from the table name.
update_statistics()
Calculates data quality metrics over all registered snapshots: null counts, distinct values, min and max values, and threshold breaches.
Calculated statistics per column:
| Metric | Description |
|---|---|
| Null count | Number of empty values per column |
| Distinct count | Number of unique values per column |
| Min / Max | Lowest and highest value |
| Text length | Breaches of configurable maximum length |
| Numeric range | Values outside a configurable range |
| Date range | Dates outside the expected period |
Usage
1. Run setup
Run setup.sql to create the required schemas and registration tables, then create_analyze_sp.sql to install the procedures.
2. Create snapshot
CALL datamodder.create_pit( source_db => 'MY_DB', source_sch => 'RAW', source_tbl => 'CUSTOMERS' ); -- Creates: MY_DB.RAW.CUSTOMERS_20240412_143000
3. Register snapshot and analyse
CALL datamodder.register_pits(); CALL datamodder.update_statistics(); -- View results: SELECT * FROM datamodder.statistics ORDER BY snapshot_ts DESC;