Data Engineering Foundations
Intermediate 15 min read Data Engineering Foundations

Data Modeling for Engineers

Get the grain right and the meters follow

Data modeling is how you decide what a table is and how tables relate. Facts and dimensions, normalization versus denormalization, and grain discipline turn raw data into analyzable structure.

data modelingfactsdimensionsgrainnormalizationstar schema
Prerequisites: OLTP vs OLAP Warehouse vs Lake
Before we start

What you will learn

  • Distinguish facts, dimensions and grain
  • Explain normalization (OLTP) vs denormalized analytical modeling
  • Avoid classic modeling traps: wrong grain, many-to-many joins, slowly changing confusion
Introduction

What is this?

Data modeling defines the structure of your analytical tables: facts (measured events: sales, sessions), dimensions (descriptive context: customer, store, date) and the grain (the level of detail of one row). Analytical modeling often denormalizes — intentionally duplicating descriptive columns to make queries fast and simple — unlike the normalized relational modeling of transactional systems.

Motivation

Why do you need it?

  • Grain is the single most error-prone decision: get it wrong and every aggregate is misleading.
  • Well-modeled tables are consumable by anyone — analysts write correct SQL without reverse-engineering joins.
  • Facts+dimensions give dashboards stable, queryable structure instead of ad-hoc columns.
  • Modeling is where data engineering's quality promises actually get encoded.
Think about it

Mental model

Imagine

A spreadsheet of receipts + a database of clients

Think of

Facts + dimensions

Receipt facts are measured transactions ('what happened'), client/dimension tables are reference data ('who/where/when they are'). You join a receipt to a client to answer 'how much did Paris customers spend?'

Mechanics

How it works

  • A fact table has low-cardinality foreign keys to dimensions plus measures (amount, quantity).
  • A dimension table describes one entity (store, customer, product) with descriptive attributes.
  • Grain is the statement 'one row = one X' (one order line, one daily store total).
  • Analytical models mix normalized (wide dims) and denormalized (some repeated attributes) pragmatically.
  • Slowly changing dimensions (SCDs) track how dimension attributes change over time without rewriting history.

Star schema: central fact surrounded by dimension tables.

fact_sales
dim_store
dim_date
dim_customer
Queries
Real world

In production

  • A daily sales fact at order-line grain joins dim_store, dim_date and dim_customer; a BI table groups by region/month in milliseconds.
  • SCD Type 2 on customer segment preserves history: 'where a promoter segment customers bought' stays answerable even after they churn.
  • A normalized OLTP core feeds a denormalized analytics mart — engineered duplication absorbs analytics load.
Guidelines

Best practices

  • State the grain of every fact in its documentation; test it with a uniqueness check.
  • Use surrogate keys in dimensions so natural-key changes don't break referential history.
  • Keep measures numeric and unambiguous (amount in currency X, excluding tax).
  • Add conformed dimensions across marts so 'store' means the same thing every report.
Watch out

Common mistakes

  • One row per customer in a fact table → revenue numbers are double-or-triple counted on joins.
  • Many-to-many dimension joins without a bridging table producing inflated counts.
  • Over-normalizing analytical tables so every dashboard session needs 6 joins.
  • Changing a dimension attribute in place, silently rewriting history for every past report.
Root cause analysis

Common issues

Error
Customer counts inflate when joining to orders
Root cause

Joining customers (one side of a one-to-many) in a fact that's per-order, fanning out rows

Fix

Aggregate to customer level first, or count DISTINCT at the right grain.

Error
Changing 'unlimited' region names breaks old reports
Root cause

SCD Type 1 overwrite erased history

Fix

Adopt SCD Type 2 with effective dates to preserve attribute history.

Error
Fact grain drifted from order-line to store-day-rec — tables silent changed meaning
Root cause

No documented grain and no uniqueness gate

Fix

Document grain in the catalog, add a uniqueness test, and split tables when grains genuinely differ.

When things break

Debugging tips

  • A doubled metric? Test uniqueness at the fact grain and your join's fan-out first.
  • Wrong historical numbers after an attribute update? Check if an SCD strategy exists at all.
  • Slow star queries? Confirm the grain is consistent with the BI grouping, then check indexes on keys.
  • Same measure, two answers? Check whether both reports read the same fact or two marts.
Interviews

Interview checkpoint

Test yourself

Quick quiz

0/2
1

Which of these belongs in a dimension table?

2

Over-normalizing an analytical mart is fine because joins are cheap.

Go deeper

Official documentation

dbt — guide to the modern data modeling
docs