< Back

Share this article

How to Optimize AWS Athena Query Performance? {Updated 2025 Guide}

June 11, 2026

e6data team

AWS Athena

Query optimization

Advanced

Athena's query engine can handle big datasets, but it requires careful optimization of data formats, partitioning strategies, and query patterns to deliver the sub-second latency your business demands. Unlike traditional databases where you control the infrastructure, Athena optimization happens at the data layer - through intelligent file organization, compression choices, and query design patterns that work with Athena's unique architecture.

This playbook cuts through the complexity with battle-tested optimization tactics from enterprise data teams managing petabyte-scale workloads. Each technique includes specific guidance on when to apply it, how to implement it correctly, and what performance gains to expect.

Performance Yardsticks

  • Query Latency · Green Zone: <3 seconds · Yellow Zone: 3-15 seconds · Red Zone: >15 seconds · What to Monitor: CloudWatch Metrics - QueryExecutionTime

  • Data Scanned per Query · Green Zone: Low scan volume · Yellow Zone: Moderate scan volume · Red Zone: High scan volume · What to Monitor: Athena Query History - DataScannedInBytes

  • Partition Pruning Efficiency · Green Zone: High elimination · Yellow Zone: Moderate elimination · Red Zone: Low elimination · What to Monitor: Query execution plan analysis

  • Concurrent Query Performance · Green Zone: No queue time · Yellow Zone: <30s queue time · Red Zone: >30s queue time · What to Monitor: Service Quotas monitoring

  • File Count per Partition · Green Zone: Low file count · Yellow Zone: Moderate file count · Red Zone: High file count · What to Monitor: S3 object count analysis

  • Average File Size · Green Zone: Optimal size range · Yellow Zone: Suboptimal size range · Red Zone: Poor size range · What to Monitor: S3 storage class analysis

  • Cost per Query · Green Zone: <$0.01 · Yellow Zone: $0.01-$0.10 · Red Zone: >$0.10 · What to Monitor: Athena billing analysis via Cost and Usage Reports

‍

Workload Taxonomy

  • BI Dashboards · Characteristics: High-frequency analytical queries with sub-second SLA requirements. Predictable query patterns hitting the same datasets repeatedly. High concurrent user loads during business hours. · Performance Requirements: Fast latency, consistent performance under load · Optimization Priority: Aggressive pre-aggregation, columnar optimization, result caching

  • Ad-hoc Analytics · Characteristics: Exploratory queries with complex joins and aggregations. Unpredictable access patterns across diverse datasets. Data science and analyst workflows requiring flexibility over speed. · Performance Requirements: Reasonable completion time, cost efficiency over speed · Optimization Priority: Intelligent partitioning, compression optimization, query cost monitoring

  • ETL/Streaming · Characteristics: High-volume data processing with throughput requirements. Batch processing workflows and data pipeline orchestration. Large-scale aggregations and transformations. · Performance Requirements: High throughput processing, predictable costs · Optimization Priority: Bulk processing optimization, file format standardization, parallel execution

‍

BI Dashboard Optimization Tactics

Implement Columnar Storage with Parquet for Faster Scans

Converting data from row-based formats like CSV or JSON to columnar Parquet is a primary optimization. Parquet significantly reduces the amount of data scanned for queries that only access a subset of columns, a common pattern in BI dashboards.

Here's how to implement this conversion effectively. Start by creating your optimized table structure:

CREATE TABLE optimized_sales_data (
    transaction_date date,
    region string,
    product_category string,
    revenue decimal(10,2),
    units_sold int,
    customer_segment string
)
STORED AS PARQUET
LOCATION 's3://your-bucket/optimized-sales-data/'
TBLPROPERTIES (
    'projection.enabled' = 'true',
    'projection.transaction_date.type' = 'date',
    'projection.transaction_date.range' = '2023-01-01,NOW',
    'projection.transaction_date.format' = 'yyyy-MM-dd'
);
CREATE TABLE optimized_sales_data (
    transaction_date date,
    region string,
    product_category string,
    revenue decimal(10,2),
    units_sold int,
    customer_segment string
)
STORED AS PARQUET
LOCATION 's3://your-bucket/optimized-sales-data/'
TBLPROPERTIES (
    'projection.enabled' = 'true',
    'projection.transaction_date.type' = 'date',
    'projection.transaction_date.range' = '2023-01-01,NOW',
    'projection.transaction_date.format' = 'yyyy-MM-dd'
);
CREATE TABLE optimized_sales_data (
    transaction_date date,
    region string,
    product_category string,
    revenue decimal(10,2),
    units_sold int,
    customer_segment string
)
STORED AS PARQUET
LOCATION 's3://your-bucket/optimized-sales-data/'
TBLPROPERTIES (
    'projection.enabled' = 'true',
    'projection.transaction_date.type' = 'date',
    'projection.transaction_date.range' = '2023-01-01,NOW',
    'projection.transaction_date.format' = 'yyyy-MM-dd'
);
CREATE TABLE optimized_sales_data (
    transaction_date date,
    region string,
    product_category string,
    revenue decimal(10,2),
    units_sold int,
    customer_segment string
)
STORED AS PARQUET
LOCATION 's3://your-bucket/optimized-sales-data/'
TBLPROPERTIES (
    'projection.enabled' = 'true',
    'projection.transaction_date.type' = 'date',
    'projection.transaction_date.range' = '2023-01-01,NOW',
    'projection.transaction_date.format' = 'yyyy-MM-dd'
);

The magic happens when you populate this table with properly compressed data. Use CTAS (CREATE TABLE AS SELECT) to convert your existing data:‍

CREATE TABLE optimized_sales_data
WITH (
  format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    external_location = 's3://your-bucket/optimized-sales-data/',
    bucketed_by = ARRAY['region'],
    bucket_count = 20
) AS
SELECT *
FROM raw_sales_data
WHERE transaction_date >= DATE('2023-01-01');
CREATE TABLE optimized_sales_data
WITH (
  format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    external_location = 's3://your-bucket/optimized-sales-data/',
    bucketed_by = ARRAY['region'],
    bucket_count = 20
) AS
SELECT *
FROM raw_sales_data
WHERE transaction_date >= DATE('2023-01-01');
CREATE TABLE optimized_sales_data
WITH (
  format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    external_location = 's3://your-bucket/optimized-sales-data/',
    bucketed_by = ARRAY['region'],
    bucket_count = 20
) AS
SELECT *
FROM raw_sales_data
WHERE transaction_date >= DATE('2023-01-01');
CREATE TABLE optimized_sales_data
WITH (
  format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    external_location = 's3://your-bucket/optimized-sales-data/',
    bucketed_by = ARRAY['region'],
    bucket_count = 20
) AS
SELECT *
FROM raw_sales_data
WHERE transaction_date >= DATE('2023-01-01');

‍Alternatives: If your data changes frequently, consider using Apache Iceberg tables for ACID transactions and schema evolution. For extremely large datasets, Delta Lake format provides similar benefits with better update performance.

Configure Aggressive Result Caching for Repeated Dashboard Queries

For frequently repeated dashboard queries, Athena's query result reuse feature eliminates redundant processing, reducing both latency and cost. Results are reused when the query string and underlying data are unchanged.

