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

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

Pushdown automata is a type of automata that can recognize context-free languages.

• Pushdown automata is a theoretical model of computation.

• It has a stack that can be used to store and retrieve information.

• It is used to recognize context-free languages.

• It is more powerful than finite automata but less powerful than Turing machines.

• Examples of context-free languages include arithmetic expressions and programming languages.

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

Connected and unconnected are two types of transformations in Informatica ETL.

• Connected transformation receives input directly from the previous transformation in the pipeline.

• Unconnected transformation does not receive input directly from the previous transformation and is called using a function.

• Connected transformation can be used in both active and passive pipelines.

• Unconnected transformation can only be used in active pipelines.

• Examples of connected transformations inclu...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.