Horizon Architects ETL Tester Interview Questions and Answers

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

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.

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.

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

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

Test data creation types include manual, automated, random, boundary, and negative testing.

• Manual testing involves creating data by hand

• Automated testing uses tools to generate data

• Random testing involves creating data randomly

• Boundary testing involves testing data at the limits of its range

• Negative testing involves testing invalid or unexpected data

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 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));

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

I will analyze the root cause and work with the team to fix the issue and prevent future occurrences.

• Analyze the bug and identify the root cause

• Work with the team to fix the issue

• Implement measures to prevent future occurrences

• Communicate the issue and resolution to stakeholders

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