Oracle Extract transform load (ETL) Interview Preparation Guide
Download PDF

Oracle Extract transform load (ETL) Interview Questions and Answers will guide us that Extract, transform, and load (ETL) is a process in database usage and especially in data warehousing that involves Extracting data from outside sources, Transforming it to fit operational needs (which can include quality levels), Loading it into the end target, So learn Oracle ETL with the help of this Extract transform load (ETL) Oracle Interview Questions with Answers guide

39 Oracle ETL Questions and Answers:

1 :: What are the various tools? Name a few?

A few more
- Cognos Decision Stream
- Oracle Warehouse Builder
- Business Objects XI (Extreme Insight)
- SAP Business Warehouse
- SAS Enterprise ETL Server

Along with the above, need to include the below tools --Informatica --Abintio --DataStage

2 :: What are snapshots? What are materialized views & where do we use them? What is a materialized view?

Materialized view is a view in wich 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.

3 :: What is fact less fact table? where you have used it in your project?

Fact less table means only the key available in the Fact there is no measures available.

factless fact table means that contains only foreign keys with out any measures example:attendance report of employees in a particular company contains no measures only

4 :: Can we look-up a table from source qualifier transformation. ie. unconnected lookup?

You cannot lookup from a source qualifier directly. However, you can override the SQL in the source qualifier to join with the lookup table to perform the lookup.

5 :: Where do we use connected and un connected 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 connceted lookup.If your data is static where your data won't change when the session loads you can go for unconnected lookups

6 :: Where do we use semi and non additive facts?

Additve: A masure can participate arithmatic calulatons using all or any demensions.

Ex: Sales profit

Semi additive: A masure can participate arithmatic calulatons using some demensions.

Ex: Sales amount

Non Additve:A masure can't participate arithmatic calulatons using demensions.

Ex: temparature

7 :: What are non-additive facts in detail?

A fact may be measure, metric or a dollar value. Measure and metric are non additive facts.

Dollar value is additive fact. If we want to find out the amount for a particular place for a particular period of time, we can add the dollar amounts and come up with the total amount.

A non additive fact, for eg measure height(s) for 'citizens by geographical location' , when we rollup 'city' data to 'state' level data we should not add heights of the citizens rather we may want to use it to derive 'count'

8 :: What is a staging area? Do we need it? What is the purpose of a staging area?

Data staging is actually a collection of processes used to prepare source system data for loading a data warehouse. Staging includes the following steps:

Source data extraction, Data transformation (restructuring),

Data transformation (data cleansing, value transformations),

Surrogate key assignments

9 :: What is a three tier data warehouse?

A data warehouse can be thought of as a three-tier system in which a middle system provides usable data in a secure way to end users. On either side of this middle system are the end users and the back-end data stores.

10 :: What are the various methods of getting incremental records or delta records from the source systems?

One foolproof method is to maintain a field called 'Last Extraction Date' and then impose a condition in the code saying 'current_extraction_date > last_extraction_date'.