Data Modeling in Redshift: A Practical Guide

A data model in Redshift has to satisfy two readers. One is the analyst, who needs tables that are easy to join and hard to misuse. The other is the engine, a columnar, massively parallel database that rewards some designs and punishes others. The good news is that the model analysts prefer, a star schema, is also one the engine handles well. The work is in the details Redshift treats differently from an ordinary database.

This guide assumes the concepts of facts and dimensions; star schema, snowflake and data vault covers them.

Start with the star

A star schema has a fact table, one row per event at a stated grain, surrounded by dimension tables describing who, what, where and when. For a music store: one row per invoice line, with dimensions for customer, track and date.

In Redshift this shape works for three reasons. Columnar storage means a wide fact table costs little, since queries read only the columns they name. Joins from a fact to small dimensions are cheap. And BI tools generate exactly these joins.

The DDL

Written from the documentation and not run; the model is illustrative:

CREATE TABLE dim_customer (
  customer_key  INTEGER      NOT NULL,
  customer_id   INTEGER      NOT NULL,   -- key in the source system
  country       VARCHAR(40),
  segment       VARCHAR(20),
  valid_from    DATE         NOT NULL,
  valid_to      DATE,
  is_current    BOOLEAN      NOT NULL,
  PRIMARY KEY (customer_key)
)
DISTSTYLE KEY DISTKEY (customer_key);

CREATE TABLE dim_date (
  date_key     INTEGER  NOT NULL,        -- 20260115
  full_date    DATE     NOT NULL,
  year         SMALLINT NOT NULL,
  month        SMALLINT NOT NULL,
  PRIMARY KEY (date_key)
)
DISTSTYLE ALL;

CREATE TABLE fact_invoice_line (
  invoice_line_id BIGINT        NOT NULL,
  date_key        INTEGER       NOT NULL REFERENCES dim_date (date_key),
  customer_key    INTEGER       NOT NULL REFERENCES dim_customer (customer_key),
  track_key       INTEGER       NOT NULL,
  quantity        SMALLINT      NOT NULL,
  amount          DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (invoice_line_id)
)
DISTSTYLE KEY DISTKEY (customer_key)
SORTKEY (date_key);

Each choice has a reason.

Keys are promises, not guards

This is the difference that catches people. In Redshift, uniqueness, primary key and foreign key constraints are informational only; they are not enforced when you load a table (Redshift docs: table constraints). The PRIMARY KEY above will not stop a duplicate customer_key from being inserted. Only NOT NULL is enforced.

They are still worth declaring. The planner uses them as hints to order joins and remove redundant ones, and the best-practice guide says to define them wherever appropriate. The condition attached is strict: the planner assumes declared keys are valid, and if they are not, some queries can return incorrect results. The guide's example is a SELECT DISTINCT returning duplicate rows. Its advice is not to define key constraints if you doubt their validity.

So integrity moves into the pipeline. After every load, test it:

-- Should return no rows
SELECT customer_key, COUNT(*)
FROM dim_customer
GROUP BY customer_key
HAVING COUNT(*) > 1;

-- Fact rows with no matching dimension row; should return 0
SELECT COUNT(*)
FROM fact_invoice_line AS f
LEFT JOIN dim_customer AS c ON c.customer_key = f.customer_key
WHERE c.customer_key IS NULL;

Tests of this kind are what tools such as dbt run as unique and relationships tests.

Distribution and sort, at model level

The table design guide names sort key and distribution style first among the decisions that influence performance (Redshift docs: designing tables). In a star schema the reasoning is:

• A fact table has one distribution key. Choose the dimension it is joined to most heavily, and give that dimension the same key, so matching rows sit on the same slice. Here that is the customer.
• Other dimensions cannot be collocated. For those the guide suggests considering ALL distribution, which puts a full copy on every node (Redshift docs: best distribution style). The date dimension above uses it. The distribution styles page adds a caution: for small dimension tables there is not a significant benefit, because redistributing them during a query is cheap.
• Sort the fact table on the column most queries filter by, usually the date.
• AUTO is a legitimate answer. It is the default for both properties, and Redshift adjusts tables as it observes queries. Specify keys by hand when you know the dominant join and can measure the result.

Redshift distribution and sort keys explains the mechanics and how to check for skew.

Note what is absent: partitions. Redshift does not require or support partitioning data within database objects. Sort keys and block metadata do the pruning that partitions do elsewhere.

Column types matter more than usual

• Use the smallest size that fits. The guide warns against maximum-width columns chosen for convenience. A country code is CHAR(2), not VARCHAR(65535).
• Store dates as DATE or TIMESTAMP, which Redshift stores more efficiently than character types.
• Use integer surrogate keys for joins. Redshift offers IDENTITY columns to generate them; the values are unique, but their order may not match the order of the source rows.
• Leave compression to Redshift. ENCODE AUTO is the default.

History in dimensions

dim_customer above carries valid_from, valid_to and is_current: a Type 2 slowly changing dimension, where a change inserts a new row instead of overwriting the old one. Facts point at the surrogate customer_key, so each sale stays attached to the customer as they were at the time. Redshift's MERGE statement, run from a staging table, is the usual way to apply such changes; loading data into Redshift with COPY shows the staging pattern.

Semi-structured data

JSON does not have to be flattened on arrival. The SUPER type stores semi-structured values, up to 16 MB per object, and AWS recommends it over VARCHAR for that purpose (Redshift docs: SUPER type). A sensible pattern is to land the raw document in a SUPER column in a staging table and extract the attributes that reports use into ordinary typed columns.

Summaries and wide tables

Two additions sit comfortably beside a star:

CREATE MATERIALIZED VIEW mv_monthly_revenue AS
SELECT d.year, d.month, c.country, SUM(f.amount) AS revenue
FROM fact_invoice_line AS f
JOIN dim_date AS d ON d.date_key = f.date_key
JOIN dim_customer AS c ON c.customer_key = f.customer_key
GROUP BY d.year, d.month, c.country;

A materialized view stores a precomputed result and suits queries that are predictable and repeated, such as those behind a dashboard (Redshift docs: materialized views).

A wide, denormalised table that pre-joins the fact with its dimensions is the other option. Columnar storage makes the repeated values cheap to store, and queries need no joins. The price is paid on change: a renamed segment means rewriting many rows. Keep the star as the source of truth and build wide tables from it.

Mistakes to avoid

• Copying the source system's normalised schema into the warehouse unchanged.
• Trusting declared keys without testing them.
• Oversized character columns everywhere.
• A different distribution key on each side of the main join.
• Updating rows one at a time instead of in batches.

For the engine underneath these choices, see Amazon Redshift explained.

Data warehousing, star and snowflake schemas and Amazon Redshift are covered in the Data Engineering course.

Sources

• Amazon Redshift best practices for designing tables (Amazon Redshift Database Developer Guide) — AWS Documentation
• Table constraints (Amazon Redshift Database Developer Guide) — AWS Documentation
• Choose the best distribution style (Amazon Redshift Database Developer Guide) — AWS Documentation
• Distribution styles (Amazon Redshift Database Developer Guide) — AWS Documentation
• Materialized views in Amazon Redshift (Amazon Redshift Database Developer Guide) — AWS Documentation
• SUPER type (Amazon Redshift Database Developer Guide) — AWS Documentation