Data Warehouse Interview Questions Introduction
Are you ready for your next Data Warehouse job interview? Do you have the answer to the Data Warehouse interview questions you may be asked? As an Data Warehouse Business Intelligence team lead and previously an Data Warhouse integration technical lead, I have had the opportunity to interview plenty of developers. I am writing this post to help you prepare for those Data Warehouse interview questions and get the job offer you are looking for.
If the following Data Warehouse Questions are not enough, just …subscribe to my email list
and get …
- 300+ Informatica and Data Warehouse Interview Questions
- My top 10 personal interview tips
- 120 Q & A Quiz to test your knowledge
The Data Warehouse interview questions in this post should cover a broad range of Data Warehouse concepts.
Enjoy the post and good luck on your next Data Warehouse job interview.
What is a data warehouse?
A data warehouse is a large store of data accumulated from a wide range of sources within an organization and used to guide business decisions.
What the process and overall purpose of data integration?
Data integration serves to take disparate sets of data and merge them together in a large store of data to create valuable business information.
What does ETL stand for?
Extract Transform Load
What is a star schema?
A star schema is a simplistic two-dimensional, star shaped data model. Its useful design connects one or more fact tables with a number of dimension tables via primary/foreign key relationships to enable strategic decision making.
What is a dimension table?
A dimension table contains the descriptive attributes of a business process event that enable filtering and grouping of facts. Fact tables can be found in both star schemas and OLAP cubes where there are relationships between dimension and fact tables. Dimension table attributes describe the “who, what, when, where, why, and how” of the business process being modeled.
What is a fact table?
Fact tables contain business process event measurements that are almost always numeric. A fact table’s grain is defined through this physical observable business event. Fact tables can be found in both star schemas and OLAP cubes where there are relationships between dimension and fact tables. There are several categories of fact tables including: Additive, Semi-Additive, and Non-Additive.
Numeric measures in a fact table fall into these three categories?
What is an Additive Fact?
An additive fact is a measure in a fact table that can be fully summed across any of the dimensions associated with it.
What is a Semi-Additive Fact?
A semi-additive fact is a measure in a fact table that can be summed across some dimensions associated with it, but not all.
What is a Non-Additive Fact?
A non-additive fact is a measure in a fact table that cannot be summed across any of the dimensions associated with it. A good example of this are ratios.
What are the three fundamental types of fact tables?
2. Periodic Snapshot
3. Accumulating Snapshot
What is a transaction fact table?
A transaction fact table represents an event that occurred at an instantaneous point in time.
What is a periodic snapshot fact table?
A periodic snapshot fact table captures cumulative business performance on regular, predictable time intervals.
What is a accumulating snapshot fact table?
Accumulating snapshot fact tables represent processes that have a definite beginning and end together with a standard set of intermediate process steps.
What is an Enterprise Data Warehouse Bus Matrix?
An enterprise data warehouse bus matrix is a document of the data warehouse bus architecture.
Name two people who have had a major influence on current data warehousing concepts and modeling?
Ralph Kimball and Bill Inman
What is a centipede fact table?
A fact table where a modeler has decided to normalize the fact table istead of snowflaking the dimension table(s) (which is also discouraged).
What is a stakeholder matrix?
A stakeholder matrix is similar to a enterprise bus matrix. However, it replaces common dimensions with stakeholder such as merchandising, marketing, store operations, logistics, and finance. It is a guide to help understand which business departments are interested in each business process.
Do conformed dimensions work for an agile development environment?
While some argue that the organizational agreement necessary to build conformed dimensions in organization does not fit well with an agile environment, arguments can be made to the contrary.
One might argue that conformed dimension allow tables to be build once and replicated rather than re-creating and re-inventing the wheel all over the organization. Development will actually begin to speed up greatly once common dimensions are established.
What is a conformed facts?
They are measures that can be re-used across multiple dimensional models. Key performance indicators (KPI) such as profit, revenue, and costs are all example of possible conformed facts.
What does it mean to define the grain of a dimensional design?
Defining the grain is a very important step in dimensional deign. The grain can be defined as the agreement of what a single fact table represents. A business process’s lowest level of captured data can be referred to as atomic grain.
What is a type 0 slowly changing dimension?
A type 0 slowly changing dimension is a dimension table with attributes values that never change. Attributes are considered and may be labeled “original”.
What is a type 1 slowly changing dimension?
A type 1 slowly changing dimension is a dimension table with current value attributes. In other works, old attribute values are overwritten by the most recent record values.
What is a type 2 slowly changing dimension?
A type 2 slowly changing dimension updates dimension attribute values by inserting a new row in the dimension and inactivating its prior record.
What is a type 3 slowly changing dimension?
A type 3 slowly changing dimension, a new row is not inserted with a change, however a prior attribute column is added and updated with the old attribute value within the same record. This allows business to understand a previous value for an attribute as well as the current one, all within the same record. Reports would then align with the current value attribute, but business could easily report on the previous value if needed.
What is a junk dimension?
A junk dimension, also referred to as an indicator or transaction profile dimension is a separate dimension table that contains flags and indicators which have been removed from a fact table. By placing these flags and indicators into their own table, we are able to clean up the complex transactional fact table.
What is a audit dimension?
While loading certain fact tables, like a shipment invoice line transaction fact, metadata is generated that can be useful to not only IT, but to business. ETL processing characteristics like data quality indicators and others can be stored in this audit dimension. Business can then possibly leverage it by helping them understand anomalous values, versions, and gain a general confidence in reported numbers.
Define Kimball’s four-step dimensional design process?
1. Select the business process.
2. Declare the grain.
3. Identify dimensions.
4. Identify the facts.
Data Warehouse Interview Questions Summary
I hope this post has given you a feel for the types of questions that can be asked in a Data Warehouse job interview. While this list of data warehouse interview questions is not comprehensive, the broad range of questions should help you gauge where you might have knowledge gaps.
All the best with the data warehouse job interviews!
Checkout my 50+ Top Informatica Interview Questions and Answers post if you are trying to prepare for a Informatica data warehouse position.
I hold a B.S. in Mathematics and M.S. in System Engineering. I have also earned a Big Data and Social Analytics Certification from MIT.
Eccella is a high end, boutique style consulting firm focused, on creating data driven companies.
Find Aaron Gendle on LinkedIn -
Latest posts by Aaron Gendle (see all)
- 5 Reasons to Quit Your Job Today - June 20, 2017
- The 2017, Top 50
Data Driven CEO’s - March 1, 2017
- 25+ Advanced Informatica Interview Questions and Answers - February 7, 2015