Architecting Data Quality in Snowflake: A Hands-on Guide to DMFs, Cortex-Code, and AI-Driven Governance

This post explores the shift toward autonomous data governance using Snowflake’s native Data Quality framework. We will break down the technical architecture of Data Metric Functions (DMFs), the role of Expectations, and the AI-driven orchestration of Cortex-Code. If you prefer to skip the conceptual overview and dive straight into the implementation, feel free to jump to the Case Study: Establishing a Quality-First Retail Environment paragraph.

General

The evolution of the modern data stack has necessitated a transition from reactive data monitoring to proactive, autonomous governance. In an era where data-driven decision-making is paramount, the reliability of the underlying assets dictates the success of downstream analytics, machine learning, and executive reporting. Snowflake’s introduction of a native Data Quality framework — composed of Data Metric Functions (DMFs), Expectations, and the AI-orchestration of Cortex-Code — represents a significant paradigm shift for data engineers. This blog post provides a thorough technical analysis of these features, exploring their architecture, syntax, and operational deployment within the Snowflake Data Cloud.

The Foundation of Snowflake Data Quality Architecture

The architecture of data quality in Snowflake is built upon the principle of native execution. Unlike legacy solutions that require extracting data to third-party platforms, Snowflake’s framework operates directly on the storage layer, ensuring that security, privacy, and performance are maintained within the account’s boundaries. This framework is an Enterprise Edition feature, reflecting the sophisticated serverless compute resources required to maintain continuous, multi-dimensional monitoring across massive datasets.

Core Dimensions of Data Health

Data quality is not a monolithic concept but a multifaceted set of dimensions. Snowflake categorizes these through System Data Metric Functions, which allow engineers to measure the state of their data across several critical vectors:

Snowflake DMF

Data Metric Functions: The Fundamental Unit

The Data Metric Function (DMF) is the atomic unit of the Snowflake data quality framework. A DMF is a specialized function that evaluates a table or view and returns a scalar value — typically a number — representing a specific quality metric. These functions are categorized into two types: System DMFs and Custom DMFs.

System DMFs are pre-defined by Snowflake and reside in the SNOWFLAKE.CORE schema. These functions are highly optimized and require no additional code to implement, providing immediate visibility into common quality issues like null rates or duplicate counts. Custom DMFs, conversely, allow engineers to define bespoke logic using SQL, such as validating a specific regex pattern for internal identifiers or performing referential integrity checks between tables.

Cortex-Code: The AI Orchestrator for Data Engineering

Cortex-Code is an AI-driven intelligent agent integrated natively into Snowflake, designed to assist with complex data engineering and administration tasks through natural language. Unlike generic LLM assistants, Cortex-Code possesses deep context awareness of the specific Snowflake environment, including schemas, Role-Based Access Control (RBAC) policies, and performance characteristics.

Agentic Workflow and Contextual Awareness

Cortex-Code operates as an agentic partner that interprets intent, creates execution plans, and selects the appropriate internal tools to complete a task. It understands the relationships between data assets and follows Snowflake best practices when generating or optimizing code. For data engineers, this means that prompts can transition from simple SQL generation to complex orchestration, such as “Scaffold a staging layer for my ecommerce data and add appropriate quality checks”.

The agent is available through two primary interfaces:

1. Snowsight Interface: Integrated directly into the Snowflake web UI, allowing for ad-hoc development, data exploration, and visual “diff views” of suggested code changes.
2. Cortex-Code CLI: A command-line interface that bridges the gap between local development (e.g., VS Code or Cursor) and the Snowflake account, supporting dbt lifecycle management and local file access.

Hands-on Walkthrough

Case Study: Establishing a Quality-First Retail Environment

To demonstrate the lifecycle of data quality management, we utilize a simulated environment representing a glasses retail store. This environment is bootstrapped using Cortex-Code to create a multi-table schema with realistic data distributions and relationships.

Bootstrapping the “Your Glasses Store” Database

