Most commonly asked Data Warehouse & Informatica ETL Interview Questions with Answer: Set 2

Most commonly asked Data Warehouse & Informatica ETL Interview Questions with Answer: Set 2

21.What is Normalization?

Normalization is a process to splits up the data into additional tables.

22. How many fact tables are there in a star schema?

Only fact table.

23.Out of star schema and snowflake schema, whose dimension table is normalized?

Snowflake schema uses the concept of normalization.

24.What is the benefit of normalization?

Helps in reducing data redundancy.

25.Which language is used for defining Schema Definition?

Data Mining Query Language (DMQL).

26.What is snow-flake schema?

Snow-flake schemais process of normalization to splits up the data into additional tables.

27.What are the different types of dimension?

Conformed Dimension, Junk Dimension, Degenerated Dimension, Role-playing dimension.

28.What is junk dimension?

Collection of random transactional codes flags and/or text attributes that are unrelated to any particular dimension.

29.What is SCD?

Slowly Changing Dimensions (SCD) - dimensions that change slowly over time rather than changing on regular schedule, time-base.

30.What is rapidly changing dimension?

Rapidly Changing Dimensions - dimensions that change rapidly over time, changing on regular schedule, time-base.

31.what are the different types of slowly changing Dimension (SCD)

Type 1: The new record replaces the original record. No trace of the old record exists.

Type 2: A new record is added into the customer dimension table.Thereby, the customer is treated essentially as two people.

Type 3: The original record is modified to reflect the change.

32.What is a mini dimension?

Slowly changing dimension type 4 is used when a group of attributes in a dimension rapidly changes and is split off to a new dimension. That new dimension is known as a mini–dimension.

33.What is ETL?

E: Extract, T: Transform, L: Load.

34.Explain what are the ETL testing operations includes?

Verify whether the data is transformed correctly according to business requirements and projected data is loaded into the data warehouse without any truncation and data loss. ETL make sure that ETL application reports invalid data and replaces with default values, make sure that data loads at expected time frame to improve scalability and performance.

35.what is the difference between data mining and data warehousing?

A data warehouse is a data store that contains historical data, whereas Data Mining is a means by which you search/analyze data to derive insights or answer questions.

36.What are the various tools used in ETL?

Ab Initio,Adeptia ETL, Alooma, Business Objects Data Services, Business Objects Data Integrator (BODI), Confluent, DBSoftLab, InformaticaPowerCenter, Information Server, Integration Services, iWay Software, Oracle Data Integrator (ODI), Oracle Warehouse Builder (OWB), Pervasive ETL, SAS ETL, Sybase ETL.

37.Explain what is transformation?

Transformation is the process of converting data from one format or structure into another format or structure. It is a fundamental aspect of most data integration and data management tasks such as data wrangling, data warehousing, data integration and application integration.

38.What are views?

The top-down view, The data source view, The data warehouse view and The business query view.

39.What is mapping in ETL?

Data mapping is a process used in data warehousing by which different data models are linked to each other using a defined set of methods to characterize the data in a specific definition.

40.What the ODS in ETL generates?

ODS generates primary keys, handles errors and rejects similar to Data warehouse.