ETL Testing ETL Performance and Scalability Testing 2 — Questions and Answers
Question 1: What is 'ETL stress testing'?
- Testing the ETL pipeline under normal expected load conditions
- Pushing the ETL pipeline beyond its normal capacity limits to observe how it behaves and fails under extreme conditions (Correct answer)
- Running the ETL once and measuring the baseline time
- Testing the stress level of the ETL development team
Correct answer: Pushing the ETL pipeline beyond its normal capacity limits to observe how it behaves and fails under extreme conditions
Stress testing deliberately overloads the ETL pipeline to identify its breaking point, failure modes, and recovery behavior when operating beyond designed capacity.
Question 2: Which database technique can dramatically improve ETL load performance by dividing large tables into smaller physical segments?
- Adding more VARCHAR columns
- Table partitioning (Correct answer)
- Increasing the number of NULL values
- Removing primary keys
Correct answer: Table partitioning
Table partitioning divides large tables into smaller, manageable segments by a partition key (e.g., date), allowing the ETL to load and query only relevant partitions for better performance.
Question 3: In ETL performance testing, what is a 'baseline measurement'?
- The minimum acceptable data quality score
- An initial performance benchmark recorded under normal conditions, used as a reference point for future comparison (Correct answer)
- The lowest row count ever observed in the target
- The network baseline speed of the ETL server
Correct answer: An initial performance benchmark recorded under normal conditions, used as a reference point for future comparison
A baseline measurement captures current ETL performance metrics (duration, throughput, resource usage) so future runs can be compared to detect regression or improvement.
Question 4: What ETL bottleneck does 'partition pruning' help address in performance testing?
- Slow transformation logic in Python scripts
- Full table scans on partitioned tables by eliminating irrelevant partitions from query execution (Correct answer)
- Slow network file transfers
- Slow ETL scheduling overhead
Correct answer: Full table scans on partitioned tables by eliminating irrelevant partitions from query execution
Partition pruning allows the query engine to skip partitions that don't match filter criteria, dramatically reducing I/O by reading only the relevant data segments.
Question 5: Which ETL performance test validates that parallel job streams do not cause database contention or deadlocks?
- Concurrency / multi-user load test (Correct answer)
- Single-thread performance test
- Schema validation test
- Data completeness test
Correct answer: Concurrency / multi-user load test
Concurrency testing runs multiple ETL streams simultaneously to verify that parallel execution does not cause locking, deadlocks, or data corruption in the target database.
Question 6: What is 'pushdown optimization' in ETL performance, and why is it tested?
- Pushing transformation logic down to junior developers
- Offloading transformation processing to the database engine rather than the ETL server to leverage native SQL optimization (Correct answer)
- Pushing data down from cloud to on-premises
- Moving indexes from memory to disk
Correct answer: Offloading transformation processing to the database engine rather than the ETL server to leverage native SQL optimization
Pushdown optimization delegates transformation logic to the source or target database using native SQL, reducing data movement and leveraging the database engine's optimized query processor.
ETL Testing ETL Performance and Scalability Testing 2
What is 'ETL stress testing'?