30+ Informatica Developer Interview Questions and Answers

If we don't specify anything in group by aggregator, it will return the result without any grouping.

• The result will contain all the rows from the input without any aggregation.

• The output will have the same number of rows as the input.

• No grouping will be performed on any column.

• The output will be similar to a select statement without a group by clause.

Answers to questions related to Informatica Developer role.

• Factless fact is a fact table that does not have any measures.

• Star schema has a single fact table and multiple dimension tables, while snowflake schema has multiple dimension tables that are further normalized.

• Oracle joins include inner join, left join, right join, and full outer join.

Update strategy is a transformation used to flag rows for insert, update, delete or reject.

• Update strategy is used to control the flow of data in a mapping.

• It is used to flag rows for insert, update, delete or reject.

• It can be used to update a target table based on a source table.

• It can be used to insert new rows into a target table.

• It can be used to delete rows from a target table.

• It can be used to reject rows that do not meet certain criteria.

IICS is a cloud-based integration platform while Power Center is an on-premise data integration tool.

• IICS is a subscription-based service while Power Center is a perpetual license product.

• IICS offers pre-built connectors to various cloud applications while Power Center requires custom coding for cloud integrations.

• IICS has a web-based interface while Power Center has a desktop-based interface.

• IICS offers real-time data integration while Power Center is more batch-oriented.

• IIC...read more

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

• Active transformations can filter, update, or modify the number of rows in a data stream (e.g. Filter, Router, Update Strategy).

• Passive transformations do not change the number of rows in a data stream, they only allow data to pass through unchanged (e.g. Expression, Lookup, Sequence Generator).

Different types of lookup caches include persistent cache, dynamic cache, and shared cache.

• Persistent cache stores lookup data in a flat file or database table for reuse across sessions.

• Dynamic cache dynamically loads lookup data into memory as needed during a session.

• Shared cache allows multiple sessions to share the same cache for improved performance.

Router transformation sends data to multiple targets based on conditions, while filter transformation filters rows based on conditions.

• Router transformation can send data to multiple targets based on conditions

• Filter transformation filters rows based on conditions

• Router transformation can be used to route data to different tables based on certain criteria

• Filter transformation can be used to remove unwanted rows from the data flow

Informatica is a data integration tool used for extracting, transforming, and loading data from various sources.

• Used for data integration and ETL processes

• Supports various data sources such as databases, flat files, and cloud applications

• Provides a graphical user interface for designing and managing data integration workflows

• Offers advanced features such as data profiling, data quality, and metadata management

I have used various transformations in Informatica such as Filter, Router, Expression, Aggregator, Joiner, Lookup, and Sorter.

• Filter transformation is used to filter rows based on a condition.

• Router transformation is used to route data to different targets based on conditions.

• Expression transformation is used to perform calculations or manipulate data.

• Aggregator transformation is used to perform aggregate calculations like sum, average, etc.

• Joiner transformation is used to jo...read more

Sorter can be active or inactive depending on the configuration.

• Sorter transformation can be configured to be active or passive.

• If active, it sorts the data based on specified criteria.

• If passive, it passes the data through without sorting.

• Sorter can be used to remove duplicates from data.

• Sorter can also be used to sort data in ascending or descending order.

To connect a session using Unix, you can use the pmcmd command with appropriate arguments.

• Use the pmcmd command followed by the connect option to connect to the Informatica session.

• Provide the necessary connection details such as domain, repository, username, and password.

• You can also specify the session name and folder path if needed.

• Example: pmcmd connect -sv MyInformaticaServer -d MyDomain -u MyUsername -p MyPassword

Database is a collection of related data while data warehouse is a large collection of data used for business analysis.

• Database is used for transactional processing while data warehouse is used for analytical processing.

• Database is designed for day-to-day operations while data warehouse is designed for decision-making purposes.

• Database is usually smaller in size while data warehouse is much larger in size.

• Database is normalized while data warehouse is denormalized for faster ...read more

