ETL Testing ETL Performance and Scalability Testing 1 — Questions and Answers
Question 1: What is the primary goal of ETL performance testing?
- Verifying that data transformations are logically correct
- Measuring whether the ETL pipeline can process required data volumes within the defined processing window (Correct answer)
- Checking that all column names match the specification
- Validating that source systems are online
Correct answer: Measuring whether the ETL pipeline can process required data volumes within the defined processing window
ETL performance testing determines whether the pipeline can handle production data volumes within the available batch window (e.g., a nightly 4-hour window).
Question 2: What is an 'ETL batch window'?
- A GUI window in the ETL tool interface
- The scheduled time period during which the ETL process must complete all data processing (Correct answer)
- A database buffer for batch inserts
- A network port used by ETL communication
Correct answer: The scheduled time period during which the ETL process must complete all data processing
The batch window is the time slot — typically overnight — allocated for ETL processing; exceeding it delays data availability and can impact business operations.
Question 3: Which metric is MOST commonly used to measure ETL throughput performance?
- Number of ETL developers on the team
- Rows processed per second (or records per minute) (Correct answer)
- Number of source tables connected
- Disk IOPS during load
Correct answer: Rows processed per second (or records per minute)
Rows per second or records per minute quantifies how fast the ETL pipeline processes data, allowing comparison against throughput requirements and benchmarks.
Question 4: In ETL scalability testing, what does 'volume testing' involve?
- Testing audio volume settings on ETL server hardware
- Running the ETL pipeline with significantly larger datasets than normal to identify degradation points and bottlenecks (Correct answer)
- Testing the number of concurrent ETL tool users
- Validating storage volume labels in the data center
Correct answer: Running the ETL pipeline with significantly larger datasets than normal to identify degradation points and bottlenecks
Volume testing loads the ETL pipeline with very large datasets — often 10x or 100x normal size — to expose performance cliffs, memory limits, and scalability bottlenecks.
Question 5: What ETL performance issue occurs when a single processing node becomes a bottleneck, slowing the entire pipeline?
- Data skew (Correct answer)
- Referential integrity violation
- Schema drift
- Null propagation
Correct answer: Data skew
Data skew happens when data is unevenly distributed across partitions or nodes, causing some workers to process far more records than others and creating a bottleneck.
Question 6: Which tool is commonly used to profile SQL query performance within an ETL pipeline in a US enterprise environment?
- Notepad++
- Execution plan analysis in SQL Server Management Studio or similar RDBMS tools (Correct answer)
- Microsoft Word
- FTP client
Correct answer: Execution plan analysis in SQL Server Management Studio or similar RDBMS tools
Execution plan analysis tools show how the database optimizer executes a query, revealing costly operations like table scans, sort spills, and missing indexes that slow ETL loads.
ETL Testing ETL Performance and Scalability Testing 1
What is the primary goal of ETL performance testing?