Horizon Architects ETL Tester Interview Questions and Answers

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 i

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

The question is asking to rearrange the numbers so that 100 is at the first row and the rest are below it.

• Create an array of strings to store the numbers

• Assign the value '100' to the first element of the array

• Use a loop to assign the numbers 1 to 99 to the remaining elements of the array

Set and join are operations used in database management systems.

• Set is used to perform mathematical set operations like union, intersection, and difference on two or more sets.

• Join is used to combine rows from two or more tables based on a related column between them.

• Set operations do not require a common column, while join operations do.

• Set operations return a result set with unique values, while join operations retur...read more

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.

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

I worked on a data migration project for a retail company.

• Migrated data from legacy systems to a new ETL platform

• Ensured data integrity and accuracy during the migration process

• Created test cases and executed them to validate the data migration

• Collaborated with developers and business analysts to troubleshoot any issues

• Provided regular updates to stakeholders on the progress of the project

Types of SCD include Type 1, Type 2, Type 3, and Type 4.

• Type 1 - Overwrite: Old record is replaced with new data.

• Type 2 - Add new row: New record is added with a new surrogate key.

• Type 3 - Update: New column is added to track changes.

• Type 4 - Hybrid: Combination of Type 1 and Type 2.

Scalar function is a function that returns a single value based on input parameters.

• Scalar functions are used in SQL to perform calculations on input values and return a single result.

• They can be used in SELECT statements, WHERE clauses, and other SQL queries.

• An example of a scalar function is the ABS() function, which returns the absolute value of a number.

Use regular expressions to extract numbers from alphanumeric data in a table.

• Use the REGEXP_REPLACE function in SQL to replace all non-numeric characters with an empty string.

• For example, SELECT REGEXP_REPLACE(column_name, '[^0-9]', '') FROM table_name;

• This will return only the numbers from the alphanumeric data in the specified column.

Query to fetch data greater than average, min and max salary.

• Calculate average, min, and max salary first

• Use a SELECT statement with WHERE clause to filter data greater than average, min, and max salary