Shell QA

Posted on Aug 16

Building an Enterprise Data Validation Framework: From Architecture to Version Control

#dataengineering #testing #python #architecture

Introduction

When managing large-scale data migrations, ETL pipelines, or multi-database reconciliations, manual verification quickly falls short. Without structured automation, data discrepancies—ranging from missing rows to subtle rounding errors—easily slip into production.

To tackle this, we built a layered, configuration-driven Data Validation Framework in Python. This framework enables engineering and QA teams to define test cases using clean YAML files, run multi-level checks, and auto-generate executive HTML reports.

Here is the complete blueprint and best practices guide covering all 10 core architectural modules.

1. Framework Architecture Overview

The framework follows a layered, configuration-driven architecture. Understanding the execution flow prevents common misconfigurations.

run_regression.py
├── config/execution.yaml (Group-level ON/OFF switches)
├── testcases/TC_XXX_TYPE.yaml (Individual test case definitions)
├── execution/validation_runner.py (Routes to correct validator)
│   ├── execution/df_loader.py (Loads DataFrames from source/target)
│   │   ├── execution/csv_loader.py (CSV path)
│   │   └── execution/sql_executor.py (SQL path)
│   └── validators/
│       ├── count_validator.py
│       ├── data_validator.py
│       ├── recon_validator.py
│       └── file_validator.py
├── utils/html_reporter.py (Generates summary HTML)
└── utils/logger.py (Rotating file logger -> logs/framework.log)

Key Design Principle: Control operates at two levels—group-level (execution.yaml) and test-case-level (enabled flag in YAML). Both must be set to true for a test case to execute.

2. Test Case Authoring

Naming Convention & Required Fields

Follow a strict pattern: TC_{NNN}_{TYPE}.yaml (e.g., TC_001_COUNT.yaml).

tc_id: TC_001_COUNT  # Must match the filename exactly
enabled: true        # Set false to skip without deleting
type: count          # count | data | recon | file
description: ">"
  Validate row count between Source DB and Target Staging table

source:
  type: sql
  path: sql/src/TC_001_COUNT.sql

target:
  type: sql
  path: sql/tgt/TC_001_COUNT.sql

ID Alignment: Always ensure tc_id matches the filename.
Human-Readable Descriptions: Populate clear descriptions that explain what business metric is being validated—this text directly feeds executive HTML summary reports.
Disabling vs. Deleting: Use enabled: false to skip tests temporarily. Never delete YAML files, as keeping them preserves history and audit trails.

3. Validation Type Selection

Choose the right strategy based on performance requirements:

Type	Scenario	When to Use
COUNT	Row Count Check	Quick sanity check. Always run first in any validation sequence.
DATA	Cell Comparison	Full cell-by-cell row matching for live database queries returning identical schemas.
RECON	Numeric Reconciliation	Column sum reconciliation with custom tolerances (e.g., max $0.01 rounding threshold).
FILE	Flat File Comparison	In-memory comparison tailored specifically for CSV or flat file extracts.

Always Lead with COUNT: A count failure signals pipeline or load failures immediately before you waste compute resources on heavy cell-level checks.
Tolerance Rules: Set tolerance explicitly in YAML. Never inflate tolerance to hide true data discrepancies without documented business justification.

4. SQL Query Management

External .sql Files: Store queries in sql/src/ and sql/tgt/ rather than inline YAML strings to maintain clean version control.
Determinism & Schema Drift: Include explicit ORDER BY clauses to ensure deterministic row ordering during DataFrame comparison. Avoid SELECT * in production tests; explicitly declare column names to prevent silent failures caused by schema drift.
Query Symmetry: Source and target queries must return matching column names and data types to prevent misleading shape mismatches.

5. Data Source & Connector Management

Modular Connectors: Encapsulate database connections inside dedicated modules under connectors/ using connection pooling. Never hardcode credentials—read them from environment files.
CSV File Hygiene: Store flat files under data/src/ and data/tgt/. Never commit real production data to version control—use anonymized or masked datasets.
Explicit Column Mapping: Use an explicit column_map block in YAML when column names differ between source and target rather than relying on position-based auto-alignment.

6. Execution Configuration

Group Switches (execution.yaml): Enable or disable entire validation suites (e.g., toggle all RECON tests off during initial staging loads).
Execution Scope: Tests run alphabetically by filename. Use clean numbering ranges or isolated directories for distinct projects.
Fail-Safe Processing: Wrap execution calls in try/except blocks so a single broken query or test case does not crash the entire regression suite.

7. Reporting & Logging

HTML Summary Reports: Automatically compiled into report/TC_000_SUMMARY.html after every execution cycle, embedding branding assets directly as base64 images.
Memory Capping: To optimize report rendering, mismatched row displays are capped (e.g., top 1,000 mismatches), with a "Download Full CSV" option embedded for deep root-cause analysis.
Log Rotation: Maintain a rotating file handler (e.g., 5 MB per log, 3 backups) at logs/framework.log set to INFO level to maintain complete audit trails.

8. File & Data Handling

NaN / Null Logic: Comparison modules enforce NaN-aware equality checks (NaN == NaN), treating matching empty/null positions across datasets as valid matches.
Numeric Coercion: Coerce string columns to numeric during RECON runs only when the operation is lossless, preventing silent data loss when reading flat files.
Sort Stability: Apply stable sorting algorithms (kind='stable') across all DataFrame columns prior to comparison for deterministic results.

9. Environment Management

Config Isolation: Isolate connection parameters by environment (config/env/qa.yaml, config/env/uat.yaml, config/env/prod.yaml).
Secrets Security: Inject sensitive credentials using environment variables or dedicated secret managers (Azure Key Vault, AWS Secrets Manager). Never commit raw passwords to repository control.

10. Framework Extension & Maintenance

Adding New Validators: Build custom modules under validators/ (e.g., schema_validator.py) adhering strictly to the framework's output contract (status, summary, src_to_tgt, tgt_to_src, matched_rows).
Connector Resiliency: Implement exponential backoff and retry logic in connectors to handle transient network blips gracefully.
Version Control Hygiene: Maintain a robust .gitignore excluding generated reports (report/.html), execution logs (logs/.log), temporary flat files (data/*/.csv), and pycache/.

Conclusion

A configuration-driven data validation framework provides the predictability and speed needed for modern data engineering pipelines. By decoupling test configuration from execution logic, teams can scale coverage effortlessly while maintaining high reliability.

How do you handle automated data validation in your ETL and pipeline workflows? Let's discuss in the comments below!

DEV Community