TOP ETL Testing Interview Questions and Answers
What is the uses of Lookup Transformation?
The Lookup Transformation is useful for:
1. Getting a related value form a table using a key column value
2. Update slowly changing dimension table
3. To check whether records already exists in the table.
What are the different sources of Source systems of Data Warehouse?
Types of Slowly Changing Dimensions:
Type – 1 (Recent updates)
Type – 11 (Full historical information)
Type – 111 (Partial historical information)
What are Update Strategy’s target table options?
Update as Update: Updates each row flagged for update if it exists in the table.
Update as Insert: Inserts a new row for each update.
Update else Insert: Updates if row exists, else inserts.
What does a Mapping document contains?
The Mapping document contains the following information :
Source Definition – from where the database has to be loaded
Target Definition – to where the database has to be loaded
Business Logic – what logic has to be implemented in staging area.
What does the Top Down Approach says?
The Top Down Approach is coined by Bill Immon. According to his approach he says “First we need to implement the Enterprisedata warehouse by extracting the data from individual departments and from the Enterprise data warehouse develop subject oriented databases called as “Data Marts”.
What does the Bottom Up Approach or Ralph Kimball Approach says?
The Bottom Down Approach is coined by Ralph Kimball. According to his approach he says “First we need to develop subject oriented database called as “Data Marts” then integrate all the Data Marts to develop the Enterprise data warehouse.
Who is the first person in the organization to start the Data Warehouse project?
The first person to start the Data Warehouse project in a organization is Business Analyst.
What is a Dimension Modeling?
A Dimensional Modeling is a high level methodology used to implement the start schema structure which is done by the Data Modeler.
What are the types of OLAPs ?
DOLAP: The OLAP tool which words with desktop databases are called as DOLAP. Example: Cognos EP 7 Series and Business Objects, Micro strategy.
ROLAP: The OLAP which works with Relational databases are called as ROLAP. Example: Business Object, Micro strategy, Cognos ReportNet and BRIO.
MOLAP: The OLAP which is responsible for creating multidimensional structures called cubes are called as MOLAP. Example: Cognos ReportNet.
HOLAP: The OLAP which uses the combined features of ROLAP and MOLAP are called as HOLAP. Example Cognos ReportNet.
What is worklet?
The worklet is a group of sessions. To execute the worklet we have to create the workflow.
Why we use lookup transformation?
Look up Transformations can access data from relational tables that are not sources in mapping. With Lookup transformation, we can accomplish the following tasks.
What is a Power Center Repository?
The Power Center Repository allows you to share metadata across repositories to create a data mart domain. In a data mart domain, you can create a single global repository to store metadata used across an enterprise and a number of local repositories to share the global metadata as needed.
What are snapshots? What are materialized views & where do we use them? What is a materialized view?
Materialized view is a view in which data is also stored in some temp table.i.e if we will go with the View concept in DB in that we only store query and once we call View it extract data from DB. But In materialized View data is stored in some temp tables.
Where do we use connected and unconnected lookups?
If return port only one then we can go for unconnected. More than one return port is not possible with Unconnected. If more than one return port then go for Connected.
If you require dynamic cache i.e where your data will change dynamically then you can go for connected lookup. If your data is static where your data won’t change when the session loads you can go for unconnected lookups.