Measuring What Matters: Elevating Data Quality with Snowflake DMFs

In today’s data-driven world, decisions are only as good as the data behind them. Poor data quality can lead to inaccurate insights, missed opportunities, and costly mistakes. Organizations need a systematic way to measure, monitor, and improve data quality.

Enter Snowflake Data Metric Functions (DMFs) — a powerful feature that helps you quantify and track data quality across your Snowflake environment.

What Are Snowflake DMFs?

Data Metric Functions (DMFs) are built-in and custom functions that allow you to measure specific aspects of data quality.

Snowflake offers system DMFs for common checks in Categories such as Accuracy, Freshness, Statistics, Uniqueness, Volume and allows you to create custom DMFs for business-specific rules.

How DMFs Work

Use system data metric function or create a custom metric function on your tables or columns.

Associate DMF to table for Continueous Monitoring

Query metric measurements from centralized table

Data Quality Workflow in Snowflake

Test it out!

-----------------  Access control setup -----------------------
USE ROLE ACCOUNTADMIN;
CREATE ROLE IF NOT EXISTS dq_role;

GRANT CREATE DATABASE ON ACCOUNT TO ROLE dq_role;
GRANT EXECUTE DATA METRIC FUNCTION ON ACCOUNT TO ROLE dq_role;
GRANT APPLICATION ROLE SNOWFLAKE.DATA_QUALITY_MONITORING_VIEWER TO ROLE dq_role;
GRANT DATABASE ROLE SNOWFLAKE.USAGE_VIEWER TO ROLE dq_role;
GRANT DATABASE ROLE SNOWFLAKE.DATA_METRIC_USER TO ROLE dq_role;

SHOW GRANTS TO ROLE dq_role;

GRANT ROLE dq_role TO ROLE SYSADMIN;
GRANT ROLE dq_role TO USER sftraining;

-----------------  Data setup -----------------------
USE ROLE dq_role;
CREATE DATABASE IF NOT EXISTS dq_db;
CREATE SCHEMA IF NOT EXISTS sch;

CREATE or replace TABLE customers (
  customer_id NUMBER(38,0),
  first_name VARCHAR(16777216),
  last_name VARCHAR(16777216),
  email VARCHAR(16777216),
  phone VARCHAR(16777216),
  created_at TIMESTAMP_NTZ(9),
  street VARCHAR(16777216),
  city VARCHAR(16777216),
  state VARCHAR(16777216),
  country VARCHAR(16777216),
  pin_code NUMBER(38,0)
);

INSERT INTO customers (customer_id, city, country, email, first_name, last_name, phone, state, street, pin_code)
VALUES
  (100001, 'Mumbai', 'India', 'rahul.sharma@', 'Rahul', 'Sharma', '9876543210', null, null, null),
  (100001, 'Mumbai', 'India', 'rahul.sharma@', 'Rahul', 'Sharma', '9876543210', 'Maharastra', null, 400001),
  (100002, 'Delhi', 'India', 'priya.verma@example.com', 'Priya', 'Verma', '9812345678', 'Delhi', null, 110001),
  (100003, 'Bengaluru', 'India', 'arjun.reddy@example.com', 'Arjun', 'Reddy', '9901234567', 'Karnataka', 'MG Road', 560001),
  (100004, 'Hyderabad', 'India', 'sneha.rao@example.com', 'Sneha', 'Rao', '9123456789', 'Telangana', 'Banjara Hills', 500034),
  (100005, 'Chennai', 'India', 'vikram.iyer@example.com', 'Vikram', 'Iyer', '9845671234', 'Tamil Nadu', 'T Nagar', 600017),
  (100006, 'Pune', 'India', 'neha.patil@example.com', 'Neha', 'Patil', '9765432109', 'Maharashtra', 'FC Road', 411004),
  (100008, 'Jaipur', 'India', 'kavita.singh@example.com', 'Kavita', 'Singh', '9823456789', 'Rajasthan', '', 302001),
  (100008, 'Jaipur', 'India', 'kavita.singh@example.com', 'Kavita', 'Singh', '9823456789', 'Rajasthan', '', 302001);

