Redshift table design: DEA-C01 study notes and practice questions
What to know about Redshift table design for the DEA-C01 exam, from the Data Store Management domain (26% of the exam guide), then 2 free questions to test it. Part of the DEA-C01 study guide.
Redshift design questions describe slow queries or uneven nodes and ask which table setting to change.
Distribution styles
Style
Rows are placed
Use for
AUTO
Redshift chooses and adjusts
The default; good starting point
EVEN
Round robin across slices
Tables not joined, or no good key
KEY
By the hash of one column
Large tables joined on that column: give both the same distribution key so joins run locally (collocated joins)
ALL
A full copy on every node
Small, slowly changing dimension tables joined often
A KEY column with few values or many nulls causes skew: one slice does most of the work. Check SVV_TABLE_INFO (the skew_rows column) and change the key with ALTER TABLE ... ALTER DISTSTYLE or ALTER DISTKEY.
Sort keys
Data is stored in sort key order, and zone maps let queries skip blocks outside a filter range.
Choose columns used in range filters, such as dates.
Compound sort keys (the default type) suit filters on the leading columns; AUTO lets Redshift choose.
VACUUM re-sorts and reclaims space (often automatic); ANALYZE updates statistics.
Other design points
Compression encodings (often AUTO, such as AZ64 and ZSTD) cut storage and I/O.
Workload management (WLM): automatic WLM with queue priorities, concurrency scaling for bursts of queries, and short query acceleration.
Query monitoring rules log, hop or abort long-running queries.
RA3 nodes and Redshift Serverless separate compute from storage; Serverless scales with RPUs.
EXPLAIN shows the plan: look for DS_BCAST_INNER or DS_DIST_BOTH (data movement) in joins.
Common traps
ALL distribution for a large fact table. It copies the whole table to every node.
A distribution key with low cardinality causing skew.
Adding nodes to fix skew. The hot slice stays hot.
A dental chain loads 2 billion appointment rows into an Amazon Redshift table. Most dashboard queries join the appointments to a patients table of about 800 MB, and they filter on appointment_date. A clinics table with 300 rows is joined in almost every query.
Most joins currently redistribute data across nodes, and the dashboards are slow. The team wants the joins to run locally and the date filters to skip blocks of rows.
Which table design meets these requirements?
Choose one answer.
AA. Use DISTSTYLE EVEN for all three tables, and set appointment_id as the sort key on each table so that the rows are ordered by the primary key.
BB. Use DISTKEY on appointment_date for the appointments table, DISTSTYLE ALL for the patients table, and a sort key on clinic_id.
CC. Use DISTSTYLE ALL for the appointments table, because the joins become local, and a sort key on patient_id for the patients table.
DD. Use DISTKEY on patient_id for appointments and patients, DISTSTYLE ALL for clinics, and a sort key on appointment_date.
Show the answer and explanation
Answer: D
A. Wrong: Even distribution spreads rows without regard to the join key, so joined rows are still moved between nodes. Sorting on the primary key does not help the date filters or the joins.
B. Wrong: Distributing on appointment_date concentrates rows on a few slices because dates cluster, and the patients join still needs redistribution. A sort key on clinic_id does not serve the date filters.
C. Wrong: Copying 2 billion rows to every node uses large amounts of storage and slows loads. A sort key on patient_id does not help the date filters on the appointments table.
D. Right: Correct. Matching distribution keys place each patient's appointments on the same node as that patient, so the join runs locally. DISTSTYLE ALL copies the small clinics table everywhere, and the date sort key lets Redshift skip blocks outside the filter. Learn more: Distribution styles (opens in a new tab)
Explanation
In Redshift, the distribution style decides where rows live and whether joins can run locally. Matching distribution keys on the large tables that join together keep their joins local, DISTSTYLE ALL suits a small dimension, and a sort key on the date filter lets Redshift skip blocks. Even distribution, skewed date keys and copying the large fact table all miss one of these goals.
A ride-sharing company keeps 4 billion trip rows in an Amazon Redshift fact table that uses city_code as its distribution key. Most trips come from five large cities. Queries that join trips to a riders table on rider_id run slowly, and one node runs at high CPU while the others are mostly idle.
The team must confirm the cause, then fix it without recreating the table or adding nodes.
Which TWO actions meet these requirements? (Choose two.)
Choose two answers.
AA. Resize the cluster to add two nodes, so that the new slices take over part of the work from the busy node.
BB. Query the SVV_TABLE_INFO system view for the trips table, and check the skew_rows ratio between its fullest and emptiest slices.
CC. Run ALTER TABLE with ALTER DISTKEY rider_id on the trips table, and distribute the riders table on rider_id as well.
DD. Change the trips table to DISTSTYLE ALL, so that every node holds a full copy and the join never moves rows.
EE. Run VACUUM SORT ONLY on the trips table, so that its rows are sorted again and the busy slice reads fewer blocks.
Show the answer and explanation
Answers: B, C
A. Wrong: The team ruled out adding nodes. Rows are still placed by the hash of city_code, so the five large cities keep landing on a few slices and the skew remains.
B. Right: Correct. The skew_rows column is the ratio of rows in the slice with the most rows to the slice with the fewest. A high value confirms that the distribution key concentrates the table on a few slices. Learn more: SVV_TABLE_INFO (opens in a new tab)
C. Right: Correct. ALTER DISTKEY changes the distribution key of an existing table without recreating it. Rider IDs have many values, so rows spread evenly, and matching keys on both tables keep the join local to each slice. Learn more: ALTER TABLE (opens in a new tab)
D. Wrong: ALL distribution suits small dimension tables. Copying 4 billion rows to every node multiplies storage and slows every load, which is far worse than the current skew.
E. Wrong: Sorting changes the order of rows within each slice, not which slice holds them. The busy slice would still hold most of the rows.
Explanation
One busy node and idle others point to distribution skew. SVV_TABLE_INFO shows it directly through skew_rows, and ALTER DISTKEY moves the table to a high-cardinality key that also matches the join, without recreating the table. Adding nodes, copying the fact table everywhere or re-sorting does not change where the rows live.
Want more on Redshift table design? The 5 full-length practice sets have 9 more Redshift table design questions, 325 in all, none of them in the free exam. One payment of $14.99, no subscription. See the practice sets →