By providing Cortex-Code with a prompt such as “Create a new database (prefix with yourname) including tables simulate glasses store sales,” the agent generates a comprehensive schema. This automated scaffolding is essential for rapid prototyping and testing data quality logic before applying it to production assets.

Review the Cortex-Code suggestion and click ‘Run’ if you are certain it won’t impact any critical production tables. :)

Cortex-Code generated the following for me:

Your results may vary slightly, but should follow a similar structure.

Navigate to the new PRODUCTS table in the Catalog to preview the data.

Security Configuration and RBAC for Data Quality

Implementing the data quality framework requires specific privileges that go beyond standard DML capabilities. The Snowflake security model ensures that only authorized roles can execute and monitor quality metrics, preventing unauthorized users from accessing potentially sensitive statistical metadata.

The following SQL grants are necessary for the engineer’s role (e.g., SNOWFLAKE_INTELLIGENCE_ADMIN):

USE ROLE ACCOUNTADMIN;
-- Grants the ability to run any DMF on the account (grant it to the role you are using)
GRANT EXECUTE DATA METRIC FUNCTION ON ACCOUNT TO ROLE SNOWFLAKE_INTELLIGENCE_ADMIN;
-- Provides usage access to the built-in system DMFs in the SNOWFLAKE database
GRANT DATABASE ROLE SNOWFLAKE.DATA_METRIC_USER TO ROLE SNOWFLAKE_INTELLIGENCE_ADMIN;
-- Allows the role to view and monitor the output of quality checks in Snowsight
GRANT APPLICATION ROLE SNOWFLAKE.DATA_QUALITY_MONITORING_VIEWER TO ROLE SNOWFLAKE_INTELLIGENCE_ADMIN;

These permissions create a secure boundary where data quality operations are performed under the oversight of account administrators, adhering to the principle of least privilege.

Now, navigate back to the new PRODUCTS table in the Catalog and select the ‘Data Quality (Preview)’ tab.

Implementing Data Metric Functions (DMFs)

Once the environment is established and permissions are granted, the next phase is the implementation of monitoring metrics. Engineers can choose between manual configuration — offering precise control — and AI-assisted suggestions, which leverage Cortex to identify patterns and risks automatically.

Manual DMF Association and Uniqueness Validation

Manual setup is appropriate when business requirements are clearly defined. For example, ensuring that the PRODUCT_ID and PRODUCT_NAME in the PRODUCTS table are unique is a fundamental prerequisite for catalog integrity. In the Snowflake UI, the user selects the "Data Quality" tab and configures a uniqueness check with a duplicate count threshold of zero. It is vital to note that in Snowflake uniqueness checks, NULL values are treated as distinct, meaning multiple NULLs will trigger a duplicate count violation.

AI-Suggested Checks via Cortex Data Quality

For larger or more complex schemas, the “Generate with Cortex Data Quality” feature simplifies the setup process. Cortex Data Quality uses the AI_COMPLETE function to intelligently suggest checks based on metadata and usage patterns. Because this process runs securely within the Snowflake perimeter, enterprise metadata remains protected throughout the recommendation phase.

Upon reviewing the suggestions, the engineer can apply multiple checks simultaneously, covering dimensions such as:

• Null Count: Ensuring mandatory fields like BRAND or COST_PRICE are populated.
• Blank Count: Detecting empty strings or whitespace-only values in text columns like CATEGORY or LENS_TYPE.
• Accepted Values: Validating that categories belong to the predefined list (e.g., Sunglasses, Prescription, Blue Light).

First, let’s walk through the manual configuration process:

Next, let’s add a test to ensure all product IDs and names are unique:

Now, let’s add the auto-generated tests:

Cortex will process the request for a moment before providing a list of suggested tests:

After reviewing the suggestions, they all make sense, so I will select all the checkboxes and apply them.

Navigate to the Monitoring page. To make things more interesting, we’ll intentionally add data that triggers test failures by using the following prompt in the Cortex-Code pane: ‘Add a few rows to my tables that will fail the DMF tests.

