Amazon Athena

By the end of this lesson you will be able to

• Explain what Amazon Athena is: a serverless query service that runs standard SQL directly on files in S3, with no cluster to run and no data to load, built on the open-source Trino and Presto engines.
• Describe how a query runs: a table definition in the AWS Glue Data Catalog, parallel reads from S3, results written to S3, and tools such as the console, JDBC and ODBC drivers and QuickSight on top.
• Estimate a query’s cost from the data it scans (5 USD per TB in us-east-1, 10 MB minimum per query) and say which queries are charged.
• Cut cost and time with the four classic techniques: columnar formats (Parquet, ORC), compression, partitioning and bigger files, and convert data with a Glue job or a CTAS statement.
• Use Federated Query to join S3 with RDS, DynamoDB and other sources through Lambda data source connectors, and know its limits.
• Control cost and access with workgroups (per-query limits, workgroup alerts, result reuse, capacity reservations), and choose between Athena, Redshift and Redshift Spectrum.

#Why Athena: question the files where they already live

Chapter 17 is about data and analytics: the services that store, process, search and show large amounts of data. The SAA exam asks several scenario questions about them, and almost every answer starts with the same fact: the data is already sitting in S3. This lesson is about the simplest way to ask it a question.

#The problem

A Dhaka e-commerce shop writes every page view, search and order to S3: clickstream from the website, access logs from its load balancer, and a daily export of orders. A year later there are terabytes of files. On a Monday morning the marketing head asks: “How many orders came from Chattogram last Eid week, and from which ad campaign?”

The traditional answer is to load the files into a database or a data warehouse first: build tables, run an import for hours, keep a server running. For a question asked once, that is far too much work. What the analyst really wants is to point SQL at the files and get an answer in seconds.

#What Athena is

Amazon Athena is a serverless interactive query service that analyses data stored in Amazon S3 using standard SQL:

• Serverless. There is no cluster to create, size, patch or stop. You open the console (or call the API) and run a query; AWS provides the compute for that query and takes it away afterwards.
• No loading. The data stays in S3 in its own files. You describe the files once as a table (columns, types, format, S3 location), and Athena reads them at query time. This is called schema on read.
• Standard SQL. Athena’s engine is built on the open-source Trino and Presto distributed SQL engines (the slides say “built on Presto”; Athena engine version 3 is based on Trino). You get SELECT, JOIN, GROUP BY, window functions, and functions for JSON, arrays, maps and geospatial data.
• Many formats. CSV and other delimited text, JSON, ORC, Avro and Parquet, plus log formats (such as CloudTrail, ALB and VPC Flow Logs) through built-in table definitions, and open table formats such as Apache Iceberg.
• Pay per query. You pay for the data each query scans, not for servers. Nothing runs between queries, so nothing is billed between them.

#How a query runs

#The pieces

#Step by step

1. Describe the files once

   Create a database and a table in the Data Catalog that points at the S3 prefix and says how to read the files. Dropping a table only deletes this description; the files in S3 stay.

2. Run SQL

   A query is submitted from the console, an API call, a JDBC or ODBC driver, or QuickSight. Athena checks your IAM permissions and looks the table up in the catalog.

3. Read only what is needed

   Athena works out which partitions and files match, and many workers read them from S3 in parallel. For columnar files it reads only the requested columns. Every byte read is counted.

4. Return and store the result

   The result goes back to the client and is saved in the result location in S3, where other tools (or a later download) can use it. Large result sets can be written in any format with UNLOAD.

Athena reads S3 with the caller’s permissions. The person or role running a query needs permission for Athena itself, for the Glue catalog (glue:GetTable, glue:GetPartitions…), s3:GetObject and s3:ListBucket on the data, s3:PutObject on the result location, and kms:Decrypt if the data is encrypted with SSE-KMS. Fine-grained table, column and row permissions are added with Lake Formation (lesson 17.6).

#Typical uses

• Log analysis: VPC Flow Logs (lesson 22.3), ALB and CLB access logs, CloudTrail logs (lesson 19.6), S3 server access logs, S3 Inventory reports. These services already write to S3, and the Athena documentation provides ready-made table definitions for them.
• Business intelligence and reporting: QuickSight dashboards with Athena as the data source, over a data lake in S3.
• Ad-hoc analysis: one-off questions from analysts and engineers, data exploration before building anything bigger.
• Data preparation: CTAS and INSERT INTO to clean, convert and reshape data into new tables in S3.

