Athena as the Lakehouse Query Engine: Cost, Performance, and Iceberg SQL

Lessons 01 through 08 gave you storage, formats, tables, pipelines and a catalog. None of that answers the question a business user actually asks: can I query this, how fast, and what will it cost? Amazon Athena is the answer AWS puts in front of SQL users: serverless, no cluster to size, no license to buy, and a bill that is a direct function of how much data your query reads. That last property is the whole lesson: in a per-scan pricing model, tuning is billing, and billing is tuning.

In this lesson you will:

• separate the three engine lanes, Athena SQL engine version 3, deprecated engine version 2, and the two Athena for Apache Spark release versions, and know which feature lives on which lane;
• price queries on $5.00 per TB scanned, reproduce AWS's own $15 → $5 →$1.25 compression story, and compute a monthly analyst workload on a 1 TB table;
• build workgroups that cut off runaway scans, pin the result output location, and isolate teams from each other's query history;
• choose between Hive-style partitioning and partition projection, then write a copy-ready projected CREATE EXTERNAL TABLE with storage.location.template;
• convert raw files with CTAS, and settle what RTAS does and does not mean in Athena SQL;
• write Iceberg SQL: time travel across the FOR SYSTEM_TIME AS OF → FOR TIMESTAMP AS OF clause change, the $snapshots metadata table, transactional MERGE INTO, OPTIMIZE and VACUUM;
• tune with the documented levers, file sizing, partition and column pruning, pushdown, join order, skew, and reproduce a ~60× before/after scan reduction on a synthetic table;
• use query result reuse and managed query results without believing they are a general cache;
• connect federated sources through JDBC and other connectors, and enforce Lake Formation permissions down to columns and rows;
• study two AWS customer cases (Easygo, Integral Ad Science) plus a sourced 2026 update box;
• practise with 11 exam-style questions and three interactive checks.

---

1. The three engine lanes: SQL v3, SQL v2 and Athena for Apache Spark

1.1 What Athena actually is in the stack

Athena is AWS's serverless interactive SQL service over data in Amazon S3 and over federated sources. In lakehouse terms it is the query layer: it reads tables registered in the AWS Glue Data Catalog, Hive-style external tables and Apache Iceberg tables, and it does not require you to provision or tune a cluster. DDL statements write metadata into the Glue Data Catalog; DML/DQL statements read (and, for Iceberg, write) data files in S3.

That split matters for cost: DDL is free and reads are metered, so the design of your table (format, columns, partitions) decides the size of every future invoice.

1.2 Engine version 3 is current; version 2 is deprecated

Athena engine version 3 is the current SQL engine, and it is Trino-based: the Athena FAQ states "Athena for SQL uses Trino with full standard SQL support" and "we aim to stay within 60-90 days of open-source Trino launches" (Athena FAQ, accessed 2026-10-11). The engine-version-3 reference page adds that AWS introduced "a continuous integration approach to open source software management that improves concurrency with the Trino and Presto projects".

AWS published a benchmark against its predecessor: on TPC-DS at 3 TB scale, engine version 3 showed ~20% query performance improvement over the latest engine version 2 release (AWS Big Data Blog, 2022-10-13).

Features that exist only on engine version 3 (this list is examinable):

Engine version 2 is Presto-based and deprecated. AWS documents that workgroups are set to Automatic (AWS upgrades them) or Manual engine version, and that "Athena upgrades your workgroups when a version is no longer offered" (Athena User Guide, engine versioning, accessed 2026-10-11). An AWS-staff answer on AWS re:Post (2024-02-23) states that version 2 is deprecated and that a workgroup can no longer be downgraded to it. There is no published end-of-support date for v2, and there is no engine version 4, version 3 is the latest as of October 2026.

• 📚 Did you know? The client drivers are versioned separately from the engine: JDBC 3.8.0 was released 2026-06-03 and ODBC 2.3.0.0 on 2026-08-21, while deprecated Athena data-catalog APIs (athena:RunQuery and friends) plus JDBC below 3.x and ODBC below 2.x stopped working on 2025-10-31. A connection error that appears "Athena-side" is often a driver-side retirement (Athena User Guide release notes, accessed 2026-10-11).

1.3 Athena for Apache Spark: two release versions, one immovable choice

Spark workloads run in a separate workgroup type. A Spark workgroup cannot use the Athena SQL query editor, and a SQL workgroup cannot use the Spark notebook editor; the analytics-engine type of a workgroup is immutable after creation (Athena User Guide: Spark considerations and limitations, accessed 2026-10-11).

