AWS Builder Center

Builder Center

Building a Cost-Efficient Data Lake with Amazon S3, AWS Glue, and Amazon Athena

Building a Cost-Efficient Data Lake with Amazon S3, AWS Glue, and Amazon Athena Short Description

Building a Cost-Efficient Data Lake with Amazon S3, AWS Glue, and Amazon Athena

Introduction

A modern data lake allows organizations to store large amounts of structured and unstructured data in a centralized location while keeping the data available for different analytics and processing workloads.
AWS provides several services that can be combined to build this architecture. Amazon S3 can act as the storage layer, AWS Glue Data Catalog can provide metadata management, and Amazon Athena can be used to query the data directly from S3 using SQL.
However, simply putting data into S3 and querying it with Athena does not automatically result in an efficient data lake.
Poor file formats, inappropriate partitioning, excessive small files, and inefficient queries can increase query execution time and the amount of data scanned.
This article explores some practical techniques for designing a more efficient and cost-conscious data lake using S3, Glue, and Athena.

Data Lake Architecture

A simple architecture can look like this:
                Data Sources
                     │
                     ▼
                 Amazon S3
                     │
          ┌──────────┴──────────┐
          │                     │
       Raw Data            Processed Data
          │                     │
          └──────────┬──────────┘
                     ▼
          AWS Glue Data Catalog
                     │
                     ▼
               Amazon Athena
                     │
                     ▼
                SQL Analytics
Each service has a different responsibility.
Amazon S3 provides durable object storage.
AWS Glue Data Catalog stores metadata such as database names, table definitions, columns, data types, and partition information.
Amazon Athena uses this metadata to understand the structure of data stored in S3 and execute SQL queries against it.
This separation between storage, metadata, and compute is one of the useful characteristics of a cloud-based data lake.

Organizing Data in Amazon S3

A data lake should have a clear organization strategy.
A common approach is to separate data into different layers:
S3 Bucket
│
├── raw/
│   └── sales/
│
├── processed/
│   └── sales/
│
└── curated/
    └── sales/
The raw layer contains data in its original or minimally transformed form.
The processed layer contains cleaned and transformed data.
The curated layer contains datasets prepared specifically for analytics and downstream consumers.
This structure makes it easier to manage different stages of the data lifecycle.

Choosing the Right File Format

One of the most important decisions in a data lake is the file format.
CSV is simple and widely supported, but it is not usually the most efficient format for analytical workloads.
For analytics, columnar formats such as Apache Parquet can provide significant advantages.
For example:
CSV

row1 → all columns
row2 → all columns
row3 → all columns
Whereas Parquet organizes data in a column-oriented structure:
Parquet

Column A → values
Column B → values
Column C → values
Column D → values
If a query only requires two columns from a dataset containing many columns, a columnar format can allow the query engine to avoid reading unnecessary columns.
For example:
SELECT customer_id, total_amount
FROM sales;
If the table contains 30 columns, storing the data in Parquet can allow Athena to process only the relevant columns rather than reading the entire dataset.
This can improve query efficiency and reduce the amount of data that needs to be scanned.

Compression

Compression is another important consideration.
Parquet supports compression codecs such as Snappy and GZIP.
Compression can reduce storage requirements and the amount of data that needs to be read from S3.
However, compression should be selected based on the workload.
For frequently queried analytical datasets, a balance between compression ratio and decompression performance is generally more useful than simply choosing the highest possible compression.

Partitioning Data

Partitioning is one of the most important techniques for optimizing Athena queries.
Instead of storing all records in a single location, data can be organized based on columns that are commonly used for filtering.
For example:
sales/
├── year=2026/
│   ├── month=01/
│   ├── month=02/
│   └── month=03/
└── year=2025/
    ├── month=01/
    └── month=02/
Suppose we have the following query:
SELECT *
FROM sales
WHERE year = 2026
AND month = 9;
If the table is properly partitioned by year and month, Athena can avoid scanning unrelated partitions.
This is known as partition pruning.
Instead of scanning the entire dataset, Athena can focus on the relevant partition.
This can significantly reduce the amount of data scanned.

Choosing Partition Columns

Partitioning does not mean that every column should become a partition column.
Choosing too many partition columns can create a large number of small directories and files.
Good partition columns are typically:
  • Frequently used in filters
  • Relatively low or moderate cardinality
  • Relevant to common query patterns
Time-based columns such as year, month, and day are common examples for event and transaction datasets.
However, the correct partition strategy depends on how the data will actually be queried.

Partition Pruning

Consider a table containing several years of transaction data.
Without effective partitioning, a query such as:
SELECT SUM(amount)
FROM sales
WHERE year = 2026;
may require Athena to scan a much larger amount of data.
With appropriate partitioning:
sales/
├── year=2024/
├── year=2025/
└── year=2026/
Athena can identify the relevant partition and avoid reading the others.
This is one reason query design and data layout should be considered together when building a data lake.

Filter Pushdown