#Pricing: you pay for the bytes you scan

The default model is per query:

• 5 USD per TB of data scanned in us-east-1 (the price on the slides, still current; a few Regions cost more).
• Rounded up to the nearest megabyte, with a 10 MB minimum per query. A query on a tiny file is billed as 10 MB.
• Free: DDL statements such as CREATE TABLE, ALTER TABLE ADD PARTITION and DROP TABLE, and failed queries.
• Charged: a cancelled query, for the data it scanned before it was stopped.
• Billed separately: S3 requests and storage for your data and results, the Glue Data Catalog beyond its free tier, and Lambda for federated queries.

Since the bill is bytes scanned, everything that makes Athena cheaper also makes it faster: fewer bytes read is less work.

A worked example: the shop’s orders table is 10 GB of CSV per day, 3.65 TB for a year.

The same question, from 18 dollars to a tenth of a cent. A dashboard that runs it every hour turns that difference into thousands of dollars a year.

#Making Athena fast and cheap

These four techniques are the heart of the notes, the slides and the exam.

#1. Columnar formats: Parquet and ORC

CSV, JSON and other text formats are row formats: each line holds a whole record, all columns together. To read two columns, Athena must read every line in full.

Apache Parquet and Apache ORC are columnar formats: inside each file the values of one column are stored together, in blocks, with statistics (minimum and maximum per block) in the file. That gives three wins:

• Read only the needed columns. A query on 2 of 20 columns reads roughly a tenth of the file.
• Better compression. Values of one column look alike (dates, cities, prices), so they compress far better than mixed rows.
• Skip blocks. With the min and max statistics Athena can skip whole blocks that cannot match the WHERE (predicate pushdown).

The notes say this cuts cost by 80–90%. That is a fair rule of thumb; with few columns and good compression the saving is often larger, as the table above shows.

Avro is also supported, but it is a row-based binary format, good for writing records one by one (streaming), not for saving scans.

#2. Compression

Compressed files are smaller, so there are fewer bytes to read from S3 and fewer to pay for. Athena reads many codecs: Snappy, gzip, zstd (Zstandard), lz4, bzip2, zlib and deflate. Two details matter:

• Inside Parquet and ORC, compression is applied per block (Snappy is the common default, zstd compresses better), so files stay splittable: many workers can read one file in parallel.
• A whole text file compressed with gzip is not splittable: one worker must read it from start to end. Many medium gzip files are fine; one huge gzip file is slow. bzip2 is splittable but slow to decompress.

Compression helps CSV too (about 3:1 in the example), but a compressed CSV still has to be read in full rows. The best result is columnar and compressed.

#3. Partitioning

Partitioning stores a table’s files in folders (S3 prefixes) by the values of one or more columns, usually date parts, so that a query only reads the folders it needs. Athena uses the Hive style, column=value in the path:

s3://shop-logs-example/orders/year=2026/month=10/day=30/part-0001.snappy.parquet
s3://shop-logs-example/orders/year=2026/month=10/day=29/part-0001.snappy.parquet

year, month and day become virtual columns of the table: they do not exist inside the files, only in the path. A query with WHERE year='2026' AND month='10' AND day='30' reads one folder and skips the other 364. This is partition pruning.

Three things to know beyond the notes:

• Partitions must be known to the catalog. New folders are invisible until they are registered: with ALTER TABLE ADD PARTITION, with MSCK REPAIR TABLE (which scans the prefix for Hive-style folders and gets slow on large tables), with a scheduled Glue crawler, or, best for time-based data, with partition projection: you describe the possible values in the table properties (for example days from 2024 onward), and Athena computes the paths at query time, with nothing to register and faster planning.
• The query must filter on the partition columns. A WHERE on an ordinary column such as order_date cannot prune anything, even if the table is partitioned by day.
• Do not over-partition. Partition by columns that most queries filter on and that have few distinct values (date, maybe country or region). Partitioning by hour and customer ID creates millions of tiny partitions; planning, listing and opening files then cost more than the reading itself. Aim for partitions holding at least a few hundred MB.

