Data Quality Monitoring

Production-grade guide to data quality monitoring covering architecture patterns, implementation strategies, testing approaches, and operational best practices for enterprise engineering teams.

Data quality monitoring tracks and reports on the correctness, completeness, and consistency of data flowing through a system. It matters when data pipelines feed applications that make decisions, when downstream consumers depend on predictable data characteristics, or when data integrity issues cause production failures.

Identify Missing Data

Monitor for records that should exist but are absent from expected locations.

Use SQL queries to detect gaps in expected data ranges:

SELECT date_trunc('day', timestamp_column) as day,
       count(*) as record_count
FROM my_table
WHERE timestamp_column >= '2023-01-01'
GROUP BY day
HAVING count(*) = 0
ORDER BY day;

Check for missing foreign key references in related tables:

SELECT source.id 
FROM source_table source
LEFT JOIN target_table target ON source.foreign_key = target.id
WHERE target.id IS NULL
AND source.processed_at >= '2023-01-01';

Validate Data Types and Ranges

Ensure values conform to expected types and numeric bounds.

Query for invalid data types in a column:

SELECT id, raw_value 
FROM table_with_malformed_data
WHERE TRY_CAST(raw_value AS INTEGER) IS NULL
AND raw_value IS NOT NULL;

Monitor for out-of-range numeric values:

SELECT count(*) as outliers
FROM sales_table
WHERE amount < 0 OR amount > 1000000;

Detect Data Duplication

Identify records that appear multiple times with identical key values.

Find exact duplicates by primary key:

SELECT id, COUNT(*) 
FROM data_table
GROUP BY id
HAVING COUNT(*) > 1;

Detect near-duplicates by comparing multiple fields:

SELECT 
    field1, field2, field3,
    COUNT(*) as duplicate_count
FROM data_table
GROUP BY field1, field2, field3
HAVING COUNT(*) > 1;

Monitor Data Freshness

Track when data was last updated to ensure timeliness.

Query for tables with stale data:

SELECT table_name, MAX(last_updated) as latest_update
FROM information_schema.tables
WHERE table_schema = 'public'
GROUP BY table_name
HAVING MAX(last_updated) < NOW() - INTERVAL '1 hour';

Check timestamp ranges in data streams:

SELECT 
    date_trunc('hour', created_at) as hour,
    count(*) as record_count
FROM event_table
WHERE created_at >= NOW() - INTERVAL '24 hours'
GROUP BY hour
ORDER BY hour;

Validate Data Consistency

Ensure data remains consistent across related systems or transformations.

Compare row counts between source and destination:

SELECT 
    'source' as system,
    count(*) as row_count
FROM source_table
UNION ALL
SELECT 
    'destination' as system,
    count(*) as row_count
FROM destination_table;

Check for data drift in computed fields:

SELECT 
    id,
    calculated_field,
    expected_value,
    ABS(calculated_field - expected_value) as difference
FROM business_data
WHERE ABS(calculated_field - expected_value) > 0.01;

Implement Threshold-Based Alerts

Set up monitoring that triggers when metrics exceed defined limits.

Monitor for zero record counts:

SELECT 
    'empty_batch' as alert_type,
    COUNT(*) as count
FROM batch_table
WHERE batch_date = CURRENT_DATE
HAVING COUNT(*) = 0;

Check for excessive data variance:

SELECT 
    AVG(value) as mean_value,
    STDDEV(value) as std_deviation,
    COUNT(*) as total_count
FROM metrics_table
WHERE timestamp >= NOW() - INTERVAL '1 hour'
HAVING STDDEV(value) > 1000;

Set Up Automated Validation Checks

Create scheduled jobs that run validation queries and report results.

Example shell script for running data quality checks:

#!/bin/bash
psql -d mydb -c "
SELECT 'missing_records' as check_type, count(*) as result
FROM table_with_expected_data
WHERE expected_column IS NULL;

SELECT 'duplicate_keys' as check_type, count(*) as result
FROM (
    SELECT id, COUNT(*) 
    FROM data_table 
    GROUP BY id 
    HAVING COUNT(*) > 1
) dups;
"

Monitor Data Volume Changes

Detect when data volumes deviate from expected patterns.

Query for sudden increases in record counts:

WITH daily_counts AS (
    SELECT 
        date_trunc('day', timestamp_column) as day,
        count(*) as daily_count
    FROM data_table
    GROUP BY day
)
SELECT 
    day,
    daily_count,
    LAG(daily_count) OVER (ORDER BY day) as previous_day_count,
    (daily_count - LAG(daily_count) OVER (ORDER BY day)) / LAG(daily_count) OVER (ORDER BY day) as growth_rate
FROM daily_counts
WHERE (daily_count - LAG(daily_count) OVER (ORDER BY day)) / LAG(daily_count) OVER (ORDER BY day) > 0.5;

Track Data Schema Changes

Monitor when column definitions or table structures change unexpectedly.

