Data Quality Checks in Snowflake: DMFs, Cost, and Anomaly Detection

Snowflake has native data quality checks built in. You attach a data metric function (DMF) to a table or column, pair it with an expectation such as VALUE = 0, put it on a schedule, and Snowflake writes every result to the SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS view. System DMFs cover nulls, blanks, duplicates, row counts, freshness, accepted values, and schema changes, and two of them support automatic anomaly detection. The catch: it needs Enterprise Edition, it bills as serverless compute under its own line item, and each check sees exactly one table. It will tell you a column went null. It will not tell you which upstream job did it or which dashboards are now wrong.

Last updated July 2026.

How do I check data quality in Snowflake?

Three ways, in increasing order of effort. Run SQL checks yourself on a schedule with tasks. Use Snowflake's native data quality monitoring, which is DMFs plus expectations plus a schedule. Or connect a monitoring platform that reads your metadata and builds the checks for you. Most teams end up running two of the three, because they solve different halves of the problem.

Start with plain SQL, because you should know what a check looks like before you automate it. The basic data quality checks that catch most real incidents are boring and short:

SELECT COUNT_IF(customer_id IS NULL) / NULLIF(COUNT(*), 0) AS null_rate, COUNT(*) - COUNT(DISTINCT order_id) AS dupes, DATEDIFF('minute', MAX(updated_at), CURRENT_TIMESTAMP()) AS freshness_lag FROM analytics.core.fct_orders;

That query is a full table scan and it costs whatever your warehouse costs to run it. Which is the first thing worth understanding about doing this at scale: the check is trivial, the scan is not.

Does Snowflake have data quality monitoring?

Yes. Data Quality Monitoring is a native Enterprise Edition feature built on data metric functions. You associate a DMF with a table, view, or column, set a schedule on the object, and Snowflake computes the metric on that cadence and logs the result to an event table. Snowsight has a UI for it, including seven-day trend charts, run history, and a link to the records that failed.

The Enterprise Edition requirement is not a footnote. If you are on Standard, none of what follows is available to you, and your options are SQL in tasks or an external tool.

What is a DMF in Snowflake?

A DMF is a function that measures one attribute of your data and returns a number. SNOWFLAKE.CORE.NULL_COUNT returns how many nulls are in a column. It does not decide whether that number is a problem. That judgment comes from the expectation you attach to it, which is what turns a measurement into a pass or fail check.

System DMFs live in the SNOWFLAKE.CORE schema and need no setup. They are grouped by category:

Category	System DMFs (SNOWFLAKE.CORE)	What it answers
Volume	ROW_COUNT	Did the expected number of rows arrive?
Freshness	FRESHNESS, DATA_METRIC_SCHEDULE_TIME	How long since the table was updated?
Accuracy	NULL_COUNT, NULL_PERCENT, BLANK_COUNT, BLANK_PERCENT, ZERO_COUNT, NEGATIVE_COUNT, FUTURE_TIMESTAMP_COUNT, INVALID_JSON_COUNT, INVALID_NUMERIC_TYPE_CAST_COUNT, SPECIAL_CHARACTER_COUNT, UNTRIMMED_STRING_COUNT, CASE_FORMAT_VIOLATION_COUNT	Are the values missing, empty, or malformed?
Uniqueness	DUPLICATE_COUNT, UNIQUE_COUNT, ACCEPTED_VALUES	Is the grain what you think it is?
Statistics	AVG, MIN, MAX, MEDIAN, STDDEV, VARIANCE, APPROX_QUANTILE_25, APPROX_QUANTILE_50, APPROX_QUANTILE_99, STRING_LENGTH_AVG, STRING_LENGTH_MIN, STRING_LENGTH_MAX	Has the distribution shifted?
Schema	SCHEMA_CHANGE_COUNT	Did columns get added, dropped, or retyped?

Most of the accuracy DMFs have both a _COUNT and a _PERCENT variant. Use the percent version for expectations. A count threshold that made sense at 10 million rows is meaningless at 400 million.

Setting up a check, end to end

The schedule lives on the table, not on the DMF. Set it once:

ALTER TABLE analytics.core.fct_orders SET DATA_METRIC_SCHEDULE = 'USING CRON 0 8 * * * UTC';