To verify the configuration, use Cortex-Code to execute the DMFs on the PRODUCTS table.

Scheduling and Automation Mechanics

Data quality monitoring is not a one-time event but a continuous process. Snowflake provides a sophisticated scheduling engine for DMFs, allowing engineers to balance the need for low-latency validation with the cost implications of serverless compute consumption.

Scheduling Options and Syntax

There are three primary ways to schedule DMF execution on a supported object:

1. Interval-Based Scheduling: Runs the metrics at a set frequency, such as every 5 minutes or every hour. This is ideal for batch pipelines with a known cadence. ALTER TABLE PRODUCTS SET DATA_METRIC_SCHEDULE = '5 MINUTE'
2. Cron-Based Scheduling: Utilizes standard cron syntax to run metrics at specific times, days of the month, or time zones. This is useful for complex reporting windows, such as “run at 8:00 AM UTC every weekday”.
3. Trigger-Based Scheduling (TRIGGER_ON_CHANGES): A dynamic option that instructs Snowflake to run the DMFs only when DML changes (inserts, updates, or deletes) occur on the table.

The TRIGGER_ON_CHANGES option is particularly efficient as it avoids redundant compute cycles when the data remains static. However, it is restricted to table-like objects and cannot be applied to standard views. Furthermore, internal maintenance tasks like reclustering do not activate this trigger.

Engineers can verify the current schedule for any object using the SHOW PARAMETERS command, which provides transparency into the automation settings:

SHOW PARAMETERS LIKE 'DATA_METRIC_SCHEDULE' IN TABLE EYLON_GLASSES_STORE.PUBLIC.PRODUCTS;

Don’t forget to disable when demo is done otherwise you will encounter compute costs

Syntax for DMF Attachment

The attachment of a DMF to a table or view is performed by asking cortex code: Attach all DMFs to the write tables in EYLON_GLASSES_STORE (change to your name) or by running a SQL query using the ALTER TABLE or ALTER VIEW command with the ADD DATA METRIC FUNCTION clause.

ALTER TABLE EYLON_GLASSES_STORE.PUBLIC.PRODUCTS SET 
  DATA_METRIC_FUNCTION SNOWFLAKE.CORE.DUPLICATE_COUNT ON (PRODUCT_NAME),
  DATA_METRIC_FUNCTION SNOWFLAKE.CORE.DUPLICATE_COUNT ON (BRAND),
  DATA_METRIC_FUNCTION SNOWFLAKE.CORE.BLANK_COUNT ON (PRODUCT_NAME),
  DATA_METRIC_FUNCTION SNOWFLAKE.CORE.BLANK_COUNT ON (BRAND);

This syntax establishes a persistent association between the object and the quality metric. Snowflake enforces a limit of 10,000 such associations per account to ensure manageable governance at scale.

You can verify the status of the DMFs using Cortex-Code or by running the following query:

SELECT * FROM TABLE(INFORMATION_SCHEMA.DATA_METRIC_FUNCTION_REFERENCES(
  REF_ENTITY_NAME => 'EYLON_GLASSES_STORE.PUBLIC.PRODUCTS', 
  REF_ENTITY_DOMAIN => 'TABLE'
));

Since the tests have just started, we’ll need to wait a few moments for the results to populate on the Monitoring page. In the meantime, you can check the status by asking Cortex-Code for the results or by running the following query:

SELECT MEASUREMENT_TIME, METRIC_NAME, ARGUMENT_NAMES, VALUE
FROM SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS
WHERE TABLE_NAME = 'PRODUCTS'
AND TABLE_SCHEMA = 'PUBLIC'
AND TABLE_DATABASE = 'EYLON_GLASSES_STORE'
ORDER BY MEASUREMENT_TIME DESC;

Next, navigate to the Monitoring page and select one of the tests — for example, select ‘Accuracy’ and click on the ‘2 blank count’ for the BRAND column.

