Data Warehouse Interview Questions and Answers
Question - 41 : - What needs to be done while starting the database?
Answer - 41 : -
Following need to be done to start the database:
- Start an Instance
- Mount the database
- Open the database
Question - 42 : - What needs to be done when the database is shutdown?
Answer - 42 : -
Following needs to be done when the database is shutdown:
- Close the database
- Dismount the database
- Shutdown the Instance
Question - 43 : - Can we take backup when the database is opened?
Answer - 43 : -
Yes, we can take full backup when the database is opened.
Question - 44 : - What is defined as Partial Backup?
Answer - 44 : -
A Partial backup in an operating system is a backup short of full backup and it can be done while the database is opened or shutdown.
Question - 45 : - What is the goal of Optimizer?
Answer - 45 : -
The goal to Optimizer is to find the most efficient way to execute the SQL statements.
Question - 46 : - What is Execution Plan?
Answer - 46 : -
Execution Plan is a plan which is used to the optimizer to select the combination of the steps.
Question - 47 : - What are the approaches used by Optimizer during execution plan?
Answer - 47 : -
There are two approaches:
Question - 48 : - What are the tools available for ETL?
Answer - 48 : -
Following are the ETL tools available:
- Informatica
- Data Stage
- Oracle
- Warehouse Builder
- Ab Initio
- Data Junction
Question - 49 : - What is the difference between metadata and data dictionary?
Answer - 49 : -
Metadata is defined as data about the data. But, Data dictionary contain the information about the project information, graphs, abinito commands and server information.
Question - 50 : - What are the different types of SCD?
Answer - 50 : -
There are six sorts of Slowly Changing Dimension that are commonly used. They are as follows:
Type 0 – Dimension never changes here, dimension is fixed, and no changes are permissible.
Type 1 – No History Update record directly. There’s no record of historical values, only the current state. A kind 1 SCD always reflects the newest values, and when changes in source data are detected, the dimension table is overwritten.
Type 2 – Row Versioning Track changes as version records which will be identified by current flag & active dates and other metadata. If the source system doesn’t store versions, then it’s usually the info warehouse load process that detects changes and appropriately manages the change during a dimension table.
Type 3 – Previous Value column Track change to a selected attribute, add a column to point out the previous value, which is updated as further changes occur.
Type 4 – History Table shows the current value in the dimension table. All changes are tracked and stored in a separate table.
Hybrid SCD – Hybrid SDC utilizes techniques from SCD Types 1, 2, and three to trace change.
Only types 0, 1, and a couple of are widely used, while the others are applied for very specific requirements.