How would you decide between using DISTKEY and SORTKEY?

DISTKEY determines how data is distributed across compute nodes, aiming to co-locate related rows for efficient joins and aggregations. SORTKEY defines the physical order of data within each distribution, optimizing range queries, filtering, and compression.

Mechanics and "Why"

DISTKEY (Distribution Key):
The DISTKEY specifies which column's values are used to distribute rows across the cluster's compute nodes (slices in Amazon Redshift). Rows with the same DISTKEY value are stored on the same slice.
* Why: This strategy is crucial for minimizing data movement (shuffle) during large table joins. When two tables are joined on their DISTKEY, the join can often occur locally on each slice without expensive network I/O, significantly improving query performance. It also benefits GROUP BY operations on the DISTKEY. A poorly chosen DISTKEY can lead to data skew, where some slices hold disproportionately more data, becoming performance bottlenecks.

SORTKEY (Sort Key):
The SORTKEY specifies the column(s) by which data is physically ordered within each slice. This can be a single column or a compound key (multiple columns).
* Why: Sorted data enables faster query execution through "zone map pruning" (or predicate pushdown). When a query filters on a SORTKEY column (e.g., a date range), the system can quickly skip large blocks of irrelevant data, reading only the necessary ones. This is similar to how micro-partitions work in Snowflake or Z-ordering in Delta Lake. Sorted data also generally compresses better and can improve the efficiency of merge joins and other operations.

Best Practices and Example

A common best practice is to choose your DISTKEY on the column most frequently used for large table joins (e.g., a foreign key to a large dimension table). Your SORTKEY should be on columns frequently used in WHERE clauses, especially date/timestamp columns for range scans, or high-cardinality columns for point lookups.

CREATE TABLE fact_sales (
    sale_id INT,
    sale_date DATE,
    customer_id INT,
    product_id INT,
    amount DECIMAL(10, 2)
)
DISTSTYLE KEY
DISTKEY (customer_id)
SORTKEY (sale_date, product_id);

In this example, customer_id as DISTKEY optimizes joins with a dim_customer table. sale_date and product_id as SORTKEY optimize queries filtering by date ranges and specific products.

In the interview, also mention…

Emphasize that the core goal of both is to optimize I/O and CPU utilization by ensuring data locality and enabling efficient pruning, which are fundamental concepts across distributed data systems, not just Redshift.