TECH **Data Quality Checks with SQL: Null Rates, Duplication, Freshness**

Data Quality Checks with SQL: Null Rates, Duplication, Freshness

A Hyper-Practical, Zero-Fluff Guide for Data Analysts & Engineers

1. What This Is & Why It Matters

You’re handed a dataset—maybe it’s customer orders, sensor readings, or ad impressions—and told to "analyze it." But before you even think about running a regression or building a dashboard, you must answer three questions:

1. Is this data complete? (Null rates)
2. Is it unique? (Duplication)
3. Is it fresh? (Freshness)

If you skip these checks, you’re flying blind. A single NULL in a critical column can break a model. Duplicate records can inflate revenue numbers by 20%. Stale data can make you optimize for last month’s trends instead of today’s.

Real-world scenario:
You’re a data analyst at an e-commerce company. Your boss asks for a report on "average order value (AOV) by region." You pull the data, calculate AOV, and present it. Later, you find out: - 15% of order_amount values were NULL (skewing the average).
- 8% of orders were duplicated (inflating revenue).
- The data was 3 days old (missing a major flash sale).

Now your report is wrong, your boss is embarrassed, and the marketing team wasted budget on a misguided campaign.

This guide teaches you how to catch these issues before they become disasters.

2. Core Concepts & Components

? Null Rate

• Definition: The percentage of records where a given column has a NULL value.
• Production insight: High null rates in key columns (e.g., customer_id, order_amount) can break joins, aggregations, and ML models. Always check before analysis.

? Duplication

• Definition: Records that are identical (or near-identical) across key columns.
• Production insight: Duplicates distort counts, sums, and averages. They often sneak in from:
• ETL pipeline bugs (e.g., double-loading a file).
• User error (e.g., submitting a form twice).
• System glitches (e.g., retries on failed API calls).

? Freshness

• Definition: How recently the data was updated (e.g., "last 24 hours").
• Production insight: Stale data = wasted decisions. If your "real-time" dashboard is 3 days old, you’re optimizing for the past.

? Data Quality Rule

• Definition: A SQL query or assertion that validates a specific expectation (e.g., "no NULL in customer_id").
• Production insight: Automate these checks in your ETL pipeline. If a rule fails, alert the team before the data hits production.

? Materialized View (for Freshness)

• Definition: A pre-computed table that refreshes on a schedule (e.g., hourly).
• Production insight: Use these to track freshness. If the view hasn’t updated, your pipeline is broken.

? Window Functions (for Duplication)

• Definition: SQL functions like ROW_NUMBER() that let you analyze data within partitions.
• Production insight: Essential for identifying duplicates without collapsing rows prematurely.

? Metadata Tables

• Definition: System tables (e.g., information_schema.tables) that track when data was last updated.
• Production insight: Query these to check freshness without scanning the entire dataset.

3. Step-by-Step Hands-On: Running Data Quality Checks

Prerequisites

• A SQL database (PostgreSQL, BigQuery, Snowflake, etc.).
• A table to analyze (e.g., orders, customers, sensor_readings).
• Basic SQL knowledge (you can write a SELECT statement).

Task: Audit a Table for Nulls, Duplicates, and Freshness

We’ll use a sample orders table with these columns: - order_id (unique identifier) - customer_id (foreign key to customers) - order_amount (numeric) - order_date (timestamp) - status (e.g., "shipped", "cancelled")

Step 1: Check Null Rates

Goal: Identify columns with high null rates.

