Day 2 - Amazon Athena: Serverless Queries and Cost Optimization

📌 Key Takeaways

• Athena is a serverless service that queries data on S3 directly with SQL, and it bills by the amount of data scanned. The less it reads, the cheaper and faster it is.
• There are three ways to reduce the scan volume: partitions (only the needed paths), columnar format + compression (only the needed columns, and smaller), and query writing (no SELECT *, filter on the partition key).
• CTAS / INSERT INTO write query results as Parquet or partitioned tables, standing in for light ETL. At most 100 partitions can be written at once.
• Federated queries use data source connectors to join sources outside S3, such as RDS, DynamoDB, and Redshift, with the same SQL. They are read-only.
• The workgroup is the unit of cost control. The per-query scan limit cancels queries that exceed it, while the workgroup-wide limit only sends an alert.

How Athena Works

Amazon Athena has no infrastructure to provision. When you submit a query, Athena runs a Trino-based engine (engine version 3) on your behalf.

-- Define an external table that points to an S3 path
CREATE EXTERNAL TABLE orders (
  order_id BIGINT,
  amount   DOUBLE,
  status   STRING
)
PARTITIONED BY (dt STRING)
STORED AS PARQUET
LOCATION 's3://datalake-clean/orders/';

• A table is nothing more than metadata in the Glue Data Catalog. No data is loaded (schema-on-read). Even if you run DROP TABLE, the files on S3 remain.
• Query results are saved as files in the S3 result location you specify. Storage charges accumulate on the result bucket too, so set a lifecycle expiration rule.
• Queries run within the account's concurrent query quota, and wait if they exceed it. DML queries have an execution time limit.

Billing Structure

• The charge is for the amount read, not the execution time. A query that took 10 minutes can be cheaper than one that took 1 second.
• If you need predictable cost and concurrency control regardless of scan volume, attach provisioned capacity to the workgroup.

💡 Related Theory: In Athena, cost optimization and performance optimization point the same way, because both come down to "reading less from S3." In a service such as Redshift where compute is reserved in advance, the bill stays the same no matter how frugal the queries are, but with Athena the bill shrinks in proportion to the queries you fix.

Three Layers for Reducing Scan Volume

Even for the same question, the amount read varies greatly with the shape of the data. Take a query that sums two columns over one day of orders as an example.

① Partitions

-- Load partition metadata (when the paths are Hive-style)
MSCK REPAIR TABLE orders;
 
-- Or add an individual partition
ALTER TABLE orders ADD PARTITION (dt='2026-06-26')
LOCATION 's3://datalake-clean/orders/dt=2026-06-26/';
 
-- Pruning works: only the dt prefix is scanned
SELECT status, COUNT(*) FROM orders
WHERE dt = '2026-06-26' GROUP BY status;

• MSCK REPAIR TABLE scans S3 and only adds partitions. It does not remove partitions that were deleted, and when there are many partitions it can take a long time and time out, ending with only some of them registered. For adding one partition every day, ALTER TABLE ADD PARTITION is the right tool.
• When there are tens of thousands of partitions, the catalog lookup becomes the bottleneck. Partition projection computes partitions from a rule without registering them.

ALTER TABLE orders SET TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.dt.type' = 'date',
  'projection.dt.range' = '2024-01-01,NOW',
  'projection.dt.format' = 'yyyy-MM-dd',
  'storage.location.template' = 's3://datalake-clean/orders/dt=${dt}/'
);

• Athena handles up to 10 million partitions in a Glue table, but a single scan can read at most 1 million partitions. This is why a query with no partition condition fails on a very large table.

② Format and Compression

• Parquet/ORC: Reads only the needed columns and skips ranges using the statistics inside the file. If the files are too small, the format overhead cancels out the benefit, so aim for 128 MB or larger.
• Compression: Scan volume is counted at the compressed size, so compression is a discount in itself. gzip has a good compression ratio and broad compatibility, and zstd balances speed and compression ratio well.
• When you compress text (CSV, JSON), most codecs force the file to be read from the beginning, so a single file cannot be split and read in parallel. In that case the number of files is the upper bound on parallelism. Parquet/ORC are divided into internal units, so they are read in parallel even when compressed.

③ Query Writing

-- SELECT only the needed columns → reduces scan volume in a columnar format
SELECT order_id, amount FROM orders WHERE dt = '2026-06-26';
-- SELECT * scans every column, so avoid it

• LIMIT does not guarantee a smaller scan. When a sort or aggregation is involved, it reads everything and then trims.
• Equality conditions work best on partition keys. When every partition key has an equality condition, the metadata is fetched directly.
• Before running, use EXPLAIN to see which partitions are targeted, and after running, check the amount actually read with EXPLAIN ANALYZE or the "data scanned" figure in the query statistics.

💡 Related Theory: Converting CSV to Parquet + compression applies "column projection (only the needed columns)," "compression (values of the same type sit together and shrink well)," and "statistics-based skipping" all at once. That is why format conversion is the first card played on an Athena cost problem. Partitions come next, and query fixes are layered on top.

