Data Warehouse Schema Design: Star Schema to Galaxy

If you need a data warehouse schema that analysts trust and BI tools love, start with a clear grain, build a clean star schema, and only snowflake or diversify when you have a reason. This practical guide to data warehouse schema design shows how to design, implement, and evolve a schema that supports consistent metrics, fast query performance, and sustainable maintenance. We’ll model a realistic orders domain, write the SQL, show dbt/YAML tests, and cover when to adopt a galaxy schema with multiple fact tables. You’ll leave with a repeatable playbook you can ship this week.

What is schema design in a data warehouse?

Schema design in a data warehouse defines how facts, dimensions, and their keys are organized so downstream teams can do reliable analytics. The goal is simple: consistent metrics at scale with minimal rework. Facts hold measurements at a chosen grain; dimensions provide descriptive attributes for slicing and grouping. A good data warehouse schema aligns to business processes, reduces data redundancy, and optimizes query paths.

What is a schema in database design?

A schema is the structural blueprint of tables, columns, keys, and constraints. In transactional systems, schemas focus on integrity and write efficiency. In warehousing, schemas emphasize analytical access patterns, stable keys, and clear data relationships.

What is the difference between a database schema and a data warehouse schema?

A database schema for OLTP normalizes heavily to avoid anomalies and optimize writes. A data warehouse schema prioritizes read paths, dimensional modeling, and reporting semantics. Expect denormalized patterns around measurements, slowly changing attributes, and conformed dimensions shared across data marts.

Which schema is used in a data warehouse?

Most teams start with a star schema for simplicity and performance, then introduce selective snowflaking or a galaxy schema as scope grows. Here’s a quick comparison for common schemas in data warehousing.

This article focuses on the practical side: how to pick a schema type and implement it well. For conceptual contrasts of star vs snowflake, see the Data Modeling topic hub.

Practical walkthrough: model an orders domain

Scenario: your orders table has 40M rows per year. You need daily GMV, AOV, retention by cohort, and product/category performance. Start with a star schema.

1) Define the grain and measures

• Grain: one row per order line (order_id + line_id). This supports item-level metrics.
• Measures: revenue, quantity, discount, tax, shipping_cost.
• Conformed dimensions: customer, product, date, store/channel.
• Profile source data to confirm keys, null rates, and outliers before you lock the grain.

2) Create the fact table

Use a surrogate key strategy for dimensions and keep the fact table narrow and additive. Choose numeric types carefully given data volume. Document the foreign key relationships so BI tools and developers know the intended join paths.

-- Fact table: orders at line grain
create table mart.fct_order_lines as
select
  ol.order_id,
  ol.line_id,
  d.date_key,
  c.customer_key,
  p.product_key,
  ch.channel_key,
  ol.quantity,
  ol.unit_price,
  ol.discount_amount,
  ol.tax_amount,
  ol.shipping_amount,
  (ol.quantity * ol.unit_price) - ol.discount_amount as revenue
from staging.order_lines ol
join dim.dim_date d on d.date = date(ol.ordered_at)
join dim.dim_customer c on c.customer_sk = ol.customer_sk
join dim.dim_product p on p.product_sk = ol.product_sk
join dim.dim_channel ch on ch.channel_sk = ol.channel_sk;

Note: the fact table references each dimension via a surrogate key; think of each as a foreign key relationship for join paths, even if your warehouse doesn’t enforce constraints.

3) Build dimension tables

Dimensions are descriptive. Use Type 2 (when appropriate) to track historical data changes for attributes like customer_tier or product_category. For an overview of snapshotting approaches, defer to dbt Snapshots: Playbook.

-- Dimension table: customer (Type 2 ready)
create table dim.dim_customer as
select
  cast(md5(concat_ws('||', customer_id, valid_from)) as bigint) as customer_key, -- surrogate
  customer_id, -- natural key
  first_name,
  last_name,
  email,
  customer_tier,
  valid_from,
  valid_to,
  is_current
from int.customer_scd2;

-- Dimension table: product (denormalized with category)
create table dim.dim_product as
select
  cast(md5(product_id) as bigint) as product_key,
  product_id,
  product_name,
  brand,
  category_id,
  category_name
from int.product_flat;

Choosing surrogate vs natural keys impacts durability and joins; see Surrogate vs Natural Keys for guidance.

4) Add dbt tests to protect data quality

At minimum: uniqueness and not_null on keys, and referential tests from facts to dimensions.