Caches are temporary storage areas that hold frequently accessed data for quick access.

• There are several types of caches including CPU cache, disk cache, browser cache, and DNS cache.

• CPU cache stores frequently accessed data from RAM for faster access.

• Disk cache stores frequently accessed data from hard disk for faster access.

• Browser cache stores frequently accessed web pages, images, and other resources for faster loading.

• DNS cache stores frequently accessed domain name info...read more

Look up is used to retrieve data from a single source, while joiner is used to combine data from multiple sources.

• Look up is used to search for a value in a data source and return a corresponding value from the same row.

• Joiner is used to combine data from two or more sources based on a common key.

• Look up is used for one-to-one mapping, while joiner is used for many-to-one or one-to-many mapping.

To find the 2nd or 3rd highest salary in SQL, use the 'ORDER BY' and 'LIMIT' clauses.

• Use the 'ORDER BY' clause to sort the salaries in descending order.

• Use the 'LIMIT' clause to specify the number of rows to return.

• For 2nd highest salary: SELECT salary FROM employees ORDER BY salary DESC LIMIT 1,1

• For 3rd highest salary: SELECT salary FROM employees ORDER BY salary DESC LIMIT 2,1

Cubes are multidimensional structures used in OLAP (Online Analytical Processing) to analyze data from multiple perspectives.

• Cubes store aggregated data for faster analysis

• They allow users to drill down into data to analyze at different levels of granularity

• OLAP cubes are used in business intelligence for complex data analysis

• Examples include sales cubes, finance cubes, and customer analysis cubes

Hive is a data warehouse infrastructure built on top of Hadoop for providing data summarization, query, and analysis.

• Hive is used for managing and querying large datasets stored in Hadoop Distributed File System (HDFS).

• It provides a SQL-like interface called HiveQL for querying data.

• Hive organizes data into tables, partitions, and buckets to optimize queries.

• It supports custom MapReduce scripts for complex data processing tasks.

