Cognizant ETL Tester Interview Questions and Answers

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

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

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

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

SCD stands for Slowly Changing Dimensions. There are three types of SCD: Type 1, Type 2, and Type 3.

• Type 1: Overwrites old data with new data.

• Type 2: Creates a new record for new data and keeps the old record for historical data.

• Type 3: Creates a new column for new data and keeps the old column for historical data.

Fact table is a table in a data warehouse that stores quantitative data about a business process.

• Contains foreign keys to dimension tables

• Stores numerical data such as sales, revenue, etc.

• Used for analysis and reporting

• Can have multiple fact tables in a data warehouse

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

The schema used in my project was a star schema.

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

• The fact table contains the measurements or metrics of the business process, while the dimension tables provide context and descriptive attributes.

• This schema is commonly used in data warehousing and business intelligence applications.

• Example: In a sales ana...read more

A data mart is a subset of a larger data warehouse that is designed to serve a specific business unit or department.

• Contains a subset of data from a larger data warehouse

• Designed to serve a specific business unit or department

• Provides a more focused view of data for analysis and reporting

• Can be created using a top-down or bottom-up approach

• Examples include sales data mart, marketing data mart, finance data mart