Data Warehouse Interview Questions and Answers

5.00 avg. rating (96% score) - 1 vote

The data warehouse is a process involving data collection and data management to present significant business acumens to businesses. Being the core of Business Intelligence (BI), data warehouse analyst is one of the most sought after careers in 2020. Thus, this guide covers frequently asked data warehouse interview questions along with answers.


Today, the data warehouse is an essential practice for almost every industry, including verticals like healthcare, IT, automation, retail, logistics, and government agencies.


This exclusive guide is specially designed for both fresher and experienced professionals. It covers extensive data warehouse interview questions that can help you crack the upcoming interview round for data warehouse analyst position.


In this guide, you will learn in detail about how a database is different from data warehousing, what is OLTP and OLAP, cloud-based data warehouses, Kimball and Inmon data warehouse designs, etc.


Top 35 Data Warehouse Interview Questions


Q1. Define data warehousing in easy words.


Ans. Data warehousing can be called a repository of data, which helps management teams in driving apt business decisions.


Q2. Are database and data warehousing the same thing?


Ans. The database is a way of storing information in an organized format, and it is represented in the form of a table, columns, and rows. Nowadays, companies use dedicated database management software (DBMS) to store crucial data. Whereas, data warehousing also helps in storing data, but it is used to store a large chunk of data, and it allows users to use data for complex queries. For this, users take the help of Online Analytical Processing (OLAP).


Though both database and data warehouses are relational data systems, they serve different purposes. Below are some key differences:


DatabaseData Warehouse
Helps in data recordingHelps in data analyzing
Uses Online Transactional Processing  (OLTP)Uses Online Analytical Processing (OLAP)
Table are normalized hence complex to useTable are denormalized thus easy to use
Application-orientedSubject-oriented
Stores data from a single applicationStores data from multiple applications
Real-time data availabilityData refreshed from the source system as per requirements
Uses ER modeling techniqueUses data modeling technique
Used in industries like banking, airlines, universities, sales, etc.Used in industries like healthcare, insurance, retail chains, telecommunications, etc.

Q3. How is OLTP different from OLAP?


Ans. OLTP stands for Online Transactional Processing, which deals with current data and is characterized by short write transactions. The main objective of OLTP is to record all the current update, insertion, and deletion, and thus, it is less time consuming and easy to maintain. Also, OLTP acts as a source of data for OLAP.


The focus of OLAP is to store historical data that has been processed by OLTP. OLAP helps in data analysis and support in reaching out to meaningful interpretations. Some of the noticeable difference between OLTP and OLAP are:


OLTPOLAP
Online transaction systemData retrieval and analysis system
Helps in data insertion, updation, and deletionHelps in deriving multi-dimensional data for analyzing
Short and frequent transactionsLong and less frequent transactions
Less complex queriesMore complex queries
Data integrity is a concernPossibilities of Data integrity is dependent on OLTP

Q4. What are some benefits of cloud-based data warehouses when compared to on premise solutions?


Ans. In the last few years, cloud computing is prevalent, and now most of the companies prefer to use cloud-based data warehouses over traditionally used on-site warehouses. Below are the top reasons for companies using cloud-based data warehouses:


  • It is easy and practical to scale data warehouse in the cloud.
  • It is economical to store data warehouse on the cloud as it eliminates the hardware and licensing cost, which is required for on-site warehouse setup.
  • The cloud data warehouse is optimized for data analytics because it uses Massively Parallel Processing (MPP) and columnar storage, which are known for offering better performance and helps in executing complex queries.


Q5. Name essential approaches to data warehouse design.


Ans. There are two data warehouse design approaches, Kimball and Inmon.


Inmon approach or top-down was proposed by Mr. Bill Inmon, the Father of data warehousing. In this approach, first, it is recommended to prepare a data warehouse, and then Data Marts are created. Through this strategy, the data warehouse becomes the central point of Corporate Information Factory (CIF), which acts as a logical framework for BI.


Kimball approach, also known as a bottom-up approach, suggests creating Data Mart first and later integrating it to a more massive data warehouse to complete a data warehouse. This integration of Data Mart is known as a data warehouse bus (BUS) architecture.


Q6. What are the advantages and disadvantages of the Inmon approach?


Ans. Below are some advantages and disadvantages of top-down or Inmon design:



Advantages of Inmon Design



Disadvantages of Inmon Design



Easy to maintain and though the initial cost is high, subsequently the project development cost is low



Represents a large chunk of data thus cost of implementing design is high



Offers consistent dimensional views of data across all Data Marts



Requires more time for initial set up



Highly robust approach toward frequent business changes



Represents substantial projects and hence it is complex



Q7. What are the advantages and disadvantages of the Kimball approach?


Ans. Below are some advantages and disadvantages of bottom-up or Kimball design:



Advantages of Kimball Design



Disadvantages of Kimball Design



