Creating Data Quality Checks in Snowsight using Snowflake Cortex

Use the Snowsight user interface to create native data quality checks in Snowflake either manually or by allowing Cortex to generate them

Photo by dan carlson on Unsplash

In my previous post Monitoring Data Quality Natively in Snowflake, I demonstrated how to configure Data Metric Functions using SQL. Since then, Snowflake has introduced a Data Quality user interface in Snowsight that makes it possible to create and manage quality checks without writing SQL. In this post, we will explore how to configure data quality checks in the user interface and how Snowflake Cortex can help generate data quality checks automatically.

Note that the Snowflake Data Quality Monitoring feature requires the Enterprise Edition or higher.

Before we get started, let’s set up a quick example. We will use a custom role called DQ_ENGINEER and grant it the required privileges (refer to Access control for data quality in the Snowflake documentation for a more detailed explanation):

-- create custom role and grant to SYSADMIN to preserve role hierarchy
use role USERADMIN;
create role DQ_ENGINEER;
grant role DQ_ENGINEER to role SYSADMIN;

-- grant required privileges to the custom role
use role ACCOUNTADMIN;
grant database role SNOWFLAKE.DATA_METRIC_USER to role DQ_ENGINEER;
grant EXECUTE DATA METRIC FUNCTION on account to role DQ_ENGINEER;
grant application role SNOWFLAKE.DATA_QUALITY_MONITORING_VIEWER 
  to role DQ_ENGINEER;

Next, we will create a database and a schema for the examples.

-- create a database and virtual warehouse and grant to the custom role
use role SYSADMIN;
create warehouse DQ_WH;
grant usage on warehouse DQ_WH to role DQ_ENGINEER;
create database RETAIL_DB;
grant ownership on database RETAIL_DB to role DQ_ENGINEER;

-- use the custom role to create a schema
use role DQ_ENGINEER;
use warehouse DQ_WH;
use database RETAIL_DB;
create schema RETAIL_DATA;

Finally, we will create a table that intentionally contains data quality issues so that we can see both successful and failed quality checks.

As we already know, although Snowflake supports defining primary and foreign keys on regular tables, it does not enforce them so it is up to us to ensure the constraints are met and data quality is achieved.

Our sample data is based on a retail example, with a customer table. The sample data is chosen so it makes it possible to perform quality checks such as:

• CUSTOMER_ID must be unique
• EMAIL must be non-null (the sample data deliberately contains a customer with a missing email address)
• Customer status must be one of a list of values (‘active’, ‘inactive’)

-- create the customers table and populate with sample data
CREATE OR REPLACE TABLE CUSTOMERS (
    customer_id INT PRIMARY KEY,
    email VARCHAR(255),
    signup_date DATE,
    status VARCHAR(20)
);

INSERT INTO CUSTOMERS VALUES
    (1, 'alice@example.com', '2023-01-15', 'active'),
    (2, 'bob@example.com', '2018-02-20', 'active'),
    (3, 'carol@example.com', '2020-03-10', 'inactive'),
    (4, 'dave@example.com', '2022-04-05', 'active'),
    (5, 'david@example.com', '2024-04-05', 'active'),
    (6, NULL, '2025-05-18', 'active');

Setting up data quality checks manually

We will begin by creating the quality checks manually so that we understand how each one is configured.

In the Snowsight user interface, navigate to Catalog, then Database Explorer and find the CUSTOMERS table. When you click on this table, you will see a pane with several tabs, including Table details, Columns, Data preview, Copy history, Data quality, and Lineage.

Click the Data quality tab. You will be given the option to generate recommendations with Cortex Data Quality or Set up manually:

Data quality tab

Let’s start by setting up the data quality checks manually by clicking the Setup manually button.

The next screen that opens is the setup quality check screen:

Setup quality check screen

We will start with a Uniqueness check on the CUSTOMER_ID column because customer identifiers are typically primary keys and duplicate values would cause downstream joins and aggregations to produce incorrect results.

Click the Uniqueness button, which creates a quality check based on Snowflake’s built-in uniqueness data metric function.

The next window lets us set up additional details related to the uniqueness check:

Uniqueness check window

Be sure to use the DQ_ENGINEER custom role we created earlier which you can choose at bottom left because this role has the required privileges. Choose the CUSTOMER_ID column from the drop-down list and leave the remaining values as default. Click Save.

You will see the uniqueness check you just created in the list of data quality checks for this table:

Uniqueness test details

