Querying S3 Data Lake Files with AWS Athena: A Practical Guide

In this article, let us look at how to use AWS Athena to query files sitting in your S3 data lake. If you have been following the data lake series, we already looked at how to design your S3 folder structure. Now it is time to actually query those files without spinning up a single server.

Athena is one of those services that sounds almost too good to be true — you point it at files in S3, write standard SQL, and pay only for the data scanned. No clusters, no loading, no infrastructure. But like most things in data engineering, the devil is in the details. Let us walk through setting it up, writing queries, and most importantly, not burning money while doing it.

What is Athena and When Should You Use It?

Athena is a serverless query engine built on Presto/Trino. You define tables that map to files in S3, and Athena runs SQL against those files on the fly. It supports formats like Parquet, ORC, JSON, CSV, and Avro. Under the hood, it reads the files directly from S3, processes the query, and returns results.

Here is where Athena fits compared to other approaches:

Approach	Best For	Not Ideal For
Athena	Ad-hoc queries, data exploration, one-off analysis	High-concurrency dashboards, sub-second queries
Redshift Spectrum	Queries that join Redshift tables with S3 data	Standalone S3 queries where you do not use Redshift
Spark on EMR	Complex transformations, ML pipelines, streaming	Simple SQL queries where you do not want cluster overhead
RDS / Aurora	OLTP workloads, frequent small queries	Scanning terabytes of raw files

In short, if you already have a data lake on S3 and someone asks you “can you pull me a report of all transactions from last quarter grouped by region”, Athena is the right answer. If they ask you to build a real-time dashboard that refreshes every second, look elsewhere.

Setting Up Athena for the First Time

Before you can run queries, you need to do a few one-time setup steps.

1. Set Up a Query Result Location

Athena stores query results somewhere in S3. You need to specify a bucket for this. Even if your query returns zero rows, Athena will write a small metadata file.

Go to the Athena console, click on “Settings” and then “Manage”. Point it to an S3 location like s3://my-athena-results/query-output/. I prefer to keep this separate from the data lake bucket so result files do not clutter the data folders.

2. Create a Database

CREATE DATABASE IF NOT EXISTS sales_data_lake
COMMENT 'Database for sales data lake queries';

That is it. Databases in Athena are just logical namespaces — they do not store anything themselves.

3. Define Your First Table

This is where things get interesting. Let us say you have Parquet files from your sales system stored in S3 under s3://my-data-lake/sales/transactions/. Here is how you create an external table over those files:

CREATE EXTERNAL TABLE IF NOT EXISTS sales_data_lake.transactions (
  transaction_id STRING,
  customer_id STRING,
  product_id STRING,
  amount DECIMAL(10,2),
  transaction_date DATE,
  region STRING
)
PARTITIONED BY (year INT, month INT, day INT)
STORED AS PARQUET
LOCATION 's3://my-data-lake/sales/transactions/'
TBLPROPERTIES ('parquet.compress'='SNAPPY');

A few things worth pointing out here:

• EXTERNAL TABLE means Athena does not own the data. If you drop the table, your S3 files are untouched.
• PARTITIONED BY helps Athena skip reading irrelevant folders. More on this shortly.
• STORED AS PARQUET is almost always the right choice. Columnar format means Athena only reads the columns your query actually uses. CSV works too, but you will pay for scanning every column every time.

Querying Partitioned Data

Partitioning is the single most important thing you can do to keep Athena fast and cheap. Without partitions, a SELECT * FROM transactions WHERE transaction_date = '2026-03-15' would scan every file in the bucket. With partitioning on year, month, and day, Athena only touches the one folder that matches.

But there is a catch: if you use the Hive-style partitioning above, you need to tell Athena about new partitions. Two ways to do this:

Option 1: Manual partition loading

MSCK REPAIR TABLE sales_data_lake.transactions;

This scans the S3 prefix and adds any new partitions it finds. It works, but it can be slow if you have thousands of partitions. I have seen this run for 10+ minutes on large lakes.

Option 2: Add partitions explicitly

ALTER TABLE sales_data_lake.transactions 
ADD PARTITION (year=2026, month=4, day=14) 
LOCATION 's3://my-data-lake/sales/transactions/year=2026/month=4/day=14/';

Faster and more predictable. If you use a pipeline to land data into S3, I recommend having that same pipeline issue the ALTER TABLE call.

Option 3: Partition projection (the better way)

Athena has a feature called partition projection where you define a pattern and Athena figures out the partitions automatically:

CREATE EXTERNAL TABLE sales_data_lake.transactions_projected (
  transaction_id STRING,
  customer_id STRING,
  amount DECIMAL(10,2),
  transaction_date DATE
)
PARTITIONED BY (dt STRING)
STORED AS PARQUET
LOCATION 's3://my-data-lake/sales/transactions/'
TBLPROPERTIES (
  'projection.enabled'='true',
  'projection.dt.type'='date',
  'projection.dt.range'='2024-01-01,NOW',
  'projection.dt.format'='yyyy-MM-dd'
);

No more MSCK REPAIR or manual partition management. This is what I use in production now and it saves a lot of headaches.

A Real Query Example

Let us say you want to find the top 5 products by revenue in the APAC region for Q1 2026:

SELECT 
  product_id,
  SUM(amount) AS total_revenue,
  COUNT(*) AS transaction_count,
  COUNT(DISTINCT customer_id) AS unique_customers
FROM sales_data_lake.transactions
WHERE region = 'APAC'
  AND year = 2026
  AND month IN (1, 2, 3)
GROUP BY product_id
ORDER BY total_revenue DESC
LIMIT 5;

Pretty straightforward SQL. The key is that WHERE year = 2026 AND month IN (1, 2, 3) is what lets Athena prune partitions and scan only the relevant folders. Without that, you would scan all partitions and your query cost could be 12x higher.

Cost Control — Do Not Skip This Section

Athena pricing is $5 per TB of data scanned. That sounds cheap until you run a SELECT * on a 10 TB dataset and realize you just spent $50 on one poorly written query.

Here is what I do to keep costs sane:

1. Use Parquet (or ORC) always. A compressed Parquet file might be 20% the size of the equivalent CSV. That is 5x cheaper right away.

2. Always filter on partition columns first. If your table is partitioned by date, never run a query without a date filter unless you mean to scan everything.

3. Set up workgroup limits. You can configure per-query and per-day data scan limits at the workgroup level:
   • Go to Athena → Workgroups → Create Workgroup
   • Set “Limit data scanned per query” to something reasonable, say 100 GB
   • Queries that exceed the limit get cancelled before they cost you money
4. Check how much data you scanned. Before running a query against production, do a dry run:

-- Use EXPLAIN to see the scan plan without executing
EXPLAIN SELECT * FROM sales_data_lake.transactions WHERE year = 2026;

1. Use views carefully. Views do not store results — they run the underlying query every time. If you nest views on top of views, you can accidentally trigger massive scans. I learned this the hard way.

Things to Be Careful About

Here are a few gotchas that I have run into over time:

Small files kill performance. Athena parallelizes by reading multiple files at once, but if you have 50,000 tiny files, the overhead of opening and closing each file dominates. Compaction jobs that merge small files into larger ones (256 MB — 1 GB range) make a huge difference.

Schema evolution is manual for some formats. If you add a new column to your pipeline and start writing Parquet files with that column, Athena tables created with explicit column definitions will not see it. You need to update the table schema. Using AWS Glue Crawler or Glue Data Catalog can help automate this.

Partition projection and Glue Catalog do not always play nicely. If you use partition projection, be aware that the partitions exist logically but are not registered in the Glue Catalog in the same way. Some tools that inspect the catalog directly might not show them. It is not a dealbreaker, just something to be aware of.

Concurrency limits. By default, Athena has soft limits on concurrent queries per account and per workgroup. If you are building an application that fires 50 queries at once, you will hit these limits. For ad-hoc use by a small team, this is rarely an issue.

What Would Change in a Production Setup

For a quick exploration, the console and manual table creation is fine. But here is what I would do differently for a real production environment:

• Manage tables with Terraform or CloudFormation. Manual console work does not scale. Define your Athena workgroups, named queries, and even the table DDL in IaC so anyone can reproduce the setup.
• Use AWS Glue Crawler to auto-detect schemas. When your upstream pipeline evolves and adds new fields, the crawler updates the schema without manual intervention.
• Separate workgroups for different teams. Give analysts a workgroup with a daily scan limit so one bad query does not blow the budget. Keep an unrestricted workgroup for the data engineering team.
• Enable CloudWatch metrics and alarms. Athena emits query metrics including scanned bytes and execution time. Set up alarms for queries that scan unusually large amounts of data — it might be a person making a mistake, or it might be a sign that your partitioning strategy needs revisiting.
• Consider named queries for common patterns. Athena lets you save named queries that your team can reuse. This keeps everyone from writing slightly different versions of the same report.

Wrapping Up

Athena is one of those services where the happy path is genuinely easy, but the real world throws enough curveballs that you need to know what you are doing. Use Parquet, partition thoughtfully, set scan limits, and keep an eye on your bills. It might sound like a lot of things to keep in mind, but once you have the patterns down, you can query terabytes of data in seconds without managing a single server. That is the part that still feels a bit like magic, even after using it for years.