• Example: CREATE TABLE employees (id INT, name ST...read more

IICS is a cloud-based data integration platform while Power Centre is an on-premise data integration tool.

• IICS is a newer platform compared to Power Centre

• IICS offers more advanced features such as AI and machine learning capabilities

• Power Centre is more suitable for large-scale data integration projects

• IICS is more flexible and scalable due to its cloud-based nature

• Power Centre requires more hardware and infrastructure to run

• Both platforms offer similar functionalities such ...read more

ETL pipeline stands for Extract, Transform, Load pipeline used to extract data from various sources, transform it, and load it into a data warehouse.

• ETL pipeline involves extracting data from multiple sources such as databases, files, APIs, etc.

• The extracted data is then transformed by applying various operations like cleaning, filtering, aggregating, etc.

• Finally, the transformed data is loaded into a data warehouse or target system for analysis and reporting.

• Example: Extract...read more

Filter is used to pass or reject data based on conditions, while router is used to route data to different targets based on conditions.

• Filter is used to include or exclude data based on specified conditions.

• Router is used to direct data to different targets based on specified conditions.

• Filter is typically used to remove unwanted data, while router is used to split data flow.

• Example: Using filter to exclude records with sales less than $1000. Using router to send high priorit...read more

Secure agent is used to securely transfer data between on-premises and cloud applications.

• Secure agent provides a secure connection between on-premises and cloud applications.

• It ensures data privacy and security during transfer.

• It allows for data integration and synchronization across different systems.

• Examples of applications that use secure agent include Salesforce, Workday, and SAP.

• Secure agent also allows for scheduling and monitoring of data transfers.

Analytical functions are used to perform calculations across a set of rows related to the current row.

• Analytical functions operate on a group of rows and return a single result for each row

• They can be used to calculate running totals, moving averages, rank, percentiles, etc.

• Examples include ROW_NUMBER(), RANK(), SUM() OVER(), AVG() OVER()

SCD2 logic in Informatica is used to track historical changes in data by creating new records for each change.

• SCD2 stands for Slowly Changing Dimension Type 2

• It involves creating new records for each change in data, while maintaining a link to the previous record

• It typically includes effective start and end dates to track the validity of each record

• SCD2 logic is commonly used in data warehousing to maintain historical data accurately

Use 'mail' command in Unix shell script to send emails.

• Use 'mail' command followed by recipient email address

• Include subject with -s flag and message body with -m flag

• Example: mail -s 'Subject' recipient@example.com -m 'Message body'

A mapplet is a reusable object in Informatica PowerCenter that contains a set of transformations.

• Mapplets can be used in multiple mappings to perform the same set of transformations.

• They can simplify mapping development by encapsulating common logic.

• Mapplets can have input and output ports to connect with other transformations.

To delete duplicate rows, we can use the DISTINCT keyword or GROUP BY clause in SQL.

• Use the DISTINCT keyword to select unique rows from a table.

• Use the GROUP BY clause to group rows with the same values and select only one row from each group.

• Use the HAVING clause with GROUP BY to filter out groups with more than one row.

• In Informatica, use the Sorter transformation to sort the data and then use the Aggregator transformation with the Group By option to remove duplicates.

Secure Agent is a lightweight program that runs on-premises or in the cloud to securely connect to data sources.

• Secure Agent is used in Informatica Cloud to securely connect to on-premises data sources

• It is a lightweight program that can be installed on-premises or in the cloud

• Secure Agent uses SSL/TLS encryption to secure data in transit

• It can be configured to run on a schedule or triggered by an event

• Examples of data sources that can be accessed using Secure Agent include d...read more

PowerCenter is an on-premise data integration tool, while IICS is a cloud-based integration platform.

• PowerCenter is on-premise, IICS is cloud-based

• PowerCenter is a traditional ETL tool, IICS is a modern integration platform

• PowerCenter requires infrastructure setup, IICS is managed by Informatica

• PowerCenter is more suitable for large enterprises with on-premise data, IICS is better for cloud-based integrations

Char is fixed length string data type, while varchar is variable length string data type.

• Char stores fixed length strings, while varchar stores variable length strings.

• Char pads with spaces to reach fixed length, while varchar does not pad.

• Char is faster for fixed length data, while varchar is more flexible for variable length data.

Types of transformations include expression, filter, aggregator, join, lookup, and more.

• Expression transformation: Perform calculations or manipulate data

• Filter transformation: Filter rows based on specified conditions

• Aggregator transformation: Perform aggregate calculations like sum, average, etc.

• Join transformation: Combine data from multiple sources based on a common key

• Lookup transformation: Retrieve data from a relational table based on a lookup condition

Star and snowflake are two types of database schema used in data warehousing.

• Star schema is simpler and easier to understand.

• Snowflake schema is more normalized and reduces data redundancy.

• Star schema is better for simple queries and reporting.

• Snowflake schema is better for complex queries and analysis.

• Star schema has fewer tables and is faster for querying.

• Snowflake schema has more tables and is slower for querying.

• Star schema is denormalized while snowflake schema is normal...read more

Truncate removes all data from a table while delete removes specific rows.

• Truncate is faster than delete as it doesn't log individual row deletions

• Truncate cannot be rolled back while delete can be

• Truncate resets the identity of the table while delete doesn't

• Truncate doesn't fire triggers while delete does

SQL joins are used to combine rows from two or more tables based on a related column between them.

• Types of SQL joins include: INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN)

• INNER JOIN returns rows when there is at least one match in both tables

• LEFT JOIN returns all rows from the left table and the matched rows from the right table

• RIGHT JOIN returns all rows from the right table and the matched rows from the left table

Types of caches include memory cache, disk cache, and browser cache.

• Memory cache stores data in memory for quick access.

• Disk cache stores data on the hard drive for faster retrieval.

• Browser cache stores web page elements to reduce loading times.

• Other types include CPU cache, page cache, and object cache.