Move ETL data quality rules into Snowflake data metric functions

What you will have at the end

A single SQL file, etl_rules_to_dmf.sql, that turns three typical ETL checks into custom data metric functions (read 2026-10-16), attaches them to an orders table, and proves that they catch planted errors and pass clean data. The old ETL rule was a WHERE clause that rejected rows. The new rule is a function that returns how many rows would have been rejected, so 0 means pass.

Snowflake describes a data metric function as something that measures an attribute of your data and returns a value; combined with an expectation, it becomes a data quality check. Data quality monitoring requires Enterprise Edition (Snowflake docs, read 2026-10-16).

Step 1. Rewrite each ETL rule as a function that counts violations

Take the condition your ETL job used to reject a row and put it in the body of a DMF. The function takes a table argument and returns a number. These are the three rules we migrated:

SQL: three custom DMFs

-- ETL rule 1: reject rows where O_TOTALPRICE is null or <= 0
CREATE OR REPLACE DATA METRIC FUNCTION RULE_PRICE_POSITIVE(arg_t TABLE(arg_c NUMBER))
RETURNS NUMBER AS
$$ SELECT COUNT(*) FROM arg_t WHERE arg_c IS NULL OR arg_c <= 0 $$;

-- ETL rule 2: reject rows where O_ORDERSTATUS is not F, O or P
CREATE OR REPLACE DATA METRIC FUNCTION RULE_STATUS_VALID(arg_t TABLE(arg_c VARCHAR))
RETURNS NUMBER AS
$$ SELECT COUNT(*) FROM arg_t WHERE arg_c IS NULL OR arg_c NOT IN ('F', 'O', 'P') $$;

-- ETL rule 3: reject orders whose customer is not in the customer table
CREATE OR REPLACE DATA METRIC FUNCTION RULE_CUSTOMER_EXISTS(
  arg_t1 TABLE(arg_c1 NUMBER), arg_t2 TABLE(arg_c2 NUMBER))
RETURNS NUMBER AS
--    NOT EXISTS, not NOT IN: one NULL key in the customer table would make NOT IN match no row.
$$ SELECT COUNT(*) FROM arg_t1
   WHERE arg_c1 IS NULL OR NOT EXISTS (SELECT 1 FROM arg_t2 WHERE arg_t2.arg_c2 = arg_t1.arg_c1) $$;

Rule 3 uses two table arguments. Snowflake documents this pattern for referential checks, and requires the second table's fully qualified name when you attach the function (Snowflake docs, read 2026-10-16).

Step 2. Attach the rules to the table, with an expectation and a schedule

Attaching is what keeps the rule with the data. The EXPECTATION says what the returned value must be; the schedule says how often Snowflake evaluates it. Snowflake documents the default schedule as one hour, shared by every DMF on the table, and says schedule changes to existing DMFs take effect after about a 10-minute lag (Snowflake docs, read 2026-10-16).

SQL: attach, then list what is attached

ALTER TABLE ORDERS SET DATA_METRIC_SCHEDULE = '60 MINUTE';
ALTER TABLE ORDERS ADD DATA METRIC FUNCTION RULE_PRICE_POSITIVE ON (O_TOTALPRICE)
  EXPECTATION price_positive (VALUE = 0);
ALTER TABLE ORDERS ADD DATA METRIC FUNCTION RULE_STATUS_VALID ON (O_ORDERSTATUS)
  EXPECTATION status_valid (VALUE = 0);
ALTER TABLE ORDERS ADD DATA METRIC FUNCTION RULE_CUSTOMER_EXISTS
  ON (O_CUSTKEY, TABLE (CONTENT_LOOP_SANDBOX.ETL_RULES_TO_SNOWFLAKE_DATA_METRIC_FUNCTIONS.CUSTOMER(C_CUSTKEY)))
  EXPECTATION customer_exists (VALUE = 0);

SELECT METRIC_NAME, REF_ENTITY_NAME, SCHEDULE, SCHEDULE_STATUS
FROM TABLE(INFORMATION_SCHEMA.DATA_METRIC_FUNCTION_REFERENCES(
  REF_ENTITY_NAME => 'CONTENT_LOOP_SANDBOX.ETL_RULES_TO_SNOWFLAKE_DATA_METRIC_FUNCTIONS.ORDERS',
  REF_ENTITY_DOMAIN => 'TABLE'))
ORDER BY METRIC_NAME;

Our run listed all three rules on the table, with the schedule started:

Output from our run

METRIC_NAME          | REF_ENTITY_NAME | SCHEDULE        | SCHEDULE_STATUS
---------------------+-----------------+-----------------+----------------
RULE_CUSTOMER_EXISTS | ORDERS          | 0 */1 * * * UTC | STARTED
RULE_PRICE_POSITIVE  | ORDERS          | 0 */1 * * * UTC | STARTED
RULE_STATUS_VALID    | ORDERS          | 0 */1 * * * UTC | STARTED        

Snowflake limits what an expectation can say: the left side must be the keyword VALUE, and arithmetic on VALUE and subqueries are not allowed (Snowflake docs, read 2026-10-16). Counting violations and expecting 0 fits inside that limit for every rule here.

Tested on

Keep the rules as code across dev, test and production

The file is plain SQL, so it can live in a GitHub repository and go through pull requests like any other code. The file as published builds a throwaway test schema and drops it at the end, so do not run it unchanged against a real schema. For dev, test or production, delete the CREATE OR REPLACE SCHEMA and DROP SCHEMA lines and every step the file marks TEST ONLY (numbered 0, 4, 5 and 6 in the file: they create the test tables, plant and delete the bad rows, and drop the schema), then change the database and schema names in the USE lines and in the two fully qualified references (the attach statement for rule 3 and the references query).

Because the rules are created and attached by name, the same file then gives each environment the same rules.

Snowflake can also run a SQL file from a Git repository clone and pass values into it with EXECUTE IMMEDIATE FROM ... USING (Snowflake docs, read 2026-10-16). We did not test that route in this guide, and the docs note that a template in a Snowflake Git repository cannot read other files from that repository, so keep each environment's rules in one file.

What it costs to run

Snowflake says DMFs use serverless compute that incurs costs, charged only when a scheduled DMF runs on an object. Creating a DMF is not billed, and neither is unscheduled use such as calling a DMF in a SELECT (Snowflake docs, read 2026-10-16). Our runs used ad hoc calls only. We did not measure the cost of the schedule, so check the DATA_QUALITY_MONITORING_USAGE_HISTORY view that the same page names, on your own account, before you attach rules to large tables.

When this is not enough

• A DMF measures; it does not act. If your ETL job also quarantined or repaired the rejected rows, that behavior has to be rebuilt separately. The function only returns a count.
• Results are not an alert. Our runs read the counts directly. Routing a failed expectation to the person who owns the rule is a separate piece of work, and we did not test it here.
• Rules that need row-level context. Our rules count rows that break a condition on one column. A rule that compares several columns, or needs history, may need a different design.
• A DMF cannot be dropped while it is attached to a table. Detach it first; DATA_METRIC_FUNCTION_REFERENCES shows where it is used (Snowflake docs, read 2026-10-16).