Amazon Athena SQL Best Practices for Faster, Cheaper Queries

Amazon Athena charges $5.00 per terabyte of data scanned. That pricing model is both Athena’s greatest strength — there are no servers to provision, no minimum commitments — and its biggest operational risk. A single poorly-written query on an unpartitioned, row-format dataset can scan terabytes and generate hundreds of dollars in charges before anyone notices. This guide covers the SQL-level optimisations, data format choices, and workgroup configurations that keep Athena fast and affordable.

Understand What Drives Athena Cost and Latency

Before optimising, understand the mechanism. Athena is built on Presto/Trino with a serverless execution model. When you run a query, Athena:

1. Plans the query and identifies which S3 objects need to be read
2. Allocates workers proportional to the data volume (you have no direct control over this)
3. Downloads the required S3 objects to worker memory
4. Executes filters, joins, aggregations, and projections
5. Writes results to your S3 results bucket

Cost is determined by step 3 — the bytes downloaded from S3. Latency is determined by the time to complete steps 2–5. Everything that reduces bytes scanned reduces both cost and latency simultaneously. This is the key insight: performance optimisation and cost optimisation are the same thing in Athena.

Use Columnar Formats: The Highest-Leverage Change

If you are running Athena against CSV or JSON files in S3, switching to Parquet or ORC is the single most impactful optimisation you can make. For a typical analytical workload touching 5 of 50 columns:

• CSV: Athena scans 100% of the data, every time
• Parquet with Snappy: Athena scans ~10% of the data (the 5 relevant columns) and skips row groups where statistics prove no matching rows exist

A 10× reduction in scan volume translates directly to a 10× reduction in cost and a significant latency improvement. Convert existing CSV datasets to Parquet using an Athena CTAS query:

-- Convert CSV dataset to partitioned Parquet in one operation
CREATE TABLE "processed_db"."orders_parquet"
WITH (
    external_location = 's3://my-data-lake/processed/orders/',
    format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    partitioned_by = ARRAY['year', 'month']
) AS
SELECT
    order_id,
    customer_id,
    order_amount,
    currency,
    order_status,
    YEAR(order_date) AS year,
    MONTH(order_date) AS month
FROM "raw_db"."orders_csv";

CTAS with Parquet output is idempotent if you write to a clean S3 prefix, making it safe to run as a one-time migration. For ongoing ingestion, configure your upstream ETL (Glue, Spark) to write Parquet from the start.

Our detailed comparison of Parquet vs. ORC on AWS covers the format selection decision in depth.

Partition Pruning: The Second Most Important Optimisation

Partitioning tells Athena which S3 prefixes to skip entirely based on query predicates. If your data is partitioned by year/month/day and your query has WHERE order_date >= DATE '2024-01-01', Athena can skip all data from 2023 and earlier without reading a single byte.

Partition pruning only works when the predicate is on the partition column and the partition column is referenced directly in the WHERE clause. Common anti-patterns that break partition pruning:

-- BROKEN: function applied to partition column prevents pruning
WHERE DATE_TRUNC('month', order_date) = DATE '2024-01-01'

-- CORRECT: direct comparison on partition column
WHERE order_date >= DATE '2024-01-01' AND order_date < DATE '2024-02-01'

-- BROKEN: partition column derived from a non-partition column
WHERE YEAR(event_timestamp) = 2024

-- CORRECT: use the actual partition columns
WHERE year = 2024

Verify partition pruning is working by inspecting the Data scanned metric in the Athena query execution details. A query that should only touch January 2024 data should scan roughly 1/36 of a 3-year dataset. If it scans the full dataset, partition pruning is not occurring.

Partition column naming: Use explicit partition columns (year, month, day) rather than storing the date as a single string partition (partition_date = '2024-01-15'). String-format date partitions prevent Athena from using range predicates for pruning — it can only prune on equality comparisons for string-typed columns.

The Small Files Problem

Athena performs a separate S3 API call for each file it reads. When data is stored as thousands of tiny files (common in Kinesis Firehose outputs or frequent Glue micro-batch jobs), Athena spends more time on S3 API overhead than on actual computation. A directory with 10,000 files of 1 MB each performs significantly worse than 100 files of 100 MB each, even though the total data is identical.

Target file size: 128 MB to 1 GB per file for Parquet. This range ensures each Athena worker has substantial work per file without files being too large to benefit from parallel reads.

Compaction strategy: Run a periodic compaction job using Athena CTAS or Glue to merge small files into appropriately-sized ones:

-- Compact small files for a specific partition
CREATE TABLE "processed_db"."orders_compacted_tmp"
WITH (
    external_location = 's3://my-data-lake/processed/orders-tmp/',
    format = 'PARQUET',
    parquet_compression = 'SNAPPY',
    partitioned_by = ARRAY['year', 'month', 'day']
) AS
SELECT * FROM "processed_db"."orders"
WHERE year = 2024 AND month = 1;
-- Then swap the S3 prefixes and update the Glue catalog partition location

Apache Iceberg’s automatic compaction is a more elegant solution for tables with ongoing small-file accumulation. See Apache Iceberg with AWS Glue for the table-format approach.

Query-Level Optimisations

Project Only the Columns You Need

-- EXPENSIVE: scans all column data even for a count query
SELECT COUNT(*) FROM orders WHERE year = 2024;

-- CHEAPER: reference a lightweight column for counting (the COUNT(*) optimisation)
-- Athena's Presto engine is smart enough to avoid full column scans for COUNT(*)
-- but this matters for SELECT *:

-- EXPENSIVE: reads all columns
SELECT * FROM orders WHERE customer_id = 'C123';

-- CHEAPER: read only what you need
SELECT order_id, order_date, order_amount, currency
FROM orders
WHERE customer_id = 'C123' AND year = 2024;

Push Filters as Early as Possible

Move filter predicates as close to the data source as possible. In complex queries with joins, ensure that filter conditions on the large fact table appear in the WHERE clause, not as a join condition that Athena evaluates after scanning both tables:

-- POTENTIALLY EXPENSIVE: large table join before filtering
SELECT o.order_id, o.order_amount, c.customer_segment
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country_code = 'CA';

-- MORE EFFICIENT: filter orders before the join using a subquery
SELECT o.order_id, o.order_amount, c.customer_segment
FROM (
    SELECT order_id, customer_id, order_amount
    FROM orders
    WHERE year = 2024  -- partition pruning on the large table
) o
JOIN customers c ON o.customer_id = c.customer_id
WHERE c.country_code = 'CA';

Athena’s query planner is sophisticated enough to push many predicates down automatically, but complex join trees can confuse the planner. When in doubt, use subqueries to force evaluation order.

Avoid SELECT DISTINCT on Large Tables

SELECT DISTINCT typically triggers a full table scan followed by a deduplication step. For large tables, this is expensive. Prefer GROUP BY with aggregation when you need unique values, and consider whether the data model can be changed to avoid the need for deduplication at query time entirely.

Approximate Aggregations for Exploratory Analysis

Athena (Presto engine) supports approximate functions that can be dramatically faster for exploratory queries:

-- Exact count distinct: expensive, requires full scan and hash set
SELECT COUNT(DISTINCT customer_id) FROM orders;

-- Approximate count distinct (HyperLogLog): fast, <2% error typical
SELECT APPROX_DISTINCT(customer_id) FROM orders;

-- Approximate percentiles: much faster than PERCENTILE_CONT for large datasets
SELECT APPROX_PERCENTILE(order_amount, ARRAY[0.5, 0.75, 0.90, 0.95, 0.99])
FROM orders
WHERE year = 2024;

Use approximate functions for exploratory analysis and dashboards where the business can tolerate a small margin of error. Reserve exact functions for financial reporting and compliance contexts.

Workgroup Configuration for Cost Governance

Set query scan limits at the workgroup level to prevent unexpected costs:

-- Athena doesn't support this via SQL; use the console, CLI, or Terraform:
-- aws athena update-work-group \
--   --work-group production-analysts \
--   --configuration-updates BytesScannedCutoffPerQuery=10737418240

For production dashboards and automated pipelines, set the cutoff higher (100 GB or unlimited) since you have validated those queries. For self-service analyst workgroups, set a 10 GB limit. A query that hits the limit fails immediately with an informative error, preventing accidental full-table scans.

Enable query result reuse at the workgroup level for dashboard queries. When multiple users open the same QuickSight dashboard within the result TTL window, Athena returns the cached result without re-scanning data:

{
  "ResultReuseByAgeConfiguration": {
    "Enabled": true,
    "MaxAgeInMinutes": 60
  }
}

Athena Query Performance Checklist

Before running an expensive query in production, verify:

• Data is in Parquet or ORC format (not CSV/JSON for analytical queries)
• Table is partitioned and the WHERE clause references partition columns directly
• Query projects only required columns, not SELECT *
• File sizes in the target partition are in the 128 MB–1 GB range (not thousands of tiny files)
• JOIN conditions reference appropriately filtered subsets of large tables
• Workgroup has a bytes-scanned limit configured for cost control
• Query result reuse is enabled if this query will be run repeatedly

For teams building self-service analytics environments on top of Athena, pairing these query optimisations with proper QuickSight SPICE architecture eliminates most Athena query costs entirely — SPICE datasets refresh on a schedule, and subsequent dashboard interactions hit in-memory cache. See Building Self-Service Analytics Platforms on AWS for the full architecture.

Real Cost Impact: A Case Study

A common scenario: an organisation has 3 years of order data (5 TB uncompressed CSV in S3), queried daily by a QuickSight dashboard refreshing at 6am.

Before optimisation:

• Format: CSV, no partitioning
• Athena scan per refresh: ~5 TB
• Cost per refresh: $25
• Monthly cost: $750

After optimisation (Parquet + date partitioning):

• Format: Parquet/Snappy, year/month/day partitions
• Query: WHERE year = 2024 AND month = 1 on a dashboard showing 90-day window
• S3 size after conversion: ~800 GB
• Athena scan per refresh: ~65 GB (only recent 90 days, only needed columns)
• Cost per refresh: $0.33
• Monthly cost: $10

That is a 98.7% reduction in Athena costs with no change to dashboard functionality, achieved through format conversion, partitioning, and targeted query predicates.

Conclusion

Amazon Athena’s pay-per-scan pricing model rewards teams that invest in data organisation and query craft. Columnar formats eliminate unnecessary column reads, partitioning eliminates unnecessary partition reads, and careful SQL ensures Athena’s execution engine has every advantage to minimise data movement. The teams that treat these as ongoing engineering disciplines — not one-time configurations — consistently run Athena at a fraction of the cost of teams that do not.

If your Athena bills are growing faster than your data volumes, there are almost certainly optimisation opportunities available. Infra IT Consulting conducts Athena cost reviews and implements data format and partitioning strategies that significantly reduce costs without disrupting existing workloads. Contact us to discuss your Athena optimisation opportunities.