Informatica Interview Questions and Answers
Informatica is one of the leading software enterprises with a monopoly in the ETL services sector. Informatica’s user-friendly UI and its variety of tools make it one of the most efficient software programs on the market. Students and job seekers in the IT sector always aspire to land a job in ETL testing services or Informatica Corporation. So, we at SLA Jobs have curated some of the top questions that are frequently asked in Informatica interviews. These questions will help you become an expert in Informatica and ETL services.3
Informatica interview questions and answers
Describe how to design a mapping to load the first three rows from a flat file to a target (Informatica interview questions for 10 years of experience)
Row numbers have to be assigned for each record. Row numbers can be generated either by expression transformation or sequence generator transformation. Finally, after that, the output can be passed to the filter transformation and the filter condition should be specified as O_count<=3.
What are the output files created by the Informatica server during session running? (Informatica interview question for 5 years experience)
- Control file: Informatica servers will always create control and target files when a session is run using an external loader. Information about the target flat file, such as the data format and loading instructions for the external loader, is contained in the control file.
- Session detail file: The load statistics for each target in mapping are contained in the session detail file. Table name and number of rows are some of the information that is contained in the session detail. This file can be viewed by double-clicking on the session in the monitor window.
- Performance detail file: This file contains session performance details that inform the user on where he or she can improve their performance. To view this file, select the performance detail option in the session property sheet.
- Informatica server log: This server creates a log for all status and error messages. The default name is “pm.server.log.’ An error log is also created for error messages. These files will be created in the Informatica home directory.
- Session log file: A server log file is created for each session. Information about sessions is written into log files, such as the initialization process, creation of SQL commands for readers and writer threads, load summary, and errors encountered. The tracing level that is set initially influences the amount of detail in the session log file.
- Reject file: This contains rows of data that the user does not write to targets.
- Post-session email: post-session email allows the user to easily communicate with the designated recipient about information about the session run.
- Indicator file: The Informatica server can be configured to create an indicator file when the flat file is used as the target.
- Output file: The Informatica server creates a target file when a session writes to it. The target file is created based on file properties in the session property sheet.
- Cache files: Cache files are created along with memory cache by the Informatica server.
Click here to learn more about the Informatica course syllabus
What are the types of lookup transformations? (Informatica PowerCenter interview questions)
The following are the types of lookup transformations –
- Relational/flat file lookup – This will perform a lookup on a flat file or a relational table. A connected Lookup transformation receives source data, along with performing a lookup, and returns data to the pipeline too.
- Pipeline lookup – To perform a lookup on application sources such as JMS or MSMQ.
- An unconnected lookup transformation is not connected to a source or target. A transformation in the pipeline is called the Lookup transformation with a LKP expression. The unconnected Lookup transformation returns only one column to the calling transformation.
- Cached or un-cached lookup. It is possible to configure the lookup transformation to cache the lookup data or directly query the lookup source every time the lookup is invoked. If the lookup source is a flat file, then the lookup is always cached.
Is it possible to configure an SQL transformation to run a stored procedure?
Yes, it is indeed possible to configure an SQL transformation to run a stored procedure.
To configure an SQL transformation to run a stored procedure, perform the following tasks:
- Properties of transformation should be defined, including the database type to connect.
- A stored procedure should be imported to create the stored procedure call and define the ports.
- Ports should be manually defined to get result sets or to get additional stored procedures that one needs to run.
- Additional stored procedure calls should be added in the SQL editor.
- An SQL query can be configured in the transformation SQL editor. When running the SQL transformation, the transformation processes the query, returns rows, and returns any database error. Click here to learn more about SQL server training in Chennai
Enunciate the data processor transformation. (Informatica developer interview questions)
Unstructured and semi-structured file formats are processed in a mapping; this process is called data processor transformation. HTML, XML, JSON, and PDF are the formats that could be configured. Structured formats like ACCORD, HIPAA, EIFACT, and SWIFT can also be converted.
A data processor transformation usually contains multiple components to process data. Each component might contain other components.
For example, receiving customer invoices in Microsoft Word files. Configuring a data processor transformation to parse the data from each word file. Customer data should be extracted from the customer table. Order information should be extracted from the Orders table only.
When creating a data processor transformation, defining an XMap, script, or library is important. An XMap converts an input hierarchical file into an output hierarchical file of another structure. A library converts an industry messaging type into an XML document with a hierarchy structure or from XML to an industry-standard format. A script can parse source documents into a format of hierarchy, convert the hierarchical format to other file formats, or map a hierarchical document to another format of hierarchy.
What is a dimensional table, and explain its types? (Informatica interview questions for 10 years experience)
The table that is present in the star schema of the data warehouse is called the dimensional table. The purpose of a dimensional table is to describe dimensions.
Types of dimensional table
- Conformed dimensions: These dimensions are the same, with every possible fact being merged. Conformed dimensions maintain consistency.
- Slowly changing dimensions (SCD): Here, the dimension attributes slowly and gradually change in time as opposed to changing at regular intervals.
- Role-playing dimensions: These dimensions are used for multiple purposes within the same database.
- Degenerated Dimensions: These dimensions are derived from the fact table and do not have their own dimension table. The attributes here are stored in the fact table, not as a separate dimension table.
- Junk Dimension: These are a collection of attributes of low cardinality. Usually, they have different transactional code flags or text attributes unrelated to any other attribute. All of those junk attributes are stored in junk dimensions.
What are some of the other names for data warehouse systems? (Informatica interview questions for experienced candidates)
The following are some of the other names for data warehouse systems:
- Business intelligence solution
- Decision support systems
- Executive information system
- Management information system
- Data warehouse
- Analytic application
What is the DTM process?
DTM stands for data transformation manager process. It was initiated by the Informatica PowerCenter integration service to run a session. The DTM is mainly used to manage threads that carry out session tasks.
The following are the tasks for DTM:
- Reading the session information
- Validating code pages
- Running the processing threads
- Running the post-session operations
- Sending post-session emails
- Forming dynamic partitions
- Creating partition groups
Describe the types of lookup caches (Informatica interview questions and answers).
Look-up caches that have various types are explained below:
- Static cache: This cache remains unchanged during the processing of the lookup by the integration services, so the integration service needs to rebuild it every time.
- Persistent cache: Since this cache is different from the static cache, the integration services will save the initial form of the cache during the first lookup transformation process and use it next time. When the integration services process the lookup, the persistent caches do not change. Persistence caches can therefore be rebuilt using the initial saved form.
- Dynamic Cache: A dynamic lookup is a cache that changes while the integration service processes the lookup. A dynamic lookup cache is built based on the first lookup request made by the integration services when the lookup process is being done. During the processing of each row, the integration service dynamically inserts or updates data in the lookup cache and passes the data to the target. The dynamic cache is usually synced with the target. Dynamic cache can be used when one wants to update a target based on new and changed records. Dynamic caches can also be used when the mapping requires a lookup on target data, but connections to the target are slow.
- Shared Cache: Shared caches can be used in multiple lookup transformations in the same mapping itself. One cache is generated for each lookup transformation, as opposed to generating multiple lookup caches each time the lookup transformation is processed.
What makes Informatica PowerCenter distinct and user-friendly from other ETL enterprises? (Informatica PowerCenter interview questions)
The following are some of the unique features of Informatica:
- Capable ETL: Informatica PowerCenter is very capable of ETL. The extracting, transforming, and loading of data from a variety of sources makes the data easy to understand and manage. The transformation is based on business rules, and the loading of transformed data into target systems, such as data warehouses, databases, and cloud storage, is efficient.
- Data Integration: PowerCenter supports integration from a diverse variety of data sources and destinations, including relational databases, cloud services, flat files, mainframes, and more.
- Scalability: PowerCenter is designed to handle large-scale data integration projects, making it suitable for enterprises with diverse data integration needs.
- Connectivity: The tool provides a variety of connectors and adapters to connect with different data sources, facilitating smooth data movement.
- Workflow Automation: PowerCenter enables the creation of workflows to orchestrate and schedule data integration tasks. This automation enhances efficiency and reduces manual intervention, thereby saving time.
- Transformation: Pointervention Center offers a wide range of transformation functions for data cleansing, enrichment, aggregation, and more. Users can easily design and customize transformations based on specific business requirements using the PowerCenrter’s user-friendly UI.
- Performance Optimization: The platform has plenty of features like push-down optimization, parallel processing, and caching to enhance data processing speed and efficiency.
- Data Profiling and Quality: Things like data profiling and identifying data issues and anomalies are done in PowerCenter using facilitated tools included in Informatica, which enable the assessment and analysis of data quality and consistency.
- Monitoring and Management: Monitoring and management tools are provided by Informatica to track job execution, manage resources, and troubleshoot issues in real time.
Don’t forget to check out our ETL testing training in Chennai to learn more about the course.
Conclusion
Informatica is one of the most efficient and leading players in the ETL services industry. ETL, data warehousing, and Informatica are very much in demand among the information technology giants. Students and job seekers in IT can be free from any worry and concern once they study all of the above interview questions and answers and can secure a prominent position in ETL, data warehousing tools, and Informatica PowerCenter enterprises. Check out our Informatica training institute in Chennai to enroll and secure your future.