be_ixf;ym_201909 d_22; ct_100

BI / DWH Testing

Shift Left (Early Testing) for BI and Data Warehouse Projects

Editor’s note: Wayne Yaddow is an independent consultant with over 20 years’ experience leading data migration/integration/ETL testing projects at organizations including J.P. Morgan Chase, Credit Suisse, Standard and Poor’s, AIG, Oppenheimer Funds, IBM, and Achieve3000.  Additionally, Wayne has taught  IIST (International Institute of Software Testing) courses on data warehouse, ETL, and data integration testing. He continues to lead numerous ETL testing and coaching projects on a consulting basis. You can contact him at

“Shift Left” testing is an approach to Data Warehouse (DWH) / BI development where testing is performed early in the lifecycle. Shifting Left refers not only to dynamic data and BI application testing—it also refers to static testing, conducting reviews and inspections, and even more unit and integration testing. In this article, we highlight:

  • Trends in Shift Left testing as it applies to DWH/BI teams
  • The value of Shift Left testing on DWH/BI projects
  • How to begin Shift Left testing processes

What is Shift Left testing?

“Shift Left” is a DevOps, and often-times Agile,  practice that provides an effective means to perform testing with (or in parallel to) development activities. That is, development, test and IT operations work together to plan, manage and execute automated and continuous testing to accelerate feedback to the business and developers. Technology is used to automate processes and virtualize components.

Testing may be performed as a part of the development process or as a service running in parallel to development activities. In either case, shifting left accelerates feedback to the product owner and developers to improve the quality of code delivered to QA (for example, system, end-to-end, and performance testing).

Shift Left is an essential capability for DevOps and Agile teams, but it can add value to all types of development teams.

Shift Left testing sums up an old idea that test experts have talked about for a long time: Testers and testing must start early and occur often. It’s about developers and testers coming together to grow and own test automation.

Trends in Shift Left testing on DWH/BI projects

Increasingly on data migration, data warehousing, business intelligence, and business analytics projects, the following roles and test types support QA (see Figure 1) — we acknowledge that different projects will likely define more or fewer roles and test types than those shown here.

Note that when testing is shifted left, product owners, project architects and others take on testing roles to aid in identifying defects/issues earlier. Their expertise can be utilized to verify and test.

Role examples include: Product owner, project architect, business and data analysts, ETL and report programmers, QA leads, system testers, IT support team (e.g., DBA’s, performance testers, etc.).

Figure 1: Examples of trending DWH/BI Shift Left responsibilities by test type and role
Source: Adapted from Agile Data Warehousing for the Enterprise, 2016, Ralph Hughes

Many of the tests listed on the left in Figure 2 are somewhat abstract and only suggestions; they are typical examples of what most teams decide upon. Note that these tests reach well beyond unit testing. Product owners and business analysts are frequently listed as the responsible party because requirements, data models, database design, and ETL loads (as a few examples) are what is to be assessed and tested early.

Note too that QA testers are not listed as responsible in Figure 2; this is primarily because testers do not typically have detailed DWH/BI specifications to design tests from, and if they did, those specs would likely change often. In addition, the QA team traditionally does not have the expertise to test the data that moves rapidly during loads from source to staging to the data warehouse, often being transformed along the way.

Figure 2: Examples of Shift Left tests and who should be responsible
Source: Adapted from Agile Data Warehousing for the Enterprise, 2016, Ralph Hughes

The value of Shift Left testing for DWH/BI projects 

Shifting quality assurance left basically means DWH/BI teams are catching bugs earlier in the process. Spending weeks or months at the end of a release cycle finding and fixing issues is inefficient—and it’ll cost you. An IBM study found that “it is 100 times more expensive to fix a defect that is found after it has been released.” That math should get most teams behind automated testing.

“The primary way you can get users what they need and what the company expects is to be testing where we’ve written, built and deployed code,” Titus Fortner of Sauce Labs has said. “Making sure you have the right tests in the right place to get rapid feedback. That’s where I see things needing to be in the industry.”

When Shift Left practices are implemented in the DWH/BI SDLC, the software system testing activities take place much earlier in software development life cycle. The goal is to fix looming issues that might crop up in the future and therefore meet the marks of quality in delivery. So when organizations adopt the Shift Left strategy, they are able to test, analyze projects, pass judgment on the system and refine it into something much better bit-by-bit.

How to begin Shift Left testing on DWH/BI projects

Pick one small team, and have a tester work with other team members on a feature from the very beginning. This should include everyone from the product manager, to business analysts, to programmers. It is important to create test automation assets because they assure rigid governance across the entire end-to-end testing process.  The entire team attends meetings together, reviews code together, tests together, and decides when requirements are complete.

Among the most critical components of your Shift left efforts should be the automated testing tools your team adopts. Testing earlier and faster is nearly impossible without automation. Depending on how far along you are in your DevOps or agile journey, you may already have an entire network of continuous integration and delivery tools. You may want to extend it with, for example, test automation that supports data testing:  column profiling, model-based testing,  support for regression and reconciliation testing, business rules/transformations testing and BI report verifications.

Ideas for automating Shift Left tests for DWH/BI

Shifting left enables early test automation. By testing earlier, teams can automate test cases earlier and reuse the automated test cases. It is good practice to execute test cases before automating them in order to make sure the test case runs smoothly when automated.

Vital check testing is one valuable way to expose data acquisition (ETL) errors early in the process. You can automatically generate vital checks for both data quality and data processing. The following test types can be easily created with automated BI/Data Warehouse testing tools:

  • Pre-screening tests: Verify source data for missing values, duplicates, data formats etc.
  • Metadata tests: Check whether table and column information has changed during loads
  • Completeness tests: Enable count comparisons between sources and targets
  • Uniqueness tests: Check for the uniqueness constraint defined files or tables
  • Referential integrity tests: Check that complete records have been copied and that technical as well as logical integrity is maintained
  • Data reconciliation tests: Perform complete source-to-target comparison—including file-to-database and database-to file comparisons

Concluding Remarks

If the idea takes hold, Shift Left testing will ensure that business analysts and developers, in particular,  take on additional testing tasks. Most already conduct comprehensive unit testing, making sure that small increments of code work even as they write them. This Agile practice is important because it incorporates testing early in the development cycle. It also emphasizes that testing is an integral part of software development and everyone is responsible for quality and success.

Encouraging developers to write reusable test scripts is one of many positive outcomes of the Shift Left movement. As developers continuously add and change code, their unit test scripts drive valuable regression tests that expose fundamental implementation issues early. This, in turn, frees QA to focus on completing higher-level testing, where they can identify integration and system-level issues.

Shifting tests left often encourages and enables early automation. By testing earlier, teams can maximize their ROI on test automation while exposing errors when they are fastest, easiest, and cheapest to fix.