Click ‘View failed records’ to open a new worksheet. Running the generated SQL will display the specific rows that failed the quality checks.

Expectations: Turning Measurements into Alerts

A DMF provides a raw number, but an Expectation provides the context for that number. An expectation is a Boolean expression paired with a DMF to determine if the measured value constitutes a quality failure. When the expression evaluates to TRUE, the data is considered healthy; if FALSE, a violation is logged.

The VALUE Keyword and Logic

Within an expectation, the keyword VALUE is used as a placeholder for the result of the DMF. This allows for the implementation of precise business thresholds:

• Zero-Tolerance: EXPECTATION no_dupes (VALUE = 0) ensures that any duplicate record triggers an immediate failure.
• Threshold-Based: EXPECTATION low_null_rate (VALUE < 10) allows for a specific count of missing values in non-critical columns before flagging an issue.
• Range Validation: EXPECTATION valid_volume (1000 < VALUE AND VALUE < 2000) can be used to monitor if a daily ingestion falls within expected bounds.

Modifying Associations with Expectations

Expectations can be added during the initial creation of an association or retroactively applied to an existing DMF using the MODIFY clause.

ALTER TABLE EYLON_GLASSES_STORE.PUBLIC.PRODUCTS
MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.NULL_COUNT ON (STOCK_QUANTITY)
ADD EXPECTATION no_missing_stock (VALUE = 0);

This multi-layered approach — separating the measurement (DMF) from the rule (Expectation) — enables graduated alerting strategies. For instance, an engineer might set one expectation for a “Warning” (e.g., VALUE > 50) and another for a "Critical" failure (e.g., VALUE > 500), each triggering different downstream notifications.

Alerting and Notification Architecture

Data quality monitoring is only as effective as the response it triggers. Snowflake’s alerting system allows for automated email or webhook notifications the moment an expectation is violated.

Notification Integrations

Alerting begins with the creation of a Notification Integration, which defines the communication channel. For email notifications, the integration must include a whitelist of verified recipients.

CREATE OR REPLACE NOTIFICATION INTEGRATION dq_notification_int
  TYPE = EMAIL
  ENABLED = TRUE
  ALLOWED_RECIPIENTS = ('data_engineers@glasses_store.com');

Enabling Database-Level Monitoring

Rather than configuring alerts table-by-table, engineers can enable notifications at the database level using the DATA_QUALITY_MONITORING_SETTINGS property. This property accepts a YAML-formatted specification.

ALTER DATABASE EYLON_GLASSES_STORE SET 
  DATA_QUALITY_MONITORING_SETTINGS = $$
    notification:
      enabled: TRUE
      integrations:
        - DQ_NOTIFICATION_INT
      metadata_included: TRUE
  $$;

When metadata_included is set to TRUE, the notification email contains the table name, column, and DMF involved, enabling the engineering team to immediately begin remediation.

Granular Suppression

In scenarios where a specific test is known to be noisy or is in development, notifications can be suppressed at the individual DMF association level.

ALTER TABLE PRODUCTS MODIFY DATA METRIC FUNCTION SNOWFLAKE.CORE.DUPLICATE_COUNT ON (NON_CRITICAL_COL) 
  SET DATA_QUALITY_NOTIFICATION = FALSE;

This flexibility ensures that teams do not suffer from “alert fatigue” while still maintaining high visibility into critical production failures.

Change the schedule to

Use Cortex-Code to insert ‘bad’ data into the PRODUCTS table to intentionally trigger DMF failures. By this time, you should begin to see the results appearing in your dashboards.

You should then receive an email notification detailing the failures, similar to this:

Advanced Patterns: Anomaly Detection and Statistical Modeling

Beyond static rules, Snowflake’s framework supports Anomaly Detection, which uses machine learning to identify quality issues based on historical patterns.

Volume and Freshness Anomalies