Query for schema differences:

SELECT 
    column_name,
    data_type,
    is_nullable
FROM information_schema.columns
WHERE table_name = 'my_table'
AND table_schema = 'public'
ORDER BY ordinal_position;

Configure Continuous Monitoring

Set up recurring checks that run on a schedule.

Example crontab entry for running data validation every 15 minutes:

*/15 * * * * /usr/local/bin/data_quality_check.sh

Handle Silent Failures

Some data quality issues do not produce errors but silently corrupt results.

Monitor for data truncation:

SELECT 
    original_length,
    truncated_length,
    CASE WHEN original_length > truncated_length THEN 'TRUNCATED' ELSE 'OK' END as status
FROM (
    SELECT 
        LENGTH(original_text) as original_length,
        LENGTH(truncated_text) as truncated_length
    FROM data_table
) check_data;

Watch for precision loss in numeric conversions:

SELECT 
    source_value,
    CAST(source_value AS DECIMAL(10,2)) as converted_value,
    CASE WHEN source_value != CAST(source_value AS DECIMAL(10,2)) THEN 'PRECISION_LOSS' ELSE 'OK' END as status
FROM numeric_table;

Common Configuration Patterns

Use environment variables for threshold values:

export DATA_QUALITY_THRESHOLD=0.01
export MIN_RECORD_COUNT=100

Store validation queries in a configuration table:

CREATE TABLE data_quality_checks (
    check_name VARCHAR(255),
    query_text TEXT,
    expected_result TEXT,
    threshold DECIMAL(10,4),
    created_at TIMESTAMP DEFAULT NOW()
);

Error Handling and Reporting

Monitor for failed validation checks and log results:

INSERT INTO quality_report (
    check_name,
    timestamp,
    status,
    message
) VALUES (
    'schema_consistency_check',
    NOW(),
    'FAILED',
    'Column type mismatch detected'
);

Sharp Edges and Failure Modes

Using COUNT(*) in a HAVING clause without proper grouping can silently return zero rows instead of reporting all violations.

The TRY_CAST function may not be available in all SQL implementations, causing validation queries to fail with "function does not exist" errors.

Queries that use LIMIT without ORDER BY can produce different results on each run due to implicit sorting behavior.

Missing indexes on columns used in data quality queries can cause monitoring jobs to take hours instead of minutes.

The LAG() window function requires explicit ordering, and without it, the previous value may be undefined or incorrect.

Data quality checks that assume all timestamps are in UTC can fail when processing data with mixed timezones.

Some databases do not support EXCEPT or INTERSECT clauses in all contexts, requiring alternative approaches for set difference operations.

Alert Configuration

Use SQL to generate alert conditions:

SELECT 
    'data_volume_sudden_drop' as alert_name,
    count(*) as violation_count
FROM (
    SELECT 
        date_trunc('day', timestamp_column) as day,
        count(*) as daily_count
    FROM data_table
    GROUP BY day
) daily_stats
WHERE daily_count < (
    SELECT AVG(daily_count) * 0.5
    FROM (
        SELECT 
            date_trunc('day', timestamp_column) as day,
            count(*) as daily_count
        FROM data_table
        WHERE timestamp_column >= NOW() - INTERVAL '30 days'
        GROUP BY day
    ) avg_stats
);

Integration with Monitoring Systems

Example integration with external alerting using HTTP POST:

curl -X POST \
  http://monitoring-service/alert \
  -H 'Content-Type: application/json' \
  -d '{
    "alert_name": "data_quality_violation",
    "severity": "high",
    "details": {
      "check": "duplicate_keys",
      "count": 15,
      "timestamp": "2023-01-01T12:00:00Z"
    }
  }'

Performance Considerations

Avoid full table scans in data quality queries by adding appropriate indexes on columns used in WHERE and GROUP BY clauses.

Use EXPLAIN to analyze query performance:

EXPLAIN ANALYZE
SELECT COUNT(*) 
FROM data_table
WHERE processed_at >= '2023-01-01'
GROUP BY DATE_TRUNC('day', processed_at);

Consider partitioning large tables to improve query performance for time-based checks.

Data Quality Reporting

Generate summary reports of recent quality issues:

SELECT 
    check_name,
    COUNT(*) as incident_count,
    MAX(timestamp) as last_occurrence
FROM quality_report
WHERE timestamp >= NOW() - INTERVAL '1 day'
GROUP BY check_name
ORDER BY incident_count DESC;

Validation Test Patterns

Use assertions in validation queries to fail fast:

WITH validation AS (
    SELECT 
        CASE WHEN COUNT(*) = 0 THEN 1 ELSE 0 END as error_flag
    FROM data_table
    WHERE required_column IS NULL
)
SELECT 
    CASE WHEN error_flag = 1 THEN 'VALIDATION_FAILED' ELSE 'VALIDATION_PASSED' END as result
FROM validation;

This page was rewritten on 15 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.