#4. Bigger files, fewer of them

Every file has a fixed cost: an S3 request, opening it, reading its footer. Millions of 1 KB files (a classic result of streaming every few seconds) make queries slow even when the bytes are few. Merge them into files of roughly 128 MB to 512 MB (the slides say “larger than 128 MB”). Too-large files hurt the other way only when they are not splittable (a single huge gzip). This merging is called compaction.

#Converting the data: Glue ETL or CTAS

Data usually arrives as CSV or JSON. Two common ways to convert it:

• An AWS Glue ETL job (the slides’ answer: “use Glue to convert your data to Parquet or ORC”): a serverless Spark job that reads the raw prefix, writes compressed, partitioned Parquet to a curated prefix, and can run on a schedule or a trigger. Job bookmarks stop it reprocessing old files. Glue is lesson 17.6.
• A CTAS statement (CREATE TABLE AS SELECT) in Athena itself: the query result becomes a new table, in the format, compression and partitioning you choose. One CTAS or INSERT INTO can write at most 100 partitions; for more, run a CTAS for the first 100 and then INSERT INTO batches of up to 100.

For streams, Amazon Data Firehose can convert JSON to Parquet or ORC on the way in, using a Glue table’s schema, and its buffer settings control the file sizes (lesson 13.6).

Try the four techniques yourself: change the format, partitioning, columns and date range, and watch the scanned bytes and the bill.

#Federated Query: SQL beyond S3

Not all data is in S3. Orders may be in Aurora, stock levels in DynamoDB, product reviews in DocumentDB, some history in an on-premises SQL Server. Athena Federated Query runs SQL across relational, non-relational, object and custom data sources, in AWS or on premises, and joins them in one statement.

How it works:

• Each source is reached through a data source connector, which runs as an AWS Lambda function in your account (built with the Athena Query Federation SDK; AWS provides about 30 ready-made connectors, deployable from the console or the Serverless Application Repository).
• Connectors exist for DynamoDB, RDS and Aurora (MySQL, PostgreSQL), Redshift, DocumentDB, OpenSearch, CloudWatch Logs and Metrics, Timestream, Neptune, ElastiCache-compatible Redis, HBase on EMR, MSK and Kafka, SQL Server, Oracle, Db2, Teradata, Snowflake, Google BigQuery and more, and you can write your own.
• Athena pushes down column lists and filters, so each source returns as little as possible. Results larger than a Lambda response are written to a spill bucket in S3.
• The query’s results are stored in S3 like any other Athena result, so they can be reused, or turned into a Parquet table with CTAS to feed the data lake.
• Cost: Athena’s per-TB price on the data scanned across all sources (rounded up to the MB, 10 MB minimum) plus the Lambda invocations, plus whatever the source charges (for example DynamoDB read units).

Limits to remember: writing to a federated source (INSERT INTO) is not supported; a connector for a database inside a VPC must run in that VPC with a network path and security group rules to it; a slow or failing connector makes the whole query slow or fail; and a federated query reads the live operational database, so heavy scans compete with the application.

#Workgroups: control cost and access

A workgroup is a named container for queries, with its own settings. Use one per team, application or environment:

• Separate result locations and encryption per workgroup (or Athena-managed results), and the option to enforce them so users cannot override them.
• Per-query data usage control: a limit on the data one query may scan, from 10 MB upward. Any query that goes over it is cancelled (and billed for what it scanned up to that point).
• Workgroup-wide data usage alerts: thresholds on the total scanned per hour or per day. They raise a CloudWatch alarm that notifies an SNS topic (email, Lambda); they do not cancel queries. To stop spending, an admin (or a Lambda function) can disable the workgroup.
• Metrics and cost allocation: per-workgroup CloudWatch metrics, and tags on workgroups to split the bill by team.
• Access control: IAM policies can allow a user to run queries only in certain workgroups.
• Query result reuse: when turned on, an identical query in the same workgroup returns the stored result of a previous run, if that run is younger than the maximum age (60 minutes by default, up to 7 days), and scans nothing. Athena does not check whether the data changed meanwhile.
• Capacity reservations are assigned to workgroups (side note above).

#Security in short

