Norstella ETL Tester Interview Questions and Answers

Project architecture refers to the overall structure of a software project, including components, modules, and their interactions.

• Project architecture defines how different components of a software project are organized and interact with each other.

• It includes the high-level design of the system, such as the database structure, data flow, and integration points.

• Project architecture also involves decisions on technologi...read more

Challenges in ETL testing include data quality issues, complex transformations, and handling large volumes of data.

• Data quality issues such as missing or incorrect data can impact testing results

• Complex transformations can be difficult to validate and may lead to errors in the ETL process

• Handling large volumes of data can slow down testing processes and require efficient testing strategies

Roles and responsibilities refer to the tasks and duties assigned to an individual within a specific job or position.

• Roles define the position or job title of an individual within an organization.

• Responsibilities outline the specific tasks, duties, and expectations associated with that role.

• Roles and responsibilities help clarify expectations, define boundaries, and ensure accountability.

• Examples include testing data e...read more

Use a dictionary to find duplicate values in an array of strings in Python.

• Create an empty dictionary to store the count of each string in the array.

• Iterate through the array and for each string, check if it exists in the dictionary. If it does, increment the count. If not, add it to the dictionary with a count of 1.

• After iterating through the array, check the dictionary for any strings with a count greater than 1. The

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;

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;

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

A deferred bug is a bug that is not fixed immediately but is scheduled to be fixed in a future release.

• Deferred bugs are usually lower priority bugs that do not impact the core functionality of the system.

• These bugs are documented and tracked for resolution in a later release.

• Examples of deferred bugs include cosmetic issues, minor usability problems, or non-critical functionality issues.

Use SQL query with ORDER BY and LIMIT to get 4th highest row based on a column

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

• Use LIMIT 1 OFFSET 3 to get the 4th highest row

• Example: SELECT * FROM table_name ORDER BY column_name DESC LIMIT 1 OFFSET 3

Inner join combines rows from two tables based on a related column between them.

• Inner join returns only the rows that have matching values in both tables

• It is used to retrieve data that exists in both tables

• Example: SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

Cross join combines each row from one table with each row from another table.

• Cross join produces a Cartesian product of the two tables involved.

• It does not require any common columns between the two tables.

• It can result in a large number of rows if the tables are big.

• Example: SELECT * FROM table1 CROSS JOIN table2;

Different types of data validation and checks in ETL testing include schema validation, data completeness checks, data transformation checks, and data quality checks.

• Schema validation ensures that the data conforms to the expected structure and format.

• Data completeness checks verify that all expected data is present and accounted for.

• Data transformation checks ensure that data is accurately transformed according to bus...read more

Slowly Changing Dimensions (SCD) include Type 1, Type 2, and Type 3 dimensions.

• Type 1: Overwrite existing data with new data, no history is kept.

• Type 2: Create a new record for each change, maintaining history.

• Type 3: Create a new attribute to store changes, keeping limited history.

Snowflake schema is a normalized form of star schema with additional dimension tables.

• Snowflake schema is a data modeling technique used in data warehousing.

• In snowflake schema, dimensions are normalized into multiple related tables.

• Snowflake schema reduces redundancy and improves data integrity.

• Star schema is a denormalized form of snowflake schema with a single dimension table.

• In star schema, dimensions are not norma...read more