What is Data Modeling?

What is Data Modeling?

Every table in your warehouse is a data model. The question is whether it was designed intentionally or just dumped from a source system. A well-designed data model answers three questions:

• • What does one row represent? (the grain)
• • What can you measure? (fact columns: revenue, quantity, duration)
• • How can you slice it? (dimension columns: customer, product, date, region)

Data modeling sits between raw source data (staging) and the analytics layer (marts). In the modern stack it is typically implemented in dbt — SQL transformations that produce clean, tested, documented models in your warehouse.

Why Data Modeling Matters

What You Can Build with Data Modeling

How Data Modeling Works

In the modern data stack, data modeling is a four-stage process: raw source data lands in staging tables, transformation logic produces intermediate models, final fact and dimension tables are materialized, and a semantic layer (metrics definitions) sits on top for BI tools.

-- models/marts/fct_orders.sql
SELECT
    -- grain: one row per order line item
    o.order_id,
    o.order_item_id,
    o.ordered_at,
    -- foreign keys → dimensions
    o.customer_id,
    o.product_id,
    d.date_day       AS order_date_key,
    -- measures (additive facts)
    o.quantity,
    o.unit_price,
    o.quantity * o.unit_price  AS gross_revenue,
    o.discount_amount,
    o.quantity * o.unit_price - o.discount_amount  AS net_revenue
FROM    {{ ref('stg_orders') }}  o
LEFT JOIN  {{ ref('dim_date') }}  d
    ON d.date_day = o.ordered_at::date

-- models/marts/dim_customers.sql
SELECT
    customer_id,
    full_name,
    email,
    country,
    customer_segment,
    first_order_date,
    lifetime_orders,
    lifetime_revenue,
    -- SCD Type 2 metadata
    dbt_valid_from,
    dbt_valid_to,
    dbt_scd_id
FROM    {{ snapshot('customers_snapshot') }}
WHERE    dbt_valid_to IS NULL  -- current records only

Data Modeling Approaches Compared

Common Mistakes

Who Should Learn Data Modeling?

Related Concepts

FAQs

What is data modeling?

Data modeling is the process of defining how data is structured, organized, and related within a database or data warehouse. A data model specifies the tables (or entities), their columns, data types, relationships (joins), and the grain (what one row represents). Good data models make queries fast, results trustworthy, and downstream logic simple. Bad models create duplicates, ambiguous joins, and slow dashboards.

What is dimensional modeling?

Dimensional modeling is a data warehouse design technique invented by Ralph Kimball. It organizes data into fact tables (measurements like revenue, clicks, orders) and dimension tables (context like customers, products, dates). Fact tables are narrow and tall; dimension tables are wide and short. The result is a star schema optimized for analytical queries — fast aggregations, intuitive joins, and business-friendly column names.

What is the difference between a star schema and a snowflake schema?

A star schema has one layer of dimension tables directly joined to the fact table — simple, fast, and easy to query. A snowflake schema normalizes dimensions further into sub-dimensions (e.g., a country table hanging off a city table which hangs off a customer table) — reducing storage but increasing join complexity. Most modern analytics warehouses (BigQuery, Snowflake, Redshift) prefer star schema because storage is cheap and join performance is excellent.

What is data vault modeling?

Data vault is a modeling approach designed for enterprise data warehouses that need to integrate data from many source systems. It uses three entity types: Hubs (unique business keys), Links (relationships between hubs), and Satellites (descriptive attributes with full history). Data vault is highly auditable and load-flexible but complex to query — most teams add a mart layer (star schema) on top for analyst consumption.

What is grain in data modeling?

The grain of a fact table defines what one row represents — the most atomic level of detail captured. For example: one row per order line item (not per order), or one row per daily user-product impression (not per session). Mixing grains in a single fact table is the most common data modeling mistake — it produces incorrect aggregations and subtle double-counting bugs.