Optimizing Athena Queries with Partitioning, Bucketing, and Compression

Hello! Welcome to the first lesson in our module on Interactive Querying and Data Lake Governance.

In our last lesson, we dove deep into optimizing Amazon Redshift queries by analyzing EXPLAIN plans. We saw how choices about join types, data distribution, and sorting could dramatically affect performance in a data warehouse. Today, we'll carry those same performance-tuning principles into the world of the data lake with Amazon Athena.

This lesson directly addresses the learning outcome: Optimize Athena queries using partitioning, bucketing, and compression.

While the goal is the same—run queries faster and cheaper—the methods are different. With a serverless engine like Athena, you don't manage a cluster. Instead, performance is almost entirely determined by how you lay out your data in Amazon S3. Mastering this is a cornerstone of modern data engineering on AWS.

AWS Athena Cost Optimization Strategies Infographic — This infographic provides a great overview of the key strategies we will cover today. The core idea is simple: the less data Athena has to scan from S3, the faster your query will be and the less it will cost.

The "Scan Less, Pay Less" Principle

Athena's pricing model is based on the amount of data scanned from S3. This makes our optimization goal very clear: we must design our data layout to minimize the bytes read for any given query. We have three main levers to achieve this:

Partitioning: Organizing data into a folder structure so Athena only reads the folders relevant to your query.
Compression & Columnar Formats: Reducing the size of your data files and enabling Athena to read only the specific columns needed.
Bucketing: A more granular way to group data within partitions to further reduce the scan scope.

To get a solid grasp of the "why" behind these techniques, let's start with a video that explains the fundamental concepts. While the demo uses AWS Glue to create the data, the principles of how the data is stored in S3 and why it improves performance are universal and directly apply to Athena.

AWS Tutorials - Partition Data in S3 using AWS Glue Job

This video from 'AWS Tutorials' provides an excellent conceptual foundation for why we partition data and use columnar formats. It clearly explains how these techniques reduce the amount of data processed.

Please watch from the beginning until 16:52. Focus on understanding: The three main methods of optimization: columnar storage, row-level partitioning, and file size optimization. The difference in data scanned between row-based (CSV/JSON) and column-based (Parquet) formats. How row-level partitioning creates a folder hierarchy in S3 and allows the query engine to skip irrelevant folders.

Deep Dive 1: Partitioning

As the video explained, partitioning is about creating a logical directory structure for your data in S3. Each level of the directory corresponds to a column in your dataset.

For example, a table of sales data might be stored in S3 with a structure like this:
s3://my-data-lake/sales/year=2023/month=12/day=25/
s3://my-data-lake/sales/year=2024/month=01/day=01/

When you query this data with a WHERE clause that filters on a partition key, Athena performs partition pruning. It intelligently navigates the S3 folder structure and only scans the data in the directories that match your filter.

A query like ... WHERE year = 2024 AND month = 1 will completely ignore all the data from 2023, drastically reducing the data scanned.

Partitioning Best Practices and Pitfalls

Choosing the right partitioning strategy is crucial. The official AWS documentation provides the definitive guide on this.

Optimize data - Amazon Athena

Please read the following sections from the official AWS documentation. This is a key reference for understanding the nuances of Athena performance tuning.

Please read the sections titled 'Partition your data', 'Pick partition keys that will support your queries', and 'Always use STRING as the type for partition keys'. Focus on the guidance for selecting good partition keys (low cardinality, frequently filtered) and the rationale for using the STRING data type.

Key considerations from this reading include:

Choose Low-Cardinality Keys: Partition on columns with a limited number of distinct values (e.g., country, year, month). Partitioning on a high-cardinality column like user_id would create millions of tiny partitions, which severely degrades performance.
Too Many Partitions: A very large number of partitions can make the query planning phase slow, as Athena needs to fetch all partition metadata from the AWS Glue Data Catalog. For these advanced cases, AWS provides two features:
- Partition Projection: Allows you to define partition rules in the table properties, so Athena can calculate partitions in memory without calling the Glue Catalog.
- Partition Indexes (in Glue): Creates an index on partition values in the Glue Catalog, speeding up the lookup of relevant partitions.
Data Type: Always define partition keys as STRING in your table DDL. This allows Athena to effectively push down the filter logic to the Glue Catalog. You can cast the column to a DATE or INTEGER within your query if needed.

Deep Dive 2: Compression and Columnar Formats

Once Athena has selected the right partitions (folders), it still needs to read the data inside the files. This is where compression and file formats come in.

