Databricks SQL: Detect Data Quality Failures with SQL Metrics

This tutorial guides you through detecting data quality failures using SQL metrics within Databricks SQL. We’ll build three progressively complex queries to identify issues in sample data.

Script 1: Detecting Null Values

This script demonstrates the simplest data quality check: identifying rows with null values in a key column.

CREATE TEMP TABLE `sales_data` (
    order_id STRING,
    customer_id STRING,
    product_name STRING,
    quantity INT,
    price DECIMAL(10, 2)
);

INSERT INTO `sales_data` (order_id, customer_id, product_name, quantity, price)
VALUES
('1', 'A123', 'Laptop', 1, 1200.00),
('2', 'B456', 'Mouse', 2, 25.00),
('3', 'C789', 'Keyboard', 1, 75.00),
('4', NULL, 'Monitor', 1, 300.00),
('5', 'A123', 'Headphones', 1, 100.00);

SELECT
    COUNT()
FROM
    `sales_data`
WHERE
    customer_id IS NULL;

Explanation:

• We create a temporary table named `sales_data` with relevant columns.
• We insert sample data into the table, including a row with a missing `customer_id`.
• The final `SELECT` statement counts the number of rows where the `customer_id` column is `NULL`. This effectively identifies rows with a missing customer.

In this simple case, the query will return 1 because there’s a single row with a null `customer_id`. This demonstrates a fundamental data quality check – verifying the presence of expected values.

Output:

<No Output>

Script 2: Detecting Duplicate Order IDs

This script identifies duplicate `order_id` values, which can indicate data entry errors or inconsistencies.

CREATE TEMP TABLE `sales_data` (
    order_id STRING,
    customer_id STRING,
    product_name STRING,
    quantity INT,
    price DECIMAL(10, 2)
);

INSERT INTO `sales_data` (order_id, customer_id, product_name, quantity, price)
VALUES
('1', 'A123', 'Laptop', 1, 1200.00),
('2', 'B456', 'Mouse', 2, 25.00),
('1', 'C789', 'Keyboard', 1, 75.00), -- Duplicate order_id
('4', NULL, 'Monitor', 1, 300.00),
('5', 'A123', 'Headphones', 1, 100.00);

SELECT
    COUNT()
FROM
    `sales_data`
GROUP BY
    order_id
HAVING
    COUNT() > 1;

Explanation:

• We recreate the `sales_data` table.
• We insert data, deliberately including a duplicate `order_id` (‘1’).
• The `GROUP BY order_id` clause groups rows with the same `order_id`.
• The `HAVING COUNT() > 1` clause filters these groups, only returning groups where the count is greater than 1 (indicating duplicates).

The query will return 1 because there’s a duplicate `order_id` (‘1’). This illustrates detecting data duplication, a common data quality problem.

Output:

<No Output>

Script 3: Detecting Negative Quantities

This script identifies rows where the `quantity` is negative, which is likely an invalid data entry.

CREATE TEMP TABLE `sales_data` (
    order_id STRING,
    customer_id STRING,
    product_name STRING,
    quantity INT,
    price DECIMAL(10, 2)
);

INSERT INTO `sales_data` (order_id, customer_id, product_name, quantity, price)
VALUES
('1', 'A123', 'Laptop', 1, 1200.00),
('2', 'B456', 'Mouse', 2, 25.00),
('3', 'C789', 'Keyboard', 1, 75.00),
('4', NULL, 'Monitor', -1, 300.00), -- Negative quantity
('5', 'A123', 'Headphones', 1, 100.00);

SELECT
    COUNT()
FROM
    `sales_data`
WHERE
    quantity < 0;

Explanation:

• We recreate the `sales_data` table.
• We insert data, including a row with a negative `quantity`.
• The `WHERE quantity < 0` clause filters the data to only include rows where the quantity is less than 0.
• The final `SELECT` statement counts the number of rows that satisfy this condition.

The query will return 1 because there’s a single row with a negative quantity. This demonstrates a data quality check based on a range constraint – ensuring values fall within acceptable bounds.

Output:

<No Output>