• IAM controls who can run queries and in which workgroup; S3, Glue and KMS permissions control what they can read.
• Encryption at rest: Athena reads data encrypted with SSE-S3, SSE-KMS or client-side encryption with KMS, and can encrypt query results with SSE-S3, SSE-KMS or client-side KMS (lesson 10.8). In transit, everything uses TLS.
• Private access: an interface VPC endpoint (PrivateLink) for Athena keeps calls off the internet.
• Fine-grained access (only some columns or rows, for example hiding customer phone numbers): AWS Lake Formation on top of the Glue Data Catalog (lesson 17.6).
• Audit: Athena API calls are logged in CloudTrail.

#Beyond plain queries

#Athena, Redshift, Redshift Spectrum and EMR

All of them can share table definitions in the Glue Data Catalog. The OLTP vs OLAP background is in lesson 16.1.

#What goes wrong

• A huge bill from one query: SELECT * on unpartitioned CSV, or a WHERE on a normal column instead of the partition column. Convert to Parquet, partition, filter on partitions, and set a per-query data limit in the workgroup.
• Zero rows for new data: the new partition is not registered. Use partition projection, a crawler, or ALTER TABLE ADD PARTITION.
• Slow queries with little data scanned: millions of small files or over-partitioning. Compact files to 128–512 MB, partition by fewer columns.
• One giant gzip file is slow: it is not splittable. Split it, or convert to Parquet.
• HIVE_BAD_DATA or wrong values: the table schema does not match the files (a column added in the middle of a CSV, a type mismatch). Fix the table definition, or use a columnar format that stores its schema.
• Access Denied: the caller lacks S3, Glue or KMS permissions, or cannot write to the result location.
• HIVE_TOO_MANY_OPEN_PARTITIONS: a CTAS or INSERT INTO writing more than 100 partitions. Split it into batches.
• A federated query times out: the Lambda connector cannot reach the source (VPC, route, security group), or the source is slow. Federated queries also load the production database.
• Stale numbers: query result reuse returned a result older than the latest data. Lower the maximum age, or turn reuse off for that query.
• Partial result files left in S3 after cancelled or failed queries. Use a lifecycle rule on the result bucket, or managed query results.
• Queries queue or are throttled at busy times: the account’s concurrent query quota is reached. Spread the load, raise the quota, or use a capacity reservation.

#Athena in system design

Common patterns:

• Serverless data lake: raw data lands in S3 (from Firehose, DMS, uploads), a Glue job or CTAS writes curated Parquet, the Glue Data Catalog describes it, Athena queries it, QuickSight shows it, Lake Formation governs who sees what. The full pipeline is lesson 17.8.
• Log analytics: CloudTrail, VPC Flow Logs, ALB and S3 access logs in S3, queried with Athena only when needed.
• Reports on a schedule: EventBridge or Step Functions start an Athena query every night; the result in S3 feeds a report or the next step of a pipeline.
• Feature exploration for ML: data scientists explore training data in S3 with SQL before SageMaker jobs (chapter 18).

When Athena is the wrong choice: low-latency lookups for an application (use DynamoDB or RDS), hundreds of BI users running heavy queries all day on the same data (Redshift is usually cheaper and faster), full-text search with relevance and partial matches (OpenSearch), and transactional updates of individual rows without Iceberg.

#Try it yourself

These commands use the AWS CLI v2 and SQL in the Athena query editor. Queries cost real money, so keep the tables small and delete the buckets at the end.

# 1. A bucket for data and a workgroup with a result location, enforced settings and a 1 GB per-query limit
aws s3 mb s3://shop-logs-example --region ap-southeast-1
aws s3 cp orders-2026-10-30.csv s3://shop-logs-example/orders_raw/year=2026/month=10/day=30/
aws athena create-work-group --name adhoc \
  --configuration 'ResultConfiguration={OutputLocation=s3://shop-athena-results-example/adhoc/},EnforceWorkGroupConfiguration=true,PublishCloudWatchMetricsEnabled=true,BytesScannedCutoffPerQuery=1073741824'

-- 2. Describe the raw CSV files once (DDL is free)
CREATE DATABASE shop;