# models/marts/schema.yml
version: 2
models:
  - name: fct_order_lines
    tests:
      - not_null:
          column_name: order_id
      - relationships:
          to: ref('dim_customer')
          field: customer_key
          column: customer_key
  - name: dim_customer
    tests:
      - unique:
          column_name: customer_key
      - not_null:
          column_name: customer_key

Organize your layers for clarity and reuse. For project layout patterns, see dbt Project Structure.

5) Query the star schema

Typical query patterns are straightforward and fast. Keep measures additive at the fact grain and use dimension table attributes for grouping.

-- GMV and AOV by week and channel
select
  d.week_start_date,
  ch.channel_name,
  sum(f.revenue) as gmv,
  sum(f.revenue) / nullif(count(distinct f.order_id), 0) as aov
from mart.fct_order_lines f
join dim.dim_date d on f.date_key = d.date_key
join dim.dim_channel ch on f.channel_key = ch.channel_key
where d.date between date '2026-01-01' and date '2026-03-31'
group by 1,2
order by 1,2;

When to snowflake a dimension

Keep dimensions denormalized until you feel pain from maintenance or truly repeated hierarchies. Then normalize selectively.

-- Snowflake the product hierarchy into related tables
create table dim.dim_category as
select
  cast(md5(category_id) as bigint) as category_key,
  category_id,
  category_name,
  parent_category_id
from int.category_raw;

alter table dim.dim_product add column category_key bigint;

update dim.dim_product p
set category_key = c.category_key
from dim.dim_category c
where p.category_id = c.category_id;

Trade-off: less duplication, but more joins per query. Only normalize what you must. If you need deep hierarchies or multiple dimension tables share sub-hierarchies, snowflaking helps.

Galaxy schema: handling multiple fact tables

As scope expands, you’ll add payments, shipments, and events. A galaxy schema aligns multiple fact tables to conformed dimensions (customer, product, date, channel). This supports cross-process metrics without redefining entities and keeps a central fact table compatible with new processes. If you’re running multiple fact tables across teams, treat conformance as a contract.

-- Additional facts aligned to conformed dimensions
create table mart.fct_payments as
select
  p.payment_id,
  d.date_key,
  c.customer_key,
  p.amount,
  p.method
from staging.payments p
join dim.dim_date d on d.date = date(p.paid_at)
join dim.dim_customer c on c.customer_id = p.customer_id and c.is_current;

create table mart.fct_shipments as
select
  s.shipment_id,
  d.date_key,
  c.customer_key,
  s.carrier,
  s.shipping_cost
from staging.shipments s
join dim.dim_date d on d.date = date(s.shipped_at)
join dim.dim_customer c on c.customer_id = s.customer_id and c.is_current;

With a galaxy schema, be strict about conformance: shared keys, shared naming, consistent semantics. If your model evolves from a central fact table to multiple processes, keep dimensions stable to avoid breaking downstream data analysis.

Design steps you can reuse

1. Identify business process and grain first. Do not start from raw data tables.
2. Define additive and semi-additive measures early.
3. Select conformed dimensions; prefer wide, denormalized first, then normalize only if needed.
4. Choose robust keys and document them across fact and dimension tables.
5. Model SCD where business meaning changes over time.
6. Test relationships and uniqueness; automate checks for nulls and outliers.
7. Plan for incremental loads; use CDC when available.
8. Validate with representative queries and BI dashboards before you commit.

For CDC strategies, see Change Data Capture Patterns. For warehouse-lake hybrid choices in a modern data warehouse, see What Is a Lakehouse?.

Performance and operations

Star schemas typically generate fewer joins and scan less data than snowflaked models. On columnar engines, co-locate dimensions and facts in the same physical schema or database to simplify access controls. Partition or cluster fact tables by date to keep scans tight, and use incremental models to cap costs. If a dimension table explodes in width, consider separating cold attributes into a secondary dimension to keep hot-query paths lean.

Operationally, treat dimensions like software: version them, test them, and snapshot slowly changing attributes. Upstream data sources change; protect yourself with contracts, schema tests, and lineage. Strong governance pays for itself in avoided rework and improved data quality.

FAQ

How do you design a data warehouse schema?

Pick a business process and its grain, list measures, define dimensions, decide keys, then prototype a star schema. Validate with real queries, add tests, and only add snowflaking or a galaxy if maintainability or reuse demands it. This is the core modeling technique behind dimensional modeling.

How to design a data warehouse schema for complex or normalized data?

Start with a simple SQL statement against your star schema. When complexity grows (many-to-many, deep hierarchies), normalize the dimension segments that cause pain and keep the rest denormalized. Consider bridge tables for fuzzy or multi-valued attributes. Use conformed dimensions to align multiple fact tables.

Which schema is used in a data warehouse?