CTAS and INSERT INTO

CTAS (CREATE TABLE AS SELECT) writes query results to a new S3 location and registers it as a table.

-- Convert the CSV source into partitioned Parquet
CREATE TABLE orders_parquet
WITH (
  format = 'PARQUET',
  write_compression = 'SNAPPY',
  partitioned_by = ARRAY['dt'],
  external_location = 's3://datalake-clean/orders_parquet/'
) AS
SELECT order_id, amount, status, dt   -- the partition column goes at the very end of the SELECT list
FROM orders_raw_csv
WHERE dt BETWEEN '2026-06-01' AND '2026-06-30';

• For a conversion with more than 100 partitions, create the first range with CTAS and append the rest range by range with INSERT INTO ... SELECT.
• If a CTAS or INSERT INTO fails or is canceled, partially written files may be left behind at the target location. The next query reads those files too, so they must be cleaned up. If you need atomic writes, use an Iceberg table.
• CTAS replaces a Glue job when "the transformation logic can be handled in SQL and it is a one-off or periodic batch." If you need complex transformations, a continuous large-volume pipeline, or fine-grained retries, use Glue or EMR.

Federated Query

Through a data source connector, you query and join sources outside S3 (RDS and Aurora, DynamoDB, Redshift, CloudWatch Logs, OpenSearch, other JDBC sources, and so on) with the same SQL.

-- Join order data on S3 with the customer master in RDS
SELECT o.order_id, c.customer_name, o.amount
FROM awsdatacatalog.sales.orders o
JOIN rds_postgres.public.customers c
  ON o.customer_id = c.customer_id
WHERE o.dt = '2026-06-26';

• Athena calls the connector, decides the range to read, manages parallelism, and pushes the filter conditions down to the source (predicate pushdown).
• Connectors have traditionally been deployed as Lambda functions in the user's account. As a result, Lambda invocation charges apply separately, and execution is bound by Lambda limits. More recently, connectors that work through a Glue connection without Lambda are also offered for some sources, but exam materials explain it on the basis of Lambda.
• It is read-only. INSERT INTO is not supported on a federated source. However, reading through federation and writing to S3 with CTAS does work (a source → lake extraction pipeline).
• Because real queries go out to the source being joined, there is load on the production database. If you repeat large joins, it is better to point at a read replica or move the data into the lake.

Workgroups and Cost Control

A workgroup is a unit for dividing query execution by team or purpose. The result location, encryption, engine version, and cost controls are attached to the workgroup.

• Only one per-query limit can be set on a workgroup, whereas the workgroup-wide alert can be set with several thresholds.
• To actually block at the workgroup-wide limit, you build it yourself, for example by connecting a Lambda to the SNS notification to disable the workgroup.
• If you let the workgroup settings override client-side settings, users cannot change the result location or encryption. This is how you enforce result encryption.
• Divide IAM permissions by workgroup, and view usage per team with CloudWatch metrics and cost allocation tags.

# Create a workgroup with a per-query scan limit of 10 GB
aws athena create-work-group \
  --name analysts \
  --configuration '{
    "ResultConfiguration": {"OutputLocation": "s3://acme-athena-results/analysts/"},
    "EnforceWorkGroupConfiguration": true,
    "PublishCloudWatchMetricsEnabled": true,
    "BytesScannedCutoffPerQuery": 10737418240
  }'

Query Result Reuse

When the same query is repeated, you can let it reuse the previous result. When a result is reused, no scan takes place.

• It applies within the same workgroup, when the query string matches and the previous result has not expired.
• If you do not specify a maximum age, it is 60 minutes, and the maximum you can specify is 7 days.
• It does not check whether the source data has changed. Within the age limit, stale results may be returned.
• It does not apply to tables in locations registered with Lake Formation or to tables with row or column permissions applied.

Symptom → Cause → Action

When to Choose Athena and When Not To

Question Clue → Answer Lookup Table

Tomorrow we cover DynamoDB, an operational store rather than an analytics engine, from the viewpoint of key design and change capture that data engineers need to know.

📖 Glossary

• Serverless: A model in which you do not launch or manage servers in advance, and resources are used only as requested.
• Scan volume: The number of bytes a query actually read from S3. It is Athena's billing basis.
• External table: A table whose data stays on S3 and only its location and schema are registered in the catalog.
• Column projection: Reading only the columns a query needs. It is effective only with columnar formats.
• CTAS: CREATE TABLE AS SELECT. Writes query results to a new location and registers it as a table.
• Federated query: A feature that queries sources outside S3 with Athena SQL through data source connectors.
• Predicate pushdown: An optimization that sends the WHERE condition down to the source so that the source filters before returning results.
• Workgroup: A unit for dividing query execution. The result location, encryption, scan limits, and metrics are attached to it.
• Query result reuse: A feature that returns the previous result of the same query again for a set period of time.
• Provisioned capacity: An Athena pricing model that bills for the usage time of reserved processing capacity (DPUs) instead of scan volume.