Data modeling for analytics: what developers should know

Data modeling for analytics: what developers should know

When we build analytics systems, the schema we design determines how fast analysts can answer questions and how sustainable our pipelines become. Most teams moving data from transactional systems into warehouses skip proper modeling, treating the analytics database like a direct copy of production—and then wonder why dashboards take 30 seconds to load or why metric definitions keep conflicting. Data modeling for analytics matters because it bridges the gap between how we build applications and how analysts query them, and the star schema remains the practical gold standard for this work.

Why transactional schemas don't work for analytics

In production systems, we optimize for writes: normalized schemas reduce data duplication, enforce consistency, and make updates fast. We store orders in one table, order items in another, customers in a third. This is correct for OLTP (online transaction processing).

Analytics queries flip the priority. An analyst asking "What's our revenue by product category and region this quarter?" needs to join dozens of tables, compute aggregations across millions of rows, and do it in seconds. A normalized schema forces that analyst to write complex joins; each additional table adds latency and complexity. By the time the query optimizer finishes, you're scanning tables multiple times.

The star schema denormalizes strategically. We accept data redundancy in exchange for query simplicity and performance. This is OLAP thinking (online analytical processing), and it's why we don't just copy production schemas into Spark or your data warehouse.

Fact tables for events

A fact table is the core of every star schema. It stores measurable events: transactions, clicks, page views, payment attempts, shipments. Each row is an atomic event with a timestamp and references to dimensions.

Think of an e-commerce system. Instead of storing orders directly, we create a fact table called fact_orders that records each order as an event:

# Logical structure of a fact table
# fact_orders in a data warehouse

CREATE TABLE fact_orders (
    order_id INT,
    order_date DATE,
    customer_key INT,           -- Surrogate key to dimension
    product_key INT,            -- Surrogate key to dimension
    region_key INT,             -- Surrogate key to dimension
    quantity INT,
    revenue DECIMAL(10, 2),
    cost DECIMAL(10, 2),
    discount DECIMAL(10, 2),
    created_at TIMESTAMP
);

# Example rows:
# order_id | order_date | customer_key | product_key | region_key | quantity | revenue
# 1001     | 2024-01-15 | 42           | 156         | 5          | 3        | 149.99
# 1002     | 2024-01-15 | 43           | 157         | 5          | 1        | 79.99

Notice several principles here:

• The fact table is narrow but tall. Many rows, few columns. Each row is one order event.
• It stores numeric measures (revenue, cost, quantity) that make sense to aggregate.
• Foreign keys point to dimension tables, not to the raw production schema.
• Every row is immutable. Once inserted, it never changes (or changes are appended as new rows, depending on slowly changing dimension strategy).

In Spark, loading and transforming data into a fact table involves extracting from your source (database, API, event stream), applying business logic, and writing to your analytics layer:

from pyspark.sql import SparkSession
from pyspark.sql.functions import col, to_date

spark = SparkSession.builder.appName("FactOrders").getOrCreate()

# Load raw orders from production database
raw_orders = spark.read \
    .jdbc("jdbc:postgresql://prod-db:5432/ecommerce", "orders", properties) \
    .filter(col("status") != "cancelled")

# Join with customer and product dimensions (already loaded)
# to get surrogate keys
customers = spark.read.table("dim_customers")
products = spark.read.table("dim_products")

fact = raw_orders \
    .join(customers, raw_orders.customer_id == customers.source_id, "left") \
    .join(products, raw_orders.product_id == products.source_id, "left") \
    .select(
        col("order_id"),
        to_date(col("created_at")).alias("order_date"),
        col("customers.customer_key"),
        col("products.product_key"),
        col("region_key"),  # Usually comes from product or customer dim
        col("quantity"),
        col("total_amount").alias("revenue"),
        col("cost_amount").alias("cost"),
        col("discount_amount").alias("discount"),
        col("created_at")
    )

fact.write.mode("overwrite").parquet("s3://warehouse/fact_orders/")

Fact tables are the answer to "What happened?" They record every event we care about, with enough detail for aggregation but no more.

Dimension tables for entities

Dimensions describe the who, what, where, when of facts. They are the context. A dimension table is wide, short, and changes slowly. Common dimensions include customers, products, dates, regions, and channels.

Let's build a dim_customers dimension:

# Dimension table: customers
# This is a slowly changing dimension (SCD Type 2 in practice)

CREATE TABLE dim_customers (
    customer_key INT PRIMARY KEY,        -- Surrogate key
    customer_id INT,                      -- Source system ID
    customer_name VARCHAR(255),
    email VARCHAR(255),
    country VARCHAR(100),
    registration_date DATE,
    lifetime_segment VARCHAR(50),        -- Derived: Bronze, Silver, Gold
    is_active BOOLEAN,
    valid_from DATE,                      -- SCD tracking
    valid_to DATE,
    dbt_updated_at TIMESTAMP
);

# Example rows:
# customer_key | customer_id | customer_name     | email           | country | lifetime_segment
# 1            | 101         | Alice Johnson     | alice@ex.com    | US      | Gold
# 2            | 102         | Bob Smith         | bob@ex.com      | CA      | Silver
# 3            | 103         | Carol Lee         | carol@ex.com    | US      | Bronze

Key characteristics of dimensions:

• They are denormalized. Customer data like name, email, and country live in one row, not split across tables.
• They contain descriptive attributes that analysts filter and group by.
• They change slowly and predictably (a customer changes their country rarely; a product category never changes).
• They have a surrogate key (more on this below) that links to fact tables.

