Study guide
6.2 Redshift Data Modeling: Distribution Styles (KEY, EVEN, ALL) & Sort Keys
AWS Certified Data Engineer - Associate DEA-C01
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Streaming Data Ingestion
1.1 Kinesis Data Streams vs. Firehose Architecture & Partitioning 1.2 Amazon MSK (Managed Streaming for Apache Kafka) & Serverless Patterns 1.3 Event-Driven Streaming Ingestion with DynamoDB Streams & Lambda 1.4 Streaming Distribution, Replay & Processing State
2 Chapter 2: Batch Data Ingestion & Migration
2.1 AWS DMS (Database Migration Service) & Change Data Capture (CDC) 2.2 S3 Batch Operations & High-Throughput Bulk Data Ingestion 2.3 Hybrid Data Transfer with Snow Family & AWS DataSync 2.4 Application, API & Managed File Ingestion
3 Chapter 3: ETL Transformation with AWS Glue
3.1 AWS Glue Jobs, DynamicFrames & PySpark Transformations 3.2 Glue Crawlers, Data Catalog & Schema Evolution 3.3 Data Preparation & Quality Rules with AWS Glue DataBrew & Data Quality 3.4 Incremental Processing with AWS Glue Job Bookmarks & Partition Processing 3.5 Technical and Business Catalogs, Connections & Lineage
4 Chapter 4: Big Data Processing & Workflow Orchestration
4.1 Amazon EMR Cluster Architecture, Spark & Hadoop Ecosystem 4.2 EMR Serverless & Instance Selection (On-Demand vs. Spot Optimization) 4.3 Serverless Orchestration with AWS Step Functions 4.4 Airflow Pipeline Orchestration with Amazon MWAA 4.5 Containers, Lambda & Distributed Processing Performance 4.6 Data Engineering Code, IaC & CI/CD
5 Chapter 5: Object Storage & Data Lake Architecture
5.1 Amazon S3 Storage Classes, Lifecycle Policies & Cost Optimization 5.2 S3 Data Lake Partitioning, Bucketing & Layout Strategies 5.3 Efficient Columnar Formats: Parquet, ORC, and Avro Schema Design 5.4 S3 Performance Optimization, Multipart Uploads & Transfer Acceleration 5.5 Apache Iceberg, S3 Tables & Open Table Operations
6 Chapter 6: Data Warehousing with Amazon Redshift
6.1 Amazon Redshift Architecture, RA3 Nodes & Managed Storage 6.2 Redshift Data Modeling: Distribution Styles (KEY, EVEN, ALL) & Sort Keys 6.3 Loading & Unloading Data with COPY/UNLOAD & Redshift Spectrum
7 Chapter 7: NoSQL, Relational & Specialty Data Stores
7.1 Amazon DynamoDB Partition Keys, Global Secondary Indexes & Data Modeling 7.2 Amazon RDS & Aurora Operations, Read Replicas & Multi-AZ 7.3 Graph & Analytical Querying with Amazon Neptune, OpenSearch & Athena 7.4 Vector Stores, Indexes & LLM Data Processing
8 Chapter 8: Pipeline Operations, Monitoring & Observability
8.1 Monitoring Pipeline Health with Amazon CloudWatch Metrics & Logs 8.2 Event-Driven Alerting & Automation with EventBridge & CloudWatch Alarms 8.3 Centralized Audit Logging with AWS CloudTrail
9 Chapter 9: Troubleshooting, Data Quality & Performance Tuning
9.1 Diagnosing & Resolving AWS Glue, EMR & Lambda Execution Failures 9.2 Redshift & Athena Query Performance Tuning & Memory Management 9.3 Implementing Data Quality Frameworks & Automated Validation Checks 9.4 Visualization, Notebooks, SQL Analytics, Sampling & Skew
10 Chapter 10: High Availability, Backup & Cost Optimization
10.1 Disaster Recovery, Cross-Region Replication & Backup Strategies 10.2 Designing High Availability Architectures Across AWS Data Services 10.3 Cost Optimization & Resource Utilization across Analytics Workloads
11 Chapter 11: Data Security, Encryption & Identity Management
11.1 Fine-Grained Access Control with AWS IAM Policies, Roles & Conditions 11.2 Data Encryption at Rest & in Transit with AWS KMS & S3 Bucket Policies 11.3 Secure Credentials & Secrets Management with AWS Secrets Manager & Parameter Store 11.4 Private Network Connectivity & Isolation with AWS PrivateLink & VPC Endpoints
12 Chapter 12: Data Governance, Privacy & Compliance
12.1 Centralized Data Lake Governance & Access Control with AWS Lake Formation 12.2 Automated Sensitive Data Discovery & PII Protection with Amazon Macie 12.3 Configuration Auditing & Compliance Enforcement with AWS Config 12.4 Data Privacy, Sovereignty, Sharing & Retention Guardrails 12.5 SageMaker Unified Studio Domains, Projects & Catalog Governance
6.2 Redshift Data Modeling: Distribution Styles (KEY, EVEN, ALL) & Sort Keys
Key Takeaways Explain This Topic AI
Distribution Styles control how table rows are partitioned across compute node slices: KEY hashes a specified column to colocate join rows, EVEN distributes uniformly via round-robin, and ALL replicates small dimension tables across all nodes.
Choosing a high-cardinality column for KEY distribution that matches the join key of large fact tables enables Colocated Joins (DS_DIST_NONE), eliminating expensive inter-node data redistribution across the network.
Zone Maps store in-memory min/max values for 1 MB disk blocks; defining appropriate Compound or Interleaved Sort Keys allows Redshift to perform block skipping during table scans.
Compound Sort Keys order data by columns in specified sequence (col1, col2), providing maximum efficiency for range queries and prefix-matching filters.
Regular execution of VACUUM (reclaiming deleted row space and re-sorting blocks) and ANALYZE (updating optimizer statistics in pg_statistic) is essential for maintaining query optimizer performance.
Last updated: August 2026
- 6.2 Redshift Data Modeling: Distribution Styles (KEY, EVEN, ALL) & Sort Keys
- Physical Data Modeling in an MPP Warehouse
In traditional relational OLTP databases, database design focuses on normalization (3rd Normal Form) to eliminate data redundancy and preserve transactional integrity. In an MPP analytical data warehouse like Amazon Redshift, physical data modeling prioritizes query execution efficiency , parallelism , and minimizing network data movement .
When a query executes across an MPP cluster, compute node slices process their local data partitions in parallel. However, if a query joins two large tables whose rows reside on different compute nodes, Redshift must transfer data across the internal interconnect network—a costly operation known as Data Redistribution . Physical data modeling in Redshift relies on two fundamental pillars to eliminate redistribution and disk I/O overhead:
Distribution Styles : Control how table rows are partitioned across cluster slices.
Sort Keys : Control physical row ordering on disk to enable Zone Map block skipping.
Redshift Data Distribution Styles
Redshift provides three primary explicit distribution styles, along with an automated option: [ KEY Distribution ] ==> Rows hashed by column value (Colocated Joins)
- [ EVEN Distribution ] ==> Round-robin row allocation (Balanced Compute, Broadcast Joins)
- [ ALL Distribution ] ==> Full table copy on every node (Dimension Table Optimization)
- 1. DISTSTYLE KEY
In KEY distribution, table rows are assigned to slices by hashing the value of a designated column ( DISTKEY ). All rows with matching key values land on the exact same compute node slice.
Primary Use Case : Joining large fact tables (e.g., fact_sales and fact_orders ). If both tables are configured with DISTSTYLE KEY on order_id , Redshift performs a Colocated Join ( DS_DIST_NONE ). Slices join their local datasets in memory without moving any data across the network.
Data Skew Warning : You must select a high-cardinality column as the DISTKEY . If a column has low cardinality or skewed value distributions (e.g., country_code where 90% of rows are 'US'), one slice will hold 90% of the table data. This creates a skewed slice , causing straggler operations where the entire cluster waits for one overloaded node to finish.
2. DISTSTYLE EVEN
In EVEN distribution, table rows are distributed across slices using a round-robin algorithm, regardless of data values.
Primary Use Case : Tables that are not frequently joined, or tables that lack a clear, high-cardinality join key.
Trade-Off : EVEN distribution guarantees perfectly balanced storage and CPU utilization across all slices. However, joining two EVEN distributed tables forces Redshift to broadcast or redistribute rows across nodes ( DS_BCAST_INNER or DS_DIST_BOTH ), incurring substantial network latency.
3. DISTSTYLE ALL
In ALL distribution, a full copy of the entire table is replicated onto slice 0 of every compute node in the cluster.
Primary Use Case : Small dimension tables (typically under 3 MB, or up to 300 MB compressed) in a star schema model (e.g., dim_store , dim_product_category ).
Benefits : When joining a massive KEY -distributed fact table with an ALL -distributed dimension table, the join executes locally on every node without network traffic.
Trade-Off : Increases storage footprint across nodes and adds maintenance overhead during INSERT , UPDATE , or DELETE statements, as changes must be synchronized across all compute nodes.
4. DISTSTYLE AUTO
By default, Redshift assigns DISTSTYLE AUTO to new tables. Redshift can begin with DISTSTYLE ALL for a small table and later choose EVEN or KEY as Automatic Table Optimization learns from table size and workload patterns. Treat the chosen style as service-managed rather than a fixed ALL-to-EVEN sequence.
- Redshift Sort Keys & Zone Map Block Skipping
- 1 MB Disk Blocks & Columnar Storage
Redshift stores data on disk in 1 MB immutable physical blocks . Unlike row-oriented databases that store entire record rows together, Redshift uses Columnar Storage . Each 1 MB block contains values for a single column. This allows query scans to read only the specific columns referenced in a SELECT statement, dramatically reducing disk I/O.
Zone Maps & Block Skipping
For every 1 MB block, Redshift maintains in-memory metadata called Zone Maps . A Zone Map records the minimum and maximum values of the column data stored within that specific 1 MB block.
When a query executes with a WHERE clause filter (e.g., WHERE sale_date >= '2026-01-01' ), Redshift compares the query predicate against the block's Zone Map:
If the query filter range falls completely outside the block's [min, max] range, Redshift skips reading that 1 MB disk block entirely .
Sorting table data directly increases Zone Map efficiency, enabling massive Block Skipping . Block 1: [2026-01-01 to 2026-01-15] --> READ BLOCK
- Block 2: [2026-01-16 to 2026-01-31] --> READ BLOCK
- Block 3: [2026-02-01 to 2026-02-15] --> SKIP BLOCK (Zone Map Pruning)
- Compound Sort Keys vs. Interleaved Sort Keys
Redshift supports two types of sort key configurations:
Attribute Compound Sort Key (Default) Interleaved Sort Key
Sorting Mechanism Hierarchical ordering by column sequence ( col1, col2, col3 ) Equal weight assignment across all specified columns
Best Query Patterns Filtering on prefix columns ( WHERE col1 = x AND col2 = y ) Multi-dimensional filtering ( WHERE col2 = y or WHERE col3 = z )
Range Scopes Exceptional for date range scans and join key matching Ideal for ad-hoc BI exploration across varying attributes
Maintenance Cost Low load overhead; fast VACUUM processing High load overhead; requires frequent VACUUM REINDEX
Best Practice : Use Compound Sort Keys for date/timestamp columns, frequently filtered dimensions, and primary join keys. Reserve Interleaved Sort Keys strictly for tables where queries filter across different attribute combinations without a dominant prefix column.
- Table Maintenance: VACUUM & ANALYZE
- 1. VACUUM Operations
Because Redshift block storage uses append-only operations, UPDATE and DELETE queries do not physically erase old data on disk immediately. Instead, rows are marked with a logical delete vector (tombstone). Furthermore, newly inserted rows are appended to an unsorted region at the end of the table.
To restore performance, data engineers must run the VACUUM command:
VACUUM DELETE ONLY : Reclaims disk space occupied by deleted rows without re-sorting the table.
VACUUM SORT : Sorts the unsorted region and merges it with existing sorted blocks.
VACUUM FULL : Reclaims space from deleted rows and re-sorts all table data blocks.
VACUUM REINDEX : Re-analyzes interleaved sort keys to restore equal column weighting.
Note : Redshift executes Auto-Vacuum in the background during periods of low cluster activity, but manual VACUUM commands remain essential after large batch ETL loads.
2. ANALYZE Operations
The Redshift cost-based query optimizer relies on accurate table statistics stored in the pg_statistic system catalog to generate optimal execution plans (e.g., choosing Hash Joins vs. Nested Loops). Running ANALYZE table_name updates column distribution statistics. Redshift automatically triggers Auto-Analyze, but executing ANALYZE explicitly after major bulk loads ensures optimal query planning.
DDL Code Example: Star Schema Data Modeling with Sort & Distribution Keys -- 1. Create Small Dimension Table using DISTSTYLE ALL
- CREATE TABLE dim_customer (
- customer_id BIGINT NOT NULL,
- first_name VARCHAR(50),
- last_name VARCHAR(50),
- email VARCHAR(100),
- account_tier VARCHAR(20),
- created_at TIMESTAMP,
- PRIMARY KEY (customer_id)
- )
- DISTSTYLE ALL
- COMPOUND SORTKEY (customer_id);
- - 2. Create Large Fact Table using DISTSTYLE KEY colocated on customer_id
- CREATE TABLE fact_online_sales (
- sales_id BIGINT NOT NULL,
- order_date DATE NOT NULL,
- customer_id BIGINT NOT NULL,
- product_id INT NOT NULL,
- store_id INT NOT NULL,
- quantity INT NOT NULL,
- total_amount NUMERIC(12, 2) NOT NULL,
- PRIMARY KEY (sales_id),
- FOREIGN KEY (customer_id) REFERENCES dim_customer(customer_id)
- )
- DISTSTYLE KEY
- DISTKEY (customer_id)
- COMPOUND SORTKEY (order_date, customer_id);
- - 3. Run EXPLAIN to verify Colocated Join execution plan (DS_DIST_NONE)
- EXPLAIN
- SELECT
- c.account_tier,
- SUM(s.total_amount) AS tier_revenue
- FROM fact_online_sales s
- JOIN dim_customer c ON s.customer_id = c.customer_id
- WHERE s.order_date >= '2026-01-01'
- GROUP BY c.account_tier;