Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 40

Optimizing Table Design with Distribution Styles and Sort Keys

Recommended resources

Welcome back! In our last lesson, we laid the foundation for our data warehouse by designing the Amazon Redshift cluster architecture, focusing on the physical infrastructure of leader and compute nodes. We learned how to select the right node type (RA3 vs. DC2) and size the cluster for a given workload.

Now that we have our cluster "hardware" in place, it's time to focus on the logical data structures within it. This lesson addresses the learning outcome: Implement optimal table design using distribution styles and sort keys.

Mastering these two concepts is arguably the most important skill for achieving high performance in Redshift. In a massively parallel processing (MPP) system, where your data lives and how it's ordered can have a dramatic impact on query speed. For the Data Engineer certification, you can expect scenario-based questions that test your ability to choose the correct design for a given query pattern.

1. The Core of Redshift Performance: Data Distribution

In the previous lesson, we established that a Redshift cluster is made up of multiple compute nodes. When you load a table, Redshift must decide how to spread the rows of that table across these nodes. This is controlled by the table's distribution style. The goal is to place data strategically to minimize data movement across the network during query execution, which is one of the most expensive operations.

To get a quick overview of why this is so critical, let's start with a short conceptual video.

Optimising Redshift DataWarehouse using DIST and SORT Key

This video from Darshil Parmar provides a clear, high-level introduction to both sort keys and distribution styles. We'll start with the distribution key section.

Watch from 05:10 to 07:29. Focus on understanding the three main distribution styles: KEY, ALL, and EVEN, and the basic use case for each.

Choosing the Right Distribution Style

As you just saw, there are three primary distribution styles you can explicitly set, plus the default AUTO option.

  • DISTSTYLE KEY (DISTKEY <column_name>): This is the most important style for performance tuning. Redshift uses a hash function on the values in the specified DISTKEY column to determine which node a row belongs to.

    • How it works: All rows with the same value in the DISTKEY column are guaranteed to be stored on the same node.
    • Best for: When you join two large tables, set the DISTKEY to the join columns (e.g., customer_id in both the sales and customers tables). This co-locates the joining rows, allowing the join to happen locally on each node without shuffling data across the network. This is the most efficient way to perform a join.

  • DISTSTYLE ALL: Redshift places a complete copy of the entire table on every single compute node.

    • Best for: Relatively small dimension tables that are frequently joined with larger fact tables. The overhead of storing extra copies is outweighed by the guarantee that the join will always be local. Use this for tables that aren't updated frequently and are small enough not to consume excessive storage.

  • DISTSTYLE EVEN: The data is distributed across the nodes in a round-robin fashion, regardless of the data values.

    • Best for: Tables that do not participate in joins, or when there is no single dominant join key. It ensures the data is spread evenly, preventing data skew (where one node holds significantly more data than others).

  • DISTSTYLE AUTO: This is the default. Redshift initially sets the style to ALL for small tables and changes it to EVEN as the table grows. While it's a reasonable default, for critical, large tables involved in performance-sensitive queries, you should make an explicit choice.

This flowchart is an excellent tool for deciding which distribution style to use.

Amazon Redshift Table Distribution Style Decision Flowchart
This flowchart guides you through selecting a `DISTSTYLE`. It starts by asking if a table is part of a JOIN. If not, `EVEN` is often suitable. If it is, it considers table size to decide between `ALL` (for small tables) and `KEY` (for large tables), emphasizing the importance of co-locating data for joins.

To see a practical example of setting a DISTKEY, let's look at a code snippet.

AWS Redshift Query Optimization Guide 2025

This article from E6DATA provides a clear, code-focused example of optimizing a table with a distribution key to improve join performance.

Read the section 'Implement Smart Distribution Keys to Eliminate Cross-Node JOINs'. Pay attention to how both the sales_facts_optimized and customers tables are assigned the same DISTKEY (customer_id) to co-locate their data.

2. Fine-Tuning with Sort Keys

While distribution styles control where data is placed across nodes, sort keys control how data is physically ordered on disk within each node.

