Dimensional modeling is a technique involved in the design of data organization structures in warehouses to ease the process of data analytics.

It starts with identifying and describing the underlying business process that determines the types of analytics that are to be performed in the future.

The result of dimensional modeling is a data warehouse schema organized into two kinds of tables: facts tables and dimension tables, which help organizations summarize, read, and aggregate metrics of interest to support end-user queries efficiently.

Dimensional Modeling Interview Questions

Data engineering interviews generally have two types of dimensional modeling questions: conceptual and case study questions.

Conceptual questions involve defining dimensional modeling concepts or answering theory-based questions on design principles. Examples include:

• What is the difference between a star schema and a snowflake schema?
• How would you decide what fields to include in a fact table?

Case study questions will ask you to design data warehouse schemas for specific business scenarios. An example may involve:

• Say you’re tasked with designing a data mart or data warehouse for a new online retailer. How would you construct the system?

  Note: Sketch a star schema to explain your design.

Course Content

This course provides a basic introduction to answering dimensional modeling interview questions.

We will cover the fundamentals of different tables and schemas in dimensional modeling and an in-depth framework for answering case studies.

While this course will serve as a good introduction to dimensional modeling, it’s not intended to be used as an in-depth resource for learning about data warehouse design in a business context.

For more step-by-step explanations of some of the topics covered within the course, we recommend checking out the Oracle Data Warehousing Guide and The Data Warehouse Toolkit.