Skip to main content

Data Quality Checks in SQL

Garbage in, garbage out. If your ELT pipeline blindly moves data from the Bronze layer to the Gold layer without validation, your business users will quickly lose trust in the dashboards.

In a modern Snowflake architecture, Data Quality (DQ) checks are typically implemented as SQL validation queries that run automatically via Tasks or dbt tests.

Topics Covered​

  • The Core Dimensions of Data Quality
  • SQL Validation Patterns (Completeness, Uniqueness, Range)
  • Referential Integrity Checks
  • Building a DQ Alerting Dashboard

The Core Dimensions of Data Quality​

Before writing SQL, you must know what you are testing. The core dimensions include:

  1. Completeness: Are there unexpected NULL values?
  2. Uniqueness: Are there duplicates where a Primary Key should exist?
  3. Validity/Range: Do the values fall within acceptable business boundaries?
  4. Referential Integrity: Do Foreign Keys actually point to existing records?

Hands-On: SQL Validation Patterns​

Let's assume we are building a pipeline for a retail bank in Mumbai. We need to validate the daily loan origination data before it hits the Silver layer.

1. Database Setup​

(Run this in your Snowflake worksheet)

USE ROLE SYSADMIN;

CREATE DATABASE IF NOT EXISTS BEEMS_BANKING_DB;
USE SCHEMA BEEMS_BANKING_DB.PUBLIC;

-- The table we want to test
CREATE OR REPLACE TABLE RAW_LOANS (
LOAN_ID STRING,
CUSTOMER_ID STRING,
LOAN_AMOUNT NUMBER,
INTEREST_RATE NUMBER(5,2),
BRANCH_CITY STRING
);

-- The Customer Master (for referential integrity)
CREATE OR REPLACE TABLE CUSTOMER_MASTER (CUSTOMER_ID STRING);

-- Insert Mock Data with intentional errors
INSERT INTO RAW_LOANS VALUES
('L-100', 'C-01', 500000, 8.5, 'Mumbai'),
('L-101', 'C-02', NULL, 9.0, 'Delhi'), -- Completeness Failure
('L-100', 'C-03', 200000, 7.5, 'Chennai'), -- Uniqueness Failure
('L-103', 'C-04', 1500000, 55.0, 'Kolkata'), -- Validity Failure (55% Interest)
('L-104', 'C-99', 300000, 8.0, 'Pune'); -- Referential Failure (C-99 doesn't exist)

INSERT INTO CUSTOMER_MASTER VALUES ('C-01'), ('C-02'), ('C-03'), ('C-04');

2. Completeness (NULL Checks)​

We expect LOAN_AMOUNT to never be NULL. The standard pattern is to write a query that returns rows only if the test fails.

-- Test: Loan Amount should not be null
SELECT
'COMPLETENESS_FAILURE' AS TEST_TYPE,
LOAN_ID,
'LOAN_AMOUNT IS NULL' AS FAILURE_REASON
FROM RAW_LOANS
WHERE LOAN_AMOUNT IS NULL;

3. Uniqueness (Duplicate Checks)​

We expect LOAN_ID to be unique. Snowflake does not enforce Primary Key constraints during data loading, so you must test for it.

-- Test: Loan ID must be unique
SELECT
'UNIQUENESS_FAILURE' AS TEST_TYPE,
LOAN_ID,
'COUNT IS ' || COUNT(*) AS FAILURE_REASON
FROM RAW_LOANS
GROUP BY LOAN_ID
HAVING COUNT(*) > 1;

4. Validity (Range Checks)​

Interest rates in India for this specific loan product should theoretically be between 6.0% and 15.0%.

-- Test: Interest Rate within bounds
SELECT
'VALIDITY_FAILURE' AS TEST_TYPE,
LOAN_ID,
'INTEREST RATE ' || INTEREST_RATE || ' IS OUT OF BOUNDS' AS FAILURE_REASON
FROM RAW_LOANS
WHERE INTEREST_RATE < 6.0 OR INTEREST_RATE > 15.0;

5. Referential Integrity (Orphan Checks)​

Every CUSTOMER_ID in the loan table must exist in the CUSTOMER_MASTER table. We use a LEFT JOIN or NOT IN to find orphans.

-- Test: Customer must exist
SELECT
'REFERENTIAL_FAILURE' AS TEST_TYPE,
r.LOAN_ID,
'CUSTOMER ' || r.CUSTOMER_ID || ' NOT FOUND IN MASTER' AS FAILURE_REASON
FROM RAW_LOANS r
LEFT JOIN CUSTOMER_MASTER c ON r.CUSTOMER_ID = c.CUSTOMER_ID
WHERE c.CUSTOMER_ID IS NULL;

Building a DQ Alerting Table​

In a production ELT pipeline, you don't run these manually. You combine them into a single UNION ALL statement and schedule it via a Snowflake Task. If the task finds any rows, it writes them to a Data Quality Audit table.

(To verify your user context before creating tasks, run SELECT CURRENT_USER();)

-- 1. Create the Audit Table
CREATE OR REPLACE TABLE DQ_AUDIT_LOG (
CHECK_TIMESTAMP TIMESTAMP DEFAULT CURRENT_TIMESTAMP(),
TEST_TYPE STRING,
LOAN_ID STRING,
FAILURE_REASON STRING
);

-- 2. Create the DQ Task
CREATE OR REPLACE TASK RUN_DQ_CHECKS
WAREHOUSE = COMPUTE_WH
SCHEDULE = '1 HOUR'
AS
INSERT INTO DQ_AUDIT_LOG (TEST_TYPE, LOAN_ID, FAILURE_REASON)
-- Paste the UNION of all tests here
SELECT 'COMPLETENESS_FAILURE', LOAN_ID, 'LOAN_AMOUNT IS NULL' FROM RAW_LOANS WHERE LOAN_AMOUNT IS NULL
UNION ALL
SELECT 'VALIDITY_FAILURE', LOAN_ID, 'INTEREST RATE OUT OF BOUNDS' FROM RAW_LOANS WHERE INTEREST_RATE < 6.0 OR INTEREST_RATE > 15.0;

-- 3. Alert on Failures
-- A downstream process or Snowflake Alert can monitor the DQ_AUDIT_LOG table.
-- If COUNT(*) > 0 for the current day, send an email to the Data Stewards!

Summary​

  • Because Snowflake does not strictly enforce Primary or Foreign keys during ingestion, you must write SQL tests.
  • The standard pattern is to write queries that return rows when a test fails.
  • You must test for Completeness (NULLs), Uniqueness (Duplicates), Validity (Ranges), and Referential Integrity (Orphans).
  • Combine tests via UNION ALL and run them automatically using Snowflake Tasks to populate an Audit Log.