Data Observability Patterns

This page describes practical approaches for monitoring and validating data quality, flow, and consistency in data systems. These patterns apply when you need to understand what data exists, how it moves, whether it's correct, and when it's available. They focus on the mechanics of observation rather than the tools that implement them.

Detecting Data Quality Issues

Monitoring for Missing or Unexpected Values

When data flows through a system, you must track when values disappear or appear unexpectedly. This pattern uses a combination of SQL queries and automated checks.

-- Check for nulls in critical fields
SELECT COUNT(*) FROM table_name WHERE critical_field IS NULL;

-- Validate ranges
SELECT COUNT(*) FROM table_name WHERE numeric_field < 0;

-- Validate against expected sets
SELECT COUNT(*) FROM table_name WHERE category NOT IN ('A', 'B', 'C');

A common failure mode occurs when queries return zero rows instead of the expected count. This can happen if:

• The target table is empty due to a failed ingestion job
• A column name has changed without updating monitoring queries
• The monitoring query runs before data is available

Tracking Schema Changes

When schemas evolve, you need to detect when new fields appear or existing ones disappear.

-- Compare current schema with expected
SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'target_table' 
AND table_schema = 'public'
ORDER BY ordinal_position;

The most common failure is when a monitoring system only checks for the presence of fields but not their types or nullability. A new field may be added with NULL as default, causing downstream consumers to fail silently.

Validating Data Distribution

Use statistical summaries to detect when data distributions change unexpectedly.

-- Get row counts by partition
SELECT date_partition, COUNT(*) 
FROM table_name 
GROUP BY date_partition 
ORDER BY date_partition;

-- Check for anomalies in value distributions
SELECT bucket, COUNT(*) 
FROM (
    SELECT CASE 
        WHEN value < 10 THEN 'low'
        WHEN value BETWEEN 10 AND 100 THEN 'medium'
        WHEN value > 100 THEN 'high'
    END as bucket
    FROM table_name
) grouped
GROUP BY bucket;

This pattern fails when the distribution baseline is not properly established. A system might consider a 20% increase in row count normal, but that represents a complete data loss for an expected daily batch.

Monitoring Data Flow

Tracking Pipeline Execution Status

Monitor when jobs start, finish, and encounter errors.

# Check job status using standard process monitoring
ps -ef | grep "data-pipeline-job"

# Using systemd service monitoring
systemctl status data-pipeline.service

A critical failure occurs when jobs appear successful but actually fail silently. This happens when:

• Jobs exit with code 0 but write to a corrupted state file
• The success signal is sent before the job completes all writes
• Error handling swallows exceptions that should fail the pipeline

Verifying Data Movement Between Systems

When data moves between systems, verify that it arrives correctly.

# Compare row counts between source and destination
psql -d source_db -c "SELECT COUNT(*) FROM table_name;" 
psql -d dest_db -c "SELECT COUNT(*) FROM table_name;"

The most common issue is when systems agree on row counts but disagree on content. A pipeline might copy 1000 rows, but if some have been modified or deleted, the total count remains correct while individual records are wrong.

Detecting Data Freshness Problems

Monitoring Time-Based Data Availability

Check that data arrives within expected time windows.

-- Check for recent data presence
SELECT MAX(timestamp_field) FROM table_name;

-- Verify minimum data age
SELECT COUNT(*) FROM table_name 
WHERE timestamp_field > NOW() - INTERVAL '1 hour';

A frequent failure mode is assuming that "data exists" means "data is fresh." If a system processes hourly data but the last successful run was 24 hours ago, queries will return stale results without any error.

Tracking Data Latency

Measure how long it takes for data to flow through systems.

-- Calculate end-to-end latency
SELECT 
    MAX(source_timestamp) as latest_source,
    MAX(destination_timestamp) as latest_destination,
    EXTRACT(EPOCH FROM (MAX(destination_timestamp) - MAX(source_timestamp))) as latency_seconds
FROM pipeline_logs;

This pattern fails when latency is measured against a baseline that doesn't account for batch processing windows. A 5-minute delay might be acceptable during off-peak hours but indicate a problem during business hours.

Identifying Data Inconsistencies

Detecting Duplicate Records

Find records that should be unique but appear multiple times.

-- Find duplicates by key
SELECT key_field, COUNT(*) 
FROM table_name 
GROUP BY key_field 
HAVING COUNT(*) > 1;

-- Find duplicates across related tables
SELECT t1.id, t2.id 
FROM table_a t1 
JOIN table_b t2 ON t1.id = t2.foreign_key 
WHERE t1.id IN (
    SELECT id FROM table_a 
    GROUP BY id HAVING COUNT(*) > 1
);

A key failure point is when duplicates are detected but not properly handled. The system might log them and continue, leaving inconsistent data in the database.

Validating Referential Integrity