The key is understanding how Athena determines cache hits. Results are reused when the query string, database, and underlying data remain unchanged. For dashboard scenarios, this means structuring your queries to maximize cache efficiency:

-- Optimized for caching: parameterized date ranges
SELECT 
    region,
    product_category,
    SUM(revenue) as total_revenue,
    COUNT(*) as transaction_count
FROM sales_data
WHERE transaction_date BETWEEN DATE('2024-01-01') AND DATE('2024-12-31')
    AND region IN ('US-EAST', 'US-WEST', 'EU')
GROUP BY region, product_category
ORDER BY total_revenue DESC;
-- Optimized for caching: parameterized date ranges
SELECT 
    region,
    product_category,
    SUM(revenue) as total_revenue,
    COUNT(*) as transaction_count
FROM sales_data
WHERE transaction_date BETWEEN DATE('2024-01-01') AND DATE('2024-12-31')
    AND region IN ('US-EAST', 'US-WEST', 'EU')
GROUP BY region, product_category
ORDER BY total_revenue DESC;
-- Optimized for caching: parameterized date ranges
SELECT 
    region,
    product_category,
    SUM(revenue) as total_revenue,
    COUNT(*) as transaction_count
FROM sales_data
WHERE transaction_date BETWEEN DATE('2024-01-01') AND DATE('2024-12-31')
    AND region IN ('US-EAST', 'US-WEST', 'EU')
GROUP BY region, product_category
ORDER BY total_revenue DESC;
-- Optimized for caching: parameterized date ranges
SELECT 
    region,
    product_category,
    SUM(revenue) as total_revenue,
    COUNT(*) as transaction_count
FROM sales_data
WHERE transaction_date BETWEEN DATE('2024-01-01') AND DATE('2024-12-31')
    AND region IN ('US-EAST', 'US-WEST', 'EU')
GROUP BY region, product_category
ORDER BY total_revenue DESC;

Alternatives: For real-time dashboards requiring fresher data, consider Amazon QuickSight SPICE caching with scheduled refreshes. Materialized views in your data warehouse can also provide similar performance benefits.

Design Partition Pruning Strategies for Time-Series Dashboard Data

Partitioning is critical for performance, as it allows Athena to prune data and scan only relevant S3 prefixes. Align your partition strategy with common filter patterns, especially for time-series data.

For time-series dashboards, hierarchical date partitioning provides the most consistent performance gains. Here's how to structure it properly:

-- Create partitioned table optimized for dashboard queries
CREATE EXTERNAL TABLE partitioned_sales_data (
    transaction_id string,
    customer_id string,
    product_id string,
    revenue decimal(10,2),
    units_sold int,
    store_location string
)
PARTITIONED BY (
    year int,
    month int,
    region string
)
STORED AS PARQUET
LOCATION 's3://your-bucket/partitioned-sales/'
TBLPROPERTIES (
    'projection.enabled' = 'true',
    'projection.year.type' = 'integer',
    'projection.year.range' = '2020,2030',
    'projection.month.type' = 'integer',
    'projection.month.range' = '1,12',
    'projection.region.type' = 'enum',
    'projection.region.values' = 'us-east,us-west,eu-central,apac',
    'storage.location.template' = 's3://your-bucket/partitioned-sales/year=${year}/month=${month}/region=${region}/'
);
-- Create partitioned table optimized for dashboard queries
CREATE EXTERNAL TABLE partitioned_sales_data (
    transaction_id string,
    customer_id string,
    product_id string,
    revenue decimal(10,2),
    units_sold int,
    store_location string
)
PARTITIONED BY (
    year int,
    month int,
    region string
)
STORED AS PARQUET
LOCATION 's3://your-bucket/partitioned-sales/'
TBLPROPERTIES (
    'projection.enabled' = 'true',
    'projection.year.type' = 'integer',
    'projection.year.range' = '2020,2030',
    'projection.month.type' = 'integer',
    'projection.month.range' = '1,12',
    'projection.region.type' = 'enum',
    'projection.region.values' = 'us-east,us-west,eu-central,apac',
    'storage.location.template' = 's3://your-bucket/partitioned-sales/year=${year}/month=${month}/region=${region}/'
);
-- Create partitioned table optimized for dashboard queries
CREATE EXTERNAL TABLE partitioned_sales_data (
    transaction_id string,
    customer_id string,
    product_id string,
    revenue decimal(10,2),
    units_sold int,
    store_location string
)
PARTITIONED BY (
    year int,
    month int,
    region string
)
STORED AS PARQUET
LOCATION 's3://your-bucket/partitioned-sales/'
TBLPROPERTIES (
    'projection.enabled' = 'true',
    'projection.year.type' = 'integer',
    'projection.year.range' = '2020,2030',
    'projection.month.type' = 'integer',
    'projection.month.range' = '1,12',
    'projection.region.type' = 'enum',
    'projection.region.values' = 'us-east,us-west,eu-central,apac',
    'storage.location.template' = 's3://your-bucket/partitioned-sales/year=${year}/month=${month}/region=${region}/'
);
-- Create partitioned table optimized for dashboard queries
CREATE EXTERNAL TABLE partitioned_sales_data (
    transaction_id string,
    customer_id string,
    product_id string,
    revenue decimal(10,2),
    units_sold int,
    store_location string
)
PARTITIONED BY (
    year int,
    month int,
    region string
)
STORED AS PARQUET
LOCATION 's3://your-bucket/partitioned-sales/'
TBLPROPERTIES (
    'projection.enabled' = 'true',
    'projection.year.type' = 'integer',
    'projection.year.range' = '2020,2030',
    'projection.month.type' = 'integer',
    'projection.month.range' = '1,12',
    'projection.region.type' = 'enum',
    'projection.region.values' = 'us-east,us-west,eu-central,apac',
    'storage.location.template' = 's3://your-bucket/partitioned-sales/year=${year}/month=${month}/region=${region}/'
);

Partition projection eliminates the need to run MSCK REPAIR TABLE commands, automatically calculating partition locations based on query filters:

-- Dashboard query with optimal partition pruning
SELECT 
    region,
    SUM(revenue) as quarterly_revenue,
    AVG(units_sold) as avg_units_per_transaction
FROM partitioned_sales_data
WHERE year = 2024
    AND month BETWEEN 7 AND 9  -- Q3 2024
    AND region = 'us-east'
GROUP BY region;
-- Dashboard query with optimal partition pruning
SELECT 
    region,
    SUM(revenue) as quarterly_revenue,
    AVG(units_sold) as avg_units_per_transaction
FROM partitioned_sales_data
WHERE year = 2024
    AND month BETWEEN 7 AND 9  -- Q3 2024
    AND region = 'us-east'
GROUP BY region;
-- Dashboard query with optimal partition pruning
SELECT 
    region,
    SUM(revenue) as quarterly_revenue,
    AVG(units_sold) as avg_units_per_transaction
FROM partitioned_sales_data
WHERE year = 2024
    AND month BETWEEN 7 AND 9  -- Q3 2024
    AND region = 'us-east'