Supported values are a minute interval ('5 MINUTE'), a cron expression, or 'TRIGGER_ON_CHANGES', which runs the DMF only when the table actually changes. The default, if you set nothing, is once an hour. Scheduling changes take about 10 minutes to take effect, so do not panic when your new cadence does not fire immediately.

Then attach the function and the expectation:

ALTER TABLE analytics.core.fct_orders ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (customer_id) EXPECTATION no_orphan_orders (VALUE = 0);

Expectations accept =, !=, <>, <, >, <=, >=, plus AND, OR, NOT, and EQUAL_NULL. Read the results back out:

SELECT measurement_time, table_name, metric_name, value FROM SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS WHERE metric_name = 'NULL_COUNT' ORDER BY measurement_time DESC;

When something fails, SYSTEM$DATA_METRIC_SCAN gives you the offending rows rather than just the count:

SELECT * FROM TABLE(SYSTEM$DATA_METRIC_SCAN(REF_ENTITY_NAME => 'analytics.core.fct_orders', METRIC_NAME => 'snowflake.core.null_count', ARGUMENT_NAME => 'customer_id'));

That function is the single most useful part of the native feature. Going from "27 nulls" to "these 27 rows, all from the same source system, all since Tuesday" is most of the debugging.

Custom DMFs are just UDFs that return a number, so they belong in version control and code review like the rest of your SQL. The tedious part is not the logic, it is generating a few hundred near-identical ALTER TABLE statements from your information schema, which is exactly the sort of boilerplate teams now hand to an assistant that plans and writes the code instead of typing out by hand.

Can Snowflake detect anomalies in data?

Yes, for two metrics only: volume and freshness. Anomaly detection works with ROW_COUNT and FRESHNESS, and it flags values outside a predicted range learned from history rather than a threshold you set. Nothing else in the DMF catalog supports it, so distribution drift on a numeric column still needs an expectation you write yourself.

ALTER TABLE analytics.core.fct_orders MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.ROW_COUNT ON () SET ANOMALY_DETECTION = TRUE;

Two details decide whether this works for you. First, training: for frequently scheduled DMFs Snowflake needs at least two weeks of history to learn weekly seasonality, and trains on up to 60 days when it has it. You are not getting useful volume anomaly detection in week one. Second, sensitivity defaults to MEDIUM and takes LOW or HIGH. If it fires every Tuesday, drop it to LOW before your team learns to dismiss it.

Check on training state with DATA_METRIC_FUNCTION_REFERENCES (the anomaly_detection_status column shows TRAINING_IN_PROGRESS), and read results from SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_ANOMALY_DETECTION_STATUS.

What do Snowflake data quality checks cost in credits?

DMFs run on serverless compute, billed in compute-hours under a "Data Quality Monitoring" line item on your statement, with the rate published in the Serverless Feature Credit Table of Snowflake's Service Consumption Table. Creating a DMF is free. Calling one ad hoc in a SELECT is free. You are billed only when a scheduled DMF computes, plus separate logging costs for the event table.

The billing model matters less than the arithmetic you control. A NULL_PERCENT DMF is a column scan. Set DATA_METRIC_SCHEDULE = '5 MINUTE' on a large fact table and you have bought 288 scans a day, per DMF, forever. The hourly default is 24. Attach six DMFs to that table and you are at 144 scans a day for one table. Nothing warns you about this.

Three habits keep the bill sane:

• Use TRIGGER_ON_CHANGES as the default. Most tables load a handful of times a day. Scanning them every five minutes measures the same rows over and over. It also removes the awkward alert where a table that legitimately did not change gets flagged.
• Do not put scanning DMFs on your biggest tables at high frequency. ROW_COUNT and FRESHNESS are cheap because they lean on metadata. NULL_PERCENT across twelve columns of a billion-row table is not.
• Watch DATA_QUALITY_MONITORING_USAGE_HISTORY in ACCOUNT_USAGE. Check it a week after rollout, not a quarter after.

The metadata you get for free