Use a star schema by default. Introduce a snowflake schema where hierarchies are shared across domains, and a galaxy schema when you need multiple fact tables across processes. These are the most common schemas in data warehousing.

Is Star Schema OLAP or OLTP?

OLAP. A star schema is optimized for analytical read patterns, not transactional writes.

Is Star Schema still relevant?

Yes. Even with scalable engines, a star schema simplifies modeling, improves consistency, and keeps queries predictable. It’s the steady base under metrics layers and semantic tooling.

Is it possible to derive insights from raw data?

You can explore raw data, but repeatable insights require clear grain, keys, and definitions. Build a star schema (or galaxy) to stabilize semantics; then use marts and dashboards reliably.

What is a data warehouse schema versus a database schema?

A data warehouse schema centers on facts and dimensions for analytics, while a database schema for OLTP centers on normalized entities for transactions. Different goals, different shapes.

Worked example: end-to-end path

Let’s wire the critical pieces together with dbt. Assume CDC flows from operational tables into staging. We snapshot customer attributes and roll them into a dimension, then assemble the fact. This fits cleanly into your data warehouse architecture and scales across domains.

-- Example incremental fact with date partitioning
{{ config(materialized='incremental', unique_key='order_id||line_id') }}
select
  ol.order_id,
  ol.line_id,
  d.date_key,
  c.customer_key,
  p.product_key,
  ch.channel_key,
  ol.quantity,
  ol.unit_price,
  ol.discount_amount,
  ol.tax_amount,
  ol.shipping_amount,
  (ol.quantity * ol.unit_price) - ol.discount_amount as revenue
from {{ ref('stg_order_lines') }} ol
join {{ ref('dim_date') }} d on d.date = date(ol.ordered_at)
join {{ ref('dim_customer') }} c on c.customer_id = ol.customer_id and c.is_current
join {{ ref('dim_product') }} p on p.product_id = ol.product_id
join {{ ref('dim_channel') }} ch on ch.channel_id = ol.channel_id
{% if is_incremental() %}
where ol.ordered_at >= dateadd(day, -2, current_date)
{% endif %};

This pattern can scale cleanly across domains in your data warehouse. Keep the star shape stable while upstream pipelines evolve.

Normalization guidance in one page

• Normalize if: the dimension is reused widely; attributes form nested hierarchies; change control or stewardship is separate.
• Do not normalize if: you add joins that do not reduce maintenance; performance matters more than minimal duplication; the attributes change rarely.
• Revisit choices quarterly; your model will evolve as products and teams grow.

Edge cases and patterns

• Many-to-many between facts and dimensions: use a bridge table with weights or counts.
• Multi-valued attributes (e.g., tags): either flatten into arrays for convenience or bridge for strictness.
• Slow attributes: snapshot and expose both current and historical views.
• Late arriving dimensions: create unknown members and backfill keys once records appear.
• Keep fact and dimension aligned: consistent naming, time zones, and currency handling across fact and dimension tables.

Governance, change, and evolution

Conformed dimensions are contracts. When you add attributes or split a dimension, maintain compatibility. Use snapshots for SCD2, and publish change logs. If your organization leans into product-oriented ownership, align schemas to product boundaries while keeping conformed entities shared.

On ingestion, prefer CDC over full reloads to control cost and latency; see CDC Patterns. Track lineage so you can find all downstream models that depend on a central fact table before you deploy breaking changes.

Putting it all together

A solid data warehouse starts with a clear star schema, adds snowflake schema patterns when hierarchies demand it, and grows into a galaxy schema as more processes join the platform. Use dbt to codify contracts and tests, choose surrogate keys wisely, and keep facts narrow and dimensions descriptive. When needed, normalize; otherwise, embrace denormalized data for speed and simplicity. As you scale, a galaxy keeps multiple fact tables in harmony via conformed dimensions.

Glossary checkpoints

• Data warehouse: Analytical store optimized for reads and conformed reporting.
• Star schema: Fact at the center, dimensions around it.
• Snowflake schema: Dimensions normalized into related tables.
• Galaxy schema: Multiple facts share conformed dimensions.
• Fact table: Measurements at a declared grain.
• Dimension table: Descriptive attributes with stable keys.

Final notes for practitioners

Keep semantics stable. Prefer explicitness over cleverness. Validate with real stakeholders using real BI queries. Revisit your choices as teams, latency, and workloads change. This approach scales across a modern data warehouse and supports predictable growth without chaos. For deeper background, see the Data Modeling hub, dbt Project Structure, and dbt Snapshots.

Before you go: build your skills with free graded practice exercises at /practice.