Study guide
9.2 Redshift & Athena Query Performance Tuning & Memory Management
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
9.2 Redshift & Athena Query Performance Tuning & Memory Management
Key Takeaways Explain This Topic AI
Amazon Redshift table distribution styles (KEY, EVEN, ALL) control inter-node data movement; matching distribution keys on large join tables eliminates network redistribution (DS_DIST_NONE).
Amazon Athena query performance and cost scale directly with S3 storage optimization: converting raw CSV/JSON to Apache Parquet/ORC with Snappy compression reduces scan volume by up to 90% while enforcing predicate pushdown.
Amazon Redshift Auto WLM (Workload Management) combined with Concurrency Scaling dynamically routes analytical queries into prioritized queues, preventing long queries from starving transactional ETL workflows.
Partition Projection in the AWS Glue Data Catalog allows Athena to calculate partition S3 URIs algorithmically, eliminating Glue metastore metadata bottlenecks and rate limits during execution over millions of partitions.
Inspecting Redshift diagnostic views (SVL_QUERY_SUMMARY, STL_EXPLAIN, STL_WLM_QUERY) reveals is_diskbased = true events, indicating query execution memory (workmem) exhaustion spilling to disk.
Last updated: August 2026
Redshift & Athena Query Performance Tuning & Memory Management
High-performance data analytics on AWS relies on optimizing two distinct query execution engines: Amazon Redshift (a managed Massively Parallel Processing data warehouse) and Amazon Athena (a serverless interactive query engine built on Apache Trino/Presto). This section examines the architectural mechanisms and configuration controls required to maximize throughput and minimize execution latencies in both systems.
Amazon Redshift Performance Optimization
Amazon Redshift achieves fast execution across petabyte-scale datasets using a Massively Parallel Processing (MPP) architecture composed of one Leader Node and multiple Compute Nodes divided into slices. Optimizing Redshift requires eliminating network data redistribution, managing memory allocation, and maintaining storage statistics.
1. Data Distribution Styles & Network Redistribution Costs
When executing joins, Redshift compute nodes must co-locate matching table rows across slices. Inter-node network data movement is the primary cause of query latency.
DISTSTYLE KEY : Hash-distributes table rows based on values in a specified column ( DISTKEY ).
Best Practice: Set DISTKEY on the join column of two large tables (e.g., fact_sales and dim_customer on customer_id ).
Result: Matching rows reside on the exact same compute node slice, yielding DS_DIST_NONE (zero network movement).
DISTSTYLE EVEN : Distributes rows round-robin across all slices.
Best Practice: Used for tables involved in no frequent joins, or to resolve data skew.
Result: Triggers DS_DIST_INNER or DS_DIST_BOTH , requiring expensive network data broadcasts across nodes during joins.
DISTSTYLE ALL : Copies the entire table to the first slice of every compute node.
Best Practice: Used for small, slowly-changing dimension tables (< 3 million rows).
Result: Guarantees local join execution ( DS_DIST_NONE ) at the cost of higher storage overhead.
DISTSTYLE AUTO : Redshift automatically manages distribution strategy, starting small tables at ALL and altering to EVEN as table size scales.
2. Sort Keys (COMPOUND vs. INTERLEAVED)
Sort keys order table rows on disk blocks, enabling Redshift block metadata (Zone Maps) to skip un-referenced data blocks during query execution.
COMPOUND SORTKEY (Default): Sorts data hierarchically based on the listed column order ( col1 , col2 ).
Optimal Use Case: Range filtering ( WHERE date >= '2026-01-01' ) or equality filters matching the primary leading columns.
INTERLEAVED SORTKEY : Gives equal weight to every column in the sort key.
Optimal Use Case: Ad-hoc multidimensional filtering across varied columns (e.g., searching by city , zip , or category independently).
Trade-off: Significantly higher VACUUM and ANALYZE processing overhead during bulk load operations.
3. Workload Management (WLM) & Disk Spill Remediation
Redshift WLM allocates cluster memory across dedicated query queues.
Auto WLM: Uses machine learning to allocate dynamic execution memory ( workmem ) based on query complexity and concurrency needs.
Identifying Memory Spills ( is_diskbased = true ): If a query's execution memory exceeds allocated queue memory, intermediate hash aggregation or sort steps spill to disk, degrading performance by 10x–100x.
Diagnosis: Query the SVL_QUERY_SUMMARY diagnostic view: SELECT query, stm, seg, step, label, is_diskbased, workmem, rows, bytes
- FROM svl_query_summary
- WHERE is_diskbased = 't'
- ORDER BY query DESC;
Remediation: Assign heavy analytical queries to dedicated high-memory WLM queues, increase wlm_query_slot_count for manual WLM, or optimize joins to reduce intermediate dataset sizes.
4. Table Maintenance: VACUUM & ANALYZE
VACUUM : Reclaims disk space from deleted rows ( VACUUM DELETE ONLY ) and re-sorts unsorted table regions ( VACUUM SORT ONLY ). Redshift performs Automatic Vacuuming in the background, but manual VACUUM is required after massive ETL updates.
ANALYZE : Updates HyperLogLog (HLL) table column statistics used by the query optimizer to choose efficient execution plans (e.g., hash joins vs. nested loops).
Amazon Athena Query Optimization & S3 Storage Design
Amazon Athena SQL commonly charges by data scanned, with rates varying by Region; provisioned capacity is also available. Performance tuning in Athena focuses on drastically reducing S3 data scan volume and eliminating metastore lookup latencies.
1. Storage Format & Compression Optimization
Converting raw unstructured or semi-structured data (CSV, JSON) into columnar formats with compression is the single most impactful Athena optimization.
- Format Storage Layout Compression Key Performance Advantage
- Apache Parquet Columnar Snappy / ZSTD Column pruning, Dictionary encoding, Min/Max statistics
- Apache ORC Columnar ZSTD / Snappy Optimized for heavy Hive/Presto workloads, Stripe indexes
- JSON / CSV Row-based None / Gzip Scans 100% of bytes regardless of SELECT column filters
Predicate Pushdown: Columnar storage allows Athena to read only the specific byte ranges and row groups required by SQL WHERE clauses, skipping irrelevant data blocks entirely.
2. Solving the Small File Problem
Having millions of small files (e.g., < 128 MB) in S3 severely degrades Athena query performance due to S3 HTTP GET request latency overhead and filesystem metadata listing delays.
Target File Size: 128 MB to 512 MB per file.
Compaction via CTAS: Use Athena CREATE TABLE AS SELECT (CTAS) to aggregate small files into optimal Parquet files: CREATE TABLE analytics_db.compacted_orders
- WITH (
- format = 'PARQUET',
- parquet_compression = 'SNAPPY',
- external_location = 's3://my-analytics-bucket/compacted_orders/',
- partitioned_by = ARRAY['year', 'month']
- ) AS
- SELECT * FROM analytics_db.raw_orders;
- 3. Partition Projection
In traditional Hive partitioning, Athena executes MSCK REPAIR TABLE or queries the AWS Glue Data Catalog to discover partition locations. For datasets with millions of partitions, Glue Metastore API rate limits introduce query start delays.
Partition Projection Implementation: Configure table properties directly in the Glue Data Catalog so Athena algorithmically calculates S3 partition locations without making Glue API calls: TBLPROPERTIES (
- 'projection.enabled' = 'true',
- 'projection.year.type' = 'integer',
- 'projection.year.range' = '2020,2030',
- 'projection.month.type' = 'integer',
- 'projection.month.range' = '1,12',
- 'projection.month.digits' = '2',
- 'storage.location.template' = 's3://my-analytics-bucket/orders/year=${year}/month=${month}/'
- )
- Redshift vs. Athena Performance Optimization Comparison
Feature / Control Amazon Redshift Amazon Athena
Primary Architecture Managed Data Warehouse (MPP Compute Nodes) Serverless Distributed Query Engine (Trino/Presto)
Join Optimization DISTSTYLE KEY co-location, DISTSTYLE ALL dimensions Optimal S3 partition keys, hash join memory scaling
Block / File Pruning COMPOUND / INTERLEAVED Sort Keys Columnar file formats (Parquet/ORC), Partition Projection
Concurrency Management Auto WLM, Concurrency Scaling Clusters Workgroups (Query limits, Cost controls, Concurrency quotas)
Execution Diagnostics SVL_QUERY_SUMMARY , STL_EXPLAIN , STL_WLM_QUERY EXPLAIN and EXPLAIN ANALYZE SQL statements