Apply data quality checks to a subset of rows¶

When you associate a data metric function (DMF) with a table or view, the DMF evaluates all rows in the column or table by default. Some use cases require running a metric against only a specific subset of rows, such as active records, rows for a particular region, or rows within a date range.

The FILTER clause lets you attach a row predicate to a DMF association so that the metric is evaluated only on rows that satisfy the condition. The DMF definition itself doesn’t change; the filter is stored in the association and applied automatically at evaluation time.

Overview¶

When you create a DMF association with a FILTER clause, Snowflake stores the predicate as part of the association metadata. At evaluation time, Snowflake applies the predicate as a WHERE clause before the DMF body runs:

• For system DMFs, Snowflake adds the filter to the scan query directly.
• For custom DMFs, Snowflake rewrites the DMF body into a CTE that pre-filters the rows before the DMF expression sees them. The DMF body itself is unchanged.

For example, if your customer_data table contains both active and inactive records and you only want to track null counts for active customers, you can associate the NULL_COUNT DMF with a filter instead of pre-filtering the data or creating a view.

Supported DMFs¶

The FILTER clause is supported for most system DMFs in the SNOWFLAKE.CORE schema and for custom DMFs.

The following DMFs aren’t supported with FILTER:

• FRESHNESS, because it operates on the table as a whole and doesn’t take column arguments.
• DMFs with reference tables (multi-table associations), because the filter can only target a single table.

Custom DMF compatibility¶

Unlike WITHIN GROUP, which requires a specific DMF body structure to inject grouping, FILTER works by creating a pre-filtering CTE that wraps the table argument before the DMF body runs. This means FILTER is compatible with all custom DMF body structures, including subqueries, CTEs, JOINs, UNION, DISTINCT, and window functions.

If an incompatible DMF is specified (such as a multi-table DMF), the error is returned at association creation time.

Create an association with a filter¶

To add a DMF association with a row filter, use the ALTER TABLE command with the FILTER clause. The clause is added to the same ADD DATA METRIC FUNCTION syntax used for standard associations, so all other association properties (such as EXPECTATION and EXECUTE AS ROLE) remain available:

ALTER TABLE <table_name>
  ADD DATA METRIC FUNCTION <dmf_name>
    ON ( <argument_column> [ , ... ] )
    FILTER ( <predicate> )
    [ ADD EXPECTATION <expectation_name> ( <expression> ) [ , ... ] ]
    [ EXECUTE AS ROLE <role_name> ]
    [ ANOMALY_DETECTION = { TRUE | FALSE } ]
    [ SENSITIVITY = { 'LOW' | 'MEDIUM' | 'HIGH' } ]

The <predicate> is any valid SQL scalar expression that can appear in a WHERE clause. It can reference columns in the table the DMF is associated with.

Parameters specific to filtering¶

Examples¶

Track null counts in the email column only for customers with status = 'ACTIVE':

ALTER TABLE customer_data
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT
    ON (email)
    FILTER (status = 'ACTIVE');

Use multiple conditions in the filter:

ALTER TABLE orders
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT
    ON (order_id)
    FILTER (region = 'US' AND order_date >= '2024-01-01');

Combine FILTER with an expectation:

ALTER TABLE customer_data
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT
    ON (email)
    FILTER (status = 'ACTIVE')
    ADD EXPECTATION no_active_nulls (value = 0);

Modify or drop a filtered association¶

The FILTER clause is set at association creation time and can’t be changed after the association is created. You can use ALTER TABLE ... MODIFY to update other properties of a filtered association, such as suspending or resuming it, or changing an expectation.

To change the filter predicate on an existing association, drop the association and re-create it:

ALTER TABLE customer_data
  DROP DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT
    ON (email);

ALTER TABLE customer_data
  ADD DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT
    ON (email)
    FILTER (status = 'ACTIVE');

View results¶

Filtered DMF results appear in the same views as standard DMF results. There’s no separate column for filter results; the metric value reflects only the rows that satisfied the FILTER predicate.

For more information about querying results, see View results of a data metric function.

View filter configuration¶

DATA_METRIC_FUNCTION_REFERENCES table function¶

The DATA_METRIC_FUNCTION_REFERENCES table function exposes the filter predicate for each association through the PROPERTIES VARIANT column using the filter key:

For example:

SELECT ref_entity_name,
       metric_name,
       properties:filter::STRING AS filter_predicate
  FROM TABLE(INFORMATION_SCHEMA.DATA_METRIC_FUNCTION_REFERENCES(
    REF_ENTITY_NAME => 'CUSTOMER_DATA',
    REF_ENTITY_DOMAIN => 'TABLE'));

Example output:

+-----------------+------------+----------------------+
| REF_ENTITY_NAME | METRIC_NAME | FILTER_PREDICATE    |
+-----------------+------------+----------------------+
| CUSTOMER_DATA   | NULL_COUNT  | status = 'ACTIVE'   |
+-----------------+------------+----------------------+

The DATA_METRIC_FUNCTION_REFERENCES view view exposes the same PROPERTIES column.

Limitations¶

• Supported actions. The FILTER clause is only supported when adding an association (ADD DATA METRIC FUNCTION). Specifying FILTER in a MODIFY or DROP statement is not valid syntax. To drop an association that has a filter, use the standard drop syntax without the FILTER clause.
• Schema-level associations. FILTER isn’t supported on schema-level DMF associations (ALTER SCHEMA ... ADD DATA METRIC FUNCTION). To filter rows, create the association at the table or view level.
• FRESHNESS DMF. FILTER isn’t supported for the FRESHNESS DMF, because it operates on the table as a whole and doesn’t take column arguments.
• Multi-table DMFs. FILTER isn’t supported for DMFs with a reference table (two-table associations), because the filter expression can only target a single table.
• WITHIN GROUP. FILTER and WITHIN GROUP can’t be combined on the same association. Create separate associations if you need both row filtering and group-level results.
• Subqueries. The filter predicate can’t contain subqueries (including IN (SELECT ...) or EXISTS (...)).
• Immutable filter. The filter predicate can’t be changed after an association is created. Drop and re-create the association to change the filter.