Data Validation Frameworks

Production engineering guide for data validation frameworks covering patterns, implementation strategies, and operational best practices.

A data validation framework ensures that data meets specified criteria before it enters a system or moves between components. It prevents invalid data from causing downstream failures, maintains consistency, and enables reliable processing. This matters when data integrity is critical to business logic, automated workflows, or compliance requirements.

Validate Schema Compliance

Validate that incoming data conforms to expected structure and types. Use SQL to check against schema definitions.

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

If a required field is missing, the query returns a count greater than zero. The framework should fail when validation thresholds are exceeded.

Validate Data Range and Bounds

Check that numeric values fall within expected ranges. Use SQL for bounds enforcement.

SELECT COUNT(*) FROM table_name WHERE numeric_column < 0 OR numeric_column > 1000;

Validation fails if count is greater than zero. Common failure mode: ignoring edge cases like NULL or NaN.

Validate Referential Integrity

Ensure foreign key relationships are valid. Use SQL to verify references exist.

SELECT COUNT(*) FROM table_a a LEFT JOIN table_b b ON a.foreign_key = b.id WHERE b.id IS NULL;

If count is greater than zero, referential integrity is broken. Silent failures occur when validation is skipped in batch jobs.

Validate Data Types

Confirm data matches expected types. Use SQL or language-specific checks.

SELECT COUNT(*) FROM table_name WHERE CAST(string_column AS INTEGER) IS NULL AND string_column IS NOT NULL;

This query identifies values that cannot be converted to integer. Common silent failure: truncation of decimal values during conversion.

Validate Uniqueness Constraints

Ensure unique columns have no duplicates. Use SQL or application-level checks.

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

If result set is not empty, duplicates exist. Framework should halt on duplicate violations unless configured otherwise.

Validate Data Length and Format

Enforce string length and format rules. Use SQL or application code.

SELECT COUNT(*) FROM table_name WHERE LENGTH(string_column) > 255;

This checks maximum character limits. Format validation often uses regular expressions in code.

Implement Validation Rules with Conditions

Define validation logic using conditional expressions. Use SQL or application code.

SELECT COUNT(*) FROM table_name WHERE CASE WHEN status = 'active' THEN priority IS NULL ELSE TRUE END;

If count is greater than zero, rule violation exists. Common mistake: forgetting to handle NULL in conditions.

Handle Validation Failures Gracefully

Define what happens when validation fails. Framework should either reject data or log and continue.

-- Example: Insert with conditional validation
INSERT INTO table_name (id, name) 
SELECT id, name FROM staging_table 
WHERE NOT EXISTS (
  SELECT 1 FROM table_name t2 WHERE t2.id = staging_table.id
);

Framework must clearly distinguish between hard errors (halt) and soft errors (log and proceed). Silent failures occur when error handling code is missing.

Log Validation Results

Record validation outcomes for monitoring and debugging. Use structured logging or database tables.

INSERT INTO validation_log (table_name, rule_name, status, timestamp, details)
VALUES ('users', 'email_format', 'failed', NOW(), '{"invalid_email": "user@invalid"}');

Validation logs should include metadata like timestamp, rule name, and failure context. Missing log entries lead to untraceable issues.

Configure Validation Thresholds

Set acceptable failure rates or counts. Use configuration files or environment variables.

VALIDATION_THRESHOLD=0.05   # 5% allowed failures

Thresholds control when to stop processing. Misconfigured thresholds cause either too strict enforcement or silent data corruption.

Apply Validation in Pipelines

Integrate validation into ETL or data ingestion workflows. Use SQL or application logic.

-- Example: Pre-validate before insert
WITH validated_data AS (
  SELECT * FROM staging_table 
  WHERE email REGEXP '^[^@]+@[^@]+\.[^@]+$'
)
INSERT INTO target_table SELECT * FROM validated_data;

Validation within pipelines must not block the entire workflow unless configured to do so. Errors in pipeline validation steps often propagate incorrectly.

Validate Data Distribution

Ensure data distribution aligns with expected patterns. Use SQL for statistical checks.

SELECT 
  COUNT(*) as total_rows,
  COUNT(CASE WHEN status = 'active' THEN 1 END) as active_count,
  COUNT(CASE WHEN status = 'inactive' THEN 1 END) as inactive_count
FROM table_name;

Distribution validation helps detect anomalies like sudden drops in active users. Framework must support configurable thresholds for distribution checks.

Enforce Validation at Runtime

Validate data as it enters the system. Use application code or middleware.

def validate_user_data(user_dict):
    if not user_dict.get('email'):
        raise ValueError("Email required")
    if not user_dict.get('age') or user_dict['age'] < 0:
        raise ValueError("Age must be positive")

Runtime validation must catch malformed data early. Silent failures happen when validation exceptions are not handled properly.

Use Validation as a Gate

Block data flow based on validation results. Framework should act as an enforcement point.

# Example: Exit if validation fails
if [ $(psql -t -A -c "SELECT COUNT(*) FROM table_name WHERE status = 'invalid'") -gt 0 ]; then
  echo "Validation failed, exiting"
  exit 1
fi

Gates must be clear and fail fast. Misconfigured gates can allow invalid data through.

Implement Validation Audits

Periodically verify historical data against current rules. Use SQL or batch scripts.

SELECT COUNT(*) FROM table_name WHERE created_at > '2023-01-01' AND status NOT IN ('active', 'inactive');

Audits help detect data that was valid at ingestion but became invalid over time. Validation audits often miss edge cases in older data.

Handle Data Quality Metrics

Track validation success rates and failure patterns. Use SQL or monitoring systems.