Loading a dimension in Spark often involves a merge or upsert pattern, especially if you track history:

from pyspark.sql.functions import max, row_number, col, current_date
from pyspark.sql.window import Window

# Load raw customer data from source
raw_customers = spark.read \
    .jdbc("jdbc:postgresql://prod-db:5432/ecommerce", "customers", properties)

# Check for changes and assign surrogate keys
existing_dim = spark.read.table("dim_customers").filter(col("valid_to").isNull())

# Use row_number to generate surrogate keys for new records
window_spec = Window.orderBy("customer_id")
new_customers = raw_customers \
    .anti_join(existing_dim, raw_customers.id == existing_dim.customer_id) \
    .withColumn("customer_key", row_number().over(window_spec) + 1000) \
    .select(
        col("customer_key"),
        col("id").alias("customer_id"),
        col("name").alias("customer_name"),
        col("email"),
        col("country"),
        col("created_at").alias("registration_date"),
        col("tier").alias("lifetime_segment"),
        col("active").alias("is_active"),
        current_date().alias("valid_from"),
        col("valid_to")  # NULL for active records
    )

new_customers.write.mode("append").saveAsTable("dim_customers")

Surrogate keys

A surrogate key is an artificial primary key, usually an integer or UUID, that we generate in the analytics layer rather than relying on source system IDs. It decouples our analytics schema from production, which is crucial for maintainability.

Why not use the production customer ID directly as a foreign key in fact tables?

• Production systems reuse IDs after deletions, causing collisions in historical data.
• Mergers and system migrations result in ID conflicts.
• Changing how we identify an entity (e.g., moving from email to phone number) breaks joins.
• Surrogate keys let us track slowly changing dimensions without duplicating fact rows.

A basic surrogate key strategy in Spark:

from pyspark.sql.functions import monotonically_increasing_id, dense_rank
from pyspark.sql.window import Window

# Strategy 1: Use monotonically_increasing_id (Spark built-in)
dim_products = spark.read.table("raw_products")
dim_with_keys = dim_products \
    .withColumn("product_key", monotonically_increasing_id())

# Strategy 2: Use dense_rank for deterministic keys
# Better for reproducibility
dim_regions = spark.read.table("raw_regions")
window_spec = Window.orderBy("region_name")
dim_regions_keyed = dim_regions \
    .withColumn("region_key", dense_rank().over(window_spec))

dim_regions_keyed.write.mode("overwrite").saveAsTable("dim_regions")

Most teams settle on either auto-incrementing integers (simple to index) or hashed UUIDs (portable across systems). The key principle: generate them once, consistently, and never change them.

Model for how analysts query

A star schema is built backward from the questions analysts ask. Before you design any table, ask: What metrics do we track? How do they filter and group data?

A typical analyst query on our star schema might be:

"Show me revenue, average order value, and order count by product category and region for the past quarter, broken down by month."

This query naturally maps to our schema:

SELECT
    DATE_TRUNC('month', fo.order_date) AS month,
    dp.category,
    dr.region_name,
    SUM(fo.revenue) AS total_revenue,
    AVG(fo.revenue) AS avg_order_value,
    COUNT(*) AS order_count
FROM fact_orders fo
JOIN dim_products dp ON fo.product_key = dp.product_key
JOIN dim_regions dr ON fo.region_key = dr.region_key
WHERE fo.order_date >= DATE '2024-01-01'
    AND fo.order_date < DATE '2024-04-01'
GROUP BY DATE_TRUNC('month', fo.order_date), dp.category, dr.region_name
ORDER BY month DESC, total_revenue DESC;

Notice:

• Only three table joins. No multi-hop navigation through normalized relationships.
• Aggregations are straightforward: sum revenue, count rows, compute average.
• Filters on dimensions (category, region) and facts (date range) are clear.
• The query scans a subset of columns, so even on large fact tables, Spark can execute it efficiently.

This is why we denormalize. The analyst writes a simple, readable query and it runs fast because the schema matches their mental model of the business.

Common mistakes

Copying the production schema verbatim. We see teams load their normalized production database directly into Parquet and call it done. Analysts then write slow, brittle queries. Invest in proper transformation upfront.

Storing too many dimensions in a single fact table. Some schemas include dozens of dimension keys per fact row. This makes the fact table wide and slow to scan. Keep fact tables narrow; most queries don't need all dimensions.

Treating fact tables as mutable. If you allow updates to historical fact rows, you break aggregations. Once an order is recorded, it's recorded. Corrections become new rows or reversals, not overwrites.

Neglecting data quality validation. Missing foreign keys, null measures, or duplicate rows silently corrupt dashboards. Add validation steps before writes: check that every product_key in the fact table exists in dim_products, that revenue is never negative, that dates are reasonable.

Ignoring slowly changing dimensions. Customer attributes change. When a customer moves to a new region or upgrades their tier, how do you handle historical queries? Do you want reports to reflect current attributes or attributes as they were on the order date? Plan this before you code.

Key takeaways

• Star schemas denormalize strategically: fact tables store events with numeric measures; dimension tables store context and attributes.
• Fact tables are narrow, tall, and immutable. Dimension tables are wide, short, and slowly changing.
• Surrogate keys decouple your analytics layer from production systems, making schemas resilient to migrations and deletions.
• Design schemas by reverse-engineering analyst queries. Simple queries mean fast execution and fewer bugs.
• Add validation to fact and dimension pipelines to catch quality issues before they reach dashboards.
• Star schemas still matter because they make analytics accessible. When analysts can write clear SQL without a PhD in your schema, you've modeled it right.