GROUP BY region;
-- Dashboard query with optimal partition pruning
SELECT 
    region,
    SUM(revenue) as quarterly_revenue,
    AVG(units_sold) as avg_units_per_transaction
FROM partitioned_sales_data
WHERE year = 2024
    AND month BETWEEN 7 AND 9  -- Q3 2024
    AND region = 'us-east'
GROUP BY region;

For tables with extremely high partition counts, enable partition indexing to dramatically improve query planning performance. Partition indexing is particularly valuable when:

  • Queries frequently filter on partition columns

  • Partition metadata retrieval becomes a bottleneck

  • You're not using partition projection

Monitor the performance difference before and after enabling partition indexing, especially for highly partitioned time-series data where partition pruning is critical for query performance.

Alternatives: For datasets with unpredictable access patterns, consider using bucketing instead of partitioning to distribute data evenly. Z-order clustering (available in Delta Lake) can optimize for multiple filter dimensions simultaneously.

Optimize File Sizes for Parallel Processing Efficiency

Athena performance depends on optimally sized files. Too many small files (< 64MB) cause metadata overhead, while files that are too large (> 1GB) limit parallelism. Consolidate small files to balance processing across workers.

When your S3 bucket contains thousands of small files from streaming ingestion, query performance suffers from excessive metadata overhead. Here's how to consolidate them into optimally sized files:

-- Consolidate small files into optimal sizes
CREATE TABLE consolidated_events
WITH (
    format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    external_location = 's3://your-bucket/consolidated-events/',
    write_compression = 'SNAPPY'
) AS
SELECT *
FROM (
SELECT 
        event_timestamp,
        user_id,
        event_type,
        properties,
        ROW_NUMBER() OVER (
            PARTITION BY DATE(event_timestamp) 
            ORDER BY event_timestamp
        ) as row_num
    FROM raw_events
    WHERE event_timestamp >= TIMESTAMP '2024-01-01 00:00:00'
)
WHERE row_num <= 10000000;  -- Target 64MB-1GB files for optimal parallel processing
-- Consolidate small files into optimal sizes
CREATE TABLE consolidated_events
WITH (
    format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    external_location = 's3://your-bucket/consolidated-events/',
    write_compression = 'SNAPPY'
) AS
SELECT *
FROM (
SELECT 
        event_timestamp,
        user_id,
        event_type,
        properties,
        ROW_NUMBER() OVER (
            PARTITION BY DATE(event_timestamp) 
            ORDER BY event_timestamp
        ) as row_num
    FROM raw_events
    WHERE event_timestamp >= TIMESTAMP '2024-01-01 00:00:00'
)
WHERE row_num <= 10000000;  -- Target 64MB-1GB files for optimal parallel processing
-- Consolidate small files into optimal sizes
CREATE TABLE consolidated_events
WITH (
    format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    external_location = 's3://your-bucket/consolidated-events/',
    write_compression = 'SNAPPY'
) AS
SELECT *
FROM (
SELECT 
        event_timestamp,
        user_id,
        event_type,
        properties,
        ROW_NUMBER() OVER (
            PARTITION BY DATE(event_timestamp) 
            ORDER BY event_timestamp
        ) as row_num
    FROM raw_events
    WHERE event_timestamp >= TIMESTAMP '2024-01-01 00:00:00'
)
WHERE row_num <= 10000000;  -- Target 64MB-1GB files for optimal parallel processing
-- Consolidate small files into optimal sizes
CREATE TABLE consolidated_events
WITH (
    format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    external_location = 's3://your-bucket/consolidated-events/',
    write_compression = 'SNAPPY'
) AS
SELECT *
FROM (
SELECT 
        event_timestamp,
        user_id,
        event_type,
        properties,
        ROW_NUMBER() OVER (
            PARTITION BY DATE(event_timestamp) 
            ORDER BY event_timestamp
        ) as row_num
    FROM raw_events
    WHERE event_timestamp >= TIMESTAMP '2024-01-01 00:00:00'
)
WHERE row_num <= 10000000;  -- Target 64MB-1GB files for optimal parallel processing

Monitor file sizes and adjust your ETL processes to maintain optimal file sizes. Target 64MB-1GB files for most workloads, with larger files (up to 1GB) for analytical queries and smaller files (64-256MB) for transactional workloads. Use AWS Glue or similar tools to automatically compact small files.

Alternatives: Amazon EMR can handle file compaction for very large datasets more efficiently than Athena. Apache Iceberg's automatic file optimization can maintain optimal file sizes automatically.

Implement Workgroup Resource Controls for Consistent Dashboard Performance

Dashboard performance depends on predictable resource allocation, especially during peak usage hours when multiple teams access the same datasets simultaneously. Athena workgroups provide the control mechanisms needed to ensure your critical dashboards maintain consistent performance even under heavy concurrent load.

Set up dedicated workgroups for different performance tiers. Your executive dashboard workgroup gets priority resource allocation.

Alternatives: Amazon QuickSight provides built-in resource management for dashboard workloads. You can also implement queue management through application-level controls.

Upgrade to Athena Engine Version 3 for Enhanced Performance

Athena Engine Version 3 delivers significant performance improvements over previous versions, including faster query execution, better memory management, and enhanced optimization capabilities. For dashboard workloads requiring consistent sub-second performance, upgrading to the latest engine version often provides immediate gains without code changes.

Engine Version 3 includes improved cost-based optimization, better predicate pushdown, and enhanced columnar processing that particularly benefits dashboard queries with repeated patterns. Here's how to configure your workgroups for optimal engine performance:

Engine Version 3 also provides better handling of complex joins and window functions commonly used in dashboard aggregations. The improved query planner automatically selects more efficient execution strategies for multi-table joins and reduces memory pressure for large aggregation operations.

Optimize Query Result Location for Faster Dashboard Loads

Co-locating your S3 query result bucket in the same region as your data sources can reduce retrieval latency by 20-40%. This minimizes cross-region data transfer costs and delays.

The key is co-locating result storage with your primary data sources and configuring optimal S3 settings for fast retrieval. When your dashboard queries consistently access the same result sets, proper result location optimization eliminates unnecessary cross-region data transfer and reduces retrieval time:

-- Configure optimized result location with same region as data sources
CREATE WORKGROUP dashboard_workgroup_optimized
WITH (
    result_configuration = {
        output_location = 's3://dashboard-results-us-east-1/athena-cache/',
        encryption_configuration = {
            encryption_option = 'SSE_S3'
        }
    },
    configuration = {
        engine_version = {
            selected_engine_version = 'Athena engine version 3'
        },
        query_result_reuse_enabled = true,
        query_result_reuse_minutes = 15
    }
);
-- Configure optimized result location with same region as data sources
CREATE WORKGROUP dashboard_workgroup_optimized
WITH (
    result_configuration = {
        output_location = 's3://dashboard-results-us-east-1/athena-cache/',
        encryption_configuration = {
            encryption_option = 'SSE_S3'
        }
    },
    configuration = {
        engine_version = {
            selected_engine_version = 'Athena engine version 3'
        },
        query_result_reuse_enabled = true,
        query_result_reuse_minutes = 15
    }
);
-- Configure optimized result location with same region as data sources
CREATE WORKGROUP dashboard_workgroup_optimized
WITH (
    result_configuration = {
        output_location = 's3://dashboard-results-us-east-1/athena-cache/',
        encryption_configuration = {
            encryption_option = 'SSE_S3'
        }
    },
    configuration = {
        engine_version = {
            selected_engine_version = 'Athena engine version 3'
        },
        query_result_reuse_enabled = true,
        query_result_reuse_minutes = 15
    }
);
-- Configure optimized result location with same region as data sources
CREATE WORKGROUP dashboard_workgroup_optimized
WITH (
    result_configuration = {
        output_location = 's3://dashboard-results-us-east-1/athena-cache/',
        encryption_configuration = {
            encryption_option = 'SSE_S3'
        }
    },
    configuration = {
        engine_version = {
            selected_engine_version = 'Athena engine version 3'
        },
        query_result_reuse_enabled = true,
        query_result_reuse_minutes = 15
    }
);

