Automating Data Quality Validation with Data Metric Functions in Snowflake

Data Metric Functions (DMFs) in Snowflake provide a powerful way to automate data quality validation and monitoring, ensuring that your data meets predefined quality standards. In this article, we’ll explore what DMFs are, how to use pre-defined DMFs, and how to create custom DMFs.

Understanding Data Metric Functions (DMFs)

DMFs allow you to create custom quality metric rules as reusable functions and apply them to one or more columns in your tables. These functions can be executed ad-hoc for testing purposes or incorporated into your data pipelines. Snowflake provides pre-defined DMFs for common quality metrics such as freshness, null count, duplicate count, unique count, and row count.

-- Example: Using pre-defined DMFs
-- Calculate the null count for the email_address column in the customer_dim table
SELECT snowflake.core.null_count(SELECT email_address FROM customer_dim);

-- Calculate the duplicate count for the email_address column in the customer_dim table
SELECT snowflake.core.duplicate_count(SELECT email_address FROM customer_dim);

-- Calculate the unique count for the email_address column in the customer_dim table
SELECT snowflake.core.unique_count(SELECT email_address FROM customer_dim);

Attaching Pre-defined DMFs to Tables

You can attach pre-defined DMFs to specific columns in your tables using the ALTER TABLE statement

-- Example: Attaching a pre-defined DMF to a column
ALTER TABLE customer_dim ADD DATA METRIC FUNCTION NULL_COUNT ON (EMAIL_ADDRESS);

-- Set the schedule to evaluate the DMF every hour
ALTER TABLE customer_dim SET DATA_METRIC_SCHEDULE = '1 HOUR';

Now, let’s create a custom DMF to count the number of null values in multiple columns:

-- Example: Creating a custom DMF
CREATE DATA METRIC FUNCTION count_of_nulls(
  ARG_T TABLE (ARG_C1 STRING, ARG_C2 STRING, ARG_C3 STRING)
)
RETURNS NUMBER
AS
$$
SELECT COUNT_IF(ARG_C1 IS NULL OR ARG_C2 IS NULL OR ARG_C3 IS NULL)
FROM ARG_T;
$$;

Once you’ve defined your custom DMF, you can attach it to specific columns in your tables:

-- Example: Attaching a custom DMF to columns
ALTER TABLE CUSTOMER_DIM
  ADD DATA METRIC FUNCTION count_of_nulls ON (FIRST_NAME, LAST_NAME, EMAIL_ADDRESS);

By incorporating DMFs into your data pipelines, you can streamline data quality validation processes and ensure that your data-driven decisions are based on accurate and reliable information. With Snowflake’s DMFs, maintaining high-quality data has never been easier