End to End Testing

Context & Problem

As part of the data transition for BYU’s ERP go-live build, our team needed to validate and prepare hundreds of SQL tables before migration.
In total, we had over 400 tables of data that required testing, validation, and correction.

Each table contained 5–30 different SQL test queries that checked data consistency, compared values between tables, and validated key relationships and IDs. These tests ensured that data integrity was maintained during the ETL (Extract, Transform, Load) process.

However, the data team at the time consisted of only 8 student developers and 5 full-time employees.
The full-time staff were focused on formatting and transformation logic, leaving the students responsible for running and checking all the validation tests manually — a tedious and time-consuming process that consumed nearly all of their bandwidth.

The obvious question was: why can’t we automate this?

The Solution

The goal was to automate SQL unit testing to validate ETL processes across multiple environments.

We built a Python-based system that:

1. Loaded SQL queries from organized .sql files.
2. Parsed each test query and executed it automatically.
3. Recorded results (pass/fail, error messages) into a centralized SQL results table.
4. Supported multiple environments, allowing tests to run against development, testing, and production systems.

This allowed us to visualize the overall health of our data pipelines and quickly identify which tests failed and why.

I designed the initial architecture for the entire system and then worked with Aaron Starkweather to implement a clean and efficient Python script. Once completed, we scheduled it to run automatically each week, ensuring constant validation and early detection of data issues.

Impact

The automation dramatically reduced manual testing time and freed up our student team to focus on more meaningful work.

• Validated 400+ SQL tables automatically
• Freed up the student developers’ time to work on higher-level tickets
• Accelerated data team productivity and delivery of updates
• Supported continuous data integrity verification from test builds through to go-live

The End-to-End Testing framework became a critical safety net throughout the entire transition process — ensuring that new changes didn’t corrupt previously validated data and helping maintain overall system reliability.

Reflection

This script accomplished exactly what it set out to do — efficiently, cleanly, and reliably.
I wouldn’t change anything about its implementation.

However, through this process I learned several key lessons:

• Automated testing is essential — whether for data, code, or full applications.
• Continuous integration saves time and prevents regressions.
• Automating validation not only improves reliability but also reduces mental load, allowing teams to focus on creative and complex problem-solving.

In short, even a simple automation can transform a repetitive process into a strategic advantage.

Summary