My question is, where does the testing take place?

Do we need to set up separate testing environment for ETL testing?

Second, once the data is loaded in staging, testing starts or you wait till it reaches target? Again in which environment testing should take place?


Answer1: As soon as the development is completed, testing should take place in dedicated testing environment. Testing environment is very small in terms of data volume but is just like production environment otherwise (for example, name of databases, schema, name of jobs and scheduling should be same as that of production). Smaller data set guarantees that the ETL jobs will execute faster as they have to process lesser data. And QA will get the execution results faster waiting time is less as QA have to execute the ETL jobs again and again during testing different scenarios and to validate bugs. And if the data set is bigger, it is frustrating for the QA to wait for the execution to complete. A lot of time is wasted in waiting.

Answer2: Yes. A dedicated testing environment is required. Generally, this environment should not be data heavy. This setup is specially needed while testing the data as soon an ETL solution is implemented by development team. Testing team should be responsible for environment setup (for example, publishing databases, executing ETL packages/workflows). If data volume is too much in the testing environment then it will take ages to complete the execution of ETL jobs.

So, the approach is to test the code against a relatively smaller data sets (in comparison to production). Once the testing is completed in such environment after that, we recommend that ETL solution should be deployed in Pre-production kind of environment where ETL solution has to process a huge volume of data. The deployment in Pre-prod is done by DBA but QA team is responsible for testing.

Answer3: We should generally wait till the data reaches the target. However, you can do a quick check to ensure that the data is loaded in staging area after the extraction has completed. But again, this decision has to do a lot with the volume of data. If it takes hours for data movement to staging then I would recommend to start testing the staging data in order to avoid any delay in un-earthing any potential issues associated to the related ETL jobs but if you have smartly created a smaller data set for testing then I would recommend waiting till the data reaches the target.


I think it will depend on the nature of the testing itself. Are you testing with large amount of production data? Are you testing with specially crafted test data?

To me if I am focused on ETL logic, the ETL is environment independent. It has a source and destination and thats pretty much it. If the test data is difficult to create and manage then that could constrain where it could be tested, ie: if stage is the only place that can hold the amount of data you want to run through the ETL then you either need to test in stage or stand up / beef up another environment.

There are several things you can test in the ETL. First do the transforms happen as expected? To test that I would create test cases that validate the ETL logic. For each test I would create the minimum amount of data to support the test. I would silo the data so that when validating the destination I would know which test generated it.

Once I have the logic covered, I would want to take a look at how the system handles load both large amount of data and running over an extended period of time. This will tie up the system for awhile so you may need to do this in an isolated environment.

It would be nice to be able to be able to articulate the throughput of the ETL, so some perf testing.

I would also want to take a look at how the ETL handles bad data or data in unexpected states.

Which environments are needed depends on whether you want to be able to do your testing in parallel along with how difficult it is to set up any dependancies.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.