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?”

1
2
3
4
5
6
7
8

SELECT 
    p.category,
    SUM(f.amount) as total_revenue
FROM SALES_FACT f
JOIN DATE_DIM d ON f.date_key = d.date_key
JOIN PRODUCT_DIM p ON f.product_key = p.product_key
WHERE d.month = 1
GROUP BY p.category

Simple, fast, and clear.

Slowly Changing Dimensions (SCDs)

Here’s a real-world problem: what happens when data in a dimension changes?

Example: A customer moves to a new address. In your CUSTOMER_DIM table, do you:

• Update the address in place? Then you lose the old address — and you won’t know when they ordered from the old address.
• Keep both the old and new address? Then you need a way to track which is current and which is historical.

That’s where Slowly Changing Dimensions (SCDs) come in. They’re techniques for handling dimension table changes over time.

Type 1: Overwrite

Simply update the old value with the new one. You lose history.

Customer 1001: 123 Main St → Update → 456 Oak Ave
(Old address is gone)

When to use: When you don’t care about history (job title, phone number)

Type 2: Keep Historical Records

Add new rows to track history, with effective date ranges.

Customer 1001: 123 Main St (effective_date: 2024-01-01, end_date: 2024-08-01)
Customer 1001: 456 Oak Ave (effective_date: 2024-08-02, end_date: NULL)

When to use: When you need to answer “what was the customer’s address at the time they made this order?”

Type 3: Keep Previous Value

Store both current and previous values in the same row.

Customer 1001: current_address: 456 Oak Ave, previous_address: 123 Main St

When to use: When you only need to track the most recent change

Why Data Modeling Matters

A good data model is the foundation of a good warehouse:

• Speed: The difference between a query that takes 5 seconds and one that takes 5 minutes
• Correctness: The difference between a dashboard you trust and one you’re always second-guessing
• Scalability: The difference between a model that works with 100K rows and one that works with 100M rows
• Collaboration: The difference between analysts who understand the data and analysts who get lost

A Simple Mental Model

The Takeaway

Data modeling is about asking a fundamental question: how should I organize this data so that it’s fast to query, accurate, and easy to understand? Different answers work for different situations. For production systems handling transactions, normalized models work best. For warehouses supporting analytics, denormalized models like star schemas are the standard. And as data changes over time, techniques like Slowly Changing Dimensions help you keep history while staying organized. Mastering data modeling is the bridge between understanding how data flows (pipelines) and knowing how to query it fast (SQL). It’s the blueprint that makes everything else work.