10+ Informatica ETL Developer Interview Questions and Answers

Delete removes specific rows from a table, truncate removes all rows, and drop removes the entire table.

• Delete is a DML operation, while truncate and drop are DDL operations.

• Delete is slower as it logs individual row deletions, while truncate and drop are faster.

• Delete can be rolled back, while truncate and drop cannot be rolled back.

• Delete can have conditions to specify which rows to delete, while truncate and drop affect the entire table.

• Example: DELETE FROM table_name WHER...read more

A SQL query to find duplicates in a table.

• Use the GROUP BY clause to group the records by the columns that may have duplicates.

• Use the HAVING clause to filter the groups that have more than one record.

• Select the columns that you want to display in the result.

• Order the result if necessary.

Use SQL merge statement to update table without update strategy

• Use SQL merge statement to update table

• Specify the source and target tables

• Define the join condition

• Specify the columns to update

• Use WHEN MATCHED THEN UPDATE statement

• Use WHEN NOT MATCHED THEN INSERT statement

OLTP and OLAP are two different types of databases used in data warehousing. Data warehousing involves the process of creating a data warehouse and data marts to store and analyze data throughout its lifecycle.

• OLTP (Online Transaction Processing) databases are used for transactional processing, while OLAP (Online Analytical Processing) databases are used for analytical processing.

• Data warehousing involves the process of extracting data from various sources, transforming it in...read more

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 from the result set, 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.

• Union is slower than Union All because it performs a distinct operation, while Union All simply concatenates the result sets.

• Use Union w...read more

Filter transformation is used to filter rows from a data source based on specified conditions.

• Filter transformation is an active transformation in Informatica PowerCenter.

• It allows you to define conditions to filter rows from the source data.

• Rows that meet the filter conditions are passed to the next transformation, while others are dropped.

• Filter transformation can be used to eliminate unwanted data or select specific data based on criteria.

• Conditions can be simple or comple...read more

Active transformations change the number of rows that pass through it, while passive transformations do not.

• Active transformations change the data stream by adding, deleting, or modifying rows

• Passive transformations do not change the data stream, but may change the data within the stream

• Active transformations include Filter, Router, Update Strategy, and Expression

• Passive transformations include Aggregator, Joiner, Lookup, and Sorter

An analytical syntax example is SELECT COUNT(*) FROM table_name;

• Analytical syntax is used to retrieve and analyze data from databases

• Commonly used in business intelligence and data warehousing

• Other examples include GROUP BY, ORDER BY, and JOIN statements

Join is used to combine data from two or more tables based on a common column, while Source Qualifier is used to filter and sort data from a single table.

• Join combines data from multiple tables, while Source Qualifier filters and sorts data from a single table.

• Join can be inner, outer, left, right, or full, while Source Qualifier can only filter and sort data.

• Join requires at least one common column between the tables being joined, while Source Qualifier does not require any ...read more

Incremental loading is a process of updating only the new or changed data in a target system.

• It reduces the time and resources required for data integration.

• It ensures that the target system is always up-to-date with the latest data.

• It involves comparing the source and target data to identify new or changed records.

• Examples include loading new customer orders or updating existing customer information.

The Aggregator transformation in Informatica is used to perform aggregate calculations on data, like sums and averages.

• Used to calculate aggregate values such as SUM, AVG, COUNT, MIN, and MAX.

• Can group data based on specified columns, allowing for multi-level aggregation.

• Example: Calculating total sales per region by grouping on 'Region' and summing 'Sales Amount'.

• Supports multiple output groups for different aggregate calculations.

• Can handle both sorted and unsorted data, bu...read more