For cross-region scenarios, use S3 Transfer Acceleration or configure regional result buckets to minimize latency. Dashboard applications should also implement intelligent result caching to avoid repeated S3 API calls for identical query results.

Alternatives: For frequently accessed results, consider copying critical result datasets to a dedicated S3 bucket with Intelligent Tiering enabled. CDN services like CloudFront can also cache static result files for global dashboard distribution.

Ad-hoc Analytics Optimization Tactics

Implement Smart JOIN Ordering for Complex Multi-Table Analysis

Join order is critical for performance. While Athena's cost-based optimizer helps, manually structuring queries to join smaller tables to larger tables is a reliable pattern. Use CTEs (WITH clauses) to pre-filter large tables before joining to reduce data shuffling and prevent memory issues.

The fundamental principle is processing the most selective filters first and joining smaller result sets before larger ones. When your analysis joins customer data (millions of rows) with product catalogs (thousands of rows) and transaction history (billions of rows), strategic ordering prevents memory overflow and reduces processing time.

-- Optimized join order: start with most selective conditions
WITH filtered_customers AS (
    SELECT customer_id, customer_segment, registration_date
    FROM customers
    WHERE customer_segment = 'enterprise'
        AND registration_date >= DATE('2023-01-01')
), 
recent_transactions AS (
SELECT 
    customer_id,
        product_id,
        transaction_date,
        revenue
    FROM transactions
    WHERE transaction_date >= DATE('2024-01-01')
        AND revenue > 1000  -- High-value transactions only
)
SELECT 
    c.customer_segment,
    p.product_category,
    COUNT(*) as transaction_count,
    SUM(t.revenue) as total_revenue,
    AVG(t.revenue) as avg_transaction_value
FROM filtered_customers c
INNER JOIN recent_transactions t ON c.customer_id = t.customer_id
INNER JOIN products p ON t.product_id = p.product_id
GROUP BY c.customer_segment, p.product_category
HAVING COUNT(*) >= 10  -- Only include meaningful volumes
ORDER BY total_revenue DESC;
-- Optimized join order: start with most selective conditions
WITH filtered_customers AS (
    SELECT customer_id, customer_segment, registration_date
    FROM customers
    WHERE customer_segment = 'enterprise'
        AND registration_date >= DATE('2023-01-01')
), 
recent_transactions AS (
SELECT 
    customer_id,
        product_id,
        transaction_date,
        revenue
    FROM transactions
    WHERE transaction_date >= DATE('2024-01-01')
        AND revenue > 1000  -- High-value transactions only
)
SELECT 
    c.customer_segment,
    p.product_category,
    COUNT(*) as transaction_count,
    SUM(t.revenue) as total_revenue,
    AVG(t.revenue) as avg_transaction_value
FROM filtered_customers c
INNER JOIN recent_transactions t ON c.customer_id = t.customer_id
INNER JOIN products p ON t.product_id = p.product_id
GROUP BY c.customer_segment, p.product_category
HAVING COUNT(*) >= 10  -- Only include meaningful volumes
ORDER BY total_revenue DESC;
-- Optimized join order: start with most selective conditions
WITH filtered_customers AS (
    SELECT customer_id, customer_segment, registration_date
    FROM customers
    WHERE customer_segment = 'enterprise'
        AND registration_date >= DATE('2023-01-01')
), 
recent_transactions AS (
SELECT 
    customer_id,
        product_id,
        transaction_date,
        revenue
    FROM transactions
    WHERE transaction_date >= DATE('2024-01-01')
        AND revenue > 1000  -- High-value transactions only
)
SELECT 
    c.customer_segment,
    p.product_category,
    COUNT(*) as transaction_count,
    SUM(t.revenue) as total_revenue,
    AVG(t.revenue) as avg_transaction_value
FROM filtered_customers c
INNER JOIN recent_transactions t ON c.customer_id = t.customer_id
INNER JOIN products p ON t.product_id = p.product_id
GROUP BY c.customer_segment, p.product_category
HAVING COUNT(*) >= 10  -- Only include meaningful volumes
ORDER BY total_revenue DESC;
-- Optimized join order: start with most selective conditions
WITH filtered_customers AS (
    SELECT customer_id, customer_segment, registration_date
    FROM customers
    WHERE customer_segment = 'enterprise'
        AND registration_date >= DATE('2023-01-01')
), 
recent_transactions AS (
SELECT 
    customer_id,
        product_id,
        transaction_date,
        revenue
    FROM transactions
    WHERE transaction_date >= DATE('2024-01-01')
        AND revenue > 1000  -- High-value transactions only
)
SELECT 
    c.customer_segment,
    p.product_category,
    COUNT(*) as transaction_count,
    SUM(t.revenue) as total_revenue,
    AVG(t.revenue) as avg_transaction_value
FROM filtered_customers c
INNER JOIN recent_transactions t ON c.customer_id = t.customer_id
INNER JOIN products p ON t.product_id = p.product_id
GROUP BY c.customer_segment, p.product_category
HAVING COUNT(*) >= 10  -- Only include meaningful volumes
ORDER BY total_revenue DESC;

Alternatives: For frequently joined tables, consider pre-computing join results as materialized tables updated nightly. Amazon Redshift Spectrum can handle very large joins more efficiently for complex analytics.

Configure Compression Strategies for Diverse Data Types

Ad-hoc analytics queries scan diverse data types, from text-heavy log data to numerical time series. Different compression algorithms excel with different data patterns, and choosing the right compression for each table can significantly reduce scan time while lowering storage costs.

Athena supports multiple compression formats, each optimized for specific data characteristics. Here's how to choose and implement the right compression strategy:

-- Text-heavy data: Parquet with Snappy for better performance than GZIP
CREATE TABLE compressed_user_logs
WITH (
    format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    external_location = 's3://your-bucket/compressed-logs/'
) AS
SELECT 
    user_id,
    session_id,
    event_timestamp,
    user_agent,
    request_uri,
    response_code,
    response_size_bytes
