A user-defined data metric function is a Snowflake function for testing table and column data against requirements that built-in metrics do not cover. It uses standard UDF-style syntax, but accepts a table and its columns as parameters. Define the function in a schema, then attach it to a table or view column for scheduled evaluation rather than placing the test inside the ingestion pipeline. section 14.3 section 14.3.2
Data metric functions run independently on a table-level schedule. A schedule can be a minute interval, a CRON expression, or a trigger on DML changes. Snowflake stores execution results in its data-quality monitoring results table, allowing quality checks to run without delaying frequent or continuous ingestion. section 14.1.2 section 14.3 section 14.3.1
Create and apply a custom metric function in four steps:
- Define a table parameter and typed column parameters; use them as placeholders in a query that returns the metric value. section 14.3.2
- Set DATA_METRIC_SCHEDULE on the target table, initially using a short interval for testing if needed. section 14.3.2
- Attach the function to the target column with ALTER TABLE ... ADD DATA METRIC FUNCTION. section 14.3.2
- Query SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS to inspect measurement time, metric, arguments, and value. section 14.3.2
Count invalid product categories
INVALID_CATEGORY accepts a table T with a CAT column and returns the count of values outside the allowed list, Bread and Pastry. Applied to PRODUCT_TBL.CATEGORY, it reports 1 when one category violates that rule. After testing at five-minute intervals, schedules can be changed to run every 24 hours or when DML changes the table. section 14.3.2 section 14.3.2 section 14.3.2
create data metric function DQ.INVALID_CATEGORY(
T table(CAT varchar))
returns integer
as
$$
select count(*)
from T
where CAT not in ('Bread', 'Pastry')
$$;
Use this definition as the baseline pattern: declare a table-shaped parameter, return a scalar metric, and express the data-quality rule as SQL. section 14.3.2
Operational visibility is available through the DATA_METRIC_SCHEDULE table parameter and the INFORMATION_SCHEMA.DATA_METRIC_FUNCTION_REFERENCES table function. The latter lists a metric’s name, referenced table or view, column arguments, and schedule. Accessing system-defined metrics and viewing results requires the documented Snowflake roles and privileges; native data-quality functionality requires Enterprise edition or higher and is unavailable on trial accounts. section 14.3.3 section 14.3
