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

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

The challenge of manual verification in large-scale data migrations, ETL pipelines, and multi-database reconciliations is addressed through the creation of a layered, configuration-driven Data Validation Framework in Python. This framework provides engineering and QA teams with a structured approach to defining test cases using clean YAML files, running multi-level checks, and generating auto-execution HTML reports.

The framework architecture consists of 10 core modules structured in a layered, configuration-driven manner. Key design principles include control operating 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.

Test case authoring requires strict naming conventions, such as TC_{NNN}_{TYPE}.yaml (e.g., TC_001_COUNT.yaml). Each test case must have a unique tc_id that matches the filename, an enabled flag set to true, and a type (count, data, recon, or file). Clear descriptions of the business metric being validated are also required.

Validation types are chosen based on performance requirements. The COUNT type is a quick sanity check for row count, DATA type checks cell-by-cell row matching for live database queries, RECON type performs column sum reconciliation with custom tolerances, and FILE type is for in-memory comparison of CSV or flat file extracts. Always lead with the COUNT type, as its failure signals pipeline or load failures before heavy cell-level checks are performed.

SQL queries are managed through external .sql files stored in separate source and target directories to maintain clean version control. Determinism and schema drift are addressed by including explicit ORDER BY clauses and avoiding SELECT * in production tests. Explicit column mapping blocks are used when column names differ between source and target, rather than relying on position-based auto-alignment.

SQL query management involves storing queries in external .sql files in dedicated source and target directories. Determinism and schema drift are addressed by including explicit ORDER BY clauses and avoiding SELECT * in production tests. Explicit column mapping blocks are used when column names differ between source and target.

Data source and connector management involves encapsulating database connections inside dedicated modules using connection pooling. Credentials are never hard-coded; instead, they are read from environment files. Flat files are stored under data/src/ and data/tgt/ directories, and real production data is never committed to version control. Explicit column mapping blocks are used when column names differ between source and target.

The framework also includes group switches for enabling or disabling entire validation suites, execution scope based on alphabetical filename order, and fail-safe processing that wraps execution calls in try/except blocks to prevent a single broken query or test case from crashing the entire regression suite.

HTML summary reports are automatically compiled into report/TC_000_SUMMARY.html after every execution cycle. Branding assets are embedded as base64 images, and memory capping caps mismatched row displays (e.g., top 1,000 mismatches) for deep root-cause analysis using a "Download Full CSV" option. Log rotation maintains a rotating file handler with a 5MB limit per log and 3 backups, set to INFO level to maintain complete audit trails.