Amazon Athena: Serverless SQL on S3
Query S3 data directly with standard SQL in Athena, optimise with columnar formats like Parquet and ORC, and partition for cost control.
Amazon Athena: Serverless SQL on S3 is a free AWS Solutions Architect lesson on CoddyKit — lesson 3 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the AWS Solutions Architect learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.
What Is Amazon Athena?
Amazon Athena is a serverless, interactive query service that lets you run standard SQL directly against data stored in Amazon S3. There are no servers to provision, no clusters to manage, and you pay only for the data scanned per query (approximately $5 per TB scanned). Athena uses Presto under the hood and integrates natively with the Glue Data Catalogue for table metadata.
Setting Up Athena: Workgroups and Output Location
Before running queries, configure an Athena Workgroup and specify an S3 path for query result output. Workgroups let you separate query history and cost tracking between teams, enforce encryption on results, and set per-query data-scan limits to prevent runaway costs. Each query result is written as a CSV to the configured S3 output bucket.
# Create a workgroup with an encrypted output location
aws athena create-work-group \
--name analytics-team \
--configuration '{
"ResultConfiguration": {
"OutputLocation": "s3://athena-results-123/analytics-team/",
"EncryptionConfiguration": {"EncryptionOption": "SSE_S3"}
},
"EnforceWorkGroupConfiguration": true,
"PublishCloudWatchMetricsEnabled": true,
"BytesScannedCutoffPerQuery": 10737418240
}'Running Your First Query
Point Athena at a Glue Data Catalogue database and run ANSI SQL. Athena supports SELECT, JOIN, GROUP BY, window functions, and CTEs. You can also use CREATE TABLE AS SELECT (CTAS) to save query results as a new table in Parquet format, effectively materialising intermediate results for faster downstream queries.
-- Query total sales by month from partitioned S3 data
SELECT
year,
month,
SUM(order_total) AS monthly_revenue
FROM my_db.curated_sales
WHERE year = '2024'
GROUP BY year, month
ORDER BY month;
-- CTAS: materialise result as Parquet for reuse
CREATE TABLE my_db.monthly_revenue
WITH (format = 'PARQUET', external_location = 's3://my-data-lake-123/curated/monthly_revenue/')
AS
SELECT year, month, SUM(order_total) AS revenue
FROM my_db.curated_sales
GROUP BY year, month;Cost Optimisation: Partition Pruning
Athena charges per TB of data scanned. The most impactful cost reduction is partition pruning: always include partition columns in your WHERE clause. If data is partitioned by year/month/day, filtering on these columns prevents Athena from scanning other partitions. Without a partition filter on a petabyte-scale table, a simple query can cost hundreds of dollars.
-- EXPENSIVE: No partition filter -> scans all data
SELECT * FROM my_db.curated_sales WHERE customer_id = '12345';
-- CHEAP: Partition filter applied -> scans only Jan 2024
SELECT * FROM my_db.curated_sales
WHERE year = '2024' AND month = '01' AND customer_id = '12345';Cost Optimisation: Columnar Format
Storing data in Apache Parquet or ORC instead of CSV or JSON drastically reduces the data Athena scans per query. A columnar query that touches 3 out of 50 columns scans only those 3 columns' data on disk. Combined with built-in compression (Snappy, Zstd), Parquet files are typically 5–10x smaller than equivalent CSV, multiplying cost savings.
-- After converting raw CSV to Parquet:
-- CSV version: 500 GB table, query scans 500 GB -> $2.50
-- Parquet version: same data compressed to 50 GB,
-- query reads only 2 columns -> scans ~2 GB -> $0.01
-- Verify table format in Glue Catalogue
SHOW CREATE TABLE my_db.curated_sales;Federated Queries with Data Source Connectors
Athena Federated Query extends Athena beyond S3 to query data in RDS, DynamoDB, Redshift, Elasticsearch, and custom sources using Lambda-based data source connectors. You deploy a connector Lambda function from the Serverless Application Repository, register it as an Athena data source, and then query across S3 tables and live databases in a single SQL JOIN — without moving any data first.
-- Federated query: join S3 Parquet with live RDS table
SELECT s.order_id, s.total, c.email
FROM my_db.curated_sales s
JOIN rds_lambda.prod_db.customers c
ON s.customer_id = c.id
WHERE s.year = '2024' AND s.month = '01';Athena and QuickSight Integration
Amazon QuickSight connects directly to Athena as a data source, enabling business analysts to build interactive dashboards from S3 data without any intermediate database. QuickSight uses SPICE (Super-fast, Parallel, In-memory Calculation Engine) to cache Athena query results for fast dashboard rendering. This serverless BI stack (S3 + Glue + Athena + QuickSight) is a common exam pattern for cost-effective analytics.
Athena Query Performance Tuning
Beyond partitioning and columnar format, further tune Athena queries by: splitting large files (aim for 128 MB–1 GB per file for parallelism), using bucketing for frequently joined columns, avoiding SELECT *, and using approximate aggregate functions like approx_distinct() and approx_percentile() when exact values are not required. These techniques reduce both cost and latency.
-- Use approx_distinct for fast cardinality estimate
SELECT
year,
approx_distinct(customer_id) AS approx_unique_customers
FROM my_db.curated_sales
WHERE year = '2024'
GROUP BY year;Controlling Access to Athena
Athena integrates with IAM for access control: users need permissions to run Athena queries (athena:StartQueryExecution), access the S3 output bucket, and read the underlying S3 data. For fine-grained column and row access, combine Athena with Lake Formation. You can also restrict a workgroup to specific databases by IAM condition keys on the workgroup ARN.
# Minimum IAM policy for an Athena analyst
{
"Effect": "Allow",
"Action": [
"athena:StartQueryExecution",
"athena:GetQueryExecution",
"athena:GetQueryResults",
"athena:StopQueryExecution",
"glue:GetDatabase",
"glue:GetTable",
"glue:GetPartitions",
"s3:GetObject",
"s3:PutObject"
],
"Resource": "*"
}Saving Query Results and Scheduled Queries
Athena query results are stored as CSV files in S3 and cached for 7 days so re-running the identical query in that window does not re-scan the data. For recurring reporting needs, use Athena Scheduled Queries to run a query on a cron schedule and save results to a new S3 location or directly into a table. Alternatively, trigger an Athena query from a Step Functions state machine or EventBridge rule.
# Start an Athena query via CLI and retrieve results
QUERY_ID=$(aws athena start-query-execution \
--query-string 'SELECT COUNT(*) FROM my_db.curated_sales WHERE year=2024' \
--work-group analytics-team \
--query 'QueryExecutionId' --output text)
# Wait and fetch results
aws athena get-query-results --query-execution-id $QUERY_IDAthena vs Redshift: Choosing the Right Tool
For the SAA-C03 exam, know when to recommend Athena versus Redshift. Choose Athena for ad hoc, infrequent queries on S3 with no infrastructure to manage. Choose Redshift when you need sub-second response times on complex joins, have a dedicated analytics team running hundreds of concurrent queries, or want to use Redshift Spectrum to extend a data warehouse with S3 data. The key signal is frequency and complexity of queries.
Quick Check
Test your understanding of AWS Solutions Architect (SAA-C03) concepts from this lesson.
Lesson Recap
In this lesson you learned: Athena charges per TB scanned so partition pruning and Parquet format are essential cost controls, Athena Federated Query extends SQL to non-S3 data sources via Lambda connectors, and Athena is best for ad hoc queries while Redshift suits high-concurrency analytics. Next up we explore Kinesis for real-time data streaming and analytics.
Frequently asked questions
Is the “Amazon Athena: Serverless SQL on S3” lesson free?
Yes — the full text of “Amazon Athena: Serverless SQL on S3” is free to read here on the web, and the AWS Solutions Architect course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the AWS Solutions Architect course, upgrade to CoddyKit PRO.
What will I learn in “Amazon Athena: Serverless SQL on S3”?
Query S3 data directly with standard SQL in Athena, optimise with columnar formats like Parquet and ORC, and partition for cost control. You practise AWS Solutions Architect with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.
Do I need any experience to start AWS Solutions Architect?
No prior experience is required. AWS Solutions Architect on CoddyKit is structured for beginners through advanced learners; this is — lesson 3 of 4, so you can start here or from the beginning and move at your own pace.
How long does the “Amazon Athena: Serverless SQL on S3” lesson take?
Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.
Can I write and run code in this AWS Solutions Architect lesson?
Yes. Every AWS Solutions Architect lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.
All lessons in this course
- Building a Data Lake on S3
- AWS Glue: ETL and Data Catalogue
- Amazon Athena: Serverless SQL on S3
- Kinesis Streams, Firehose, and Real-Time Analytics