Ensure that foreign key relationships remain valid.

-- Check for orphaned records
SELECT COUNT(*) FROM table_a 
WHERE foreign_key NOT IN (SELECT id FROM table_b);

-- Verify referential consistency
SELECT a.id, b.id 
FROM table_a a 
LEFT JOIN table_b b ON a.foreign_key = b.id 
WHERE b.id IS NULL;

The most common failure occurs when referential integrity is enforced only at the application level. A system might validate relationships during insert but not during updates or deletes.

Implementing Data Validation Checks

Creating Automated Validation Scripts

Write scripts that run periodic checks against data quality rules.

#!/bin/bash
# check-data-quality.sh

# Check for nulls
if [ $(psql -d database -t -c "SELECT COUNT(*) FROM table_name WHERE critical_field IS NULL;" | tr -d ' ') -gt 0 ]; then
    echo "ERROR: Found null values in critical field"
    exit 1
fi

# Check row count
expected=1000
actual=$(psql -d database -t -c "SELECT COUNT(*) FROM table_name;" | tr -d ' ')
if [ "$actual" -ne "$expected" ]; then
    echo "ERROR: Expected $expected rows, found $actual"
    exit 1
fi

The key failure mode is when validation scripts don't handle edge cases. A script might check row counts but ignore the case where a table is empty when it should never be.

Setting Up Data Comparison Tools

Compare data between systems to detect inconsistencies.

-- Compare specific columns between tables
SELECT 
    source_table.id,
    source_table.value as source_value,
    dest_table.value as dest_value
FROM source_table
FULL OUTER JOIN dest_table ON source_table.id = dest_table.id
WHERE source_table.value != dest_table.value OR source_table.value IS NULL OR dest_table.value IS NULL;

This approach fails when the comparison is not properly indexed. A full table scan for large datasets can take hours and may miss real-time issues.

Managing Observability Alerts

Creating Alert Conditions

Define thresholds that trigger notifications.

-- Alert when data freshness exceeds threshold
SELECT 
    CASE 
        WHEN MAX(timestamp_field) < NOW() - INTERVAL '1 hour' 
        THEN 'ALERT: Data is stale'
        ELSE 'OK'
    END as status
FROM table_name;

The most common alert failure is when alerts are too noisy. A system might generate thousands of alerts for minor deviations, making it impossible to notice real problems.

Tracking Alert Resolution

Monitor when alerts are acknowledged and resolved.

-- Log alert events
INSERT INTO alert_log (alert_type, severity, message, timestamp, resolved)
VALUES ('data_quality', 'high', 'Null values detected', NOW(), FALSE);

The sharp edge here is that alerts can be acknowledged without actually fixing the underlying problem. A system might mark an alert as resolved but continue to produce the same data quality issues.

Testing Observability Systems

Validating Monitoring Infrastructure

Ensure that monitoring systems themselves are working correctly.

# Test database connectivity
psql -d database -c "SELECT 1;" > /dev/null 2>&1 && echo "Connected" || echo "Failed"

A common failure is when monitoring systems themselves become unreliable. If a health check fails, it's difficult to determine if the problem is in the monitored system or in the monitoring system.

Verifying Alert Delivery

Test that alerts actually reach their intended recipients.

# Send test alert
echo "Test alert message" | mail -s "Data Observability Test" [email protected]

The key issue is that alert delivery systems can fail silently. A notification might be queued but never sent, leaving teams unaware of real problems.

Handling Data Observability Failures

Recovering from Monitoring Gaps

When monitoring systems fail, maintain fallback mechanisms.

-- Manual verification of data quality
SELECT COUNT(*) FROM table_name WHERE timestamp_field > '2023-01-01';

The critical failure point is when monitoring gaps are not detected. A system might assume everything is fine because the last check succeeded, but the monitoring process itself has stopped.

Managing False Positives

Avoid alert fatigue by properly tuning validation criteria.

-- Use rolling windows to reduce false positives
SELECT COUNT(*) 
FROM table_name 
WHERE timestamp_field > NOW() - INTERVAL '30 minutes'
AND timestamp_field < NOW() - INTERVAL '1 hour';

A common mistake is using absolute thresholds instead of relative ones. A check that triggers when there are more than 10 nulls might be fine during normal operation but cause alerts for every batch during system maintenance.

Key Failure Modes

Several patterns fail silently in production:

1. Silent data corruption: Systems continue processing even when data is inconsistent
2. Timing dependencies: Monitoring runs before data is fully available
3. False negatives: Validation passes when it should fail
4. False positives: Alerts fire for non-issues
5. Configuration drift: Monitoring rules change without documentation
6. Resource exhaustion: Monitoring processes consume more resources than available

The patterns described here are designed to be robust against these failures while remaining simple enough to maintain across different systems and teams.

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.