CREATE EXTERNAL TABLE shop.orders_raw (
  order_id string, customer_id string, city string, amount double, campaign string, order_ts timestamp
)
PARTITIONED BY (year string, month string, day string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION 's3://shop-logs-example/orders_raw/'
TBLPROPERTIES ('skip.header.line.count'='1');

-- 3. Register the partitions that already exist (Hive-style folders)
MSCK REPAIR TABLE shop.orders_raw;

-- 4. Convert to compressed, partitioned Parquet with CTAS
CREATE TABLE shop.orders
WITH (format = 'PARQUET', write_compression = 'SNAPPY',
      external_location = 's3://shop-logs-example/orders/',
      partitioned_by = ARRAY['year','month','day'])
AS SELECT order_id, customer_id, city, amount, campaign, order_ts, year, month, day
FROM shop.orders_raw;

-- 5. A query that prunes partitions and reads two columns
SELECT city, count(*) AS orders
FROM shop.orders
WHERE year = '2026' AND month = '10' AND day = '30'
GROUP BY city ORDER BY orders DESC;

# 6. Run the same query from the CLI and read the statistics and the result
QID=$(aws athena start-query-execution --work-group adhoc \
  --query-string "SELECT city, count(*) AS orders FROM shop.orders WHERE year='2026' AND month='10' AND day='30' GROUP BY city" \
  --query QueryExecutionId --output text)
aws athena get-query-execution --query-execution-id "$QID" \
  --query 'QueryExecution.[Status.State,Statistics.DataScannedInBytes,Statistics.EngineExecutionTimeInMillis]'
# [ "SUCCEEDED", 1874211, 912 ]
aws athena get-query-results --query-execution-id "$QID" --max-items 3
# { "ResultSet": { "Rows": [
#     { "Data": [ { "VarCharValue": "city" }, { "VarCharValue": "orders" } ] },
#     { "Data": [ { "VarCharValue": "Dhaka" }, { "VarCharValue": "8412" } ] },
#     { "Data": [ { "VarCharValue": "Chattogram" }, { "VarCharValue": "1284" } ] } ] } }

# 7. Turn on result reuse for one run (up to 60 minutes old)
aws athena start-query-execution --work-group adhoc \
  --query-string "SELECT count(*) FROM shop.orders WHERE year='2026'" \
  --result-reuse-configuration 'ResultReuseByAgeConfiguration={Enabled=true,MaxAgeInMinutes=60}'

# 8. Clean up
aws athena delete-work-group --work-group adhoc --recursive-delete-option
aws s3 rb s3://shop-logs-example --force

Also try in the console: run step 5 against orders_raw and against orders, and compare Data scanned under the result. Then open the workgroup’s Workgroup data usage alerts and add an hourly alert with an SNS topic.

#Summary

Key takeaways

• Athena = serverless SQL directly on files in S3: no cluster, no loading, standard SQL on the Trino/Presto engine, tables described in the Glue Data Catalog (schema on read), results written to S3.
• Formats: CSV, JSON, ORC, Avro, Parquet (and logs, Iceberg). Common uses: VPC Flow Logs, ELB and CloudTrail logs, ad-hoc analysis, BI with QuickSight.
• Price: 5 USD per TB scanned (us-east-1), rounded to the MB, 10 MB minimum per query. DDL and failed queries are free; cancelled queries pay for what they scanned. Optional capacity reservations bill per unit-hour instead.
• Cheaper is faster: columnar formats (Parquet, ORC), compression (Snappy, zstd, gzip…), partitioning (year=2026/month=10/day=30/, filter on the partition columns, register or project partitions), bigger files (128–512 MB instead of millions of tiny ones). Exam answer: convert to Parquet/ORC and partition.
• Convert with a Glue ETL job or a CTAS statement (at most 100 partitions per CTAS or INSERT INTO); Firehose can write Parquet directly.
• Federated Query: one SQL across RDS, Aurora, DynamoDB, Redshift, DocumentDB, on-premises databases and more, through data source connectors on Lambda; results stored in S3; read-only.
• Workgroups: per-query data limit (cancels the query), workgroup-wide alerts (CloudWatch + SNS, do not cancel), separate results and encryption, tags, query result reuse, capacity reservations.
• Athena for ad-hoc and occasional queries on S3; Redshift for heavy, frequent BI on loaded data; Redshift Spectrum to query S3 from an existing Redshift cluster.

Self-check: Amazon Athena30 questions