Data Warehouse Modeling with Claude: Star Schema and Dimensional Modeling

1. Why data warehouse modeling needs its own approach

Data warehouse modeling is fundamentally different from classic OLTP schema design. While a transactional system is optimized for normalization, short write paths and consistency guarantees, an analytical model targets fast read access across large data volumes, an understandable structure for analysts, and stable historical reportability. Mixing up these two worlds produces data warehouse schemas that are normalized, yet unusably slow for reporting queries.

Claude for data warehouse modeling knows the established patterns from Ralph Kimball's dimensional modeling approach: fact tables with metrics, dimension tables with descriptive attributes, and a star schema as the central structure. The value lies in Claude being able to derive a fitting dimensional model from a description of the source system and the planned analyses, instead of making every decision from scratch.

It is important to distinguish this from generic schema design: the question here is not whether a single table is normalized, it is the entire analytical architecture with facts, dimensions, granularity and historical change tracking. The following sections address exactly these specific questions of data warehouse modeling.

2. Designing fact and dimension tables with Claude

The starting point of any data warehouse modeling is the distinction between facts and dimensions. A fact table contains the measurable metrics of a business process, such as revenue, quantity or processing time, together with foreign keys to the associated dimensions. Dimension tables provide the descriptive context, such as product, customer, time or location. Claude helps propose the right split from a description of the business process, including which attributes belong in the fact table and which are better modeled as a dimension.

-- Star schema: fact_orders surrounded by conformed dimensions
CREATE TABLE dim_date (
  date_key        INT PRIMARY KEY,
  full_date       DATE NOT NULL,
  day_of_week     SMALLINT NOT NULL,
  month_name      VARCHAR(20) NOT NULL,
  quarter         SMALLINT NOT NULL,
  fiscal_year     SMALLINT NOT NULL,
  is_weekend      BOOLEAN NOT NULL
);

CREATE TABLE dim_product (
  product_key     SERIAL PRIMARY KEY,
  product_id      INT NOT NULL,     -- natural key from source system
  product_name    VARCHAR(255) NOT NULL,
  category        VARCHAR(100) NOT NULL,
  brand           VARCHAR(100),
  valid_from      DATE NOT NULL,
  valid_to        DATE,
  is_current      BOOLEAN NOT NULL DEFAULT TRUE
);

CREATE TABLE fact_orders (
  order_line_key  BIGSERIAL PRIMARY KEY,
  date_key        INT NOT NULL REFERENCES dim_date(date_key),
  product_key     INT NOT NULL REFERENCES dim_product(product_key),
  customer_key    INT NOT NULL REFERENCES dim_customer(customer_key),
  quantity        INT NOT NULL,
  unit_price      NUMERIC(12,2) NOT NULL,
  net_revenue     NUMERIC(12,2) NOT NULL,
  discount_amount NUMERIC(12,2) NOT NULL DEFAULT 0
);

A common beginner mistake is writing attributes such as the product name directly into the fact table instead of referencing the dimension through the foreign key. This causes redundancy and makes it impossible to track historical changes to the product name. Claude reliably flags such modeling mistakes when you submit the table design for review.

3. Implementing slowly changing dimensions with Claude

Dimension attributes change over time, for example when a customer changes address or a product moves into a different category. Slowly changing dimensions of type 2 solve this problem by inserting a new row with a new surrogate key on a change, instead of overwriting the existing row. This keeps the historical association in past facts correct. Claude for data warehouse modeling generates the corresponding upsert pattern including validity periods.

-- SCD Type 2 upsert: close old version, insert new version
WITH changed_rows AS (
  SELECT s.product_id, s.product_name, s.category, s.brand
  FROM staging_products s
  JOIN dim_product d ON d.product_id = s.product_id AND d.is_current = TRUE
  WHERE d.category IS DISTINCT FROM s.category
     OR d.brand IS DISTINCT FROM s.brand
)
UPDATE dim_product d
SET valid_to = CURRENT_DATE - INTERVAL '1 day',
    is_current = FALSE
FROM changed_rows c
WHERE d.product_id = c.product_id AND d.is_current = TRUE;

INSERT INTO dim_product (product_id, product_name, category, brand, valid_from, valid_to, is_current)
SELECT product_id, product_name, category, brand, CURRENT_DATE, NULL, TRUE
FROM changed_rows;

The critical part of the implementation is that fact tables must never reference the natural key directly, they must always reference the surrogate key of the dimension version that was valid at the time of the business event. Claude can consistently check this referencing logic throughout the ETL process and surface typical mistakes such as accidentally joining against the current instead of the historically correct dimension version.

4. Choosing the right granularity and surrogate keys

The granularity of a fact table, meaning what exactly a row represents, is the most important and hardest to change retroactively design decision of a data warehouse. A fact table at order line grain allows granular analysis, an aggregated table at order grain is more compact but loses detail information. Claude helps determine granularity based on concrete planned questions, for example whether analysts will ever need to evaluate individual product lines within an order.

Using surrogate keys instead of natural keys as the primary key in dimensions is another central pattern. Natural keys from source systems can change, be ambiguous, or collide between multiple source systems. An artificial, sequential surrogate key decouples the warehouse from these uncertainties and is additionally more performant for JOIN operations than composite natural keys.

5. Generating dbt models with Claude