Contains consistent Data Marts which are easy to deliver



The overall cost is high



Data Marts showcase reporting capabilities



Data Mart and data warehouse positions are differentiated



Initial setup is quick and easy hence it is easy to accommodate new business units by merely creating new Data Marts and clubbing it with other data warehouses



Sometimes difficult to maintain



Q8. Which are the different types of data warehousing?


Ans. There are three types of data warehousing:


  1. Enterprise Data Warehouse

It merges organizational data from its different functional areas in a centralized manner. It helps with data extracting and transforming and offers a detailed overview of any particular object in the data model.


  1. Operational Data Store

It gives an option to produce the date from the database instantly and supports business operations by integrating contrast data from multiple sources.


  1. Data Mart

Data Mart stores data from a particular functional area, and it comprises a subset of data that is saved in the data warehouse. It helps the analyst in swiftly analyzing the data by shrinking the volume of a large chunk of data.


Q9. Name 3 types of Data Mart.


Ans. Below are the 3 different types of Data Marts:


  1. Dependent – It sources organizational data from a single data warehouse and helps in developing more Data Marts.
  2. Independent – Here, no data is dependent on central or enterprise data warehouse, and data can be used separately for conducting an independent analysis.
  3. Hybrid – It helps in ad hoc integration and is used when a data warehouse comprises inputs from different sources.

Q10. What is data warehouse architecture?


Ans. Conceptualized with a relational database management system (RDBMS), data warehouse architecture serves as a central repository for informational data. Here, the central repository includes several key components that make the environment operative, compliant, and accessible to operational systems.


Q11. What is the three-tier architecture of a data warehouse?


Ans. Below is the three-tier data warehouse architecture:


data warehouse architecture


Image Source – Digital Vidya


  1. Bottom Tier

It represents the data warehouse database server, which is also known as the relational database system. It uses backend tools and utilities that are used to feed data and perform functions like – Extract, Clean, Load, and Refresh.


  1. Middle Tier

It represents the OLAP Server, which is a form of extended relational database management system. It is known to implement multidimensional data and operations.


  1. Top Tier

It factors the front-end client layer and holds query, analysis, and data mining tools.


Q12. What are the different stages of data warehouse decision support evolution?


Ans. Below are the 5 stages involved in data warehouse decision support evolution:


  1. Report
  2. Analyze
  3. Predict
  4. Operationalize
  5. Active warehousing

Q13. Name the components of data warehousing.


Ans. Below are the 5 components of data warehousing:


  1. Data Warehouse Database
  2. Sourcing, Acquisition, Clean-up and Transformation Tools (ETL)
  3. Metadata
  4. Query Tools
  5. Data warehouse Bus Architecture

Q14. Name some of the popular data warehouse tools.


Ans. Below is the list of popular query tools:



Tools


Availability

Amazon Redshift



Licensed



Teradata



Licensed



Oracle 12c



Licensed



Informatica



Licensed



IBM Infosphere



Licensed



ParAccel (acquired by Actian)



Open Source



Ab Initio Software



Licensed


Cloudera

Open Source



Q15. What do you know about Amazon Redshift’s architecture?


Ans. Amazon Redshift, based on PostgreSQL, is the most popular cloud services offered by Amazon Web Services. This tool is popularly used for handling Petabyte-scale data. Its unique features help the analyst to query data in seconds. With almost negligible cost, Redshift is easy to set up and maintain.


Redshift can be integrated with other BI and analytical tools and works with Extract, Transform, and Load (ETL) tools.


Below are some features of Redshift:


  1. Columnar storage and MPP processing
  2. Compression (column-level operation)
  3. Management and Security
  4. Data Types
  5. Updates and Upserts

Q16. State something about real-time data warehousing.


Ans. Real-time data warehousing is a concept, which reflects the real-time state of the warehouse by capturing the data as soon as it occurs. It has low latency data, which is fast, scalable, and simple to use.


Q17. What are the benefits of real-time data warehousing?


Ans. Below are some benefits of using real-time data warehousing:


  • Eases decision making
  • Resolves the problem of ideal data load
  • Ensures quick recovery and permits more rapid interventions
  • Eliminates batch window
  • Easy to optimize by running transformations in the database


Q18. What should you avoid when planning to construct a real-time data warehouse?


Ans. One must avoid mistakes like:


  • Not focusing on data integrity when constructing real-time data
  • Overlooking traditional OLTP systems
  • Not initiating business process changes in real-time data warehousing


Q19. What do you mean by SCD?


Ans. SCD stands for a slowly changing dimension, which is used to store and manage historical data. It is among the most critical tasks that support tracking dimension record history.


Q20. Which are the three types of SCD?


Ans. Below are the three types of slowly changing dimension:


  1. SCD 1 – Overwriting current record with the new record
  2. SCD 2 – Creating another dimension record to an existing customer dimension table
  3. SCD 3 – Creating a current value field to include new data

