Shift left testing is an approach to Data Warehouse (DW) / 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 DW/BI teams
  • The value of shift left testing on DW/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 Quality Assurance (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 DW/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 less 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.).

Examples of trending DW/BI shift left responsibilities
Figure 1: Examples of trending DW/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 DW/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.

examples of shift left testing
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 on DW/BI projects

Shifting quality assurance left basically means DW/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 DW/BI SDLC, the software system testing activities takes 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. See Figure 3.

shift left testing value
Figure 3: Value of shift left testing vs. those of traditional testing practices. Source:

How to begin shift left testing on DW/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 automation of DW/BI shift left tests

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 return on investment on test automation while exposing errors when they are fastest, easiest, and cheapest to fix.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

About the author 

Wayne Yaddow

Wayne Yaddow is an independent consultant with more than 20 years’ experience leading data integration, data warehouse, and ETL testing projects with J.P. Morgan Chase, Credit Suisse, Standard and Poor’s, AIG, Oppenheimer Funds, and IBM. He taught IIST (International Institute of Software Testing) courses on data warehouse and ETL testing and wrote DW/BI articles for Better Software, The Data Warehouse Institute (TDWI), Tricentis, and others. Wayne continues to lead numerous ETL testing and coaching projects on a consulting basis. You can contact him at

You may also like:

Managing DW/ BI data integration risks through data reconciliation and data lineage processes