Data Observability

Data observability is the practice of monitoring and understanding the flow, health, and behavior of data systems to detect anomalies, track data lineage, and ensure quality. It matters when you need to understand what your data pipeline is doing, why it's failing, and whether data is flowing as expected across systems.

Monitor Data Flow and Status

Use a combination of log aggregation and metrics collection to track pipeline execution. For example, a batch job might emit structured logs like:

{"timestamp": "2023-10-15T12:00:00Z", "job_id": "etl-20231015", "status": "started", "step": "source_read"}

The system should capture these in a log store like Elasticsearch or a data warehouse. Querying for status changes helps detect job failures or delays:

SELECT job_id, status, timestamp FROM logs 
WHERE job_id = 'etl-20231015' 
ORDER BY timestamp;

A missing status: finished log entry may indicate a job that timed out or crashed without reporting success. The system should also expose metrics via Prometheus or similar:

# HELP batch_job_duration_seconds Duration of batch jobs in seconds
# TYPE batch_job_duration_seconds histogram
batch_job_duration_seconds{job="etl-20231015", status="success"} 42.1

Detect Data Quality Issues

Set up checks that validate data at key points in the pipeline. For instance, a query to detect missing values:

SELECT COUNT(*) FROM table_name WHERE column_name IS NULL;

Or check for unexpected data types:

SELECT COUNT(*) FROM table_name WHERE CAST(column_name AS INTEGER) IS NULL;

A job should fail or emit an alert when these checks return non-zero results. If a downstream consumer expects 1000 rows and receives 950, the system should flag this discrepancy. The check can be part of a SQL-based monitoring tool or a separate script that runs after each step.

Track Data Freshness

Use timestamps to ensure data is current. A simple freshness check might query for the latest timestamp in a table:

SELECT MAX(timestamp_column) FROM table_name;

If this value is older than a threshold, the system should trigger an alert or fail the pipeline. For example, if your system expects hourly updates and the last entry is more than 90 minutes old, it may indicate a failure in the upstream data source.

Validate Data Volume and Consistency

Monitor for unexpected changes in row counts or volume. A simple script to compare counts:

# Get row count from source
SOURCE_COUNT=$(psql -t -A -F',' -c "SELECT COUNT(*) FROM table_name;" | tr -d '\n')

# Get row count from destination
DEST_COUNT=$(psql -t -A -F',' -c "SELECT COUNT(*) FROM table_name;" | tr -d '\n')

if [ "$SOURCE_COUNT" != "$DEST_COUNT" ]; then
  echo "Row count mismatch: $SOURCE_COUNT vs $DEST_COUNT"
fi

This check should run regularly, perhaps as a cron job or part of a scheduled workflow. If the counts don’t match, it may indicate a failed or skipped data transfer.

Set Up Alerting for Anomalies

Define thresholds and alert conditions based on historical behavior or business rules. For example, if the average number of records per hour drops below 100, trigger an alert:

# Query historical averages
HISTORICAL_AVERAGE=$(psql -t -A -F',' -c "
  SELECT AVG(record_count) 
  FROM (
    SELECT DATE_TRUNC('hour', timestamp_column) as hour, COUNT(*) as record_count
    FROM table_name
    WHERE timestamp_column > NOW() - INTERVAL '7 days'
    GROUP BY hour
  ) subquery;
")

If record_count drops below HISTORICAL_AVERAGE * 0.8, send a notification. Alerting systems should distinguish between transient issues and persistent failures.

Monitor Data Lineage

Track how data moves through the system. A lineage graph can be built by recording each job's inputs and outputs:

INSERT INTO data_lineage (job_id, input_table, output_table, timestamp)
VALUES ('etl-20231015', 'source_table', 'staging_table', NOW());

A query to check the lineage for a table:

SELECT job_id, input_table, output_table FROM data_lineage
WHERE output_table = 'staging_table'
ORDER BY timestamp DESC LIMIT 1;

If a table is used in multiple jobs, the system should track all dependencies and alert when a source table is missing or altered.

Handle Silent Failures

Many observability tools silently ignore failures or miss events. For example, a log parser may silently skip malformed JSON lines:

# This will silently skip lines without timestamp
jq '.timestamp' logs.txt | grep -v null

To avoid silent failures, implement explicit validation in your monitoring pipeline. Log errors for malformed lines or missing fields, and ensure the system fails fast on critical issues.

Configure Monitoring Without Overhead

Avoid complex or slow monitoring systems that slow down data processing. A lightweight setup uses simple SQL queries and logs instead of heavy telemetry agents. For example, a monitoring script can run in the same container as the pipeline job, without needing external services:

# Example: Monitor job status
if [ -f "/tmp/job_status" ]; then
  STATUS=$(cat /tmp/job_status)
  echo "Job status: $STATUS"
else
  echo "Job status: unknown"
fi

Identify Failure Patterns

Look for consistent failure patterns in logs or metrics. A job may consistently fail on specific partitions or during certain hours:

SELECT DATE_TRUNC('hour', timestamp_column), COUNT(*) 
FROM logs 
WHERE status = 'failed' AND job_id = 'etl-20231015'
GROUP BY 1
ORDER BY 1;

If failures occur only at 3 AM, the issue might be a resource limit or a timing conflict with other processes.

Validate Data Consistency Across Environments

Ensure data is consistent between development, staging, and production. A checksum can be computed for each dataset:

# Compute MD5 of table data
psql -t -A -F',' -c "SELECT md5(string_agg(row_data, '')) FROM (
  SELECT * FROM table_name ORDER BY id
) subquery;" > checksum.txt

Compare this checksum between environments. If values differ, the system should flag the inconsistency.

Detect Data Loss or Corruption

Monitor for missing or corrupted data points. For example, check that no IDs are duplicated:

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

If duplicates are found, log them and possibly trigger a reprocessing job. Also, monitor for gaps in sequences or timestamps:

SELECT id FROM table_name WHERE id NOT IN (
  SELECT generate_series(MIN(id), MAX(id)) FROM table_name
);

This query finds missing IDs in a sequence.

Monitor System Resources

Ensure that pipeline jobs have sufficient resources to run. If a job consistently runs out of memory or CPU, the system should detect this and alert:

# Example: Check memory usage of running job
ps aux | grep "job_name"

Resource monitoring can be done with standard Unix tools or integrated into your orchestration platform.

Validate Data Types and Schema

Ensure data remains consistent in structure. If a column is expected to be an integer but receives text, the system should detect this:

SELECT COUNT(*) FROM table_name WHERE CAST(column_name AS INTEGER) IS NULL;

If the count is non-zero, the job should fail or log the issue.

Handle Time Zone and Clock Issues

Ensure that all systems use consistent time zones. A mismatch in time zone handling can cause data to appear in wrong partitions or fail validation checks:

SELECT timezone('UTC', timestamp_column) FROM table_name LIMIT 1;

If timestamps are stored inconsistently, it may cause downstream issues in data processing.

Review and Improve Observability

Regularly review alerts and logs to improve monitoring coverage. If certain types of failures occur repeatedly, add new checks or modify existing ones. For example, if a job fails due to missing data, add a check for source availability:

SELECT COUNT(*) FROM source_table WHERE date_column = CURRENT_DATE;

If the count is zero, log an error and potentially trigger a retry or alert.

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.