Most Asked Data Warehouse Interview Questions and Answers

Data Warehouse Interview Questions and Answers most commonly asked for Experienced PDF, Freshers candidates for Employment.

What is a Redo Log?

The set of Redo Log files YSDATE, UID, USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.

What are the steps involved in Database Shutdown?

Close the Database; Dismount the Database and Shutdown the Instance.

For faster process, what we will do with the Universe?

For a faster process create aggregate tables and write better sql so that the process would fast.

What are Data Marts?

A data mart is a collection of tables focused on specific business group/department. It may have multi-dimensional or normalized. Data marts are usually built from a bigger data warehouse or from operational data.

What is critical column?

Let us take one ex: Suppose ‘XYZ’ is customer in Bangalore, he was residing in the city from the last 5 years, in the period of 5 years he has made purchases worth of 3 lacs. Now, he moved to ‘HYD’. When you update the ‘XYZ’ city to ‘HYD’ in your Warehouse, all the purchases by him will show in city ‘HYD’ only. This makes warehouse inconsistent. Here CITY is the Critical Column. Solution is use Surrogate Key.

What is Difference between E-R Modeling and Dimensional Modeling?

Basic difference is E-R modeling will have logical and physical model. Dimensional model will have only physical model. E-R modeling is used for normalizing the OLTP database design.Dimensional modeling is used for de-normalizing the ROLAP/MOLAP design.

What is a Hash Cluster?

A row is stored in a hash cluster based on the result of applying a hash function to the row’s cluster key value. All rows with the same hash key value are stores together on disk.

What are the types of Synonyms?

There are two types of Synonyms Private and Public

What are the advantages of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG mode?

Complete database recovery from disk failure is possible only in ARCHIVELOG mode. Online database backup is possible only in ARCHIVELOG mode.

What is type 2 version dimension?

Version dimension is the SCD type II in real time it using because of it will maintain the current data and full historical data.

What is the data type of the surrogate key?

There is no data type for a Surrogate Key. Requirement of a surrogate Key: UNIQUE Recommended data type of a Surrogate key is NUMERIC.

What is the main difference between star and snowflake star schema? Which one is better and why?

If u have one to may relation ship in the data then only we choose snowflake schema, as per the performance-wise every-one go for the Star schema. Moreover, if the ETL is concerned with reporting means choose for snowflake because this schema provides more browsing capability than the former schema.

What is conformed fact?

Conformed dimensions are the dimensions, which can be used across multiple Data Marts in combination with multiple facts tables accordingly.

Describe Referential Integrity?

A rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns (the dependent value) matches a value in a column of a related table (the referenced value). It also specifies the type of data manipulation allowed on referenced data and the action to be performed on dependent data as a result of any action on referenced data.

What are the Referential actions supported by FOREIGN KEY integrity constraint?

Update And Delete Restrict – A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade – When a referenced row is deleted all associated dependent rows are deleted.

What are the different modes of mounting a Database with the Parallel Server?

Exclusive Mode If the first instance that mounts a database does so in exclusive mode, only that Instance can mount the database. Parallel Mode If the first instance that mounts a database is started in parallel mode, other instances that are started in parallel mode can also mount the database.

What is unit testing?

The Developer created the mapping that can be tested independently by the developer individually.

What are Fact, Dimension, and Measure?

Fact is key performance indicator to analyze the business. Dimension is used to analyze the fact. Without dimension there is no meaning for fact.

What is the difference between dependent data warehouse and independent data warehouse?

Dependent departments are those, which depend on a data ware to for their data.Independent department are those, which get their data directly from the operational data sources in the organization.

What are the methodologies of Data Warehousing?

Every company has methodology of their own. However, to name a few SDLC Methodology, AIM methodology is standard used.

What is schema?

A schema is collection of database objects of a User.

Do you View contain Data?

Views do not contain or store data.

Can Full Backup be performed when the database is open?


What is Informatica Architecture?

Informatica Architecture contains Repository, Repository server, Repository server administration console, sources, repository server and Data warehousing and it have the Designer, Work for manager, work for monitor combination of all these are called Informatica Architecture.

What are the different types of data warehousing?

Types of 
data warehousing are:

1. Enterprise Data warehousing

2. ODS (Operational Data Store)

3. Data Mart

Which technology should be used for interactive data querying across multiple dimensions for a decision making for a DW?


What is BUS Schema?

BUS Schema is composed of a master suite of confirmed dimension and standardized definition if facts.

What is Table?

A table is the basic unit of data storage in an ORACLE database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

What is the use of Control File?

When an instance of an ORACLE database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

What are the steps involved in Instance Recovery?

Rolling forward to recover data that has not been recorded in data files yet has been recorded in the on-line redo log, including the contents of rollback segments.

Rolling back transactions that have been explicitly rolled back or have not been committed as indicated by the rollback segments regenerated in step a.

1) Releasing any resources (locks) held by transactions in process at the time of the failure.

2) Resolving any pending distributed transactions undergoing a two-phase commit at the time of the instance failure.


Popular posts from this blog

TOP Agile Testing Interview Questions and Answers

Latest Agile Testing Interview Questions and Answers

Most Asked ADO.NET Interview Questions and Answers