Basic dataware-housing interview questions

What is datawarehouse?
Datawarehouse is a

• Non-volatile
• Integrated
• Subject-oriented
• Time-varient

database system

What are the steps involved in loading a  datawarehouse?
There are 3 steps in datawarehousing,

• data-extraction from the OLTP system
• Apply the required transformations on the data
• Load the data into datawarehouse

What is fact table?
Fact table is  a table, which is having the multiple foreign keys of the dimension tables and measures.Fact table is also called as child table having dimension codes and facts/measures.

What is dimension table?
Dimension table is a table,which is having the descriptive information of an entity.This is also called as master table having the primary key.
Always the data flow from dimension table to fact table.

What is data-modelling?
The process of storing and retrieving the data from datawarehouse is called as data-modelling.

What is star schema and snow-flake schema?What is the difference between those?
Star is one fact and many dimensions where as Snowflake design is one fact and many dimensions and dimensions can have additional dim relations.

No.of joins in the snow-flake schema is more compare to star schema.

Difference between OLTP and OLAP systems?
OLTP(Online transactional process):

Dynamic data
volatile
Normalised data
Efficient for data storing
OLAP(Online Analytical process):

90%static,10%Dynamic data
Non-volatile
De-normalised data
Efficient for data retrieving

What are different types of dimensions?

• confirmed dimension
• slowly changing dimension
• rapidly changing dimension
• De-generated dimension

What are different types of fact tables?

• Additive fact table
• semi-additive fact table
• Non-additive or fact-less fact table

What is Federated Query?

A query is fired across multiple sources is called as federated query.

like

select cust_first_name,revenue from customers,sales

customers->SQL,sales->DB2 data sources