Currently, Snowflake can automatically detect anomalies in the volume (record count) and freshness (update frequency) of data. This approach is particularly effective for identifying “silent” pipeline failures where the schema remains valid, but the data flow has stopped or significantly deviated from the norm. The sensitivity of these algorithms can be adjusted to match the volatility of the underlying dataset.

Custom Statistical DMFs

For data engineers requiring more control, Custom DMFs can implement statistical validation. For example, a DMF could calculate the standard deviation of a price column and return a value indicating how many records fall more than three sigmas from the mean. This utilizes the same SQL expression logic used in standard UDFs but within the specialized DMF lifecycle.

Governance, Costs, and Best Practices

As with any serverless feature, data quality monitoring incurs costs that must be governed effectively. These costs are billed under the “Data Quality Monitoring” category and use serverless compute resources, which are independent of user-managed warehouses.

Usage Monitoring and Optimization

Engineers should regularly query the DATA_QUALITY_MONITORING_USAGE_HISTORY view to track credit consumption. To optimize costs, the following strategies are recommended:

• Incremental Checks: Use TRIGGER_ON_CHANGES to ensure checks only run when data actually changes when data is rarely changes.
• Graduated Scheduling: Set higher-frequency schedules (e.g., 5 minutes) for critical operational tables and lower-frequency schedules (e.g., 12 hours) for historical archives.
• Association Cleanup: Regularly review and drop DMF associations that are no longer providing actionable insights to stay within the 10,000 association limit.

Lifecycle Management in dbt and CI/CD

Integrating data quality into the development lifecycle ensures that “bad data” never reaches downstream consumers. Snowflake DMFs can be incorporated into CI/CD pipelines as automated checkpoints. For example, a post-load script can call SYSTEM$EVALUATE_DATA_QUALITY_EXPECTATIONS on a staging table. If any violations are detected, the deployment process can be halted, preventing a faulty data load from corrupting the production warehouse.

While dbt tests have long been the standard for shifting data quality “left” by validating logic during the transformation pipeline, Snowflake Data Metric Functions (DMFs) introduce a shift toward continuous, serverless monitoring that lives directly within the storage layer. dbt testing is inherently batch-oriented and tied to the deployment lifecycle — perfect for ensuring that a model is correct at the moment it is built — but it remains “blind” to data drift that occurs between scheduled runs. In contrast, Snowflake DMFs operate as always-on metadata services that track metrics like freshness, uniqueness, and custom business logic on a scheduled heartbeat, independent of whether a dbt job has been triggered. For a modern data stack, the two are often complementary rather than mutually exclusive: use dbt to enforce strict “guardrail” tests that block bad data from reaching production tables, and leverage Snowflake DMFs for long-term observability and alerting on the steady-state health of your data over time.

Conclusion: The Path Toward Autonomous Governance

The Snowflake Data Quality framework, powered by DMFs, Expectations, and Cortex-Code, provides a comprehensive, native solution for modern data engineering teams. By moving validation directly into the data layer, Snowflake eliminates the latency and security risks associated with third-party tools while offering the scalability of serverless compute.

For data engineers, the journey begins with identifying critical data dimensions and leveraging Cortex-Code to scaffold and suggest initial checks. As the environment matures, the integration of custom business logic, automated alerting, and anomaly detection transforms data quality from a manual chore into a proactive, self-healing component of the data pipeline. In the “Glasses Store” case study, we see that what once took days of script writing can now be achieved in minutes through natural language and native automation, ensuring that every insight is built upon a foundation of trust and reliability.

Read more: https://docs.snowflake.com/en/user-guide/data-quality-ui-setup#label-data-quality-ui-setup-cortex

To stay updated on more Snowflake-related posts, follow me at my Medium profile: Eylon’s Snowflake Articles.

I’m Eylon Steiner, Engineering Manager for Infostrux Solutions and a Snowflake Data Superhero. You can follow me on LinkedIn.

Subscribe to Infostrux Medium Blog at https://blog.infostrux.com for the most interesting Data Engineering and Snowflake news. Follow Infostrux’s open-source efforts through GitHub.