Data Modeling for Analytics: A Practical Guide

Data modeling is the process of deciding how to structure your analytical data — what tables to create, what columns they have, and how they relate to each other. A good model makes querying fast and intuitive. A bad model makes every analyst write 15-join queries that nobody can debug.

Why modeling matters

Raw source data is optimized for applications, not for analysis. An e-commerce app might have 40 normalized tables with foreign keys everywhere. An analyst asking “what is our revenue by product category and customer segment?” should not need to understand the application’s internal schema.

Data models sit between raw data and business users. They pre-join, pre-aggregate, and pre-clean so that downstream queries are simple.

Kimball — dimensional modeling

The most widely used approach for analytics. Created by Ralph Kimball. The core idea: organize data around business processes (orders, shipments, clicks) using fact tables surrounded by dimension tables in a star schema.

Key principles

1. Identify the business process. Each major process gets its own fact table. Orders, payments, page views, shipments.

2. Declare the grain. The grain defines what one row in the fact table represents. “One row per order line item” or “one row per daily account snapshot.” This is the most important decision. Get it wrong and everything downstream breaks.

3. Choose dimensions. Dimensions answer who, what, where, when. Customer, product, store, date.

4. Identify measures. Measures are the numeric values you aggregate — revenue, quantity, cost, duration.

CREATE TABLE fact_order_items (
    order_item_id   BIGINT,
    order_id        BIGINT,
    date_key        INT,
    customer_key    INT,
    product_key     INT,
    quantity        INT,
    unit_price      DECIMAL(10, 2),
    discount        DECIMAL(10, 2),
    line_total      DECIMAL(12, 2)
);

The grain here is “one row per order line item.” Every analyst who queries this table knows exactly what each row means.

Conformed dimensions

Dimensions shared across multiple fact tables are called conformed dimensions. A dim_date table used by both fact_orders and fact_shipments ensures consistent date attributes (fiscal quarter, is_weekend) across all reports.

SELECT
    d.fiscal_quarter,
    SUM(o.line_total) AS order_revenue,
    SUM(s.shipping_cost) AS shipping_cost
FROM fact_order_items o
JOIN fact_shipments s ON o.order_id = s.order_id
JOIN dim_date d ON o.date_key = d.date_key
GROUP BY d.fiscal_quarter;

Same dim_date, same fiscal quarter logic, consistent numbers.

Inmon — enterprise data warehouse

Bill Inmon’s approach is top-down. You build a single, normalized (3NF) enterprise data warehouse first, then create denormalized data marts for specific business units.

Sources → ETL → Enterprise DW (3NF) → Data Marts (Star Schema)
                                           ↓
                                    BI Tools / Reports

Strengths:

• Single source of truth — all data goes through one normalized model.
• Better data integrity — normalization reduces redundancy and update anomalies.
• Good for organizations with complex, enterprise-wide data governance needs.

Weaknesses:

• Slower to deliver — you must build the entire normalized model before creating data marts.
• More complex queries — normalized schemas require more joins.
• Less intuitive for analysts and BI tools.

In practice, most modern teams use Kimball-style dimensional modeling. The speed of delivery and simplicity of star schemas outweigh Inmon’s theoretical purity.

Data Vault 2.0

Data Vault is a hybrid approach designed for agility and auditability. It uses three entity types:

Hubs — business keys

Hubs store unique business identifiers. One hub per business concept.

CREATE TABLE hub_customer (
    hub_customer_hash  CHAR(32) PRIMARY KEY,
    customer_bk        VARCHAR(50),
    load_date          TIMESTAMP,
    record_source      VARCHAR(100)
);

Links — relationships

Links capture relationships between hubs.

CREATE TABLE link_customer_order (
    link_hash          CHAR(32) PRIMARY KEY,
    hub_customer_hash  CHAR(32),
    hub_order_hash     CHAR(32),
    load_date          TIMESTAMP,
    record_source      VARCHAR(100)
);

Satellites — descriptive attributes

Satellites store attributes and their history. A new row is added when any attribute changes.

CREATE TABLE sat_customer_details (
    hub_customer_hash  CHAR(32),
    load_date          TIMESTAMP,
    full_name          VARCHAR(200),
    email              VARCHAR(200),
    city               VARCHAR(100),
    hash_diff          CHAR(32),
    record_source      VARCHAR(100),
    PRIMARY KEY (hub_customer_hash, load_date)
);

When to use Data Vault:

• Multiple source systems feeding the same entities.
• Frequent source changes — Data Vault absorbs schema changes without restructuring.
• Strong audit requirements — full history of every attribute change.
• Large teams where parallel development matters.

When to skip it: small teams, simple data sources, or projects where speed of delivery is more important than flexibility.

Incremental models

In production, you never rebuild entire tables from scratch every run. Incremental models process only new or changed data.

-- dbt incremental model
{{
    config(
        materialized='incremental',
        unique_key='event_id',
        incremental_strategy='merge'
    )
}}

SELECT
    event_id,
    user_id,
    event_type,
    event_timestamp,
    properties
FROM {{ source('raw', 'events') }}

{% if is_incremental() %}
WHERE event_timestamp > (SELECT MAX(event_timestamp) FROM {{ this }})
{% endif %}

On the first run, this processes everything. On subsequent runs, it only processes events newer than the latest existing timestamp.

Modern patterns

One Big Table (OBT)

A single, heavily denormalized table with all the columns you need. No joins required.

CREATE TABLE obt_orders AS
SELECT
    o.order_id,
    o.order_date,
    o.total_amount,
    c.full_name AS customer_name,
    c.segment AS customer_segment,
    c.country AS customer_country,
    p.product_name,
    p.category AS product_category,
    p.brand AS product_brand
FROM fact_orders o
JOIN dim_customer c ON o.customer_key = c.customer_key
JOIN dim_product p ON o.product_key = p.product_key;

Good for: simple dashboards, ML feature tables, small teams who want zero-join queries.

Bad for: large-scale warehouses where storage duplication matters, or where multiple fact tables share dimensions.

Activity schema

A single wide event stream table where every business event is a row.

CREATE TABLE activity_stream (
    activity_id         BIGINT,
    entity_id           VARCHAR(100),
    activity            VARCHAR(100),
    activity_timestamp  TIMESTAMP,
    feature_1           VARCHAR(500),
    feature_2           VARCHAR(500),
    feature_3           VARCHAR(500),
    revenue_impact      DECIMAL(12, 2)
);

Flexible and schema-light. Works well for event-driven analytics but loses the clarity of purpose-built fact tables.

Choosing an approach

Factor	Kimball	Inmon	Data Vault
Team size	Small to large	Large	Medium to large
Time to deliver	Fast	Slow	Medium
Flexibility	Medium	Low	High
Query simplicity	Simple	Complex	Complex (needs business vault)
Audit trail	Limited	Medium	Full
Best for	Most analytics teams	Enterprise governance	Multi-source, agile environments

For most teams starting out, Kimball with dbt is the right default. Build star schemas, test with dbt, and iterate fast.

Next steps

• Data Warehouse Concepts — star schemas and dimensional modeling in depth.
• ETL vs ELT Pipelines — how data gets into your models.
• Data Lakes vs Warehouses vs Lakehouses — where your models live.