SELECT 
  rule_name,
  COUNT(*) as total_checks,
  SUM(CASE WHEN passed = TRUE THEN 1 ELSE 0 END) as passed_count
FROM validation_results 
GROUP BY rule_name;

Metrics help identify which validations are failing most often. Framework should expose these metrics for monitoring.

Configure Validation Rules Dynamically

Allow rules to be updated without redeployment. Use configuration files or databases.

{
  "rules": [
    {
      "name": "email_format",
      "query": "SELECT COUNT(*) FROM table_name WHERE email NOT REGEXP '^[^@]+@[^@]+\.[^@]+$'",
      "threshold": 0
    }
  ]
}

Dynamic rules must be validated before application. Misconfigured rules can silently bypass validation.

Validate Data Consistency

Ensure data across related tables remains consistent. Use SQL joins or application logic.

SELECT a.id, b.related_id 
FROM table_a a 
JOIN table_b b ON a.id = b.table_a_id 
WHERE a.status != b.status;

Consistency checks are often overlooked in validation frameworks. Framework should support multi-table validations.

Set Up Validation Notifications

Alert on validation failures. Use email, webhook, or monitoring system integrations.

# Example: Send alert on validation failure
if [ $(psql -t -A -c "SELECT COUNT(*) FROM table_name WHERE status = 'failed'") -gt 0 ]; then
  curl -X POST -H "Content-Type: application/json" \
    -d '{"message": "Validation failed"}' \
    https://monitoring.example.com/alerts
fi

Notifications must be reliable and actionable. Silent failures in alerting logic are common.

Run Validation in Background

Schedule periodic validation runs. Use cron or job schedulers.

# Example: Run weekly validation checks
0 0 * * 0 /usr/local/bin/run_validation.sh --weekly

Background validation must not interfere with production workflows. Concurrent validation jobs can cause race conditions.

Validate Data Before Processing

Ensure data meets criteria before starting downstream operations. Use SQL or application logic.

-- Example: Validate before processing
WITH valid_data AS (
  SELECT * FROM raw_table 
  WHERE required_column IS NOT NULL
)
SELECT COUNT(*) FROM valid_data;

Pre-processing validation helps avoid failures in subsequent steps. Framework should support early exit on validation failure.

Monitor Validation Performance

Track how long validations take and whether they affect throughput. Use logging or metrics.

# Example: Log validation duration
start_time=$(date +%s)
psql -c "SELECT COUNT(*) FROM table_name WHERE status = 'invalid'"
end_time=$(date +%s)
duration=$((end_time - start_time))
echo "Validation took ${duration} seconds"

Performance issues in validation can slow entire systems. Framework should expose timing information for optimization.

Support Validation Rollbacks

Allow reverting data when validation fails. Use transactional SQL or backup mechanisms.

BEGIN;
INSERT INTO table_name SELECT * FROM staging_table WHERE validation_passed = TRUE;
-- If validation fails, rollback
ROLLBACK;

Rollbacks must be atomic and complete. Partial rollbacks cause data inconsistency.

Validate Data in Parallel

Run validations across multiple data partitions. Use SQL or distributed processing.

-- Example: Validate in parallel batches
SELECT COUNT(*) FROM table_name WHERE id BETWEEN 1 AND 1000;
SELECT COUNT(*) FROM table_name WHERE id BETWEEN 1001 AND 2000;

Parallel validation improves performance but increases complexity. Framework must handle partial failures correctly.

Define Validation Policies

Establish clear rules for when and how validation occurs. Use configuration or documentation.

validation_policies:
  - name: "strict"
    rules:
      - email_format
      - age_range
      - unique_id

Policies must be versioned and documented. Inconsistent policies lead to unpredictable validation behavior.

Integrate Validation with CI/CD

Ensure validations run in automated testing environments. Use scripts or tools.

# Example: Run validation in CI pipeline
if ! psql -c "SELECT COUNT(*) FROM table_name WHERE status = 'invalid'"; then
  echo "Validation failed in CI"
  exit 1
fi

CI integration must be fast and reliable. Slow validations can block deployments.

Validate Against External Sources

Check data against external systems or reference data. Use SQL joins or API calls.

SELECT t.id, e.external_id 
FROM table_name t 
LEFT JOIN external_reference e ON t.external_id = e.id 
WHERE e.id IS NULL;

External validation is error-prone due to network issues or schema mismatches. Framework should handle transient failures gracefully.

Track Validation History

Maintain logs of validation runs and results. Use database tables or log files.

CREATE TABLE validation_history (
  id SERIAL PRIMARY KEY,
  run_time TIMESTAMP,
  rule_name VARCHAR(255),
  status VARCHAR(50),
  failure_count INTEGER
);

History enables auditing and trend analysis. Missing history makes debugging difficult.

Handle Validation Exceptions

Define how to handle validation errors in different contexts. Use structured error handling.

try:
    validate_data(data)
except ValidationError as e:
    logger.error(f"Validation failed: {e}")
    # Decide whether to continue or halt

Exception handling must be consistent across all validation points. Inconsistent handling leads to unpredictable behavior.

Validate Data Types in JSON

Ensure JSON fields contain correct types. Use SQL or application logic.

SELECT COUNT(*) FROM table_name 
WHERE json_column->>'age' IS NOT NULL 
AND NOT (json_column->>'age' ~ '^[0-9]+$');

JSON validation often fails silently on malformed data. Framework should detect and report such issues.

Validate Against Business Rules

Implement domain-specific validations beyond technical checks. Use application logic or SQL.

SELECT COUNT(*) FROM table_name 
WHERE amount > 10000 AND approved_by IS NULL;

Business rule validation must be clear and testable. Framework should support rule-based validation configuration.

This page was rewritten on 16 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.