Q21. Define Schema in data warehousing.


Ans.



Schema



Description



Bus Schema



It works on top-down planning concepts and contains a set of tightly integrated data marts, which are directly linked with conformed dimensions and fact tables.



Star Schema



Each dimension is represented with only one dimension table, which consists of a set of attributes.



Snowflake Schema



Some dimensional tables are normalized, which splits the data into additional tables.



 


Q22. State the difference between star and snowflake schema.


Ans. Below is the list of differences between star schema and snowflake schema:



Star Schema



Snowflake Schema



Dimension hierarchy is stored in a dimensional table



Hierarchy is divided into multiple tables



Dimension table surrounded fact tables



Other dimension tables further surround dimension tables



A single join reflects the relation between fact and dimension table



Requires multiple joins to establish the relationship



DB design is simple



DB design is complex



Data redundancy is possible



Data redundancy is hardly possible



Fast cube processing



Cube processing is a bit slow



Denormalized Data structure



Normalized Data Structure



Q23. Define galaxy schema.


Ans. Galaxy schema, also known as Fact Constellation Schema, contains two fact tables along with dimensional tables. In other words, it can be called a combination of stars.


Q24. What are the types of fact tables?


Ans. In the dimensional model, the fact table is the primary table, which contains facts and foreign keys to the dimension table. It is used for measurement in the business process. The fact table has three different types:



Fact Table Types



Description



Additive



All dimensions must have measures



Semi-Additive



Measures must be added to only some dimensions and not all



Non-Additive



Only contains some fundamental unit of measurement



Q25. What are the types of dimension tables?


Ans. Joined via a foreign key, a dimension table includes the dimension of facts. It is also known as denormalized tables that offer descriptive characteristics of facts. Below are the types of dimension tables:


  • Conformed dimensions
  • Outrigger dimensions
  • Shrunken rollup dimensions
  • Dimension-to-dimension table joins
  • Junk dimensions


Q26. Give the steps to start and shut down the database.


Ans. Below are the steps to start a database:


  • Start an instance
  • Mount the database
  • Open the database


Below are the steps to shut down a database:


  • Close the database
  • Dismount the database
  • Shutdown the instance


 


Q27. Define the surrogate key.


Ans. Surrogate key functions as a substitute for natural primary key.


 


Q28. What do you mean by virtual data warehousing?


Ans. It is a collective view of the finished data, and it does not include historical data. The main objective of the virtual data warehouse is to help in making analytical decision making and translating raw data into a more presentable format. Along with this, it also offers a semantic map.


 


Q29. Define XMLA.


Ans. XMLA or XML for Analysis is the Simple Object Access Protocol, which is used as a standard for obtaining data in OLAP.


 


Q30. Differentiate between View and Materialized View.


Ans. Below table highlights the difference between view and materialized view:


 



View



Materialized View



Provides tail raid data to access data from its table



Contains pre-calculated data



Does not occupy space due to its logical structure



Occupy physical data space



All changes are affected in corresponding tables



No changes are affected in similar tables



 


Q31. When do you use bteqexport?


Ans. Whenever the total number of rows is less than half a million, bteqexport is used.


 


Q32. When do you use fastexport?


Ans. “fastexport” is used when the total number of rows is more than half a million.


 


Q33. Name the primary functions of dimensions.


Ans. The primary functions of the dimensions are:


  • Filtering
  • Grouping
  • Labeling


 


Q34. As a data warehouse manager, what were your key job responsibilities in the previous company?


Ans. Some of my prime responsibilities are:


  • Work on creating data warehouse process models
  • Verify the integrity of warehouse data and ensure consistent changes
  • Implement data extraction procedures
  • Maintain data standards
  • Handle data related troubleshooting
  • Use different computer language and methods to perform data analysis
  • Implement metadata processes
  • Review data designs, codes, and test plans
  • Use database management system software like Apache, MongoDB, Oracle to smoothly perform data warehousing functions


 


Q35. Which data warehousing skills you master?


Ans. In the data warehousing interview, you can talk about your critical technical skills. You can say – Some of my strengths are:


  • Enterprise system management software
  • Apache Avro
  • Human resource management software HRMS
  • Data mining software like Rapid-I RapidMiner, SAP NetWeaver Business Warehouse
  • CRM software
  • Data analysis
  • MS office


Above are some of the frequently asked data warehouse interview questions, which you must prepare before appearing for an interview round.


 


About the Author

Pratibha Roy

Pratibha Roy

A content specialist by profession, Pratibha has four+ years of experience in writing engaging articles and blogs. She strongly believes in the power of words and equips new ideas with the motive of imparting comprehensive knowledge to the readers. When away from work, she loves reading books with a cup of hot coffee in hand.
Topics : IT and Telecom