Cognizant Interview Questions on Informatica

Star Schema in a data warehouse, in which the center of the star can have one fact table and a number of associated dimension tables. As its structure resembles a star so it is known as a star schema.

In the data warehouse, Snowflake Schema is a logical arrangement of tables in a multidimensional database

distinct property(keyword) is used to eliminate duplicate rows in Informatica from a database and a flat-file.

We can eliminate duplicate rows from the flat-file by using group by function in an aggregator or in source qualifier in database.

You can select distinct all or by using sorter transformation in flat-file.

You can remove duplicates values in Informatica from DB as follows:

You can use the Distinct option of the Source Qualifier in the source table and load the target accordingly.

You can remove duplicates values in Informatica from Flat-file as follows:

When the source system is a Flat File distinct option is disabled in the source qualifier. You may use a Sorter Transformation and check the Distinct option. Then all the columns will the selected as keys, in ascending order by default.

The different types of SCD are as follows:

Type 0 – Fixed Dimension. No changes allowed, dimension never changes.

Type 1 – No History. Update record directly, there is no record of historical values, only the current state.

Type 2 – Row Versioning.

Type 3 – Previous Value column.

Type 4 – History Table. Type 6 – Hybrid SCD.

Performance tuning is the improvement of system performance that starts with the identification of bottlenecks in the source, target, and mapping and further to session tuning. Performance tuning aimed to optimize session performance by eliminating performance bottlenecks to get a better acceptable ETL load time.

Lookup transformation is a kind of join operation. Lookup transformation is a passive transformation. It has one of the joining tables as the source data and the other joining table known as the lookup table. Basically, It is used to look up a source, source qualifier, or target to get the relevant data. It also returns the result of the lookup to the target or another transformation.

The difference between joiner and lookup transformation is as follows:

The different types of loading in Informatica are as follows:

• Normal loading
• Bulk loading

Normal loading loads record by record and writes log for that. Bulk loading loads the data fast and recovered easily.

Incremental data loading is defined as the process of loading the selective data. It is either updated or creates a new source system to the target system. This is different from a full data load where entire data is processed each load.

Normalizer, Cobol sources, XML sources, XML Source Qualifier transformations, Target definitions, Pre- and Post- session Stored Procedures, Other Mapplets are not supported by Mapplets.

A bridge table acts between a fact table and a dimension table. It is used to resolve many-to-many relationships between a fact and a dimension. The bridge table will contain only two dimension columns and key columns in both dimensions.