Additionally, you will notice that the data quality schedule is set to 1 hour, which is the default, meaning that Snowflake will check for data quality every hour:

Data quality schedule

For this demonstration, let’s reduce the interval to 5 minutes so that we can see the results sooner. You can do that by clicking the Settings button and adjusting the schedule to your preferred interval. Alternatively, checks can be scheduled to run at specific times or triggered whenever the table changes.

Get Maja Ferle’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

After 5 minutes (or your chosen interval), we should see in the Monitoring tab that the data quality check was executed and was successful:

Data quality results with a passing check

Now let’s add another data quality check that we know should fail, that is on the column EMAIL which should not have null values, but we inserted a null value for testing. The null check on EMAIL is a common completeness validation for customer master data.

Click the Add quality drop-down button, and add another manual data quality check, this one as nulls in the form “I expect the count of nulls in EMAIL to be equal to 0.”

Notice that the check doesn’t just calculate the number of null values. By defining the expectation that the null count must equal zero, Snowflake creates a data quality check that evaluates the result of the data metric function and compares it to the expectation to determine whether the check passed or failed.

After another 5 minutes, we should see the following result, which shows that one quality check passed and one failed:

Data quality results with a failed check

Creating quality checks manually gives you control over which metrics are evaluated and the expectations applied. However, defining dozens of checks across many tables can become repetitive. This is where Snowflake Cortex can help by analyzing your tables and suggesting appropriate quality checks automatically.

Setting up data quality checks with Cortex

Let’s see what Cortex can do.

Note that setting up data quality checks using Cortex Data Quality is a preview feature at the time of writing.

First, check if you have access to the SNOWFLAKE.CORTEX_USER database role so you can use Cortex. By default, this role is granted to the PUBLIC role so everyone in the Snowflake account can use it. However, if your Snowflake administrator had revoked it from the PUBLIC role, you will have to grant it to the DQ_ENGINEER role:

use role accountadmin;
grant database role SNOWFLAKE.CORTEX_USER to role DQ_ENGINEER;

Click the Add quality check button again, but this time, choose the option Generate with Cortex Data Quality.

This step may take a short while to complete, because Cortex analyzes the table metadata, column names, data types and sample data to recommend quality checks that are likely to be useful. As always with AI, keep in mind that the recommendations are intended as a starting point and should always be reviewed before being accepted.

For our sample table, Cortex recommends several additional checks, including:

• Status column should only contain valid business states (‘active’, ‘inactive’)
• Row count to detect data loss or unexpected growth
• Freshness of signup date which could indicate stale data
• Null count of signup date
• Duplicate count of email

Depending on your data, Cortex could suggest different options. Let’s choose the first one that checks whether the STATUS column contains valid values. Click the checkbox next to the suggested quality check to select it and then click Apply:

Accepted Cortex-suggested quality check

Notice that we didn’t have to choose a metric type or define an expectation ourselves because Cortex generated them for us. Similarly as with the manually created quality checks, after applying Cortex-generated quality checks, we will see the results of the checks in the Monitoring tab in the user interface:

Outcome of the Accepted values check

In this example, we see that the Accepted values check passed.

Monitoring data quality results

Although we created data quality checks in the Snowsight interface, the underlying Data Metric Function results are also stored in the DATA_QUALITY_MONITORING_RESULTS view, allowing them to be queried using SQL, for example:

select measurement_time, table_name, metric_name, 
  metric_schema, metric_database, argument_names, value
from SNOWFLAKE.LOCAL.DATA_QUALITY_MONITORING_RESULTS 
order by measurement_time desc;

The results should be the same as you have seen in the Snowsight interface:

Output when querying the DATA_QUALITY_MONITORING_RESULTS view

This makes it straightforward to integrate data quality monitoring with existing operational dashboards, notification workflows or reporting solutions.

Summary

The Snowsight interface is an excellent way to explore the available quality checks, validate your assumptions and prototype monitoring rules. For production deployments, however, infrastructure-as-code remains the preferred approach because it provides repeatability, version control and automated deployment across environments.

Even though we don’t write SQL in the Snowsight interface, it still generates the underlying Data Metric Functions and expectations for us. By clicking the Edit SQL button when defining a quality check, we can inspect the code Snowflake created, copy the generated statements and incorporate them into deployment scripts.

I’m Maja Ferle, Snowflake Data Superhero and a senior consultant at In516ht. You can get in touch with me on LinkedIn or read my latest book, Snowflake Data Engineering.