Dimensional modeling
The discipline of structuring warehouse data into facts and dimensions (usually star schemas) optimized for analytical querying and human comprehension, popularized by Ralph Kimball.
Dimensional modeling is the method behind star schemas. You identify the business processes you want to measure, declare the grain of each, pick the dimensions that describe those events, and pick the measures the fact will store. Done well, it produces a warehouse that's both fast to query and easy for a new analyst to navigate.
It stands in contrast to third-normal-form (3NF) modeling used in transactional databases, which optimizes for write integrity and storage, not analytical reads. Analytics engineers deliberately denormalize into facts and dimensions because analytics is read-heavy and join-sensitive.
The canonical reference is Kimball's “The Data Warehouse Toolkit.” You don't need to memorize it, but the four-step design process — pick the process, declare the grain, choose dimensions, choose facts — is worth internalizing.
Dimensional modeling is the difference between a warehouse people trust and self-serve from, and a pile of tables only its author understands. It's the core craft that distinguishes an analytics engineer from someone who just writes SQL.
The four-step process is also a reliable interview framework: walking through pick-process → declare-grain → choose-dimensions → choose-facts out loud signals real modeling experience.
- Porting a transactional (3NF) schema straight into the warehouse and querying it directly, which is slow and unreadable for analytics.
- Designing tables around a specific report instead of around a business process, so they don't generalize.
- What are Kimball's four steps of dimensional modeling?
- 1) Select the business process, 2) declare the grain, 3) identify the dimensions, 4) identify the facts (measures). Following them in order avoids most modeling mistakes.
- ArchitectureImplementing Data Products in a Data Mesh: Essential Strategies and Practices
- ArchitectureWhat Is a Lakehouse? Architecture & Use-Cases Explained in Depth
- FundamentalsChange Data Capture Patterns for Analytics Pipelines: The Complete Guide
- FundamentalsSurrogate vs Natural Keys: Choosing the Right Primary Key for Databases
Learn this by building, not memorizing.
Definitions get you the vocabulary. The platform gets you the skill — graded exercises, real projects, and a portfolio capstone.