What Is Data Modeling (and Why It Matters)
You’ve learned about data pipelines, data types, and how data flows from source to warehouse. But once data lands in your warehouse, you face a new question: how should you organize it? That’s where data modeling comes in. A data model is like an architectural blueprint for your data: it decides which tables exist, how they connect to each other, and what information goes where. Get it right, and queries are fast and answers are clear. Get it wrong, and you’re stuck rewriting everything later.
Think of it like designing a restaurant
Imagine you’re opening a restaurant and need to keep track of everything.
- Bad design: You throw all information into one giant notebook. Every page has customer names, orders, menu items, and prices all mixed together. When your manager asks “How much revenue did we make yesterday?” you have to flip through hundreds of pages, manually collecting and calculating. Slow, error-prone, and painful.
- Good design: You have separate notebooks organized like a filing system. One notebook for customers (name, phone, email), one for orders (date, customer ID, total), one for menu items (name, price, ingredients). When you need to answer a question, you know exactly where to look. Customer name? Check the customer notebook. Find their orders? Match the customer ID in the orders notebook. Quick, organized, reliable.
That’s the difference between having no data model and having a good data model.
What Is a Data Model?
A data model is the design of how data is organized in a database or data warehouse. It decides:
- What tables exist (customers, orders, products)
- What columns each table has (customer table: name, email, address)
- How tables connect (orders are linked to customers via customer ID)
- What data goes where (should “revenue” be calculated or stored?)
A good data model is:
- Efficient: Queries run fast because data is organized in a way that computers can find information quickly
- Accurate: Data is organized to prevent duplicates and inconsistencies
- Understandable: When someone asks a question, you know which tables to look at and how to join them
- Flexible: When the business changes, you can adapt without rebuilding everything
An Example: E-commerce Data Model
Let’s say you’re building a warehouse for an e-commerce company. Here’s what a data model might look like:
erDiagram
CUSTOMERS ||--o{ ORDERS : places
ORDERS ||--o{ ORDER_ITEMS : contains
PRODUCTS ||--o{ ORDER_ITEMS : "ordered in"
CUSTOMERS ||--o{ ADDRESSES : has
CUSTOMERS {
int customer_id PK
string name
string email
date signup_date
}
ADDRESSES {
int address_id PK
int customer_id FK
string street
string city
string state
}
ORDERS {
int order_id PK
int customer_id FK
date order_date
decimal total_amount
}
ORDER_ITEMS {
int order_item_id PK
int order_id FK
int product_id FK
int quantity
decimal price_per_item
}
PRODUCTS {
int product_id PK
string name
string category
decimal price
}
Notice:
- Each entity (customers, orders, products) has its own table
- Tables are connected via IDs (customer_id, product_id, etc.)
- Data lives in only one place: no duplication
- To answer “What did customer 1001 order?” you join three tables: customers → orders → order_items
Two Main Approaches to Data Modeling
There are different ways to design a data model, and the choice depends on your use case.
1. Normalized Models (for transactional systems)
Normalized models eliminate redundancy by breaking data into small, related tables.
Example:
CUSTOMERS: customer_id, name, email
ORDERS: order_id, customer_id, order_date
ORDER_ITEMS: order_item_id, order_id, product_id, quantity
PRODUCTS: product_id, name, price
Pros:
- Data appears in one place only (no duplication)
- Updates are easy: change a customer’s email once, it’s updated everywhere
- Saves storage space
Cons:
- Queries require joining many tables (slower)
- More complex to write queries
When to use: OLTP systems (production databases handling transactions), where data changes frequently and consistency is critical.
2. Denormalized Models (for analytical systems)
Denormalized models combine related data into fewer, wider tables to make queries faster.
Example of a denormalized table:
ORDER_FACTS: order_id, customer_name, customer_email,
order_date, product_name, product_category,
quantity, price_per_item, total_amount
Pros:
- Queries are faster: all data in one table, no joins needed
- Easier to write queries
- Optimized for reading
Cons:
- Data is duplicated (customer name appears in many rows)
- Updates are harder: if a customer’s email changes, you have to update many rows
- Uses more storage
When to use: OLAP systems (data warehouses for analysis), where data changes infrequently and query speed matters more.
flowchart LR
A["Normalized<br/>(Small tables,<br/>joined)"] <-->|Opposite ends<br/>of the spectrum| B["Denormalized<br/>(Wide tables,<br/>few joins)"]
A -->|Good for| C["OLTP<br/>(Fast writes,<br/>frequent updates)"]
B -->|Good for| D["OLAP<br/>(Fast reads,<br/>analysis)"]
The Star Schema (Your First Advanced Model)
Once you understand normalization vs denormalization, you’re ready for the star schema, one of the most popular data warehouse design patterns.
A star schema has:
- Fact table (center): Contains numbers you want to measure (sales, clicks, views)
- Dimension tables (points of star): Contain descriptions (who, what, when, where)
Example:
flowchart TB
subgraph Dimensions
D1["🕐 DATE_DIM<br/>date_key, year,<br/>month, quarter"]
D2["👤 CUSTOMER_DIM<br/>customer_key,<br/>name, email"]
D3["📦 PRODUCT_DIM<br/>product_key,<br/>name, category"]
D4["🏢 STORE_DIM<br/>store_key,<br/>location, region"]
end
F["💰 SALES_FACT<br/>date_key, customer_key,<br/>product_key, store_key,<br/>quantity, amount"]
D1 --> F
D2 --> F
D3 --> F
D4 --> F
Why it’s called a “star”: The fact table in the center connects to multiple dimension tables, forming a star shape.
Advantages:
- Simple structure that’s easy to understand
- Fast queries: typically just join fact to dimensions
- Easy to add new dimensions without changing existing tables
- Perfect for dashboards and analytics
Example query: “How much revenue did we make in January, by product category?”
| |