The 2025-11-21 What's New announcement states that Athena for Apache Spark "now runs on Spark 3.5.6", is "optimized for open table formats including Apache Iceberg and Delta Lake", supports Spark Connect, and "now enforces table-level access controls defined in AWS Lake Formation". The serverless Spark engine runs on Firecracker micro-VMs and "launches and scales in seconds" (AWS Big Data Blog, 2026-07-02).

Two traps: you cannot upgrade a workgroup from PySpark engine version 3 to Apache Spark version 3.5 (create a new workgroup instead), and cross-engine view queries are not supported: a view created by Athena SQL is not queryable by Athena for Spark.

---

2. Pricing: $5.00 per TB scanned, and why format is a cost control

2.1 The meter

AWS's FAQ claims that compression, partitioning and columnar conversion cut per-query cost by 30% to 90%. That claim is not marketing abstraction: the pricing page walks through the arithmetic itself.

2.2 Worked example E1: AWS's own three-step compression story

The pricing page's canonical example is a table with 4 equally sized columns, stored as uncompressed text, totalling 3 TB on S3, where the query reads one column:

AWS states the "3x savings from compression and 4x savings for reading only one column", giving "$1.25". Read that as a design rule: the first lever (compression) and the second lever (columnar pruning) multiply, they do not add. A modern lakehouse variant, ZSTD at the Iceberg default, which Athena writes at level 3, compresses better still, but AWS publishes no official ratio for it, so treat any "10× with Zstd" figure as illustrative and yours to measure.

2.3 Worked example E2: 1 TB table, a month of analyst queries

Scenario (all figures as of Oct 2026): one fact table, 1 TB uncompressed, 10 equal-width columns, 365 daily partitions. The dashboard query reads one column and filters to one day. The team runs 30 queries per day × 22 workdays = 660 queries per month.

Row C is 0.333 TB ÷ 10 columns = 0.0333 TB scanned; row D divides that by 365 again because only one partition is opened: 0.0333 ÷ 365 = 91.3 MB, which is 0.0000871 TB × $5 = $0.000435 per query, and 660 × $0.000435 ≈ $0.29. Row E assumes the same query string repeats inside a 60-minute reuse window across 8 hours × 22 days = 176 distinct computations.

The anti-pattern to price too: the same Parquet table queried with SELECT * reads all ten columns, so row C's saving disappears: 0.333 TB → $1.67 per query → $1,100 per month, purely from a projection list.

The bar chart is the argument for treating format, projection list and partition predicate as billing decisions: the difference between row A and row D is a factor of about 11,000, and not one byte of it came from a faster engine.

• 📚 Did you know? The 10 MB minimum floors a single query at 10 MB x $5 / 1,048,576 MB = $0.0000477. That is why row D is never zero, and why a dashboard that fires 500 tiny queries a day still registers a bill, but it is also why DDL being free is worth real money: you can rebuild a table's format and partitions all afternoon for nothing and pay for it only on the first SELECT.

2.4 The other two meters

Capacity Reservations are the predictable-spend alternative: reserve DPUs and the attached workgroup stops incurring per-TB charges. One DPU is 4 vCPU and 16 GB of memory; the minimum reservation is 4 DPUs (increments of 4), with up to 100 reservations and 1,000 DPUs per account and Region. The pricing page's own worked example:

Since 2026-02-01 reservations are purchasable at 1-minute granularity with a 4-DPU minimum, which AWS says delivers "cost savings up to 95% for short-duration query workloads", and "you pay only for capacity that you reserve and there are no data scanned charges" (AWS What's New, 2026-02-01).

Federated queries bill per TB scanned "aggregated across data sources, rounded up to the nearest megabyte with a 10 megabyte minimum per query, unless Provisioned Capacity is used", and they "invoke AWS Lambda functions in your account, and you are charged for Lambda use at standard rates" (Athena pricing, accessed 2026-10-11). Federation therefore has two meters, not one.

---

3. Workgroups: the operational control plane

A workgroup is the unit of isolation and enforcement for Athena. It scopes query history, saved queries and result location, and it carries the settings that decide which engine version runs, how results are encrypted, and how much data a query is allowed to read before it dies.

3.1 Worked example E3: fencing analysts with a per-query cutoff

The console field is Data scanned limit, valid from 10 MB to 7 EB. The API field is BytesScannedCutoffPerQuery. An analyst runs a full-table scan of a 50 GB table with a 10 GiB cutoff:

AWS's own workgroup write-up pairs this with a per-workgroup CloudWatch alarm and a Lambda function that disables the workgroup when an aggregate threshold is breached (AWS Big Data Blog, 2019-07-18): the two controls are complementary: the per-query cutoff stops one bad statement, the per-workgroup alarm stops a runaway batch.

3.2 Output location, and isolating admins from analysts

OutputLocation is where result sets, manifest files and execution metadata land, and it is the object of the security question in Section 10: query results cannot be registered with Lake Formation, so the bucket prefix is governed by S3/IAM alone. Pair the location with workgroup membership so each population's SQL text and result sets are visible only to itself; this is exactly the pattern Integral Ad Science published: "By defining an Athena workgroup per business unit, IAS also takes advantage of assigning per-department billing tags and query limits to help with cost management" (AWS Big Data Blog, 2021-09-23).

---

4. Partitioning versus partition projection

4.1 The classic path: key=value folders and a registration job

Hive-style partitioning stores order_date=2026-10-10/ segments under the table location, and the catalog does not know they exist until you run MSCK REPAIR TABLE or ALTER TABLE ADD PARTITION. The mechanism works, and it brings two operational costs: a registration step in every ingestion pipeline, and a partition count ceiling: Glue supports tables with up to 10 million partitions, but Athena can scan at most 1 million partitions in a single query, and every extra partition key multiplies the number of files your writes produce.

4.2 The projection path: compute the paths, do not list them

Partition projection puts the partition logic in table properties instead of in the catalog. The Athena documentation is explicit: "During query execution, Athena uses this information to project the partition values instead of retrieving them from the AWS Glue Data Catalog or external Hive metastore… removes the need to manually create partitions." There is no repair job, no catalog sync, and no partition-registration step in the pipeline, and "queries for values that are beyond the range bounds defined for partition projection do not return an error… returns zero rows".

There are four projection types (Athena User Guide: supported types for partition projection, accessed 2026-10-11):

4.3 Worked example E4: a copy-ready projected table

Five teaching points fall out of that statement:

1. No MSCK REPAIR TABLE is ever needed: new days appear simply by being written to S3.
2. The predicate must match the configured format. With .format = 'yyyy/MM/dd', the value 2026/10/10 works; DATE '2026-10-10' and 2026-10-10 do not.
3. storage.location.template must contain one placeholder per partition column and end with /: otherwise Athena composes paths you did not write.
4. Out-of-range values return zero rows, silently. A "the data is missing" ticket is very often a .range that stops at NOW-1MONTH.
5. Always bound every projected key. Omitting device makes Athena LIST every device prefix for that day against the shared S3 budget of 5,500 GET requests per second per partitioned prefix.

• 📚 Did you know? Projection does not remove the scan ceiling; it removes the catalog cost. Athena still reads at most 1 million partitions per query, and Glue partition indexes exist for tables with tens of thousands of partitions and beyond. Projection is the answer to "my partition metadata is huge"; indexes and pruning are the answer to "my partitions are many but registered".

---

5. CTAS: turning raw files into a table you can afford to query

5.1 CREATE TABLE AS SELECT and its property traps

CTAS materializes a query result into a new table, which makes it the cheapest possible ETL job: no cluster, no separate step, and CTAS itself is DDL-shaped work that still counts as DML for service quotas. The documented properties:

Two hard limits to memorize: 100 partitions per CTAS statement (and per INSERT INTO), which fails as HIVE_TOO_MANY_OPEN_PARTITIONS, and the DML service-quota accounting mentioned above.

5.2 Worked example E5: raw CSV to Iceberg silver, in one statement

Note what is not in that statement: format is lowercase (uppercase FORMAT fails the query), partitioning uses an Iceberg transform rather than partitioned_by, and location is supplied because Iceberg CTAS does not accept external_location.

5.3 The RTAS question

REPLACE TABLE AS SELECT is documented in the Iceberg project's Spark documentation, and you will find it in Spark-based material. It does not appear in the Athena SQL user guide's CREATE TABLE AS SELECT / CTAS pages as of 2026-10-11, so in Athena SQL the replacement patterns are: CTAS into a new table then swap, INSERT OVERWRITE for Hive tables, or Spark (Glue, EMR, or Athena for Apache Spark) when you truly need an in-place replace. Do not assume RTAS exists in Athena SQL because it exists in Iceberg docs; that assumption fails at parse time in front of a user.

---

6. Iceberg SQL: time travel, snapshots, MERGE and maintenance

6.1 Reading a table as of a moment

Athena "can perform read, time travel, write, and DDL queries on Apache Iceberg tables", and exposes metadata tables $files, $manifests, $history, $partitions, $snapshots, $refs. The clause you use depends on the engine version, and this is the single most common migration bug in the lesson:

Timestamps written without a time zone are interpreted as UTC. The Athena documentation states the replacement directly: "The FOR SYSTEM_TIME AS OF and FOR SYSTEM_VERSION AS OF clauses in Athena engine version 2 have been replaced by the FOR TIMESTAMP AS OF and FOR VERSION AS OF clauses in Athena engine version 3."

6.2 MERGE INTO: the transactional upsert, merge-on-read only

MERGE INTO "is transactional and is supported only for Apache Iceberg tables in Athena engine version 3". You may use an arbitrary number of WHEN clauses; the first matching clause per source row executes, and Athena raises a user error if one target row matches multiple source rows.

The boundary to memorise: "Athena SQL does not currently support the copy-on-write approach. The UPDATE, MERGE INTO, and DELETE FROM operations always use the merge-on-read approach with positional deletes, regardless of specified table properties"; those table properties are ignored, and copy-on-write work belongs to Apache Spark on Amazon EMR or AWS Glue (Athena user guide; AWS Prescriptive Guidance, both accessed 2026-10-11).

6.3 Maintenance: OPTIMIZE and VACUUM

OPTIMIZE is "supported only for Apache Iceberg tables" and "only partition columns are allowed in the WHERE clause"; it is charged by the data scanned during the rewrite, so an already-compact table costs approximately nothing. VACUUM performs snapshot expiration and orphan file removal; the S3 API charges for those requests still apply. If you would rather not schedule either, Glue table optimizers can run compaction, snapshot retention and orphan-file cleanup automatically.

• 📚 Did you know? A pinned view is the cheapest way to expose "the state five minutes ago" without users writing time-travel SQL: AWS's own upsert blog pins a view with FOR TIMESTAMP AS OF current_timestamp + interval '-5' minute, so downstream consumers always read a stable previous state while MERGE INTO keeps writing new snapshots underneath them (AWS Big Data Blog, 2023-04-27).

---

7. Performance tuning: the nine levers

7.1 File sizing and layout

7.2 Pruning, pushdown, joins and skew

1. Partition pruning only happens if the query filters every partition key; projection makes it free but does not make it automatic.
2. Column pruning is the difference between row C and row A in E2: project the columns you need and never SELECT * in production SQL.
3. Predicate pushdown into Parquet row groups / ORC stripes uses min/max statistics; which is why sorted or clustered writes prune better than random ones.
4. Join order: put the larger table on the LEFT (probe) side of a distributed hash join, and prefer equality joins over LIKE or inequality predicates.
5. Skew: "If many rows have the same values for the columns used in the join condition, one node can end up having to process a large part of the join itself, while other nodes are idle." More workers never fix single-key skew: change or salt the key, or narrow the window's PARTITION BY.
6. Query shape: LIMIT with ORDER BY, UNION ALL over UNION, numbers over strings, regexp_like over an unanchored LIKE, approx_distinct over COUNT(DISTINCT), and UNLOAD for large result exports instead of a single CSV result write.
7. S3 Select is not on this list: AWS closed it to new customers on 2024-07-25, announced no new capabilities, and names Athena as the recommended approach for querying data in S3 (AWS Storage Blog, 2024-07-25). Older tuning articles that recommend it are stale.

7.3 Worked example E6: reproducing a ~60× before/after with pruning

This is the synthetic drill that makes the Easygo case study below concrete. A 400 GB Parquet table with 60 daily partitions (~6.67 GB per day) and 10 columns, queried for one day:

Stage 1 alone, the same query with a partition predicate added, is the 60× drill: identical engine, identical format, identical column list, sixty times fewer bytes. Stage 2 is the second, independent lever from Section 2.2, and it multiplies rather than adds.

Treat ~60× as the lab target you can reproduce on synthetic data, and keep the customer's own published number separate: Easygo reports 95% faster query performance, not a 60× speedup (the Easygo case study below). Never restate one as the other.

---

8. Result caching: reuse, managed results, and what is not cached

8.1 Query result reuse

Reuse is "a previous execution of the query within the same workgroup… if Athena finds a match, it bypasses execution and returns the query result from the previous, matching execution." Its rules (engine version 3 only):

Managed query results are the separate feature that removes your bucket entirely: "Query results are available for 24 hours. Query results are stored at no cost to you during this period", retrieved through workgroup-scoped GetQueryResults / GetQueryResultsStream. Managed results do not support result reuse; you pick one or the other.

8.2 Worked example E7: what reuse is worth

A dashboard issues 40 identical queries per hour against a table refreshed nightly; each run scans 0.2 GB.

The BI-layer pairing is the point: SPICE bills the refresh, reuse bills the identical re-run, and a direct query bills every view render. AWS says it plainly for Quick Suite / QuickSight: "If you use a data source that charges per query, you're charged for querying the data when you first create the dataset and later when you refresh the dataset."

• 📚 Did you know? Reuse is workgroup-scoped by design. Two teams issuing byte-identical SQL in different workgroups will not see each other's cached results; which is a governance feature, not a bug: it prevents one team's reused result from satisfying another team's different Lake Formation permissions.

---

9. Federated queries: JDBC and friends, through Lambda

Athena federated queries push filters and projections into the source system, so a join against a live OLTP database does not pull the whole table first. There are two connector families (Athena User Guide: run federated queries, accessed 2026-10-11):

Practical details:

• The generic AthenaJdbcConnector (2022.4.1) is deprecated: use the database-specific connectors for MySQL, PostgreSQL, Redshift, Oracle, SQL Server, plus DynamoDB, DocumentDB, OpenSearch, Timestream, BigQuery, Snowflake, CloudWatch and TPC-DS in the connector matrix.
• Multiplexer handlers route by catalog name, so one Lambda can serve several catalogs; credentials come from AWS Secrets Manager.
• Results larger than Lambda's response limit spill to a customer S3 bucket (spill-bucket parameters), and cross-account federation needs a resource-based Lambda policy + spill-bucket access + AssumeRole.
• Passthrough queries (table functions) push the whole statement to the source for supported connectors.
• Billing: per TB scanned across sources plus Lambda invocation charges: model both before promising a federation saving.

---

10. Lake Formation: what is enforced, and what is not

For data registered with Lake Formation, "Each time an Athena principal… runs a query on data registered using Lake Formation, Lake Formation verifies that the principal has the appropriate Lake Formation permissions… Lake Formation vends temporary credentials to Athena, and the query runs." Fine-grained access, column, row/cell data filters, LF-Tags, for Iceberg, Hudi, Delta and Hive requires Athena engine version 3.

The boundaries are the examinable part:

Also remember lakeformation:GetDataAccess is required on the principal path, and CSE-KMS S3 resources registered with Lake Formation cannot be queried in Athena.

---

2026 Updates (as of October 2026)

• 📚 Did you know? The re:Invent 2025 announcement of a serverless notebook with Athena for Apache Spark closes a long-standing gap: Spark notebooks without a cluster and without a separate notebook service, which is why "use a notebook" stopped being an argument against Athena.

---

Real-World Case Studies

AWS publishes what these mechanisms look like in production. Every figure below is customer-reported and unaudited, with the source named so you can check it: the examinable point is the pattern, not the marketing.

Case A, Easygo: a streaming-first lakehouse where Athena does the querying

"Changing our architecture to a more modern lakehouse on AWS helped us improve performance and set up a foundation we can reuse as we grow into other markets.": Carine Oliveira, Engineering Manager, Easygo (AWS case study, accessed 2026-10-11)

The architecture line AWS publishes is the lesson's own spine: "a streaming-first pattern using Amazon Kinesis to capture and process events in real time, Amazon Simple Storage Service(Amazon S3) as the central data lake, and Amazon Athena for faster querying on partitioned datasets." Partitioned datasets + Athena is Sections 4 and 7 in one sentence.

Read the numbers correctly. The published metric is 95% faster; which for the quoted 1-hour workload means ~10 minutes, a factor of about 6, not 60. The ~60× figure in Section 7.3 is a synthetic lab target (a 400 GB table scanned whole versus one day and two columns) that you can reproduce yourself to feel what pruning does. Restating Easygo's 95% as "60× faster" or "two orders of magnitude" misquotes a customer metric.

Case B, Integral Ad Science: one workgroup per business unit

The lesson angle: workgroups are the billing unit and Lake Formation is the data unit, and they must be aligned. IAS used tags for who may see what and workgroups for who may scan how much: two controls, two different questions, neither a substitute for the other.

• 📚 Did you know? Both cases are dated snapshots: IAS's blog is from 2021 and Easygo's case study is undated, so treat their numbers as the state of that migration, not a current specification: the same discipline lesson 07 applies to crawlers and lesson 08 applies to DPU rates.

---

Practice Questions