Infosys ETL Tester Interview Questions and Answers

Use OFFSET and FETCH in SQL to retrieve specific records from a table.

• Use OFFSET to skip the first n-1 records

• Use FETCH to limit the number of records to be retrieved

• Example: SELECT * FROM table_name OFFSET 4 ROWS FETCH NEXT 5 ROWS ONLY

Constraints are rules or conditions that must be followed in order to ensure data integrity and accuracy.

• Constraints define the limits or restrictions on data values in a database table

• Examples of constraints include primary key, foreign key, unique key, and check constraints

• Constraints help maintain data quality and consistency in a database

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 at...read more

Joins are used to combine rows from two or more tables based on a related column between them.

• Joins are used in SQL to retrieve data from multiple tables based on a related column.

• Common types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

• Example: SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

Window functions perform calculations across a set of table rows related to the current row, without collapsing results.

• Window functions are used for calculations like running totals, moving averages, and ranking.

• Example: ROW_NUMBER() assigns a unique sequential integer to rows within a partition.

• Example: SUM() OVER (PARTITION BY department ORDER BY salary) calculates total salary per department.

• Example: RANK() OVER (O...read more

A domain name from an email refers to the part after the @ symbol in an email address.

• The domain name is typically the name of the organization or service provider associated with the email address.

• For example, in the email address example@gmail.com, 'gmail.com' is the domain name.

dense_rank() assigns a unique rank to each distinct row, row_number() assigns a unique sequential integer to each row.

• dense_rank() is used to assign a unique rank to each distinct row based on the specified order.

• row_number() is used to assign a unique sequential integer to each row in the result set.

• Example: dense_rank() - SELECT dense_rank() OVER (ORDER BY salary) AS rank FROM employees;

• Example: row_number() - SELECT...read more

SCD types refer to slowly changing dimensions in data warehousing. Star schema is denormalized while snowflake schema is normalized.

• SCD types refer to how dimensions change over time in a data warehouse

• Type 1: Overwrite existing data with new data

• Type 2: Keep track of historical data with new records

• Type 3: Keep track of some historical data with limited history

• Star schema is denormalized with a central fact table conn...read more

Group by clause is used in SQL to group rows that have the same values into summary rows.

• Group by clause is used with aggregate functions like SUM, COUNT, AVG, etc.

• It is used to group rows based on one or more columns specified in the query.

• Group by clause must be used after the WHERE clause and before the ORDER BY clause.

• Example: SELECT department, COUNT(*) FROM employees GROUP BY department;

In five years, I envision myself as a senior ETL Tester, leading projects and mentoring junior team members in data quality assurance.

• Career Advancement: I aim to progress to a senior ETL Tester role, where I can take on more complex projects and responsibilities.

• Leadership Role: I see myself leading a team of testers, guiding them in best practices for ETL processes and data validation.

• Mentorship: I want to mentor jun...read more

Seeking new challenges and growth opportunities in a different environment.

• Desire for career advancement

• Interest in learning new technologies

• Seeking a more challenging role

• Company restructuring or downsizing

• Relocation to a different city

My strengths include attention to detail and problem-solving skills. My weakness is sometimes being too critical of my own work.

• Strength: Attention to detail - I always ensure that all aspects of the testing process are thoroughly checked.

• Strength: Problem-solving skills - I am able to quickly identify and resolve issues that arise during testing.

• Weakness: Being too critical of my own work - I sometimes spend too much ...read more

SCD Type 2 stands for Slowly Changing Dimension Type 2, which is a data warehousing concept used to track historical changes in data.

• SCD Type 2 maintains historical data by creating new records for changes and preserving old records

• It includes additional columns like start date, end date, and version number to track changes

• Example: If a customer changes their address, a new record is created with the updated address wh...read more

Query to find duplicates in a database table

• Use GROUP BY and HAVING clause to identify duplicate records

• Select columns to check for duplicates

• Use COUNT() function to count occurrences of each unique value

Use SQL query with ORDER BY and LIMIT to fetch nth highest salary

• Use ORDER BY salary DESC to sort salaries in descending order

• Use LIMIT n-1,1 to fetch the nth highest salary

• Replace n with the desired position of the salary

Use SQL query to fetch the number 3 from a database table.

• Use SELECT statement with WHERE clause to filter for the number 3

• Example: SELECT * FROM table_name WHERE column_name = 3

Slowly Changing Dimensions (SCD) are used in data warehousing to track changes to data over time. Types include Type 1, Type 2, and Type 3.

• Type 1 SCD: Overwrites old data with new data, losing historical information.

• Type 2 SCD: Creates a new record for each change, preserving historical data.

• Type 3 SCD: Tracks changes by adding columns to the existing record, allowing for limited historical analysis.

Use the GROUP BY clause with COUNT() function to find duplicates in SQL.

• Use GROUP BY clause with COUNT() function to group the records by the columns you want to check for duplicates

• Filter the results by using HAVING clause to only show records with count greater than 1

• Example: SELECT column1, column2, COUNT(*) FROM table_name GROUP BY column1, column2 HAVING COUNT(*) > 1;

Test Case is a detailed set of conditions and steps to be followed to validate a particular functionality, while Test Scenario is a high-level description of the functionality to be tested.

• Test Case is detailed and specific, while Test Scenario is high-level and general.

• Test Case includes steps, expected results, and test data, while Test Scenario is more of an overview.

• Test Case is used for detailed testing, while Tes...read more

Bug life cycle is the process of a bug from identification to resolution in software testing.

• Bug is identified by tester

• Bug is reported in bug tracking tool

• Bug is assigned to developer

• Developer fixes the bug

• Bug is retested by tester

• Bug is closed if fixed or reopened if not