10 Data Warehousing Interview Questions and Answers

If you talk to any computer major they will tell you the importance of data warehousing. What’s surprising is that centralizing data has become crucial for a variety of different industries and departments within companies. Marketing, sales, manufacturing, and distribution are just a few of the business operations that hinge on effective data warehousing.

So what is data warehousing? And no, it’s not a giant room that stores the robot from Star Trek. Data warehousing is the repository for all the data that a business collects, across many sources of the enterprise, typically used for analysis and reporting.

If you’re going for a job as important as data warehousing, there’s a good chance the questions will be difficult and specific. Don’t go unprepared.

Here are 10 data warehousing interview questions and answers.

10. What’s the point of a data warehouse?

Data warehouses are critically important for evaluating trends and data for the purpose of analyzing raw numbers to predict buying habits, effective product roll-out, and to help formulate strategies for future projects and existing operations.

9. What’s the difference between a data mart and a data warehouse?

Data marts are used for specific departments inside an organization like finance, HR, or sales. A data warehouse stores various data marts in a centralized location, whether that be the cloud or a local server.

8. What is OLTP?

OLTP stands for Online Transactional Processing, and is the system that collects data during a business transaction. The data is then used for analysis.

7. What is OLAP?

OLAP stands for Online Analytical Processing. OLAP is multidimensional and is denormalized to increase the speed of data retrieval.

6. What is a Conformed Dimension?

A conformed dimension is one that can be used across a variety of different data sets and remain constant. An example of a conformed dimension would be ‘customer‘ or ‘address‘ because they can be used in several departments for similar purposes. Another example would be ‘date’ and ‘time‘.

5. What is Fact?

No, this is not some weird existential question. Fact in data warehousing is a numerical figure that can be aggregated for analysis. This is a quantifiable figure of a certain object or dimension.

4. Name the four different types of dimensions.

The four different types of dimensions are the, Conformed, Junk, Degenerated, and Role Playing Dimension.

3. What are the four stages of data warehousing?

The four stages are Offline Operational Database, Offline Data Warehouse, Real Time Datawarehouse, and Integrated Datawarehouse.

2. What is SSIS?

SSIS stands for SQL Server Integration Services and is a crucial tool in data extraction and loading. SSIS can also be used to update multidimensional cube data, or perform automated maintenance.

1. Tell me about yourself.

Once you have the technical aspects of data warehousing down, it’s important that you show that you would be a good person to manage the data warehouse. This includes being personally organized, highly technical, and with an acute attention to detail. The importance of data is diminished if that data is not being evaluated or tracked efficiently.

A very important part of managing a data warehouse is understanding the terminology that goes along with it. While much of this seems very advanced – and it is to a certain degree – data warehousing ultimately relies on logic, and the conformation of useful data. Before you go on that interview make sure you understand SQL servers and how data is transmitted from different sources, as well as how to organize that data and extract use from the analyzed results. If you come prepared and continue practicing your technical knowledge, there’s no reason you won’t be able to land the job.