FROM raw_user_logs
WHERE event_timestamp >= TIMESTAMP '2024-01-01 00:00:00'
    AND event_timestamp <= CURRENT_TIMESTAMP  -- Prevent future timestamps
    AND user_id IS NOT NULL
    AND session_id IS NOT NULL
    AND response_code BETWEEN 100 AND 599  -- Valid HTTP status codes
    AND response_size_bytes >= 0  -- Non-negative response size
    AND LENGTH(user_agent) <= 1000  -- Reasonable user agent length
    AND LENGTH(request_uri) <= 2000;  -- Reasonable URI length
-- Text-heavy data: Parquet with Snappy for better performance than GZIP
CREATE TABLE compressed_user_logs
WITH (
    format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    external_location = 's3://your-bucket/compressed-logs/'
) AS
SELECT 
    user_id,
    session_id,
    event_timestamp,
    user_agent,
    request_uri,
    response_code,
    response_size_bytes
FROM raw_user_logs
WHERE event_timestamp >= TIMESTAMP '2024-01-01 00:00:00'
    AND event_timestamp <= CURRENT_TIMESTAMP  -- Prevent future timestamps
    AND user_id IS NOT NULL
    AND session_id IS NOT NULL
    AND response_code BETWEEN 100 AND 599  -- Valid HTTP status codes
    AND response_size_bytes >= 0  -- Non-negative response size
    AND LENGTH(user_agent) <= 1000  -- Reasonable user agent length
    AND LENGTH(request_uri) <= 2000;  -- Reasonable URI length
-- Text-heavy data: Parquet with Snappy for better performance than GZIP
CREATE TABLE compressed_user_logs
WITH (
    format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    external_location = 's3://your-bucket/compressed-logs/'
) AS
SELECT 
    user_id,
    session_id,
    event_timestamp,
    user_agent,
    request_uri,
    response_code,
    response_size_bytes
FROM raw_user_logs
WHERE event_timestamp >= TIMESTAMP '2024-01-01 00:00:00'
    AND event_timestamp <= CURRENT_TIMESTAMP  -- Prevent future timestamps
    AND user_id IS NOT NULL
    AND session_id IS NOT NULL
    AND response_code BETWEEN 100 AND 599  -- Valid HTTP status codes
    AND response_size_bytes >= 0  -- Non-negative response size
    AND LENGTH(user_agent) <= 1000  -- Reasonable user agent length
    AND LENGTH(request_uri) <= 2000;  -- Reasonable URI length
-- Text-heavy data: Parquet with Snappy for better performance than GZIP
CREATE TABLE compressed_user_logs
WITH (
    format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    external_location = 's3://your-bucket/compressed-logs/'
) AS
SELECT 
    user_id,
    session_id,
    event_timestamp,
    user_agent,
    request_uri,
    response_code,
    response_size_bytes
FROM raw_user_logs
WHERE event_timestamp >= TIMESTAMP '2024-01-01 00:00:00'
    AND event_timestamp <= CURRENT_TIMESTAMP  -- Prevent future timestamps
    AND user_id IS NOT NULL
    AND session_id IS NOT NULL
    AND response_code BETWEEN 100 AND 599  -- Valid HTTP status codes
    AND response_size_bytes >= 0  -- Non-negative response size
    AND LENGTH(user_agent) <= 1000  -- Reasonable user agent length
    AND LENGTH(request_uri) <= 2000;  -- Reasonable URI length

For structured numerical data, Parquet with Snappy compression provides the best balance of compression ratio and query performance.

Alternatives: LZ4 compression offers faster decompression for frequently accessed data. ZSTD provides better compression ratios for archival data accessed less frequently.

Optimize Data Sampling and Sorting with ORDER BY and LIMIT

Data type selection significantly impacts query performance by affecting storage efficiency, compression ratios, and processing speed. Choosing optimal data types for your analytical workloads can reduce data scanned by 15-30% and improve query execution time, especially for large-scale aggregations and comparisons.

The key insight is matching data types to actual data characteristics rather than using oversized defaults. When your analysis involves large tables with suboptimal data types, storage overhead compounds across billions of rows, directly increasing costs and scan time:

-- Standard exact functions (slower for large datasets)
SELECT 
    product_category,
    COUNT(DISTINCT customer_id) as unique_customers,
    COUNT(DISTINCT transaction_id) as total_transactions
FROM transactions
WHERE transaction_date >= DATE('2024-01-01')
GROUP BY product_category;

-- Optimized approximate functions (40% faster)
SELECT 
    product_category,
    approx_distinct(customer_id) as approx_unique_customers,
    approx_distinct(transaction_id) as approx_total_transactions,
    -- Get approximate percentiles for revenue distribution
    approx_percentile(revenue, 0.5) as median_revenue,
    approx_percentile(revenue, 0.95) as p95_revenue
FROM transactions
WHERE transaction_date >= DATE('2024-01-01')
GROUP BY product_category;
-- Standard exact functions (slower for large datasets)
SELECT 
    product_category,
    COUNT(DISTINCT customer_id) as unique_customers,
    COUNT(DISTINCT transaction_id) as total_transactions
FROM transactions
WHERE transaction_date >= DATE('2024-01-01')
GROUP BY product_category;

-- Optimized approximate functions (40% faster)
SELECT 
    product_category,
    approx_distinct(customer_id) as approx_unique_customers,
    approx_distinct(transaction_id) as approx_total_transactions,
    -- Get approximate percentiles for revenue distribution
    approx_percentile(revenue, 0.5) as median_revenue,
    approx_percentile(revenue, 0.95) as p95_revenue
FROM transactions
WHERE transaction_date >= DATE('2024-01-01')
GROUP BY product_category;
-- Standard exact functions (slower for large datasets)
SELECT 
    product_category,
    COUNT(DISTINCT customer_id) as unique_customers,
    COUNT(DISTINCT transaction_id) as total_transactions
FROM transactions
WHERE transaction_date >= DATE('2024-01-01')
GROUP BY product_category;

-- Optimized approximate functions (40% faster)
SELECT 
    product_category,
    approx_distinct(customer_id) as approx_unique_customers,
    approx_distinct(transaction_id) as approx_total_transactions,
    -- Get approximate percentiles for revenue distribution
    approx_percentile(revenue, 0.5) as median_revenue,
    approx_percentile(revenue, 0.95) as p95_revenue
FROM transactions
WHERE transaction_date >= DATE('2024-01-01')
GROUP BY product_category;
-- Standard exact functions (slower for large datasets)
SELECT 
    product_category,
    COUNT(DISTINCT customer_id) as unique_customers,
    COUNT(DISTINCT transaction_id) as total_transactions
FROM transactions
WHERE transaction_date >= DATE('2024-01-01')
GROUP BY product_category;

-- Optimized approximate functions (40% faster)
SELECT 
    product_category,
    approx_distinct(customer_id) as approx_unique_customers,
    approx_distinct(transaction_id) as approx_total_transactions,
    -- Get approximate percentiles for revenue distribution
    approx_percentile(revenue, 0.5) as median_revenue,
    approx_percentile(revenue, 0.95) as p95_revenue
FROM transactions
WHERE transaction_date >= DATE('2024-01-01')
GROUP BY product_category;

When to use approximate vs exact functions:

  • Approximate: Initial exploration, dashboards, trending analysis, data profiling

  • Exact: Final reports, regulatory compliance, financial calculations, SLA monitoring