-- Null rate per column
SELECT
  COUNT(*) AS total_rows,
  SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END) AS null_order_id,
  SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS null_customer_id,
  SUM(CASE WHEN order_amount IS NULL THEN 1 ELSE 0 END) AS null_order_amount,
  SUM(CASE WHEN order_date IS NULL THEN 1 ELSE 0 END) AS null_order_date,
  SUM(CASE WHEN status IS NULL THEN 1 ELSE 0 END) AS null_status,
  ROUND(100.0 * SUM(CASE WHEN order_id IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS pct_null_order_id,
  ROUND(100.0 * SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS pct_null_customer_id,
  ROUND(100.0 * SUM(CASE WHEN order_amount IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS pct_null_order_amount,
  ROUND(100.0 * SUM(CASE WHEN order_date IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS pct_null_order_date,
  ROUND(100.0 * SUM(CASE WHEN status IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS pct_null_status
FROM orders;

Expected Output:
| total_rows | null_order_id | null_customer_id | null_order_amount | ... | pct_null_customer_id | |------------|---------------|------------------|-------------------|-----|----------------------| | 10000 | 0 | 150 | 200 | ... | 1.50 |

What to look for:
- order_id and customer_id should never be NULL (they’re primary/foreign keys).
- order_amount nulls might be acceptable (e.g., refunds), but flag if >5%.
- order_date should almost never be NULL.

Step 2: Check for Duplicates

Goal: Find exact or partial duplicates.

Option A: Exact Duplicates (all columns match)

-- Count exact duplicates
SELECT
  COUNT(*) AS total_duplicates
FROM (
  SELECT
    *,
    COUNT(*) OVER (PARTITION BY order_id, customer_id, order_amount, order_date, status) AS dup_count
  FROM orders
) t
WHERE dup_count > 1;

Option B: Partial Duplicates (key columns match)

-- Find duplicates on order_id (should be unique)
SELECT
  order_id,
  COUNT(*) AS dup_count
FROM orders
GROUP BY order_id
HAVING COUNT(*) > 1;

Expected Output:
| order_id | dup_count | |----------|-----------| | 12345 | 2 |

What to look for:
- order_id should never have duplicates (it’s a primary key).
- If customer_id + order_date has duplicates, investigate (e.g., same customer placing multiple orders in a day).

Step 3: Check Freshness

Goal: Verify the data is up-to-date.

Option A: Max Timestamp (if order_date exists)

-- Last order date
SELECT MAX(order_date) AS last_order_date
FROM orders;

Option B: Metadata Tables (PostgreSQL example)

-- Last update time (PostgreSQL)
SELECT
  table_name,
  last_autovacuum,
  last_autoanalyze
FROM information_schema.tables
WHERE table_name = 'orders';

Option C: Row Count Over Time (if you track history)

-- Daily row count (if you have a date column)
SELECT
  DATE_TRUNC('day', order_date) AS day,
  COUNT(*) AS row_count
FROM orders
GROUP BY 1
ORDER BY 1 DESC;

Expected Output:
| day | row_count | |-----------|-----------| | 2023-10-15| 1200 | | 2023-10-14| 1100 | | 2023-10-13| 0 | ⚠️ Problem: No data for 10/13

What to look for:
- If MAX(order_date) is >24 hours old, your pipeline might be broken.
- If row counts drop suddenly, data might be missing.

Step 4: Automate Checks with Assertions

Goal: Turn these checks into reusable SQL that fails loudly if data is bad.

-- Data quality assertions (PostgreSQL example)
DO $$
BEGIN
  -- Assert: No NULL customer_id
  IF EXISTS (SELECT 1 FROM orders WHERE customer_id IS NULL) THEN
    RAISE EXCEPTION 'Data quality check failed: NULL customer_id found';
  END IF;

  -- Assert: No duplicate order_id
  IF EXISTS (
    SELECT order_id, COUNT(*)
    FROM orders
    GROUP BY order_id
    HAVING COUNT(*) > 1
  ) THEN
    RAISE EXCEPTION 'Data quality check failed: Duplicate order_id found';
  END IF;

  -- Assert: Data is fresh (updated in last 24 hours)
  IF (SELECT MAX(order_date) FROM orders) < NOW() - INTERVAL '24 hours' THEN
    RAISE EXCEPTION 'Data quality check failed: Data is stale (older than 24h)';
  END IF;
END $$;

How to use this:
- Run this in your ETL pipeline (e.g., Airflow, dbt, or a cron job).
- If any assertion fails, the query will error out and alert your team.

4. ? Production-Ready Best Practices

? Security

• Least privilege: Only grant SELECT on tables needed for checks (not UPDATE/DELETE).
• Secrets: If connecting to a production DB, use a secrets manager (e.g., AWS Secrets Manager, HashiCorp Vault).

? Cost Optimization

• Avoid SELECT *: Only query columns you need for checks.
• Use sampling: For large tables, check a random 1% sample first: sql SELECT * FROM orders TABLESAMPLE SYSTEM(1);
• Materialized views: Pre-compute checks for frequently accessed tables.

? Reliability & Maintainability

• Naming conventions: Prefix data quality tables with dq_ (e.g., dq_orders_null_rates).
• Tagging: In cloud databases (e.g., BigQuery), tag tables with data_quality:critical.
• Idempotency: Ensure checks can run multiple times without side effects.

? Observability

• Metrics: Track null rates, duplicate counts, and freshness over time (e.g., in Grafana).
• Alerts: Set up alerts if:
• Null rate in customer_id > 0%.
• Duplicate order_id count > 0.
• Data older than 24 hours.
• Logging: Log check results to a table for auditing: sql CREATE TABLE data_quality_logs (
check_name TEXT,
table_name TEXT,
check_time TIMESTAMP,
status TEXT, -- "PASS" or "FAIL"
details JSONB );

5. ⚠️ Common Mistakes & Traps

6. ? Exam/Certification Focus

Typical Question Patterns

1. "Which query identifies null rates in a table?"
2. Trap: Candidates forget to use COUNT(*) as the denominator.

3. Answer: SUM(CASE WHEN column IS NULL THEN 1 ELSE 0 END) / COUNT(*).

4. "How do you find duplicate records?"

5. Trap: Candidates use GROUP BY without HAVING COUNT(*) > 1.

6. Answer: GROUP BY key_columns HAVING COUNT(*) > 1.

7. "How do you check if data is fresh?"

8. Trap: Candidates only check MAX(timestamp) but ignore row count.
9. Answer: Check both MAX(timestamp) and row count trends.

Key Trap Distinctions

• NULL vs. 0 vs. empty string (''):
• NULL = missing data.
• 0 = valid value (e.g., $0 order).
• '' = empty string (e.g., no status).
• Exact vs. partial duplicates:
• Exact: All columns match.
• Partial: Key columns match (e.g., customer_id + order_date).

Scenario-Based Question

"You’re analyzing a users table and notice that 30% of email values are NULL. What do you do?"
- Bad answer: "Ignore it and proceed with analysis." - Good answer:
1. Check if NULL emails are expected (e.g., guest users).
2. If not, investigate the source (e.g., form submission bug).
3. Document the issue and decide whether to:
- Exclude NULL emails from analysis.
- Impute missing values (e.g., [email protected]).
- Fix the data at the source.

7. ? Hands-On Challenge

Challenge:
You’re given a transactions table with these columns: - transaction_id (unique) - user_id (foreign key) - amount (numeric) - transaction_date (timestamp)

Write a query that: 1. Calculates the null rate for each column.
2. Identifies duplicate transaction_id values.
3. Checks if the data is fresh (updated in the last 24 hours).

Solution:

-- Null rates
SELECT
  COUNT(*) AS total_rows,
  ROUND(100.0 * SUM(CASE WHEN transaction_id IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS pct_null_transaction_id,
  ROUND(100.0 * SUM(CASE WHEN user_id IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS pct_null_user_id,
  ROUND(100.0 * SUM(CASE WHEN amount IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS pct_null_amount,
  ROUND(100.0 * SUM(CASE WHEN transaction_date IS NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS pct_null_transaction_date
FROM transactions;

-- Duplicates
SELECT
  transaction_id,
  COUNT(*) AS dup_count
FROM transactions
GROUP BY transaction_id
HAVING COUNT(*) > 1;

-- Freshness
SELECT
  MAX(transaction_date) AS last_transaction_date,
  COUNT(*) AS total_transactions
FROM transactions;

Why it works:
- Null rates: Uses CASE WHEN to count NULL values and divides by total rows.
- Duplicates: Groups by transaction_id and filters for counts > 1.
- Freshness: Checks both the latest timestamp and row count.

8. ? Rapid-Reference Crib Sheet

Null Rate Checks

-- Null rate for a single column
SELECT
  COUNT(*) AS total_rows,
  SUM(CASE WHEN column IS NULL THEN 1 ELSE 0 END) AS null_count,
  ROUND(100.0 * null_count / total_rows, 2) AS pct_null
FROM table;

Duplicate Checks

-- Exact duplicates (all columns)
SELECT *, COUNT(*) OVER (PARTITION BY col1, col2, ...) AS dup_count
FROM table
QUALIFY dup_count > 1;

-- Partial duplicates (key columns)
SELECT col1, col2, COUNT(*) AS dup_count
FROM table
GROUP BY col1, col2
HAVING COUNT(*) > 1;

Freshness Checks

-- Max timestamp
SELECT MAX(timestamp_column) AS last_update FROM table;

-- Metadata (PostgreSQL)
SELECT last_autovacuum FROM information_schema.tables WHERE table_name = 'table';

-- Row count over time
SELECT DATE_TRUNC('day', timestamp_column) AS day, COUNT(*) AS row_count
FROM table
GROUP BY 1
ORDER BY 1 DESC;

Automated Assertions (PostgreSQL)

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM table WHERE key_column IS NULL) THEN
    RAISE EXCEPTION 'NULL key_column found';
  END IF;
END $$;

⚠️ Exam Traps

• NULL in aggregations: AVG(column) ignores NULL values (use COALESCE).
• GROUP BY without HAVING: Won’t filter duplicates.
• Freshness ≠ recency: Check both MAX(timestamp) and row count.

9. ? Where to Go Next

1. dbt Data Quality Tests – Learn how to automate checks in dbt.
2. Great Expectations – Open-source tool for data validation.
3. SQL for Data Analysis (O’Reilly) – Chapter 5 covers data quality.
4. PostgreSQL information_schema – Metadata tables for freshness checks.