Before you schedule anything, note that Snowflake already tracks a lot of this and you can read it without scanning a single row. INFORMATION_SCHEMA.TABLES carries LAST_ALTERED, ROW_COUNT, and BYTES per table, live. ACCOUNT_USAGE.TABLES holds the same with history (and roughly 90 minutes of latency). ACCOUNT_USAGE.COPY_HISTORY shows what landed from your loads, and ACCESS_HISTORY shows who read what.

SELECT table_schema, table_name, row_count, DATEDIFF('minute', last_altered, CURRENT_TIMESTAMP()) AS mins_stale FROM information_schema.tables WHERE table_schema = 'CORE' AND table_type = 'BASE TABLE' ORDER BY mins_stale DESC;

That one query gives you freshness and volume across an entire schema for close to nothing. This is the whole argument for metadata-first monitoring: 80 percent of real incidents (a load that did not run, a load that delivered half the rows, a column that changed shape) show up in metadata, and the metadata is already there. Query-based checks are for the remaining 20 percent, where you genuinely need to look at values.

One caveat people hit: LAST_ALTERED updates on DDL as well as DML, and a MERGE that matched zero rows still touches the table. Metadata freshness and MAX(updated_at) freshness disagree sometimes, and the disagreement is usually the incident.

Where native checks stop

Native DMFs are genuinely good at the thing they do: measuring one table, on a schedule, inside Snowflake, with no extra vendor. What they do not do is the incident.

	SQL in tasks	Native DMFs + expectations	Observability platform
Setup	You write every check	DDL per table and column	Connect once, monitors generated
Edition needed	Any	Enterprise+	Any
Thresholds	You hard-code them	Expectations you hard-code; learned ranges for volume and freshness only	Learned per table across all pillars
Cost driver	Warehouse scans	Serverless compute-hours per scheduled run	Metadata reads, mostly flat
Cross-table lineage	No	No	Column-level, across the warehouse
Blast radius on alert	No	Downstream assets in Snowsight	Affected models and dashboards, in the alert
Alerting	You build it	Query the results view and build it	Slack and PagerDuty, routed by severity
Non-Snowflake sources	No	No	BigQuery, Databricks, Redshift too

The honest summary of that table: DMFs answer "is this table okay right now". Continuous monitoring answers "what broke, when, why, and who should care". If you run one warehouse, have fewer than 50 tables that matter, and someone owns the checks, native DMFs plus a scheduled query against the results view is a real, defensible setup. Build it.

The line gets crossed somewhere around the point where you are writing DDL for the four hundredth column, or the third time an alert fires and nobody can tell whether the exec dashboard is affected. DMFs have no idea what column-level lineage is, so every alert starts an investigation from zero, and the investigation is the expensive part, not the detection.

A sane starting configuration

If you are setting this up this week, in this order:

1. Tier your tables. Ten to thirty of them actually matter. Rank by downstream dashboards and models, not by row count.
2. Put ROW_COUNT and FRESHNESS with ANOMALY_DETECTION = TRUE on every tier-one table. Cheap, no thresholds to invent, and they need the two weeks of training to start, so start them now.
3. Add NULL_PERCENT on join keys and DUPLICATE_COUNT on primary keys, tier one only, on TRIGGER_ON_CHANGES.
4. Add ACCEPTED_VALUES on status and enum columns, since those break silently when a producer adds a state nobody told you about.
5. Wire the results view to something that pages a human. A check nobody sees is not a check.

Step five is where most native rollouts stall. The data is in the view, and the view is not a pager.

Getting the parts DMFs do not cover

If you want the detection without the DDL and the lineage that turns an alert into a diagnosis, that is what we built. Connect a read-only Snowflake user and dataobservability.ai profiles your tables and generates freshness, volume, schema, distribution, and lineage monitors in about 15 minutes, learns thresholds from your history rather than asking you to guess, and routes alerts to Slack or PagerDuty with the affected downstream models and dashboards attached. It is metadata-first, so it does not sit on your warehouse re-scanning tables, and it is dbt-native if you use dbt. Details are on our Snowflake data observability page, pricing starts at $99 a month, and the 14-day free trial takes no card.

Weighing options more broadly, our breakdown of data quality tools covers where native checks, open source, and platforms each make sense. But if native DMFs cover you, use them. They are included in what you already pay Snowflake, and the best monitoring is the monitoring someone maintains.