Alternatives: For use cases requiring both speed and accuracy, consider pre-computing exact aggregations as materialized views that refresh on a schedule. For extremely large datasets, tools like Apache DataSketches provide more sophisticated approximate algorithms.

Optimize GROUP BY Column Ordering for Memory Efficiency

The order of columns in a GROUP BY clause impacts memory usage. Order columns from highest cardinality (most unique values) to lowest to reduce memory pressure and prevent query failures.

The key principle is ordering columns from highest to lowest cardinality (most unique values to least unique values). This ordering allows Athena to build smaller, more efficient hash tables in the early stages of grouping, reducing memory requirements and improving cache efficiency.

-- Memory-efficient window function usage
SELECT 
    customer_id,
    transaction_date,
    daily_revenue,
    -- Limit window size to prevent memory issues
    AVG(daily_revenue) OVER (
        PARTITION BY customer_id 
        ORDER BY transaction_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as seven_day_avg_revenue,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id 
        ORDER BY daily_revenue DESC
    ) as revenue_rank_within_customer
FROM daily_customer_metrics
WHERE transaction_date >= DATE('2024-07-01')
    AND daily_revenue > 0
QUALIFY revenue_rank_within_customer <= 10;  -- Top 10 days per customer
-- Memory-efficient window function usage
SELECT 
    customer_id,
    transaction_date,
    daily_revenue,
    -- Limit window size to prevent memory issues
    AVG(daily_revenue) OVER (
        PARTITION BY customer_id 
        ORDER BY transaction_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as seven_day_avg_revenue,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id 
        ORDER BY daily_revenue DESC
    ) as revenue_rank_within_customer
FROM daily_customer_metrics
WHERE transaction_date >= DATE('2024-07-01')
    AND daily_revenue > 0
QUALIFY revenue_rank_within_customer <= 10;  -- Top 10 days per customer
-- Memory-efficient window function usage
SELECT 
    customer_id,
    transaction_date,
    daily_revenue,
    -- Limit window size to prevent memory issues
    AVG(daily_revenue) OVER (
        PARTITION BY customer_id 
        ORDER BY transaction_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as seven_day_avg_revenue,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id 
        ORDER BY daily_revenue DESC
    ) as revenue_rank_within_customer
FROM daily_customer_metrics
WHERE transaction_date >= DATE('2024-07-01')
    AND daily_revenue > 0
QUALIFY revenue_rank_within_customer <= 10;  -- Top 10 days per customer
-- Memory-efficient window function usage
SELECT 
    customer_id,
    transaction_date,
    daily_revenue,
    -- Limit window size to prevent memory issues
    AVG(daily_revenue) OVER (
        PARTITION BY customer_id 
        ORDER BY transaction_date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as seven_day_avg_revenue,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id 
        ORDER BY daily_revenue DESC
    ) as revenue_rank_within_customer
FROM daily_customer_metrics
WHERE transaction_date >= DATE('2024-07-01')
    AND daily_revenue > 0
QUALIFY revenue_rank_within_customer <= 10;  -- Top 10 days per customer

Alternatives: For extremely high-cardinality groupings, consider using approximate functions or pre-aggregating data with materialized views to reduce memory requirements.

Design Predicate Pushdown Patterns for Nested JSON Analysis

For complex string filtering, a single regexp_like() is more performant and readable than multiple LIKE clauses. Use regular expressions for sophisticated pattern matching in log analysis or data validation.

When your analytical queries need to filter text data using multiple patterns, consolidating these into a single regular expression reduces CPU overhead and simplifies query logic. This optimization is particularly valuable for log analysis, text mining, and any scenario involving complex string pattern matching.

-- Complex nested analysis with efficient JSON processing
WITH purchase_analysis AS (
    SELECT 
        user_id,
        event_timestamp,
        CAST(JSON_EXTRACT_SCALAR(event_data, '$.purchase.amount') AS decimal(10,2)) as purchase_amount,
        JSON_EXTRACT_SCALAR(event_data, '$.purchase.currency') as currency,
        JSON_EXTRACT_SCALAR(event_data, '$.purchase.payment_method') as payment_method,
        CARDINALITY(CAST(JSON_EXTRACT(event_data, '$.purchase.items') AS array<json>)) as item_count
    FROM optimized_events
    WHERE year = 2024
        AND month BETWEEN 7 AND 9
        AND event_type = 'purchase'
        AND JSON_EXTRACT_SCALAR(event_data, '$.purchase.amount') IS NOT NULL
)
SELECT 
    payment_method,
    currency,
    COUNT(*) as transaction_count,
    SUM(purchase_amount) as total_volume,
    AVG(purchase_amount) as avg_transaction_size,
    AVG(item_count) as avg_items_per_purchase
FROM purchase_analysis
WHERE purchase_amount BETWEEN 10 AND 10000  -- Filter after extraction
GROUP BY payment_method, currency
ORDER BY total_volume DESC;
-- Complex nested analysis with efficient JSON processing
WITH purchase_analysis AS (
    SELECT 
        user_id,
        event_timestamp,
        CAST(JSON_EXTRACT_SCALAR(event_data, '$.purchase.amount') AS decimal(10,2)) as purchase_amount,
        JSON_EXTRACT_SCALAR(event_data, '$.purchase.currency') as currency,
        JSON_EXTRACT_SCALAR(event_data, '$.purchase.payment_method') as payment_method,
        CARDINALITY(CAST(JSON_EXTRACT(event_data, '$.purchase.items') AS array<json>)) as item_count
    FROM optimized_events
    WHERE year = 2024
        AND month BETWEEN 7 AND 9
        AND event_type = 'purchase'
        AND JSON_EXTRACT_SCALAR(event_data, '$.purchase.amount') IS NOT NULL
)
SELECT 
    payment_method,
    currency,
    COUNT(*) as transaction_count,
    SUM(purchase_amount) as total_volume,
    AVG(purchase_amount) as avg_transaction_size,
    AVG(item_count) as avg_items_per_purchase
FROM purchase_analysis
WHERE purchase_amount BETWEEN 10 AND 10000  -- Filter after extraction
GROUP BY payment_method, currency
ORDER BY total_volume DESC;
-- Complex nested analysis with efficient JSON processing
WITH purchase_analysis AS (
    SELECT 
        user_id,
        event_timestamp,
        CAST(JSON_EXTRACT_SCALAR(event_data, '$.purchase.amount') AS decimal(10,2)) as purchase_amount,
        JSON_EXTRACT_SCALAR(event_data, '$.purchase.currency') as currency,
        JSON_EXTRACT_SCALAR(event_data, '$.purchase.payment_method') as payment_method,
        CARDINALITY(CAST(JSON_EXTRACT(event_data, '$.purchase.items') AS array<json>)) as item_count
    FROM optimized_events
    WHERE year = 2024
        AND month BETWEEN 7 AND 9
        AND event_type = 'purchase'
        AND JSON_EXTRACT_SCALAR(event_data, '$.purchase.amount') IS NOT NULL
)
SELECT 
    payment_method,
    currency,
    COUNT(*) as transaction_count,
    SUM(purchase_amount) as total_volume,
    AVG(purchase_amount) as avg_transaction_size,
    AVG(item_count) as avg_items_per_purchase
