Data Quality Engineering | The Garnet Wiki

Data Quality Engineering is the practice of building systems that detect, prevent, and correct data anomalies in data pipelines before they affect downstream consumers. This discipline matters when data integrity is critical to business operations, such as during financial reporting, customer analytics, or regulatory compliance.

Monitor Data Quality Metrics

Check Row Count Consistency

Use SQL to compare row counts between source and destination systems:

-- Verify row count matches between source and target
SELECT 
    'source' as system,
    COUNT(*) as row_count
FROM source_table
UNION ALL
SELECT 
    'target' as system,
    COUNT(*) as row_count
FROM target_table;

When counts don't match, the difference indicates missing or duplicate rows. A mismatch in count can indicate failed data loads, partial writes, or truncation errors.

Validate Data Type Constraints

Use schema validation queries to check for type violations:

-- Check for NULL values in required columns
SELECT COUNT(*) as null_count
FROM table_name
WHERE column_name IS NULL;

-- Check for out-of-range numeric values
SELECT COUNT(*) as range_violation_count
FROM table_name
WHERE numeric_column < 0 AND numeric_column > 1000;

Data type violations silently propagate through pipelines, causing runtime errors in downstream systems. These failures often appear as "invalid input syntax" errors in PostgreSQL or "NumberFormatException" in Java applications.

Track Data Freshness

Monitor data age using timestamp comparisons:

-- Check if latest record is within expected time window
SELECT 
    MAX(timestamp_column) as latest_timestamp,
    NOW() - MAX(timestamp_column) as age
FROM table_name;

If freshness exceeds configured thresholds, the pipeline should trigger alerts. This prevents processing of stale data that may no longer reflect current business conditions.

Implement Data Validation Rules

Define Acceptance Criteria

Create validation queries for business rules:

-- Validate customer balance is non-negative
SELECT COUNT(*) as invalid_balance_count
FROM customers
WHERE balance < 0;

-- Validate email format
SELECT COUNT(*) as invalid_email_count
FROM users
WHERE email NOT LIKE '%@%.%';

Validation rules should be stored in configuration files or database tables, not hardcoded in applications. This enables non-engineers to modify criteria without code changes.

Configure Rule Enforcement

Set up validation job configurations:

# Example rule configuration file
rules:
  - name: "positive_balance"
    query: "SELECT COUNT(*) FROM accounts WHERE balance < 0"
    threshold: 0
    severity: "error"
    
  - name: "valid_email_format"
    query: "SELECT COUNT(*) FROM users WHERE email NOT LIKE '%@%.%'"
    threshold: 0
    severity: "warning"

The validation job should exit with non-zero status code when any rule exceeds its threshold. A job that passes despite data quality issues silently introduces bad data into production systems.

Handle Data Quality Failures

Implement Automated Remediation

Create scripts to correct common data issues:

#!/bin/bash
# Example: Remove duplicates from table
psql -d database_name <<EOF
DELETE FROM table_name
WHERE ctid NOT IN (
    SELECT min(ctid)
    FROM table_name
    GROUP BY unique_key_column
);
EOF

Automated remediation should log actions taken and provide rollback mechanisms. Without proper logging, a cleanup job may appear to succeed while leaving data in inconsistent state.

Configure Alerting

Set up monitoring for validation failures:

-- Create alert trigger for failed validations
CREATE OR REPLACE FUNCTION check_data_quality()
RETURNS TRIGGER AS $$
BEGIN
    IF (SELECT COUNT(*) FROM validation_table WHERE status = 'failed') > 0 THEN
        -- Send notification to monitoring system
        PERFORM pg_notify('data_quality_alert', 'Validation failures detected');
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Monitoring systems should distinguish between transient issues and persistent problems. A validation job that fails once due to network issues should not trigger alerts, but repeated failures should.

Establish Data Quality Dashboards

Build visualization queries for quality metrics:

-- Show daily data quality trends
SELECT 
    DATE_TRUNC('day', check_time) as day,
    COUNT(*) as total_checks,
    SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) as failed_checks
FROM validation_results
GROUP BY day
ORDER BY day DESC;

Dashboards should display both absolute failure counts and percentage changes over time. Sudden spikes in failures often indicate upstream system issues, while gradual degradation may suggest accumulating data quality problems.

Build Data Quality Testing Infrastructure

Create Test Data Sets

Generate test data that exercises quality rules:

-- Insert test records with known quality issues
INSERT INTO test_table (id, name, email, balance) VALUES
(1, 'Test User', 'invalid-email', 100.00),
(2, NULL, '[email protected]', -50.00);

Test data should include edge cases and violation examples that match real-world problems. This ensures validation rules work correctly for actual business scenarios.

Integrate Quality Checks into CI/CD

Add quality validation steps to deployment pipelines:

# Example CI workflow step
- name: Validate Data Quality
  run: |
    psql -d test_database -c "SELECT COUNT(*) FROM data_quality_checks WHERE status = 'failed'"
    if [ $? -ne 0 ]; then
      echo "Data quality validation failed"
      exit 1
    fi

Validation should be mandatory in all environments, not just production. A pipeline that passes validation in staging but fails in production indicates inconsistent test environments or missing validation rules.

Monitor Validation Performance

Track Query Execution Times

Monitor performance of quality checks:

-- Measure execution time of validation queries
SELECT 
    query_name,
    AVG(execution_time) as avg_time,
    MAX(execution_time) as max_time
FROM validation_logs
GROUP BY query_name;

Slow validation queries can become bottlenecks in data pipelines. Queries that take longer than configured timeouts should trigger alerts.

Log Validation Results

Maintain audit trails of validation outcomes:

-- Store detailed validation results
CREATE TABLE validation_audit (
    id SERIAL PRIMARY KEY,
    rule_name VARCHAR(255),
    check_time TIMESTAMP,
    result_count INTEGER,
    status VARCHAR(50),
    details JSONB
);

Audit logs should contain enough information to investigate individual failures. A log entry without context about which data caused a failure is worthless for debugging.

Handle Data Quality Configuration

Version Control Validation Rules

Store validation configurations in version control:

# data_quality_rules.yaml
validation_rules:
  - name: customer_email_format
    sql: "SELECT COUNT(*) FROM customers WHERE email NOT LIKE '%@%.%'"
    threshold: 0
    description: "All customer emails must follow standard format"
    
  - name: account_balance_positive
    sql: "SELECT COUNT(*) FROM accounts WHERE balance < 0"
    threshold: 0
    description: "Account balances cannot be negative"

Configuration files should be validated before deployment. A malformed YAML file or SQL syntax error in validation rules can cause entire validation jobs to fail silently.

Manage Rule Dependencies

Track relationships between validations:

-- Create dependency tracking table
CREATE TABLE validation_dependencies (
    dependent_rule VARCHAR(255),
    required_rule VARCHAR(255),
    dependency_type VARCHAR(50)
);

Some validations depend on others completing successfully. For example, a customer address validation may require the customer exists first. Failure to account for these dependencies can cause cascading failures.

Common Pitfalls and Sharp Edges

Silent Data Loss in Validation

Validation rules that return zero results are often misinterpreted as success:

-- This query might be interpreted as "no failures" when it's actually "no data"
SELECT COUNT(*) FROM table_name WHERE condition = 'value';

Always include explicit null handling and ensure validation queries return meaningful counts even when no violations exist.

Confusing Error Types

Distinguish between different types of data quality failures:

Missing Data: When expected values are absent (NULL, empty strings)
Invalid Format: When data doesn't match expected structure
Out-of-Range Values: When numeric values exceed valid bounds
Inconsistent Relationships: When related data violates business rules

Each failure type should trigger different alerting or remediation actions. A system that treats all quality failures the same is less effective than one that distinguishes between them.

Production vs Test Environment Differences

Validation behavior may differ between environments:

-- Production query might have different results than test
SELECT COUNT(*) FROM large_table WHERE date_column >= '2023-01-01';

Test data sets should mirror production data characteristics, including volume, distribution, and edge cases. A validation that works on 100 records may fail on 10 million records due to performance or resource constraints.

Configuration Drift

Validation rules can drift over time without proper change management:

# Rule that becomes outdated
rules:
  - name: "legacy_field_validation"
    sql: "SELECT COUNT(*) FROM old_table WHERE obsolete_field = 'value'"
    threshold: 0

Regular audits of validation configurations should identify deprecated rules and ensure all active rules remain relevant to current business requirements.

Data Quality Engineering is about building robust, observable systems that maintain data integrity throughout the pipeline lifecycle. The key is to treat quality checks as first-class citizens in the data infrastructure, not afterthoughts that get added when problems appear.

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.