dbt has become the standard tool for keeping transformation logic for data warehouses versioned, testable and documented. Claude for data warehouse modeling generates complete dbt models including schema definitions with tests for uniqueness and referential integrity, which is time consuming to write manually.

# schema.yml — dbt model definitions with built-in data tests
version: 2

models:
  - name: fact_orders
    description: "Order line grain fact table, one row per product line item"
    columns:
      - name: order_line_key
        tests: [unique, not_null]
      - name: date_key
        tests:
          - not_null
          - relationships: { to: ref('dim_date'), field: date_key }
      - name: product_key
        tests:
          - not_null
          - relationships: { to: ref('dim_product'), field: product_key }
      - name: net_revenue
        tests:
          - not_null
          - dbt_utils.accepted_range: { min_value: 0 }

  - name: dim_product
    description: "SCD Type 2 product dimension"
    columns:
      - name: product_key
        tests: [unique, not_null]
      - name: product_id
        tests: [not_null]
      - name: is_current
        tests:
          - accepted_values: { values: [true, false] }

The advantage of this approach is that model definition and data quality checks converge into a single, versioned artifact. Claude can also derive a fitting dbt model, including materialization strategy, from an existing SQL transformation, for example whether a model should be built as a view, table or incremental model.

6. From OLTP source system to dimensional mapping

The transition from a relational source system to a dimensional model requires an explicit mapping: which source tables feed which dimension, which transaction table becomes the fact table, and how multiple source tables get consolidated into a single conformed dimension. Claude helps document this mapping systematically and translate it into executable transformation code.

import pandas as pd

# Map OLTP source tables to the conformed customer dimension
customers_raw = pd.read_sql("SELECT * FROM oltp.customers", oltp_conn)
addresses_raw = pd.read_sql("SELECT * FROM oltp.customer_addresses WHERE is_primary = true", oltp_conn)

dim_customer = (
    customers_raw
    .merge(addresses_raw, on="customer_id", how="left")
    .assign(
        customer_key=lambda df: df.index + 1,
        valid_from=pd.Timestamp.today().normalize(),
        valid_to=pd.NaT,
        is_current=True,
    )
    .rename(columns={"city": "billing_city", "country": "billing_country"})
    [["customer_key", "customer_id", "full_name", "billing_city",
      "billing_country", "valid_from", "valid_to", "is_current"]]
)

dim_customer.to_sql("dim_customer", warehouse_conn, if_exists="append", index=False)

A common mistake with this mapping is processing multiple addresses per customer without a clear priority, which creates duplicates in the dimension. During code review, Claude typically points to defining a clear filter condition such as the primary address before the merge happens.

7. Partitioning and indexing the data warehouse

Fact tables quickly grow into tens of millions of rows, which is why partitioning by time, usually by month or quarter, is a standard pattern. Claude helps propose a fitting partitioning strategy based on the expected query pattern, for example whether most analysts filter by the last twelve months or scan entire histories.

-- Range partitioning by month for fact_orders
CREATE TABLE fact_orders (
  order_line_key  BIGSERIAL,
  date_key        INT NOT NULL,
  product_key     INT NOT NULL,
  customer_key    INT NOT NULL,
  net_revenue     NUMERIC(12,2) NOT NULL,
  order_date      DATE NOT NULL
) PARTITION BY RANGE (order_date);

CREATE TABLE fact_orders_2026_07 PARTITION OF fact_orders
  FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');

CREATE INDEX idx_fact_orders_2026_07_product ON fact_orders_2026_07 (product_key);
CREATE INDEX idx_fact_orders_2026_07_customer ON fact_orders_2026_07 (customer_key);

Without partitioning, queries that only touch the last month still have to scan the entire table, which becomes increasingly noticeable as data volumes grow. Claude also points out when columnar storage, for example in Snowflake or BigQuery, solves the partitioning problem for analytical workloads differently from the start compared to a row oriented database.

8. Validating and testing the warehouse model

A dimensional model without systematic validation leads to silent errors, such as duplicated facts from faulty JOINs or wrong totals from missing filter conditions on historical dimension versions. Claude for data warehouse modeling helps design validation queries that systematically surface exactly these error classes before a new model goes into production.

A proven pattern is reconciling the aggregated sum from the fact table against an independently calculated control total from the source system for the same period. If both values diverge, that points to duplicate rows, lost records during loading, or wrong join conditions. Claude can suggest such reconciliation queries as a fixed part of the deployment pipeline, so model errors surface automatically instead of being discovered first in a wrong management report.

9. OLTP schema versus dimensional model compared

The following table contrasts the key differences between a normalized OLTP schema and a dimensional data warehouse model.

Both modeling worlds have their justification, each solving a different problem. Claude helps build a consistent, well documented dimensional model at the interface between both, without having to repeat the typical modeling mistakes.

10. Summary

Data warehouse modeling with Claude starts with a clear separation of fact and dimension tables in a star schema and the right choice of granularity, before any ETL code exists. Slowly changing dimensions of type 2 secure historical correctness, surrogate keys decouple the warehouse from uncertainties in source systems. dbt models with built-in tests make transformation logic versioned and verifiable.

Partitioning by time period and systematic validation queries against independent control totals round out the approach. Claude does not replace the domain decision about granularity and business metrics, but it delivers a well founded, technically correct draft that avoids the typical modeling mistakes that regularly cause wrong reports in grown data warehouses.

11. FAQ: Data Warehouse Modeling with Claude