100+ ETL Tester Interview Questions and Answers

The number of staging, dimension and fact tables in source and target systems need to be compared.

• Compare the number of staging, dimension and fact tables in source and target systems.

• Check if the table names and column names are consistent in both systems.

• Verify if the data types and data values are matching in both systems.

• Ensure that the ETL process is properly mapping the data from source to target systems.

• Perform data profiling to identify any discrepancies between the s...read more

Union combines the result sets of two or more SELECT statements, while Union All combines all rows from two or more SELECT statements.

• Union removes duplicate rows, while Union All does not.

• Union requires the number and order of columns in all SELECT statements to be the same, while Union All does not have this requirement.

• Example: SELECT column1 FROM table1 UNION SELECT column1 FROM table2;

• Example: SELECT column1 FROM table1 UNION ALL SELECT column1 FROM table2;

At landing and staging area, I will perform data validation to ensure accuracy and completeness of data.

• Validate data against source system

• Check for missing or duplicate data

• Verify data types and formats

• Ensure data integrity and consistency

• Perform data profiling and data quality checks

Test plan is a detailed document that outlines the testing approach and activities. Test strategy is a high-level document that outlines the testing objectives and methods.

• Test plan is more detailed than test strategy

• Test plan includes specific test cases and test scenarios

• Test strategy outlines the overall testing approach and objectives

• Test strategy is created before the test plan

• Test strategy is reviewed and updated regularly

To find the last 5 records, use the ORDER BY clause with a descending order and limit the result to 5. To find unique records, use the DISTINCT keyword.

• To find the last 5 records, use the ORDER BY clause with a descending order and limit the result to 5.

• Example: SELECT * FROM table_name ORDER BY column_name DESC LIMIT 5

• To find unique records, use the DISTINCT keyword.

• Example: SELECT DISTINCT column_name FROM table_name

Requirement Traceability Matrix is a document that links requirements to test cases. Compatibility testing checks if software works on different platforms.

• Requirement Traceability Matrix (RTM) is used to ensure that all requirements are tested and covered in test cases.

• RTM helps in tracking the progress of testing and ensures that all requirements are met.

• Compatibility testing is done to ensure that software works on different platforms, browsers, and devices.

• Compatibility te...read more

Self join is joining a table with itself. Types of joins are inner, outer, left and right. CDC is change data capture used for tracking data changes.

• Self join is used when we need to join a table with itself to retrieve data.

• Types of joins are inner, outer, left and right join.

• CDC is used to track data changes in the source system and apply those changes to the target system.

• CDC can be used in ETL testing to verify that the data is being correctly captured and transformed.

I will produce test plan, test cases, test scripts, defect reports, and test summary report.

• Test plan outlining the testing approach and scope

• Test cases with steps, expected results, and actual results

• Test scripts for automation

• Defect reports with steps to reproduce and severity

• Test summary report with overall results and recommendations

Explaining the differences between union and union all, having and where, rank and dense rank(), minus and intersection.

• Union combines the result sets of two or more SELECT statements, while Union All returns all rows including duplicates.

• Having is used to filter the results of an aggregate function in a GROUP BY clause, while Where is used to filter rows before grouping.

• Rank assigns a unique rank to each row within a result set, while Dense Rank assigns the same rank to rows...read more

Unique key allows null values while primary key does not.

• Primary key is a unique identifier for a record in a table.

• Unique key allows null values but primary key does not.

• A table can have only one primary key but multiple unique keys.

• Example: Employee ID can be a primary key while email can be a unique key.

Files can be compared in Unix using the diff command.

• Use the 'diff' command followed by the two file names to compare them.

• The output will show the differences between the two files.

• Use the '-y' option to display the differences side by side.

• Use the '-q' option to only show if the files are different or not.

• Use the '-r' option to compare files in directories recursively.

Fact table, dimension table, star schema, and snowflake schema are concepts in data warehousing.

• Fact table contains the measures or metrics of the data warehouse.

• Dimension table contains the attributes or dimensions of the data warehouse.

• Star schema is a type of schema where a fact table is connected to multiple dimension tables.

• Snowflake schema is a type of schema where dimension tables are normalized into multiple related tables.

• These concepts are used to design and organiz...read more

To display horizontal Oracle into vertical Oracle, we can use the PIVOT function in SQL.

• The PIVOT function is used to transform rows into columns.

• It requires an aggregate function to be specified.

• The PIVOT function can be used with the SELECT statement.

• The PIVOT function can also be used with dynamic SQL.

• Example: SELECT * FROM table_name PIVOT (SUM(column_name) FOR pivot_column IN (value1, value2, value3));