FROM purchase_analysis
WHERE purchase_amount BETWEEN 10 AND 10000  -- Filter after extraction
GROUP BY payment_method, currency
ORDER BY total_volume DESC;
-- Complex nested analysis with efficient JSON processing
WITH purchase_analysis AS (
    SELECT 
        user_id,
        event_timestamp,
        CAST(JSON_EXTRACT_SCALAR(event_data, '$.purchase.amount') AS decimal(10,2)) as purchase_amount,
        JSON_EXTRACT_SCALAR(event_data, '$.purchase.currency') as currency,
        JSON_EXTRACT_SCALAR(event_data, '$.purchase.payment_method') as payment_method,
        CARDINALITY(CAST(JSON_EXTRACT(event_data, '$.purchase.items') AS array<json>)) as item_count
    FROM optimized_events
    WHERE year = 2024
        AND month BETWEEN 7 AND 9
        AND event_type = 'purchase'
        AND JSON_EXTRACT_SCALAR(event_data, '$.purchase.amount') IS NOT NULL
)
SELECT 
    payment_method,
    currency,
    COUNT(*) as transaction_count,
    SUM(purchase_amount) as total_volume,
    AVG(purchase_amount) as avg_transaction_size,
    AVG(item_count) as avg_items_per_purchase
FROM purchase_analysis
WHERE purchase_amount BETWEEN 10 AND 10000  -- Filter after extraction
GROUP BY payment_method, currency
ORDER BY total_volume DESC;

Alternatives: Consider AWS Glue crawlers to automatically detect and extract JSON schema into structured columns. Amazon OpenSearch can provide better performance for full-text search across JSON documents.

Implement Query Cost Monitoring for Exploratory Analysis

Ad-hoc analytics can generate unpredictably expensive queries, especially when analysts explore large datasets without understanding the underlying data distribution. Implementing proactive cost monitoring prevents surprise bills while maintaining the flexibility analysts need for discovery workflows.

  1. Set up cost controls at multiple levels using Athena workgroups and CloudWatch alerts. Start with query-level limits that prevent runaway queries.

  2. Create monitoring queries that help analysts understand the cost impact of their exploration patterns using Athena's query history and CloudWatch metrics.

  3. Implement query patterns that provide cost estimates before execution by analyzing table metadata and applying selective filtering to reduce data scanning.

Alternatives: AWS Cost Explorer provides detailed cost analysis across all AWS services. Third-party tools like Monte Carlo or Datadog can provide more sophisticated query cost monitoring.
‍

ETL/Streaming Optimization Tactics

Implement Bulk INSERT Strategies for High-Volume Data Loading

For high-volume ETL, use bulk INSERT patterns with CTAS instead of row-by-row insertions. This leverages Athena's parallel processing capabilities for maximum throughput.

The foundation of efficient bulk loading is understanding Athena's parallel processing capabilities. Instead of row-by-row insertions, design your ETL to process data in large, optimally-sized batches that saturate Athena's available parallelism. For incremental loading patterns, use MERGE operations with Iceberg tables to handle updates efficiently.

Monitor bulk operation performance and adjust parallelism based on throughput metrics using Athena's query history and CloudWatch monitoring.

Alternatives: AWS Glue provides better control over parallelism for very large ETL jobs. Amazon Kinesis Data Firehose can handle streaming ingestion more efficiently than batch loading.

Configure Automated File Compaction for Streaming Ingestion

Streaming ingestion often creates many small files, which hurts query performance. Implement an automated compaction process to periodically consolidate these files into optimally sized Parquet files (64MB-1GB).

The challenge is balancing compaction frequency with resource usage. Too frequent compaction wastes compute resources, while infrequent compaction allows performance to degrade. Here's an effective automated compaction strategy:

-- Create compaction staging table for hourly consolidation
CREATE TABLE compacted_streaming_events
WITH (
    format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    external_location = 's3://streaming-data/compacted-events/',
    write_compression = 'SNAPPY'
) AS
SELECT 
    event_id,
    event_timestamp,
    user_id,
    event_type,
    session_id,
    properties,
    -- Add compaction metadata
    CURRENT_TIMESTAMP as compaction_timestamp,
    COUNT(*) OVER (
        PARTITION BY DATE(event_timestamp), HOUR(event_timestamp)
    ) as events_in_hour
FROM streaming_events_raw
WHERE event_timestamp >= CURRENT_TIMESTAMP - INTERVAL '2' HOUR
    AND event_timestamp < CURRENT_TIMESTAMP - INTERVAL '1' HOUR
    -- Only compact hours with significant volume
    AND (
        SELECT COUNT(*) 
        FROM streaming_events_raw s2 
        WHERE DATE(s2.event_timestamp) = DATE(streaming_events_raw.event_timestamp)
            AND HOUR(s2.event_timestamp) = HOUR(streaming_events_raw.event_timestamp)
    ) >= 1000
ORDER BY event_timestamp;
-- Create compaction staging table for hourly consolidation
CREATE TABLE compacted_streaming_events
WITH (
    format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    external_location = 's3://streaming-data/compacted-events/',
    write_compression = 'SNAPPY'
) AS
SELECT 
    event_id,
    event_timestamp,
    user_id,
    event_type,
    session_id,
    properties,
    -- Add compaction metadata
    CURRENT_TIMESTAMP as compaction_timestamp,
    COUNT(*) OVER (
        PARTITION BY DATE(event_timestamp), HOUR(event_timestamp)
    ) as events_in_hour
FROM streaming_events_raw
WHERE event_timestamp >= CURRENT_TIMESTAMP - INTERVAL '2' HOUR
    AND event_timestamp < CURRENT_TIMESTAMP - INTERVAL '1' HOUR
    -- Only compact hours with significant volume
    AND (
        SELECT COUNT(*) 
        FROM streaming_events_raw s2 
        WHERE DATE(s2.event_timestamp) = DATE(streaming_events_raw.event_timestamp)
            AND HOUR(s2.event_timestamp) = HOUR(streaming_events_raw.event_timestamp)
    ) >= 1000
ORDER BY event_timestamp;
-- Create compaction staging table for hourly consolidation
CREATE TABLE compacted_streaming_events
WITH (
    format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    external_location = 's3://streaming-data/compacted-events/',
    write_compression = 'SNAPPY'
) AS
SELECT 
    event_id,
    event_timestamp,
    user_id,
    event_type,
    session_id,
    properties,
    -- Add compaction metadata
    CURRENT_TIMESTAMP as compaction_timestamp,
    COUNT(*) OVER (
        PARTITION BY DATE(event_timestamp), HOUR(event_timestamp)
    ) as events_in_hour
FROM streaming_events_raw
WHERE event_timestamp >= CURRENT_TIMESTAMP - INTERVAL '2' HOUR
    AND event_timestamp < CURRENT_TIMESTAMP - INTERVAL '1' HOUR
    -- Only compact hours with significant volume
    AND (
        SELECT COUNT(*) 
        FROM streaming_events_raw s2 
        WHERE DATE(s2.event_timestamp) = DATE(streaming_events_raw.event_timestamp)
            AND HOUR(s2.event_timestamp) = HOUR(streaming_events_raw.event_timestamp)
    ) >= 1000
