What you will be able to do
- Explain the difference between what a data metric function measures and what an expectation decides
- Pick the system DMF that detects a given quality issue: NULLs, blanks, duplicates, untrimmed strings or unparseable numbers
- Attach DMFs and expectations to a table with SQL and control how often they run
- Predict when DMF use is billed and which objects cannot have a DMF set on them
Key concept
DMF + expectation = data quality check — A data metric function only measures something about your data, such as a count of NULLs or duplicates. An expectation is the rule that decides whether that number passes or fails, and Snowflake reports a failure as a violation.
1.Measuring is not judging: DMFs and expectations
Before you clean a dataset you have to find out what is wrong with it, and in Snowflake the main tool for that is the data metric function (DMF). A DMF measures one attribute of the data, for example how many NULLs a column holds or how recently a table was updated. It returns a number that reflects the data's current state. That's all it does.
A DMF on its own doesn't say whether its value is a problem. The documentation calls it a building block of a data quality check. To get a check, you pair it with an expectation. Each time the DMF returns a value, Snowflake compares it with the expectation, and any value that fails is reported as an *expectation violation* for you to act on. You can set this up in Snowsight, where you choose the DMF and the expectation together, or work with expectations directly in SQL.
Two other features are built on top of DMFs. Anomaly detection uses historical DMF results to flag values that fall above or below a predicted range. At present this works for the volume and freshness of your data. The DMF schedule controls how often DMFs run on a table or view. By default they run once every hour.
Checkpoint 1 of 6· Check yourself
Your team wants an alert whenever orders.order_id contains duplicates. Which statement about the pieces involved is correct?
A DMF is only the measurement. Pass or fail comes from the expectation, and the schedule only controls how often the measurement runs.
“a DMF is a building block of a data quality check.”Source: docs.snowflake.com
Sources1
2.System DMFs that detect common quality issues
You usually don't need to write a DMF. Snowflake ships system DMFs in the SNOWFLAKE.CORE schema of the shared SNOWFLAKE database. Snowflake maintains them, so you can't rename them or change what they do, and you can assign several to the same table or view. They fall into categories: accuracy, freshness, schema, statistics, uniqueness and volume. Most of the issues a cleaning job deals with are covered by the accuracy and uniqueness categories. If there is no system DMF for the metric you want to monitor, you can define a custom DMF instead.
| Cleaning problem | System DMF | What it measures |
|---|---|---|
| Missing values | NULL_COUNT / NULL_PERCENT | NULL values in a column, as a count or a percentage |
| Empty strings | BLANK_COUNT / BLANK_PERCENT | Blank values in a column |
| Duplicate keys | DUPLICATE_COUNT | Duplicate values in a column, including NULL values |
| Cardinality | UNIQUE_COUNT | Unique, non-NULL values in a column |
| Stray whitespace | UNTRIMMED_STRING_COUNT | Non-NULL strings with leading or trailing whitespace |
| Inconsistent casing | CASE_FORMAT_VIOLATION_COUNT | Non-NULL strings that are not all-uppercase, all-lowercase or title-case |
| Text that should be numeric | INVALID_NUMERIC_TYPE_CAST_COUNT | Non-NULL strings that cannot be parsed as numeric |
| Broken JSON payloads | INVALID_JSON_COUNT | Non-NULL strings that are not valid JSON |
| Values that break a rule you define | ACCEPTED_VALUES | Whether values match a Boolean expression |
| Statistical outliers | OUTLIER_COUNT / EXTREME_OUTLIER_COUNT | Numeric values outside the asymmetric Tukey fences, or the extreme ones |
| Unexpected negatives or zeros | NEGATIVE_COUNT / ZERO_COUNT | Numeric values that are negative, or equal to zero |
| Dates in the future | FUTURE_TIMESTAMP_COUNT | Date/timestamp values in the future relative to the scheduled evaluation time |
| Stale or shrinking tables | FRESHNESS / ROW_COUNT | Data freshness, and the number of records in the table or view |
Checkpoint 2 of 6· Match them up
Match each symptom in a contacts table to the system DMF that would detect it
Tap a term, then the definition that fits it.
Each system DMF measures one specific attribute. Leading or trailing whitespace, mixed casing, unparseable numbers and repeated values each have their own function.
“Determine how many non-NULL values in a string column have leading or trailing whitespace.”Source: docs.snowflake.com
3.Attaching DMFs, writing expectations and setting the schedule
You can call a DMF directly to try it out before you rely on it. To have it run automatically, you associate it with a table or view and specify which columns it receives as arguments. On an existing object you do this with ALTER TABLE or ALTER VIEW:
ALTER TABLE t
ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT
ON (c1);Some DMFs, such as ROW_COUNT, take no column, so you write ON (). ACCEPTED_VALUES takes a column plus a lambda, for example ON (age, age -> age = 5), and returns the number of records that don't match. To start monitoring the moment an object exists, add a WITH DATA METRIC FUNCTION clause to the CREATE statement and attach the expectation in the same place:
CREATE OR REPLACE TABLE orders (
order_id NUMBER,
customer_id NUMBER
)
WITH DATA METRIC FUNCTION (
SNOWFLAKE.CORE.NULL_COUNT
ON (customer_id)
EXPECTATION no_null_customers ( VALUE = 0 ),
SNOWFLAKE.CORE.DUPLICATE_COUNT
ON (order_id)
EXPECTATION no_duplicate_orders ( VALUE = 0 )
);The syntax has strict rules. The left side of every expectation must be the keyword VALUE. The allowed operators include =, <, >=, AND, OR, NOT and EQUAL_NULL. Arithmetic on VALUE, subqueries and quoted 'VALUE' are rejected. The bindings attach atomically: if one is invalid, the whole CREATE fails. A clone, or a table created with LIKE, inherits the source's DMF bindings.
The DATA_METRIC_SCHEDULE object parameter controls how often the DMFs run, and every DMF on a table or view shares that one schedule. You can set it as a number of minutes, as a cron expression, or as 'TRIGGER_ON_CHANGES' so the DMFs run when DML modifies the table. The trigger approach is only available for certain kinds of tables, and reclustering does not trigger a run.
Checkpoint 3 of 6· Fill the gap
Which parameter completes this statement so that the table's DMFs run every 5 minutes?
ALTER TABLE hr.tables.empl_info SET ? = '5 MINUTE';DATA_METRIC_SCHEDULE is the object parameter that controls how often all DMFs on a table run. TRIGGER_ON_CHANGES is one possible value of it, not the parameter name.
Source: docs.snowflake.comCheckpoint 4 of 6· Put it in order
Put the life of a data quality check in order
- 1.Associate the DMF and its expectation with the table
- 2.A failing value is reported as an expectation violation
- 3.The DMF runs on the table's DATA_METRIC_SCHEDULE
- 4.The returned value is compared to the expectation
Once the DMF is associated, the schedule runs it, and each value it returns is compared with the expectation. Failures are reported as violations. Calling the DMF directly first is an optional way to test it, not a required step.
“You can associate a DMF with a table or view to automatically call it on regular intervals.”Source: docs.snowflake.com
Checkpoint 5 of 6· Exam question
An analyst loads the same events repeatedly into `customer_events`. Rows sharing an `event_id` are duplicates, and only the most recently loaded row per `event_id` must land in a new table, without a self-join. Which statement does this?
Correct answer: B — CREATE TABLE events_clean AS SELECT * FROM customer_events QUALIFY ROW_NUMBER() OVER (PARTITION BY event_id ORDER BY loaded_at DESC) = 1
- A. Incorrect. Partitioning by `loaded_at` groups rows by load time rather than by event, so duplicates of one event in different loads each rank first inside their own partition and are all kept.
- B. Correct. ROW_NUMBER numbers rows within each `event_id` from newest to oldest, and QUALIFY filters to number 1 after the window is computed, so exactly one latest row per event survives.
- C. Incorrect. Selecting all columns while grouping only by `event_id` is invalid because the other columns are neither grouped nor aggregated, and HAVING cannot compare against a per-group maximum this way.
- D. Incorrect. DISTINCT only removes rows identical in every column, so reloaded events that differ in `loaded_at` stay as duplicates, and the LIMIT arbitrarily truncates the table.
Sources3
4.What DMFs cost and where you cannot use them
Scheduled DMFs run on serverless compute. Those credits appear under "Data Quality Monitoring" on the monthly bill, and the logging of metric results into the event table appears separately as "Logging". Creating a DMF is free. Billing applies only when a scheduled DMF is computed on an object, so the ad-hoc profiling queries above are not billed under Data Quality Monitoring. That makes calling DMFs directly a reasonable way to profile data before cleaning it. To track spend, query DATA_QUALITY_MONITORING_USAGE_HISTORY.
You can set a DMF on tables (including temporary and transient tables), views, materialized views, dynamic tables, event tables, external tables and Iceberg tables. You cannot set one on a hybrid table or a stream.
When you read results from the DATA_QUALITY_MONITORING_RESULTS view, filter on measurement_time rather than the scheduled time. Rows can be inserted between the two.
Checkpoint 6 of 6· Check yourself
Which situation adds credits under Data Quality Monitoring?
Only scheduled computation of a DMF on an object is billed. Creating a DMF and calling one in a SELECT are not, and a DMF cannot be set on a hybrid table at all.
“Billing occurs only when a scheduled DMF is computed on an object.”Source: docs.snowflake.com
Sources1
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Running a DMF in a SELECT to profile a table costs the same Data Quality Monitoring credits as a scheduled DMF.Why is that wrong?
Only scheduled DMF computation is billed. Calling a DMF in a SELECT is unscheduled usage and is not billed under Data Quality Monitoring.
Covered in What DMFs cost and where you cannot use them
2.You can run NULL_COUNT every 5 minutes and DUPLICATE_COUNT daily on the same table by giving each DMF its own schedule.Why is that wrong?
DATA_METRIC_SCHEDULE is set on the table or view, and every DMF associated with it runs on that one schedule.
Covered in Attaching DMFs, writing expectations and setting the schedule
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“A DMF measures an attribute of your data such as how many NULL values exist in a column”
↩︎ Measuring is not judging: DMFs and expectations“By default, the DMF schedule runs a DMF once every hour.”
↩︎ Measuring is not judging: DMFs and expectations“If there isn’t a system DMF for the metric that you want to monitor, you can define a custom DMF.”
↩︎ System DMFs that detect common quality issues“You cannot set a DMF on a hybrid table or a stream object.”
↩︎ What DMFs cost and where you cannot use them“An expectation is combined with a DMF to create a data quality check.”
↩︎ Key concept“You are not billed for unscheduled data metric function usage, such as calling a DMF with a SELECT statement.”
↩︎ Exam trap 1“When a DMF returns a value, it’s compared to the expectation’s definition to determine whether data passed or failed the check.”
↩︎ Prediction“a DMF is a building block of a data quality check.”
↩︎ Checkpoint“You are not billed for unscheduled data metric function usage, such as calling a DMF with a SELECT statement.”
↩︎ Prediction“Billing occurs only when a scheduled DMF is computed on an object.”
↩︎ Checkpoint - 2.
“Snowflake provides system DMFs in the CORE schema of the shared SNOWFLAKE database.”
↩︎ System DMFs that detect common quality issues“Determine the number of duplicate values in a column, including NULL values.”
↩︎ System DMFs that detect common quality issues“Determine how many non-NULL values in a string column have leading or trailing whitespace.”
↩︎ Checkpoint - 3.
“The left side of the comparison must be the keyword VALUE.”
↩︎ Attaching DMFs, writing expectations and setting the schedule“CLONE: The cloned object inherits DMF bindings from the source.”
↩︎ Attaching DMFs, writing expectations and setting the schedule“The trigger approach is only available for certain kinds of tables.”
↩︎ Attaching DMFs, writing expectations and setting the schedule“All data metric functions on a table or view follow the same schedule.”
↩︎ Exam trap 2“You can associate a DMF with a table or view to automatically call it on regular intervals.”
↩︎ Checkpoint