Most Redshift performance work we get called into is not a tuning problem. It is a modeling problem that surfaced as a tuning problem: a 40-table "normalize everything" schema lifted from an OLTP database, or a single 900-column wide table that every dashboard scans end to end. Redshift is an MPP columnar engine, and the shape of your tables decides how much data moves between slices. That is the cost driver nothing else fully compensates for.
This is a design guide for the dimension/fact layer on Amazon Redshift in 2026: when a star schema still wins, when to denormalize, how distribution and sort interact with that choice, and how to validate the model before it has three years of data in it.
Why star schemas still win on Redshift
The marketing line is that modern engines make modeling obsolete. In practice, on Redshift:
- Joins on the distribution key are local. A fact table distributed on
customer_idjoined to a customer dimension distributed oncustomer_idis aDS_DIST_NONEjoin: no data crosses the network. The same join against a differently distributed table can becomeDS_BCAST_INNERorDS_DIST_BOTH, which is where the seconds go. - Small dimensions are free.
DISTSTYLE ALLputs a full copy on every node. A 200,000-row dimension replicated everywhere removes the redistribution entirely, at the cost of storage you will not notice. - Columnar scans reward narrow facts. A fact table of keys plus measures gives you very high compression and small per-query scan footprints. Attribute columns dragged into the fact inflate every scan whether the query needs them or not.
A conformed star — narrow facts, DISTSTYLE ALL dimensions, surrogate or natural keys with consistent types — remains the default we recommend for a shared analytics layer.
The shape, concretely
CREATE TABLE dim_customer (
customer_key BIGINT NOT NULL, -- surrogate, stable
customer_id VARCHAR(64) NOT NULL, -- natural key from the source
customer_name VARCHAR(256),
segment VARCHAR(32),
country_code CHAR(2),
valid_from TIMESTAMP NOT NULL,
valid_to TIMESTAMP,
is_current BOOLEAN NOT NULL,
PRIMARY KEY (customer_key) -- informational only, see below
)
DISTSTYLE ALL
SORTKEY (customer_id, valid_from);
CREATE TABLE fact_order_line (
order_line_id BIGINT NOT NULL,
order_id BIGINT NOT NULL,
customer_key BIGINT NOT NULL,
product_key BIGINT NOT NULL,
order_ts TIMESTAMP NOT NULL,
quantity INTEGER,
net_amount DECIMAL(18,4),
discount_amount DECIMAL(18,4)
)
DISTSTYLE KEY
DISTKEY (customer_key)
COMPOUND SORTKEY (order_ts, product_key);
Three decisions are embedded there, and each is worth stating explicitly.
Distribute the fact on the join key you actually filter and group by most. Not the highest-cardinality column for its own sake; the one that appears in the heavy joins. If your heaviest workload is customer-level aggregation, customer_key is right. If it is product analytics, it is not.
Sort on the time column you filter on. Nearly every fact query has a date range predicate. A leading order_ts in the sort key lets zone maps skip most blocks. This is the single highest-leverage choice in the DDL.
Constraints are not enforced. Redshift accepts PRIMARY KEY, UNIQUE and FOREIGN KEY and uses them for planning, but never checks them. Declare them — the optimizer genuinely uses them for join elimination and subquery decorrelation — and then test them, because nothing else will.
Where to denormalize, and where not to
Denormalization on Redshift is a tool, not a philosophy. Our rule of thumb:
| Situation | Model |
|---|---|
| Dimension under ~5M rows, reused by many facts | Keep separate, DISTSTYLE ALL |
Attribute used as a filter on nearly every fact query (e.g. country_code) | Duplicate it into the fact as well |
| Dimension with high churn, SCD2 history needed | Keep separate; versioning a wide fact is miserable |
| One BI tool, one dashboard family, fixed questions | A wide pre-joined table or materialized view on top of the star |
| Hierarchies 4+ levels deep (org, product taxonomy) | Flatten into the dimension, not into the fact |
Note the last row of the "wide table" case: build the wide table on top of the star, as a materialized view or a dbt model, rather than instead of it. You keep one governed source of truth and still serve the flat shape BI tools like. Redshift's incremental materialized view refresh handles many of these cheaply, and auto-refresh means the dashboard layer stays current without an orchestration step.
Grain, first and in writing
Every fact table needs one sentence: one row per ___. "One row per order line, after returns are applied." If you cannot write that sentence, the table will end up with double-counting measures and nobody will be able to say whether a number is wrong.
Two practical consequences:
- Additive, semi-additive, non-additive.
net_amountsums across every dimension. A daily balance snapshot sums across accounts but not across time. A ratio sums across nothing — store the numerator and denominator, compute the ratio in the query. - Factless facts are fine. Events with no measure (a login, a page view, an eligibility window) still belong as fact tables; the measure is the count.
Surrogate keys without a sequence
Redshift has IDENTITY columns, but values are not gap-free and not ordered across slices, and IDENTITY interacts badly with reloads. For dimension surrogate keys we usually prefer a deterministic hash of the natural key plus the version timestamp:
-- Deterministic, reload-safe, parallel-friendly
SELECT
STRTOL(LEFT(MD5(customer_id || '|' || TO_CHAR(valid_from,'YYYYMMDDHH24MISS')), 15), 16)
AS customer_key,
customer_id,
...
FROM stg_customer;
Deterministic keys mean a full rebuild produces the same keys, so facts loaded earlier still join. If you need human-readable keys, keep a separate key-map table and MERGE into it.
Joining a fact to an SCD2 dimension at load time ("point-in-time lookup") is the step people skip:
UPDATE fact_order_line f
SET customer_key = d.customer_key
FROM dim_customer d
WHERE d.customer_id = f.src_customer_id
AND f.order_ts >= d.valid_from
AND f.order_ts < COALESCE(d.valid_to, '2999-01-01');
Resolve the key once, at load. Never make dashboards do a range join against history at query time.
Column types are a modeling decision
Three that consistently cost people money:
VARCHARoversizing.VARCHAR(65535)everywhere forces Redshift to allocate on the declared width for intermediate results and hash tables, even though storage compresses. Size to the real maximum plus headroom.DECIMALvsDOUBLE PRECISION. Money isDECIMAL. Scientific measures are floats. Mixing them in a join key is a silent performance and correctness trap.SUPERfor genuinely variable payloads only. Semi-structured columns are great for raw event attributes; they are a poor substitute for modeling the 12 fields everybody queries. Shred the hot fields into typed columns and leave the long tail inSUPER.
Let ATO do the second pass
Automatic Table Optimization will adjust sort and distribution keys based on observed workload if you leave them AUTO. That is a good default for tables whose access pattern you cannot predict yet. For the core fact tables of a star schema you usually can predict it, and pinning the keys avoids a few weeks of the system guessing. Check what Redshift would have done either way:
SELECT type, database, table_name, ddl, auto_eligible
FROM SVV_ALTER_TABLE_RECOMMENDATIONS;
If ATO keeps recommending a different distribution key than the one you chose, that is a signal your assumed workload and the real one differ — go look at SYS_QUERY_HISTORY before arguing with it.
Validating the model before the data grows
Do this while the warehouse is small and changes are cheap:
- Check the grain.
SELECT count(*), count(DISTINCT order_line_id) FROM fact_order_line;— these must match. Run it as a scheduled test, not once. - Check referential integrity. Anti-join every fact key against its dimension; a nonzero count means orphan rows and understated totals in BI tools that inner-join.
- Read the plans.
EXPLAINyour five heaviest queries and grep forDS_BCAST_INNERandDS_DIST_BOTH. Each one is a redistribution your model could have avoided. - Check skew.
SELECT slice, COUNT(*) FROM fact_order_line GROUP BY 1 ORDER BY 2 DESC;— if one slice holds a large multiple of the median, your distribution key has a hot value (a null placeholder, a default "unknown" customer) and you wantDISTSTYLE EVENor a salted key instead. - Check unsorted percentage.
SVV_TABLE_INFO.unsortedclimbing on a fact table means loads are arriving out of sort order and your zone maps are decaying.
SELECT "table", size, diststyle, sortkey1,
skew_rows, unsorted, stats_off
FROM SVV_TABLE_INFO
ORDER BY size DESC
LIMIT 25;
That one query is a useful weekly health check for the entire model.
Data Vault, One Big Table, and other reasonable answers
Star schemas are the default, not the only answer. Data Vault suits heavily regulated environments with many source systems and audit requirements, at the cost of a lot of joins — on Redshift that means putting serious thought into hub/link distribution and almost always building a star-shaped presentation layer on top. One Big Table works for narrow, well-understood product analytics where storage is cheaper than the modeling effort. What does not work is arriving at either by accident, which is what happens when a lift-and-shift of an OLTP schema is never revisited.
Getting it right the first time
Modeling mistakes are the most expensive kind on a data warehouse, because every downstream object encodes them and every fix is a migration. If you are standing up a new Redshift warehouse, or your existing one is slow in a way that tuning has stopped helping, our Data Modeling & Architecture practice does exactly this work — and a short Redshift POC is usually enough to prove a model on your real workload before you commit to it. Get in touch if you want a second pair of eyes on a schema.