SQL query to find the last day of the previous month.

• Use the DATEADD function to subtract one day from the first day of the current month

• Use the DAY function to get the day of the month

• Subtract the day of the month from the date to get the last day of the previous month

To find the nth highest from a table, use the SQL query with the ORDER BY and LIMIT clauses.

• Write a SQL query to select the column you want to find the nth highest from.

• Use the ORDER BY clause to sort the column in descending order.

• Use the LIMIT clause to specify the number of rows to return, where n-1 is the value for LIMIT.

• The nth highest value will be the first row returned by the query.

Components of test cases include test case ID, test case description, test steps, expected results, actual results, and status.

• Test case ID: unique identifier for the test case

• Test case description: brief description of the test case

• Test steps: detailed steps to execute the test case

• Expected results: expected outcome of the test case

• Actual results: actual outcome of the test case

• Status: pass/fail status of the test case

The SQL query to find records that never contain flag 0 in the critical column.

• Use the NOT EXISTS operator to check if there are no records with flag 0 for each distinct value in the critical column.

• Write a subquery to select distinct values from the critical column.

• Join the main table with the subquery using the critical column.

• Filter the result by checking if there are no records with flag 0 for each distinct value in the critical column.

Prerequisites for ETL testing include understanding of data warehousing concepts, SQL, and ETL tools.

• Understanding of data warehousing concepts

• Proficiency in SQL

• Familiarity with ETL tools such as Informatica, Talend, or SSIS

• Knowledge of data mapping and transformation

• Ability to write test cases and execute them

• Experience in data validation and reconciliation

• Understanding of data quality and data profiling

• Knowledge of source and target systems

• Ability to troubleshoot issues and...read more

Incomplete testing can lead to undetected defects and potential risks in production.

• Undetected defects can cause system failures and downtime.

• Incomplete testing can lead to security vulnerabilities.

• Lack of testing can result in poor user experience and customer dissatisfaction.

• Incomplete testing can lead to increased maintenance costs and longer development cycles.

To fetch last 5 records from a table in SQL

• Use SELECT statement to retrieve data from the table

• Use ORDER BY clause to sort the data in descending order based on a column

• Use LIMIT clause to limit the number of rows returned to 5

To validate between two integers, compare each corresponding digit and check if they are equal.

• Convert both integers to strings

• Pad the source integer with leading zeros to match the length of the target integer

• Compare each digit of the source and target integers

• If any digit is not equal, the validation fails

Requirements are mapped to test cases by analyzing the requirements and creating test cases that cover all the aspects of the requirement.

• Analyze the requirement document thoroughly

• Identify the key functionalities and features

• Create test cases that cover all the aspects of the requirement

• Ensure that the test cases are traceable to the requirement

• Review and validate the test cases with stakeholders

The output for inner join will be the rows where both col1 and col2 have non-null values.

• Inner join returns only the matching rows from both tables.

• In this case, the output will be the rows with non-null values in both col1 and col2.

• Rows with null or blank values in either col1 or col2 will be excluded from the output.

Dimensions are attributes or characteristics of data that can be used for analysis and reporting.

• Dimensions are used in data warehousing and business intelligence to categorize and organize data.

• Types of dimensions include time, geography, product, customer, and organization.

• Dimensions can be hierarchical, with subcategories and levels of detail.

• Dimensions are often used in conjunction with measures, which are the numerical values being analyzed.

Low, Medium, and High priority defects are categorized based on their impact on the system and urgency of fixing.

• Low priority defects are minor issues that do not significantly impact the system's functionality.

• Medium priority defects are issues that have a noticeable impact on the system but are not critical.

• High priority defects are critical issues that severely impact the system's functionality and need immediate attention.

• Examples: Low priority - Spelling errors, Medium p...read more

White box testing is testing the internal structure of the application while black box testing is testing the functionality without knowledge of the internal structure.

• White box testing is also known as clear box testing or structural testing.

• It requires knowledge of the internal workings of the application.

• Examples include unit testing and integration testing.

• Black box testing is also known as functional testing.

• It does not require knowledge of the internal workings of the a...read more

Union combines and removes duplicates, while union all combines without removing duplicates.

• Union removes duplicates from the result set, while union all does not.

• Union is slower than union all because it has to perform an additional step to remove duplicates.

• Union all is used when duplicates are desired in the result set.

• Example: SELECT column1 FROM table1 UNION SELECT column1 FROM table2;

• Example: SELECT column1 FROM table1 UNION ALL SELECT column1 FROM table2;