When data is sorted, Redshift maintains metadata about the minimum and maximum values stored in each 1 MB block of data. When a query includes a filter condition (e.g., WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'), the query optimizer can use this metadata to rapidly skip over any blocks that don't contain data in that range. This is known as zone map pruning and can drastically reduce the amount of data that needs to be scanned from disk.

Let's return to the introductory video to see this explained visually.

Optimising Redshift DataWarehouse using DIST and SORT Key

We'll now watch the first part of the same video, which explains the concept of sort keys.

Watch from 01:16 to 05:10. Focus on how sorting the data by date allows Redshift to jump directly to the relevant blocks of data, avoiding a full table scan.

Types of Sort Keys

There are two types of sort keys, but for the exam and most use cases, you'll focus on COMPOUND.

  • COMPOUND SORTKEY: This is the default and most common type. You specify one or more columns (e.g., SORTKEY (region, sale_date)). The data is sorted first by the leading column (region), and then by the second column (sale_date) within each region, and so on.

    • Best for: When your queries consistently filter on a primary column, and sometimes on secondary columns. For example, queries that always filter on sale_date will benefit greatly. The order of columns in the COMPOUND key is critical.

  • INTERLEAVED SORTKEY: This key gives equal weight to all columns in the sort key. It's useful when your query filter patterns are unpredictable and can use any subset of the columns in the sort key.

    • Caution: Interleaved sort keys have a higher performance cost for data loading and VACUUM operations. You should generally prefer COMPOUND unless you have a clear use case for interleaved sorting.

This flowchart can help you decide when and how to apply a sort key.

Amazon Redshift Sort Key Decision Flowchart
This flowchart helps you decide on a sort key strategy. It suggests considering a sort key if it can improve query performance by enabling more efficient joins (MERGE JOIN) or by allowing Redshift to skip data blocks (zone maps). It guides you toward `COMPOUND` for predictable filter patterns and `INTERLEAVED` for varied patterns.
Test your understanding!

You have a large fact_sales table (billions of rows) and a smaller dim_store table (hundreds of rows). Your most frequent and critical dashboard query joins these tables on store_id and filters for sales within the last 7 days using the transaction_date column.

How would you define the DISTSTYLE and SORTKEY for the fact_sales table? What about for the dim_store table?

Show answer

  • fact_sales table:

    • DISTSTYLE KEY (store_id): To co-locate sales data with store data for efficient joins.
    • SORTKEY (transaction_date): To allow Redshift to quickly find sales from the last 7 days by scanning only the relevant data blocks.

  • dim_store table:

    • DISTSTYLE ALL: Since the table is small and frequently joined, copying it to every node is efficient. It guarantees joins are always local without relying on a key. Alternatively, DISTSTYLE KEY (store_id) would also work perfectly to co-locate with the fact table. ALL is often simpler for small, static dimensions.
    • SORTKEY: A sort key is likely unnecessary for a small table of a few hundred rows, as a full scan is already very fast.

3. A Practical Demonstration: From Default to Optimized

Theory is great, but seeing the impact is better. Let's watch a practical demo that puts these concepts together. The video will show you:

  1. Creating a table with default settings (DISTSTYLE AUTO).
  2. Creating an optimized table with an explicit DISTKEY and SORTKEY.
  3. Comparing the query EXPLAIN plans for a join between these tables to see the performance difference.

Amazon Redshift for Beginners: Hands-On Guide to Get Started [Part 2] #aws #redshift

This hands-on video from ETL SQL walks through the entire process of table creation and optimization. It's the perfect way to see how your design choices translate into real-world query plans.

Watch from 17:01 to 26:25. First (17:01 - 20:02), observe the creation of the orders_optimized table where the DISTKEY and SORTKEY are explicitly defined. Then (20:02 - 26:25), pay close attention to the EXPLAIN plan comparison. Note the difference between a broadcast step (less efficient) and the ideal Distribution: None (or DS_DIST_NONE), which indicates a perfectly co-located join.

The video demonstrates the tangible result of good design. An EXPLAIN plan showing DS_DIST_NONE for a join between two large tables confirms that your DISTKEY strategy is working, as no data needs to be redistributed across the cluster to perform the join.

4. Best Practices and Evaluation

To round out your knowledge for the exam, it's essential to be familiar with the official AWS best practices.

Best practices for designing Amazon Redshift tables

This AWS Prescriptive Guidance document provides the authoritative best practices for table design. We'll focus on the sections for sort keys and distribution styles.

Read the sections 'Understand how sort keys work' and 'Choose the right table distribution style'. Key takeaways to look for are the advice to avoid compressing sort key columns and to choose a high-cardinality distribution key to prevent data skew.

Conclusion

In this lesson, we moved from the physical cluster design to the logical table design, which is where performance is won or lost. By carefully selecting distribution styles and sort keys, you can guide the Redshift query optimizer to execute queries with maximum efficiency.

Key Takeaways:

  • Distribution Style (DISTSTYLE) controls how data is spread across nodes. The goal is to co-locate data to minimize data movement during joins.
    • Use DISTKEY on the join columns for large tables.
    • Use DISTSTYLE ALL for small dimension tables.
    • Use DISTSTYLE EVEN for tables that don't join or have no obvious key.
  • Sort Key (SORTKEY) controls the physical order of data on disk within each node. The goal is to allow Redshift to skip large blocks of irrelevant data using zone maps.
    • Use a COMPOUND SORTKEY on columns frequently used in WHERE clauses (especially dates).
  • Always verify your design by inspecting the EXPLAIN plan of your critical queries. Look for the absence of costly redistribution steps.

Next Up:
We've designed our cluster and learned how to structure our tables for optimal performance. The next logical step is to populate those tables with data. In the next lesson, we will cover how to "Load data into Redshift from S3 using the COPY command with appropriate parameters," the most performant and scalable method for data ingestion in Redshift.