How We Automated ETL Regression Testing at Scale for an Enterprise Bank - 200M Records Validated in Under 2 Hours

One of the Top 5 Canadian Banks

• The bank's Equity Markets & Prime Brokerage Technology business ran roughly 800 batches in production. Whenever batch logic changed, the team needed to regression-test every affected ETL job to confirm production functionality wasn't disrupted - but with 800 batches and constant time pressure, there was no practical way to do this by hand. ETL batch configurations lived in database tables that weren't consistently promoted across environments, making structure and data comparison tedious, and schema or stored-procedure changes that weren't properly promoted to other environments caused recurring production errors.

• Use DQV to compare ETL job outputs after running them in both SIT-A (the development environment) and SIT-B (the production-mirror environment) - covering Data Load jobs (table-to-table), Data Extract jobs (table-to-file), and File Delivery jobs (file metadata: size, checksum, last-modified)
• Run periodic structure comparisons between SIT-A and SIT-B database schemas - tables, views, and stored procedure definitions - to catch definition drift in schema before it reached production
• Compare database metadata across 9 schemas (83,725 passed / 350 failed objects) in under 2 minutes
• Scale to a full 200-million-record data comparison across 79 tables in 1 hour 45 minutes that includes few larger BLOB/CLOB columns to compare
• Highlight differences at the field level, and integrated DQV into the DevOps pipeline to remove manual steps from the release process