ORDER BY event_timestamp;
-- Create compaction staging table for hourly consolidation
CREATE TABLE compacted_streaming_events
WITH (
    format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    external_location = 's3://streaming-data/compacted-events/',
    write_compression = 'SNAPPY'
) AS
SELECT 
    event_id,
    event_timestamp,
    user_id,
    event_type,
    session_id,
    properties,
    -- Add compaction metadata
    CURRENT_TIMESTAMP as compaction_timestamp,
    COUNT(*) OVER (
        PARTITION BY DATE(event_timestamp), HOUR(event_timestamp)
    ) as events_in_hour
FROM streaming_events_raw
WHERE event_timestamp >= CURRENT_TIMESTAMP - INTERVAL '2' HOUR
    AND event_timestamp < CURRENT_TIMESTAMP - INTERVAL '1' HOUR
    -- Only compact hours with significant volume
    AND (
        SELECT COUNT(*) 
        FROM streaming_events_raw s2 
        WHERE DATE(s2.event_timestamp) = DATE(streaming_events_raw.event_timestamp)
            AND HOUR(s2.event_timestamp) = HOUR(streaming_events_raw.event_timestamp)
    ) >= 1000
ORDER BY event_timestamp;

Implement intelligent compaction logic that adapts to data volume patterns by monitoring file count and size thresholds or set up scheduled compaction jobs for Iceberg tables that handle this automatically.

Alternatives: Amazon Kinesis Data Firehose provides built-in buffering and compression for streaming data. AWS Lambda can trigger compaction jobs based on CloudWatch metrics.

Optimize Large Result Sets with UNLOAD for Faster Output

To export large result sets (>1GB), use the UNLOAD command instead of SELECT. UNLOAD writes data in parallel to multiple compressed files, which is up to 90% faster and reduces storage by 75%.

When your ETL query produces results larger than 1GB, UNLOAD can deliver up to 90% faster execution times and 75% storage reduction compared to standard SELECT statements. Here's how to implement it effectively:

-- Inefficient: Standard SELECT for large result export
-- SELECT * FROM large_transactions_table LIMIT 85000000;  -- Avoid for large exports

-- Efficient: UNLOAD for large result sets
UNLOAD (
    SELECT 
        transaction_id,
        customer_id,
        product_category,
        revenue,
        transaction_timestamp
    FROM transactions 
    WHERE transaction_date >= DATE('2024-01-01')
        AND revenue > 100
    ORDER BY transaction_timestamp DESC
) 
TO 's3://etl-output-bucket/processed-transactions/'
WITH (
    format = 'PARQUET',
    compression = 'SNAPPY',
    field_delimiter = ',',
    partitioned_by = ARRAY['product_category']
);
-- Inefficient: Standard SELECT for large result export
-- SELECT * FROM large_transactions_table LIMIT 85000000;  -- Avoid for large exports

-- Efficient: UNLOAD for large result sets
UNLOAD (
    SELECT 
        transaction_id,
        customer_id,
        product_category,
        revenue,
        transaction_timestamp
    FROM transactions 
    WHERE transaction_date >= DATE('2024-01-01')
        AND revenue > 100
    ORDER BY transaction_timestamp DESC
) 
TO 's3://etl-output-bucket/processed-transactions/'
WITH (
    format = 'PARQUET',
    compression = 'SNAPPY',
    field_delimiter = ',',
    partitioned_by = ARRAY['product_category']
);
-- Inefficient: Standard SELECT for large result export
-- SELECT * FROM large_transactions_table LIMIT 85000000;  -- Avoid for large exports

-- Efficient: UNLOAD for large result sets
UNLOAD (
    SELECT 
        transaction_id,
        customer_id,
        product_category,
        revenue,
        transaction_timestamp
    FROM transactions 
    WHERE transaction_date >= DATE('2024-01-01')
        AND revenue > 100
    ORDER BY transaction_timestamp DESC
) 
TO 's3://etl-output-bucket/processed-transactions/'
WITH (
    format = 'PARQUET',
    compression = 'SNAPPY',
    field_delimiter = ',',
    partitioned_by = ARRAY['product_category']
);
-- Inefficient: Standard SELECT for large result export
-- SELECT * FROM large_transactions_table LIMIT 85000000;  -- Avoid for large exports

-- Efficient: UNLOAD for large result sets
UNLOAD (
    SELECT 
        transaction_id,
        customer_id,
        product_category,
        revenue,
        transaction_timestamp
    FROM transactions 
    WHERE transaction_date >= DATE('2024-01-01')
        AND revenue > 100
    ORDER BY transaction_timestamp DESC
) 
TO 's3://etl-output-bucket/processed-transactions/'
WITH (
    format = 'PARQUET',
    compression = 'SNAPPY',
    field_delimiter = ',',
    partitioned_by = ARRAY['product_category']
);

UNLOAD supports multiple output formats optimized for different downstream systems. Monitor the performance difference and adjust your ETL pipelines to use UNLOAD for any result sets expected to exceed 100MB. The parallel writing capability scales automatically with result size, providing consistent performance improvements for large datasets.

Alternatives: For very large exports exceeding 10GB, consider using AWS Glue for more sophisticated parallel processing. Amazon Redshift's UNLOAD command provides similar functionality for data warehouse environments.

‍

When AWS Athena optimization reaches its limits: The e6data alternative

Even after implementing Parquet partitioning, aggressive result caching, smart JOIN ordering, and automated file compaction, some BI/SQL workloads still face performance bottlenecks. That's where e6data comes in.

e6data is a decentralized, Kubernetes-native lakehouse compute engine delivering significantly faster query performance with lower compute costs through per-vCPU billing and zero data movement. It runs directly on existing data formats (Delta/Iceberg/Hudi, Parquet, CSV, JSON), requiring no migration or rewrites. Teams often keep their existing Athena platform for development workflows while offloading performance-critical queries to e6data for sub-second latency and high concurrency.

Key benefits of the e6data approach:

  • Superior performance architecture: Decentralized vs. legacy centralized systems eliminates coordinator bottlenecks, delivers sub-second latency, and handles high concurrent users without SLA degradation through Kubernetes-native stateless services

  • Zero vendor lock-in: Point directly at current lakehouse data with no movement, migrations, or architectural changes required. Full compatibility with existing governance, catalogs, and BI tools

  • Predictable scaling & costs: Granular 1-vCPU increment scaling with per-vCPU billing eliminates cluster waste and surprise cost spikes. Instant performance with no cluster spin-up time or manual tuning overhead

Start a free trial of e6data and see performance comparison on your own workloads. Use our cost calculator to estimate potential gains.

Book a demo on your own workloads

Reach out to book a demo, share challenges you're facing, and tell us how this fits into what you're currently working on or thinking about.

Prefer to self-serve? Problems we're solving →

An actual person replies. By submitting, you acknowledge your personal information will be processed in accordance with our Privacy Policy.