Cognizant ETL Tester Interview Questions, Process, and Tips for Experienced

Unique key allows null values while primary key does not.

• Primary key is a unique identifier for a record in a table.

• Unique key allows null values but primary key does not.

• A table can have only one primary key but multiple unique keys.

• Example: Employee ID can be a primary key while email can be a unique key.

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

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.

ETL architecture refers to the design and structure of the ETL process.

• ETL architecture involves three main components: extraction, transformation, and loading.

• Data is extracted from various sources, transformed to fit the target system, and loaded into the target database.

• ETL architecture can be implemented using different tools and technologies, such as ETL software, data integration platforms, and cloud-based soluti...read more

A fact is a piece of information that is known to be true or proven through evidence.

• Facts are objective and can be verified through research or observation.

• Facts are not opinions or beliefs.

• Examples of facts include the boiling point of water, the population of a city, and historical events that have been documented.

• Facts can be used to support arguments or conclusions.

• Facts can change over time as new information is

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

SCD types are used to track changes in data over time in a data warehouse.

• 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 each change and keeps a history of changes.

• Type 3 keeps both old and new data in the same record.

• SCD types are important for maintaining data integrity and accuracy in a data w

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

Prerequisites for ETL testing include understanding of data warehousing concepts, SQL, and ETL tools.

• Understanding of data warehousing concepts

• Proficiency in SQL

• Familiarity with ETL tools such as Informatica, Talend, or SSIS

• Knowledge of data mapping and transformation

• Ability to write test cases and execute them

• Experience in data validation and reconciliation

• Understanding of data quality and data profiling

• Knowledge of s...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

A data repository is a centralized location where data is stored, managed, and maintained.

• It is used to store and manage data in a structured manner

• It can be a database, data warehouse, or data lake

• It allows for easy access and retrieval of data

• Examples include Hadoop Distributed File System (HDFS), Amazon S3, and Oracle Database

To identify latest record in SCD, check the effective end date column.

• Look for the record with the latest effective end date

• Effective end date column should have the maximum date value

• If there are multiple records with the same effective end date, choose the one with the latest modified date