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.
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 →