Columnar Formats (Apache Parquet, Apache ORC): As you saw in the first video, these formats store data by column, not by row. Since most analytical queries only select a subset of columns (SELECT user_id, purchase_amount, ...), this allows Athena to read only the data for the columns you requested. This is a massive improvement over row-based formats like CSV or JSON, where the engine must read the entire row even to get a single column's value.
Compression (Snappy, ZSTD, Gzip): Compressing your data files reduces their size in S3. This means less data needs to be transferred over the network to the Athena engine, resulting in faster queries and lower costs. Snappy is a very common choice as it offers a good balance of compression ratio and fast decompression speed, and it is "splittable," which allows for better query parallelism.
The "Small Files" Problem: A data lake with millions of tiny files performs poorly. There is overhead for every single file that Athena must open and process. It's far more efficient to have a smaller number of larger files (ideally 128MB - 1GB). Tools like AWS Glue can be configured to compact small files into larger ones during your ETL process.

Let's refer back to the AWS documentation for the official guidance on these topics.

Optimize data - Amazon Athena

Let's continue with the 'Optimize data' documentation to cover file formats, compression, and the small files problem.

Please read the sections titled 'Use columnar file formats', 'Compress data', and 'Avoid having too many files'. Pay attention to the benefits of Parquet/ORC and the trade-offs between different compression types.

Test your understanding!

You are designing a data lake table for website clickstream events. The raw data arrives as millions of small JSON files per hour. Analysts will primarily query this data by event_date (e.g., '2024-05-21') and by event_type (e.g., 'page_view', 'add_to_cart'). They typically only need 5-10 columns out of the 50 available in each event.

What would be your recommended strategy for partitioning, file format, and compression to prepare this data for Athena?

Show answer

Partitioning: Partition by event_date (/event_date=2024-05-21/). This is a low-cardinality, common filter. Partitioning by event_type as a second level (/event_date=.../event_type=.../) would also be a good strategy.
File Format: Convert the data from JSON to Apache Parquet. This columnar format will be highly effective because queries only need a subset of the 50 columns.
Compression: Use Snappy compression. It's efficient and splittable, working well with Parquet.
File Size: Implement an ETL process (e.g., using AWS Glue) to compact the incoming small JSON files into larger Parquet files (e.g., a few hundred MB each) within each partition.

Deep Dive 3: Bucketing

What if you need to filter on a high-cardinality column, like customer_id? Partitioning by customer_id would be a disaster, creating millions of partitions. This is where bucketing comes in.

Bucketing is an optimization technique that you apply within a partition. It distributes data into a fixed number of files (buckets) based on the hash of a specific column's value.

AWS Athena Bucketing for Query Optimization — This diagram illustrates how bucketing works. Within a partition (like a specific day), data is distributed into a pre-defined number of buckets based on the `customer_id`. When you query for a specific `customer_id`, Athena calculates which bucket that ID belongs to and only reads that single file, skipping the rest.

The following AWS Big Data Blog post provides a fantastic, practical walkthrough of implementing and measuring the impact of bucketing.

Optimize data layout by bucketing with Amazon Athena ...

This blog post provides a real-world example of using bucketing to optimize Athena queries, with clear performance comparisons.

Please read the following parts of the article: The introduction and 'Example use case' sections to understand the scenario. The section 'Optimize data layout using Athena CTAS' to see how to create a bucketed table and the performance difference it makes. Finally, review the 'Results' table at the end to see the summary of performance improvements. Focus on the dramatic reduction in both runtime and scanned data size when moving from a non-bucketed to a bucketed table.

As the article demonstrates, adding bucketing on top of partitioning and columnar formats can further reduce data scanned by over 80% for appropriate queries, pushing performance even higher and costs even lower.

Conclusion

You've now learned the three most important techniques for optimizing Amazon Athena queries. Unlike traditional databases, performance tuning in the data lake is all about the physical layout of your files on S3.

Key Takeaways:

Athena's performance and cost are directly tied to the amount of data scanned from S3.
Partitioning is your first and most powerful tool. Organize data in S3 directories based on low-cardinality columns that are frequently used in WHERE clauses.
Use columnar formats like Parquet or ORC to allow Athena to read only the columns required by your query.
Always compress your data. Snappy is a great default choice for its balance of speed and size reduction.
Use bucketing for queries that filter on specific values of a high-cardinality column (like a user or device ID), which are unsuitable for partitioning.
Consolidate small files into larger ones to reduce query planning overhead and avoid S3 request throttling.

Next Up:
Now that you know how to write efficient queries by optimizing the underlying data, our next lesson will focus on managing users and controlling costs at a higher level. We will learn how to configure Athena workgroups for cost control and query access management. This will allow you to set spending limits and segregate workloads for different teams using your data lake.