Another optimization technique is filter pushdown.
The basic idea is to apply filtering as early as possible so that unnecessary data does not need to be processed.
For example:
SELECT customer_id, amount
FROM sales
WHERE amount > 1000;
When supported by the underlying format and query engine, filtering can reduce the amount of data that needs to be processed.
Partition pruning and predicate/filter pushdown are related but different concepts.
Partition pruning determines which partitions need to be accessed.
Filter pushdown can reduce the amount of data processed within the relevant files.
Using both effectively can improve query efficiency.

AWS Glue Data Catalog

Amazon S3 provides storage, but it does not by itself provide a relational table structure that Athena can directly use for SQL analytics.
This is where the AWS Glue Data Catalog becomes important.
The Glue Data Catalog stores metadata such as:
Database
   ↓
Table
   ↓
Columns
   ↓
Data Types
   ↓
Partitions
   ↓
S3 Location
For example, a Glue table might contain:
Table: sales

Columns:
customer_id → bigint
product_id  → string
amount      → double
sale_date   → date

Partitions:
year
month
Athena can then use this metadata when querying the underlying S3 data.

The Small Files Problem

One problem that is often overlooked in data lakes is the small-files problem.
Imagine a dataset containing thousands or millions of tiny Parquet files:
file_001.parquet
file_002.parquet
file_003.parquet
...
file_500000.parquet
Even if the total amount of data is not extremely large, having a very large number of small files can introduce overhead during processing.
This can affect:
  • Metadata operations
  • File listing
  • Query planning
  • Task scheduling
  • Overall query performance
Instead of producing large numbers of tiny files, data pipelines should generally aim for appropriately sized files.
File sizing should be considered alongside partitioning and workload characteristics rather than using a single fixed size for every dataset.

Query Design Matters

The way a query is written can also affect efficiency.
For example, avoid scanning columns that are not required.
Instead of:
SELECT *
FROM sales
WHERE year = 2026;
prefer:
SELECT customer_id, amount
FROM sales
WHERE year = 2026;
The second query communicates that only the required columns should be returned.
Similarly, filtering on partition columns can allow Athena to eliminate irrelevant partitions.

Measuring Query Efficiency

Optimization should always be measured.
Some useful metrics include:
  • Data scanned
  • Query execution time
  • Number of partitions accessed
  • File count
  • Storage size
  • Query frequency
A useful experiment is to compare the same dataset before and after optimization.
For example:
Dataset Design
Query
Data Scanned
Execution Time
CSV, unpartitioned
Filter by date
Measured value
Measured value
Parquet, unpartitioned
Filter by date
Measured value
Measured value
Parquet, partitioned
Filter by date
Measured value
Measured value
The actual results depend on the dataset, file sizes, compression, partitioning strategy, and query.
The important point is to measure the difference instead of assuming that an optimization will automatically improve performance.

Designing a Cost-Efficient Data Lake

A practical workflow can look like this:
Raw Data
   ↓
Amazon S3
   ↓
Transform / Convert
   ↓
Parquet
   ↓
Partition Data
   ↓
AWS Glue Data Catalog
   ↓
Amazon Athena
   ↓
Analyze Query Performance
   ↓
Optimize
The goal is not simply to minimize storage cost.
A good design considers the entire workload:
Storage
   +
Data Processing
   +
Query Performance
   +
Data Scanned
   +
Operational Complexity
Optimizing one component while ignoring the others can create new problems elsewhere.
For example, excessive partitioning might reduce scanned data for some queries but create too many small files and increase operational overhead.

Practical Optimization Checklist

Before considering a data lake optimized, I would check:
  • Is the data stored in an analytical format such as Parquet?
  • Is compression being used appropriately?
  • Are partition columns aligned with actual query patterns?
  • Are queries filtering on partition columns when appropriate?
  • Is unnecessary data being scanned?
  • Are there excessive small files?
  • Is the Glue Data Catalog metadata accurate?
  • Are query results and execution metrics being monitored?
  • Have optimizations been validated using actual measurements?

Key Takeaways

A cost-efficient data lake is not created simply by moving data into Amazon S3.
The way data is stored, organized, partitioned, cataloged, and queried has a direct impact on performance and resource usage.
Some of the most important practices include:
  1. Use columnar formats such as Parquet for analytical workloads.
  1. Apply compression appropriately.
  1. Choose partition columns based on actual query patterns.
  1. Use partition pruning to avoid scanning unnecessary data.
  1. Use filter and predicate pushdown where supported.
  1. Avoid excessive numbers of small files.
  1. Use AWS Glue Data Catalog to manage metadata.
  1. Write queries that read only the required data.
  1. Measure query performance and data scanned before and after optimization.

Conclusion

Amazon S3, AWS Glue Data Catalog, and Amazon Athena provide the building blocks for a scalable serverless data lake architecture.
However, scalability alone does not guarantee efficiency.
Data engineers need to think about how data is physically organized in S3 and how analytical queries will access that data. File format, compression, partitioning, metadata, and query design all contribute to the overall performance of the system.
The key lesson is simple: a good data lake is not just about storing data cheaply; it is about designing the data layout so that the data can also be processed and queried efficiently.
# amazon-s3# aws-glue# amazon-athena# databases# relational-database
Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.
Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article