select * from customers;

-------------- Explore system DMFs -------------
select snowflake.core.null_percent (
    select state from customers
    );

select snowflake.core.duplicate_count (select customer_id from customers);    

 -- Execute the BLANK_COUNT data metric function to return the number of blank values: 
  SELECT snowflake.core.blank_count (SELECT street FROM dq_db.sch.customers);

   -- To return the table rows containing blank values in the street column, execute the SYSTEM$DATA_METRIC_SCAN function on the name column. 
  SELECT *
  FROM TABLE(SYSTEM$DATA_METRIC_SCAN(
    REF_ENTITY_NAME  => 'dq_db.sch.customers',
    METRIC_NAME  => 'snowflake.core.blank_count',
    ARGUMENT_NAME => 'street'
   ));

-- Update records with a new value   
  UPDATE dq_db.sch.customers
  SET street = null
  WHERE dq_db.sch.customers.customer_id IN (
    select customer_id from table(system$data_metric_scan(
  REF_ENTITY_NAME => 'dq_db.sch.customers',
  METRIC_NAME => 'snowflake.core.blank_count',
  ARGUMENT_NAME => 'street'
  )));

  SELECT snowflake.core.blank_count (SELECT street FROM dq_db.sch.customers);

-----------------  Create and work with DMFs -----------------------

  CREATE DATA METRIC FUNCTION IF NOT EXISTS
  invalid_email_count (ARG_T table(ARG_C1 STRING))
  RETURNS NUMBER AS
  'SELECT COUNT_IF(FALSE = (
    ARG_C1 REGEXP ''^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$''))
    FROM ARG_T';

  ALTER TABLE customers SET DATA_METRIC_SCHEDULE = '5 MINUTE';
-- We can also use cron expressions or trigger events associated with DML operations that affect the table.

ALTER TABLE customers ADD DATA METRIC FUNCTION
  invalid_email_count ON (email);

ALTER TABLE customers ADD DATA METRIC FUNCTION
  snowflake.core.duplicate_count ON (customer_id);

-- check the references  
 SELECT * FROM TABLE(INFORMATION_SCHEMA.DATA_METRIC_FUNCTION_REFERENCES(
  REF_ENTITY_NAME => 'dq_db.sch.customers',
  REF_ENTITY_DOMAIN => 'TABLE')); 

-- View the DMF results

 SELECT * FROM SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS_RAW;
SELECT * FROM SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS;

 SELECT scheduled_time, measurement_time, table_name, metric_name, value
FROM SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS;

  -----------------  View your serverless credit consumption -----------------------

  USE ROLE dq_role;
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.DATA_QUALITY_MONITORING_USAGE_HISTORY
WHERE TRUE
AND START_TIME >= CURRENT_TIMESTAMP - INTERVAL '20 days'
LIMIT 100;

  ------------- Unset the DMFs from the table -------------------------
ALTER TABLE customers DROP DATA METRIC FUNCTION
  invalid_email_count ON (email);  

ALTER TABLE customers DROP DATA METRIC FUNCTION
  snowflake.core.duplicate_count ON (customer_id);    

Best Practices

• Start with critical data elements (customer, financial, compliance data).
• Use DMFs in pipelines for continuous monitoring.
• Visualize metrics in Dashboards (Snowflake + BI tools).
• Automate alerts for proactive remediation.

Data quality isn’t a one-time effort — it’s a continuous journey. With Snowflake DMFs, organizations can measure what matters, build trust in their data, and make confident decisions.

References: https://docs.snowflake.com/en/user-guide/ui-snowsight/snowsight-templates

Follow and Clap if you like the content and feel free to ask if you have any questions in the comments. I will be more than happy to assist and guide you.