Snowflake Data Quality Monitoring

Wrong data hurts more than missing data, because people trust numbers that look fine. Duplicate customers, empty email fields, and stale tables quietly damage reports. Snowflake data metric functions measure quality inside the platform on a schedule, so problems surface before business users find them.

The Health Checkup Comparison

A yearly health checkup measures weight, blood pressure, and sugar levels. Doctors compare numbers with healthy ranges and flag anything unusual. Data metric functions act as those measurements for tables. Each function returns a number such as the count of empty values, and teams watch those numbers over time.

DATA QUALITY LOOP
=================

 +---------+    +-----------------+    +---------------+    +----------+
 | TABLE   |--->| DATA METRIC     |--->| RESULTS TABLE |--->| ALERT    |
 | email,  |    | FUNCTIONS run   |    | stores each   |    | emails   |
 | amount  |    | on a schedule   |    | measurement   |    | the owner|
 +---------+    +-----------------+    +---------------+    +----------+

Built-In Metrics

FUNCTION                           WHAT IT MEASURES
---------------------------------  -------------------------------------
SNOWFLAKE.CORE.NULL_COUNT          Empty values in a column
SNOWFLAKE.CORE.BLANK_COUNT         Empty strings in a column
SNOWFLAKE.CORE.DUPLICATE_COUNT     Repeated values in a column
SNOWFLAKE.CORE.UNIQUE_COUNT        Distinct values in a column
SNOWFLAKE.CORE.ROW_COUNT           Total rows in a table
SNOWFLAKE.CORE.FRESHNESS           Age of the newest data in a column

Trying a Metric by Hand

SELECT SNOWFLAKE.CORE.NULL_COUNT(SELECT email FROM sales_db.public.customers);
SELECT SNOWFLAKE.CORE.DUPLICATE_COUNT(SELECT customer_id FROM sales_db.public.customers);

Each call returns one number. A manual check works well while exploring a new table.

Scheduling Metrics on a Table

-- Set how often metrics run
ALTER TABLE sales_db.public.customers
  SET DATA_METRIC_SCHEDULE = '60 MINUTE';

-- Attach metrics to columns
ALTER TABLE sales_db.public.customers
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (email);

ALTER TABLE sales_db.public.customers
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.DUPLICATE_COUNT ON (customer_id);

ALTER TABLE sales_db.public.customers
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.ROW_COUNT ON ();

The schedule accepts cron expressions and a TRIGGER_ON_CHANGES option that measures after each data change. Snowflake runs the measurements on serverless compute and stores results in a system view.

Reading the Results

SELECT measurement_time, table_name, metric_name, argument_names, value
FROM SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS
WHERE table_name = 'CUSTOMERS'
ORDER BY measurement_time DESC
LIMIT 20;

Check the current Snowflake documentation for the exact view name and columns in your account, since monitoring features evolve.

Writing a Custom Metric

Business rules need custom functions. A custom metric counts rows that break a rule, such as negative order amounts.

CREATE DATA METRIC FUNCTION governance.dq.negative_amount_count(
  t TABLE(amount NUMBER))
RETURNS NUMBER
AS 'SELECT COUNT(*) FROM t WHERE amount < 0';

ALTER TABLE sales_db.public.orders
  ADD DATA METRIC FUNCTION governance.dq.negative_amount_count ON (amount);

Turning Metrics into Action

A number on a screen changes nothing unless someone acts. Pair metrics with alerts. An alert checks the latest measurement and emails the table owner when the value passes a limit. Repeat offenders deserve a root cause fix in the source system.

QUALITY RULE EXAMPLES
=====================
Rule                           Metric              Limit
-----------------------------  ------------------  -------------
Every customer has an email    NULL_COUNT (email)  0
No repeated customer IDs       DUPLICATE_COUNT     0
Table refreshed today          FRESHNESS           under 24 hours
Daily load has enough rows     ROW_COUNT           above 10000

Good Habits

  • Start with a few critical columns, such as keys and amounts.
  • Measure raw and final tables to locate where problems enter.
  • Choose schedules that match how often data changes.
  • Name an owner for every monitored table.
  • Review monitoring cost through serverless usage history.

A Practical Scenario

A retailer notices that a revenue dashboard shows a sudden drop each Monday. The data engineer attaches row count and freshness metrics to the orders table. Monday measurements reveal a delayed weekend file. The team fixes the file delivery schedule and adds an alert, and the dashboard stays steady afterward.

Frequently Asked Questions

Does monitoring slow my tables?

Measurements run on serverless compute outside your warehouse, so loading jobs and queries keep their speed. Frequent schedules on very large tables raise the monitoring cost, so choose intervals with care.

Can I watch a view?

Metrics attach to tables and certain table-like objects. Monitor the underlying tables, and review current documentation for the list of supported object types.

Who should own quality rules?

Data owners know what good data looks like. Engineers set up the functions, and owners agree on limits and respond to alerts.

Rollout Advice

Begin with the ten tables that matter most to the business. Agree on limits with each table owner, review measurements weekly for the first month, and expand coverage once the team trusts the results.

Key Points

  • Data metric functions measure quality inside Snowflake.
  • Built-in metrics cover nulls, duplicates, uniqueness, row counts, and freshness.
  • Schedules run measurements automatically and store results.
  • Custom metrics encode business rules.
  • Alerts turn measurements into action.
  • The feature requires Enterprise Edition or higher.
Previous lesson
Back to course
Next lesson