How to Design Star vs Snowflake Schemas in Redshift

Which schema—star or snowflake—delivers better performance and maintainability in Amazon Redshift?

Choose, create, and query star and snowflake schemas efficiently in Amazon Redshift.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why compare star and snowflake schemas in Redshift?

Schema choice controls query speed, storage cost, and ETL complexity. Redshift’s columnar engine favors wide tables and fewer joins, making star schemas popular, but snowflake schemas can save space and simplify dimension maintenance.

What is a star schema in Redshift?

A star schema keeps one fact table and several denormalized dimension tables.Each dimension stores descriptive columns directly, letting BI tools join with a single key and Redshift scan fewer tables.

What is a snowflake schema in Redshift?

A snowflake schema further normalizes dimensions into sub-tables. Shared attributes move to child tables, reducing duplication but re-introducing joins.This can lower storage but may slow queries unless you use materialized views or result caching.

When should I pick a star schema?

Pick star schemas for interactive dashboards, ad-hoc exploration, or when analysts need low-latency joins on large fact tables like Orders. Denormalization minimizes joins and maximizes Redshift’s data skipping.

When is a snowflake schema better?

Choose snowflake schemas when dimensions change frequently or share data (e.g., Products and Categories).Normalization avoids update anomalies and shrinks storage, especially with many multilingual attributes.

Does Redshift Spectrum change the decision?

Yes.If you query S3 via Spectrum, a snowflake on Parquet can stay normalized while Redshift’s fact table stays star-like, balancing storage and performance.

Best practices for either schema

1) Sort and distribute the fact table on high-cardinality keys like id or date ranges.
2) Keep dimension tables <1 GB for fast broadcast joins.
3) Add FOREIGN KEY constraints as NOT VALID to aid query planner without enforcement cost.

How to migrate from snowflake to star in Redshift?

Create a new denormalized dimension with CREATE TABLE AS, backfill data with INSERT ...SELECT, then swap tables using ALTER TABLE ... RENAME. Re-run analyze and vacuum to refresh statistics.

Performance tuning tips

Use EXPLAIN to spot unnecessary joins, add materialized views for frequent snowflake joins, and leverage RESULT_CACHE for repeated queries.

Why How to Design Star vs Snowflake Schemas in Redshift is important

How to Design Star vs Snowflake Schemas in Redshift Example Usage


-- Star schema join
SELECT o.id, c.name, o.total_amount
FROM orders_fact o
JOIN customers_dim c ON o.customer_id = c.customer_id
WHERE o.order_date >= DATEADD(day, -30, CURRENT_DATE);

-- Snowflake schema join
SELECT o.id, cp.name, o.total_amount
FROM orders_fact o
JOIN customers_dim c  ON o.customer_id = c.customer_id
JOIN customer_profile_dim cp ON cp.customer_id = c.customer_id
WHERE o.order_date >= DATEADD(day, -30, CURRENT_DATE);

How to Design Star vs Snowflake Schemas in Redshift Syntax


-- Star schema example (denormalized dimension)
CREATE TABLE customers_dim (
  customer_id   INT   DISTKEY SORTKEY,
  name          VARCHAR(255),
  email         VARCHAR(255),
  created_at    TIMESTAMP
);

-- Snowflake schema example (normalized dimensions)
CREATE TABLE customers_dim (
  customer_id INT DISTKEY SORTKEY,
  created_at  TIMESTAMP
);
CREATE TABLE customer_profile_dim (
  customer_id INT,
  name        VARCHAR(255),
  email       VARCHAR(255)
);

Common Mistakes

Blindly denormalizing everything. Oversized dimension tables (>5 GB) negate star-schema speed gains. Fix by denormalizing only high-use attributes and keeping heavy text in a lookup table.
Normalizing small dimensions. Splitting tiny tables increases joins without storage benefit. Fix by leaving dimensions with <1 M rows in a single table.