DEA-C01 Practice Question 64: Redshift Sort Key Date Range
AWS Certified Data Engineer – Associate (DEA-C01) · Test 7 · question 4 of 10
An Amazon Redshift fact table holds five years of sales rows. The rows were loaded per customer and backfilled over time, so they are not stored in date order. Nearly every dashboard query filters on a single month of sale_date, yet the queries scan a large share of the table and run slowly. The table currently uses SORTKEY NONE with DISTSTYLE EVEN. Which change most directly reduces the amount of data these queries read?
Show answer and explanation
Answer:
Redshift stores columnar data in 1 MB blocks and keeps the minimum and maximum values of each block as metadata. AWS explains that *"sorting enables efficient handling of range-restricted predicates"*: if a query uses a range-restricted predicate, *"the query processor can use the min and max values to rapidly skip over large numbers of blocks."* With five years of data sorted by date and a one-month filter, *"you can remove up to 98 percent of the disk blocks from the scan."* Making sale_date the sort key (option C) enables this block skipping.
- A is wrong because DISTRIBUTION ALL copies the whole table to every node. That helps joins on small dimension tables and would multiply storage for a large fact table, without helping date-range scans. - B is wrong because concurrency scaling adds capacity for concurrent queries. Each query still reads the same unneeded blocks. - D is wrong because a distribution key controls how rows are spread across nodes. Distributing on a date column would also tend to skew data toward the nodes that hold the most recent dates.
Reference: Sort keys