Cloud / AWS Athena Interview questions

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 or clusters to provision, and you never load the data anywhere first.

You define a table that points at an S3 prefix, describe the schema, and start querying. Under the hood, the SQL engine is based on Trino (earlier engine versions used Presto), and table metadata normally lives in the AWS Glue Data Catalog.

It is a good fit for ad hoc analysis of logs, clickstream data, and data lake files where you want answers quickly without managing infrastructure.

The features interviewers usually look for:

• Serverless - nothing to provision, patch, or scale.
• Standard SQL - ANSI SQL with Trino/Presto functions for arrays, maps, JSON, and window operations.
• Pay per query - billed on the amount of data scanned.
• Multiple formats - CSV, JSON, Parquet, ORC, Avro, and more.
• Glue integration - shared catalog with Glue, EMR, and Redshift Spectrum.
• Federated queries - reach DynamoDB, RDS, and other sources through Lambda connectors.
• Table formats - Apache Iceberg with ACID operations, plus read support for Hudi and Delta Lake.

It also has JDBC/ODBC drivers and an API, so BI tools such as QuickSight can sit on top of it.

Athena charges per terabyte of data scanned by your queries (the standard rate is about $5 per TB, though you should check the current price for your region). Each query is rounded up to a 10 MB minimum.

DDL statements such as CREATE TABLE and ALTER TABLE are free, and so are failed queries. Cancelled queries are charged for the data scanned up to the point of cancellation.

You also pay separately for S3 storage, S3 requests, and any Glue crawler or catalog costs. For steady heavy workloads, capacity reservations let you pay per DPU-hour instead of per TB scanned.

Athena reads both text-based and columnar formats. Each one is handled by a SerDe (serializer/deserializer) that you name in the table DDL, or implicitly through STORED AS.

Athena also reads compressed files (Snappy, GZIP, ZSTD, BZIP2, LZO) and detects the codec from the file or table properties. Columnar formats are the usual choice for production workloads.

One practical tip: if your files are CSV or JSON, convert them to Parquet early. The format you choose affects cost and speed more than almost any other setting.

The AWS Glue Data Catalog is the default metastore for Athena. It stores databases, table definitions, column types, partition lists, and table properties. The data itself stays in S3.

Because the catalog is shared, a table created through Athena DDL is immediately visible to Glue ETL jobs, EMR, and Redshift Spectrum. Glue crawlers can populate it automatically by inferring schemas from S3.

Athena also supports an external Hive metastore through a Lambda connector, but most teams stay with Glue.

Athena also respects Glue table properties like classification and partition keys. If a table looks empty, first confirm that the database and table exist in the Glue console for the same region.

Use CREATE EXTERNAL TABLE with the column list, a format, and the S3 location. Athena only registers metadata, so creating the table is instant and free.

CREATE EXTERNAL TABLE sales (
  order_id string,
  amount double,
  order_date date
)
PARTITIONED BY (region string)
STORED AS PARQUET
LOCATION 's3://my-bucket/sales/';

Alternatives are a Glue crawler, the console's table wizard, or CTAS to create a table from a query result. Remember to load partitions afterward if the table is partitioned.

If the S3 path holds files with different schemas, queries return NULLs or errors. Keep one schema per location, and use a header-skip property such as skip.header.line.count='1' for CSV files with headers.

A SerDe (serializer/deserializer) is the library that tells Athena how to turn bytes in a file into columns and rows. You pick one per table.

Parquet and ORC have built-in SerDes that you get through STORED AS. Picking the wrong SerDe is a common reason for shifted columns or null values.

You can inspect the SerDe on an existing table with SHOW CREATE TABLE. For JSON, the OpenX SerDe can ignore malformed records with ignore.malformed.json, which is useful for messy logs.

Partitioning splits a table into slices based on column values, usually reflected as S3 prefixes such as s3://bucket/logs/year=2026/month=10/. When a query filters on a partition column, Athena reads only the matching prefixes.

Since billing follows scanned bytes, partition pruning directly lowers both cost and runtime. Common partition keys are date, region, and tenant.

Avoid over-partitioning. Thousands of tiny partitions add catalog overhead and create many small files.

A typical query then looks like WHERE year='2026' AND month='10'. If you forget the partition filter, Athena scans the whole table, so it pays to train analysts to include it or use workgroup limits as a safety net.

There are four common ways, from manual to fully automatic:

1. ALTER TABLE ... ADD PARTITION to register specific partitions.
2. MSCK REPAIR TABLE to scan S3 and add all Hive-style partitions it finds.
3. A Glue crawler on a schedule.
4. Partition projection, which skips the catalog and computes partitions from table properties.

ALTER TABLE sales ADD IF NOT EXISTS
  PARTITION (region='eu') LOCATION 's3://my-bucket/sales/region=eu/';

Projection is usually the best choice for predictable layouts like dates.

After adding partitions, verify with SHOW PARTITIONS sales. If new data arrives daily, automate registration with a Lambda that runs ADD PARTITION on S3 events, or switch to projection.

A workgroup isolates queries, settings, and cost for a team or application. It is the main governance tool in Athena.

• Own query result location and encryption settings, optionally enforced.
• Data usage limits per query or per time window, with alarms.
• Separate IAM permissions and cost allocation tags.
• A pinned engine version and CloudWatch metrics.

A default workgroup called primary exists in every account. Enforcing workgroup settings overrides whatever the client sends.

Typical setup: one workgroup per team or per environment, such as analytics, etl, and adhoc. Each gets its own limits, so a runaway experiment cannot burn the whole budget.

Every query writes its output to an S3 location you configure, either per query, per client, or per workgroup. Results land as a CSV file plus a small .metadata file named after the query execution ID.

You need write access to that bucket, and the output can be encrypted with SSE-S3, SSE-KMS, or CSE-KMS. Since these files pile up, add an S3 lifecycle rule to expire them.

Anyone who can read the results bucket can read query output, so lock it down like source data.

In the console, the default location is set in settings, and a workgroup can override it. A missing or unwritable results bucket is a classic cause of the error "No output location provided".

CTAS (CREATE TABLE AS SELECT) creates a new table from a query result and writes the data to S3 in the format you choose. It is the quickest way to convert CSV to Parquet or to compact small files.

CREATE TABLE sales_parquet
WITH (
  format = 'PARQUET',
  write_compression = 'SNAPPY',
  external_location = 's3://my-bucket/sales_parquet/',
  partitioned_by = ARRAY['region']
) AS
SELECT order_id, amount, order_date, region FROM sales_csv;

Partition columns must come last in the SELECT list.

CTAS also supports bucketed_by and compression settings. If the target external_location is not empty, the query fails, so use a fresh prefix each run.

Parquet and ORC are the best choices. Both are columnar, compressed, and carry min/max statistics, so Athena can read only the columns it needs and skip whole row groups.

Parquet is the more common pick because it works across Spark, Glue, and Redshift Spectrum. Snappy is a good default compression, and ZSTD gives smaller files at slightly higher CPU cost.

Row formats like CSV and JSON force Athena to read every byte of every row, which makes queries slower and more expensive.

A good rule is files between 128 MB and 1 GB, partitioned by the main filter. Row groups around 128 MB give Parquet readers a good balance between parallelism and metadata overhead.

A view is a saved SELECT statement stored in the Glue Data Catalog. It holds no data. Each time you query the view, Athena runs the underlying query.

CREATE OR REPLACE VIEW recent_orders AS
SELECT * FROM sales WHERE order_date > current_date - INTERVAL '30' DAY;

Views are handy for hiding joins, exposing a safe subset of columns, or giving analysts a stable name. They do not speed anything up, and scanned bytes are billed against the base tables.

Views can be nested, but deep chains make debugging harder. If a base table changes shape, the view may break, so test it after schema changes. Federated or Iceberg views have their own caveats, so check current documentation.

Use the Athena API through any AWS SDK. The flow is asynchronous: start a query, poll its status, then fetch results.

qid = athena.start_query_execution(
    QueryString='SELECT count(*) FROM sales',
    WorkGroup='analytics',
    ResultConfiguration={'OutputLocation': 's3://my-results/'})['QueryExecutionId']
athena.get_query_execution(QueryExecutionId=qid)
athena.get_query_results(QueryExecutionId=qid)

JDBC and ODBC drivers cover BI tools, and the awswrangler library offers a pandas-friendly wrapper.

Poll get_query_execution until the state is SUCCEEDED, FAILED, or CANCELLED. For big results, paginate get_query_results, or simply read the CSV straight from the results bucket. You can also pass ExecutionParameters for parameterised SQL.

"$path" is a hidden pseudo column that returns the full S3 URI of the file each row came from. It is not part of your schema, so you must quote it.

SELECT "$path", count(*) FROM sales GROUP BY "$path";

It is useful for debugging bad rows, finding which file holds a bad record, and spotting many tiny files. Athena also exposes "$file_size" and "$file_modified_time".

A common trick is SELECT "$path", count(*) ... GROUP BY 1 ORDER BY 2 DESC to find which files hold most rows, or to confirm that partition pruning actually limited the files read.

Federated Query lets Athena run SQL against data that is not in S3, such as DynamoDB, RDS, Redshift, DocumentDB, or CloudWatch Logs. Each source is reached through a data source connector that runs as an AWS Lambda function.

You register the connector as a data source in Athena, then reference it in SQL like any catalog, and even join it with S3 tables in one statement.

SELECT o.order_id, c.name
FROM s3_lake.orders o
JOIN "lambda:rds_connector".public.customers c ON o.cust_id = c.id;

It is great for occasional joins and exploration. For heavy recurring workloads, copy the data into S3 instead.

Performance tip: federated joins pull data through Lambda, so filter on the federated side and keep the result small. Also give the connector's Lambda enough memory and set its spill bucket in the connector configuration.

Athena shines when you want SQL over files without standing up infrastructure.

• Querying CloudTrail, ALB, VPC Flow, and S3 access logs for security and troubleshooting.
• Ad hoc analysis on a data lake in S3.
• Backend for QuickSight dashboards.
• Lightweight ETL using CTAS and INSERT INTO.
• Exploring data before committing to a warehouse load.

It is not meant for low-latency, high-concurrency application queries. A database or warehouse fits those better.

Teams also use Athena as a quick validation layer for data pipelines, running row-count checks or schema checks on files before they are loaded elsewhere.

Security is layered, because Athena touches IAM, S3, Glue, and optionally KMS.

1. IAM policies control who can run queries and which workgroups, catalogs, and databases they can use.
2. S3 bucket policies protect both the source data and the results bucket.
3. Lake Formation adds table, column, and row-level permissions.
4. Encryption with SSE-S3, SSE-KMS, or CSE-KMS, plus TLS in transit.
5. VPC endpoints keep traffic off the public internet, and CloudTrail records API activity.

A least-privilege pattern: analysts get athena:StartQueryExecution on one workgroup, read access to specific S3 prefixes, and glue:GetTable on chosen databases, nothing broader. Lake Formation then trims what they see inside tables.

Athena for Apache Spark lets you run interactive Spark code (PySpark) in notebooks without managing a cluster. You create a Spark-enabled workgroup, start a session, and run cells.

It suits tasks that are awkward in SQL, such as complex Python transformations, ML feature prep, or using pandas and visualisation libraries on lake data. Billing is based on DPU-hours used by the session.

SQL-only users can stay on regular Athena SQL. Spark is for when you need code.

Sessions have idle timeouts and DPU limits, so close notebooks when finished. Spark workgroups cannot be mixed with SQL workgroup settings, which keeps governance clear.

Parquet stores data by column, not by row. If your query touches 3 of 40 columns, Athena reads just those 3 column chunks, and billing drops with it.

Parquet files also hold min/max statistics per row group, so a filter like WHERE amount > 1000 can skip groups that cannot match. The data is compressed and carries its own schema and types, so there is no text parsing.

Parquet also supports dictionary and run-length encoding, so repeated values such as country codes compress very well. In practice, a 10 GB CSV table often becomes 1-2 GB of Parquet, and queries touching a few columns scan a small fraction of that.

With partition projection, Athena calculates partition values and S3 paths from table properties instead of fetching them from the Glue catalog. No MSCK REPAIR or crawler is needed.

TBLPROPERTIES (
  'projection.enabled'='true',
  'projection.dt.type'='date',
  'projection.dt.range'='2025/01/01,NOW',
  'projection.dt.format'='yyyy/MM/dd',
  'storage.location.template'='s3://bucket/logs/${dt}/')

Supported types are enum, integer, date, and injected. When a query filters on dt, Athena generates only the matching paths. It is faster on tables with very many partitions and new data is visible immediately.

Projection works best when the path template matches the actual layout exactly. If the pattern is wrong, queries return zero rows with no error. Check the template first when results look empty, and always include a filter on the projected column.

Choose projection when your partition layout is predictable: dates, hours, numeric ranges, or a fixed list of values. Typical examples are ALB, CloudFront, and CloudTrail logs.

Remember that projection is an Athena feature only. Other engines like Spark or Redshift Spectrum will not see projected partitions in the catalog.

One more factor is freshness. A crawler runs on a schedule, so new partitions are invisible until it finishes, while projection makes tomorrow's folder queryable the moment data lands. For a partitioned CloudFront log table, that difference matters.

Athena is a serverless query engine over S3. Redshift is a managed data warehouse with its own columnar storage and compute nodes (or serverless RPUs).

Pick Athena for sporadic, exploratory queries; pick Redshift for consistent dashboards and heavy joins.

Many teams use both: raw and rarely queried data stays in S3 and Athena, while curated, frequently used marts live in Redshift. Redshift also supports federated queries and Spectrum, so the two blend into one architecture.

Both query S3 data with SQL and can share the Glue Data Catalog. The difference is where the compute runs.

Use Spectrum when you already run Redshift and want to join warehouse tables with lake data. Use Athena for standalone lake queries.

Spectrum pushes S3 scans to a shared layer outside your cluster, so large scans do not use cluster CPU. Both engines read the same Glue tables, which makes it easy to try each on the same data and compare cost and speed.

Cost follows bytes scanned, so every trick aims to scan less.

1. Convert to Parquet or ORC with compression.
2. Partition by common filters and always filter on those columns.
3. SELECT only needed columns, never SELECT *.
4. Compact small files to 128 MB or larger.
5. Use workgroup data limits to cap runaway queries.
6. Enable query result reuse for repeated queries.
7. Consider capacity reservations for steady heavy use.

Always verify improvements by comparing the data-scanned figure in the query stats.

Example: a query scanning 2 TB of CSV costs about $10. Converting to Parquet and filtering on a date partition might scan 20 GB, which is roughly $0.10. Measure with the data-scanned figure shown under every query result.

Each S3 object needs its own GET request and file open. With thousands of tiny files, request overhead and listing time dominate, while actual data reading is trivial.

Parquet makes it worse, because every file carries its own footer and metadata that must be read. Work also cannot be split efficiently, so parallelism is wasted on tiny tasks. Heavy request rates can even trigger S3 throttling.

The usual target is files of 128 MB or more. Fix it by compacting with CTAS or INSERT INTO, a Glue or Spark job, or Kinesis Firehose buffering, then check the result with "$path" counts.

A quick diagnostic: count files with SELECT count(DISTINCT "$path") and compare to total data size. If the average file is under a few MB, compaction is likely your biggest win.

When you submit SQL, Athena goes through these stages:

flowchart LR
  A["SQL submitted"] --> B["Parse and plan"]
  B --> C["Fetch table and partition metadata from Glue"]
  C --> D["Prune partitions"]
  D --> E["Distributed workers read S3 splits"]
  E --> F["Join, aggregate, sort"]
  F --> G["Write results to S3 as CSV"]

The coordinator builds a distributed plan. Workers in the Trino-based engine read S3 splits in parallel and exchange data in memory. Results go to your results bucket and the console reads them from there.

Query statistics show queue time, planning time, and engine execution time, which help locate the slow stage.

Failures can occur at any stage: planning problems appear as syntax or catalog errors, while runtime problems show up as resource or S3 errors. Knowing the stage tells you where to look first, whether in the Glue catalog, IAM, or the data itself.

Dropping a standard Athena table (Hive-style external table) removes only its metadata from the Glue Data Catalog. The files in S3 stay untouched.

DROP TABLE sales;  -- S3 files remain

That is why the table is called external. If you also want the data gone, delete the S3 prefix yourself. Be careful with Iceberg tables, which manage their own files, so check how the engine version handles data removal before dropping them in production.

This design is why you can safely recreate a table with a different schema over the same data. It is also a trap: dropping the table does not clean up storage costs, and old result or temp files can linger in S3.

This error appears when a partition's schema (column types or order) differs from the table schema in the catalog. It usually happens after a table was altered or when different files were written with different types.

1. Compare the table and partition columns with SHOW CREATE TABLE and DESCRIBE.
2. Identify the odd partition with "$path".
3. Recreate the table or drop and re-add the affected partitions.
4. For Parquet or ORC, consider reading by column name and standardise the writer's types.

Preventing it means keeping schema changes additive and writing consistent types upstream.

If many partitions are affected, scripting the repair is easier than manual edits. Pull the partition list with SHOW PARTITIONS, drop the bad ones in bulk, and re-add them once the table definition is final.

MSCK REPAIR TABLE lists the entire S3 prefix to discover Hive-style partitions, then adds them in batches. With many partitions or deep prefixes, this becomes slow and can time out.

Alternatives:

• ALTER TABLE ADD PARTITION for just the new partitions, often triggered by an event.
• Partition projection, which removes the need to register partitions.
• Glue crawlers on a schedule for irregular layouts.

It also only finds paths in key=value format. Other layouts need manual registration.

For very large tables, running MSCK REPAIR repeatedly after each load is wasteful. If you must use it, limit the S3 prefix to recent data, or move to projection when the layout follows a clear pattern.

Hive-style tables are folders of files described by the catalog. Iceberg tables add a metadata layer (snapshots and manifests) that tracks exactly which files make up the table.

CREATE TABLE orders_ice (id bigint, amt double, dt date)
PARTITIONED BY (dt)
LOCATION 's3://bucket/orders_ice/'
TBLPROPERTIES ('table_type'='ICEBERG');

Iceberg also tracks partition spec history, so you can change the partitioning scheme later without rewriting old data. Run OPTIMIZE ... REWRITE DATA USING BIN_PACK regularly to compact files and VACUUM to expire snapshots.

Every write to an Iceberg table creates a new snapshot. Athena can read an older snapshot by timestamp or by version ID.

SELECT * FROM orders_ice FOR TIMESTAMP AS OF (current_timestamp - interval '1' day);
SELECT * FROM orders_ice FOR VERSION AS OF 949530903748831860;

It helps with auditing, reproducing reports, and recovering from bad updates. You can find snapshot IDs in the "orders_ice$history" metadata table.

Snapshots are removed by VACUUM, so you can only travel back as far as your retained history.

Combine time travel with MERGE INTO carefully: if a bad merge runs, you can read the earlier snapshot and rewrite the table from it. Keep retention long enough for your audit needs, because VACUUM permanently removes old files.

Partition on columns with low cardinality that you filter on often (date, region). Use bucketing for high-cardinality columns such as user_id or order_id, where partitioning would create millions of tiny folders.

Bucketing hashes the column into a fixed number of files. Athena can then skip files for equality filters and reduce shuffle on joins between tables bucketed the same way.

CREATE TABLE orders_b
WITH (format='PARQUET', bucketed_by=ARRAY['user_id'], bucket_count=16) AS
SELECT * FROM orders;

The two combine well: partition by date, bucket by user_id.

Pick the bucket count with care: too few makes huge files, too many creates tiny ones. Powers of two like 16, 32, or 64 are typical, and the count cannot be changed without rewriting the data.

A single CTAS or INSERT INTO statement can write to at most 100 partitions. Going beyond that fails with an error about too many open writers.

Workarounds:

1. Run CTAS for a subset (for example one month), then use INSERT INTO in several batches, each under 100 partitions.
2. Use bucketing inside CTAS to spread data without extra partitions.
3. Loop over date ranges from a script or Step Functions.

INSERT INTO events_p
SELECT * FROM events_raw WHERE dt BETWEEN '2026-01-01' AND '2026-03-31';

Batching by a partition-friendly range keeps each statement within the limit.

Check the error text if a CTAS fails midway. Partial output may remain in the S3 target, so clean the prefix before retrying, otherwise the next attempt fails because the location is not empty.

If you define nested fields as struct, array, or map types, use dot notation. If a field stays a raw JSON string, use JSON functions.

-- struct and array columns
SELECT user.name, item
FROM events CROSS JOIN UNNEST(items) AS t(item);

-- raw JSON string
SELECT json_extract_scalar(payload, '$.user.id') FROM raw_events;

UNNEST turns an array into rows. json_extract returns JSON, while json_extract_scalar returns a plain string. Typed columns are faster and cleaner, so prefer them once the schema is stable.

Case matters in JSON keys under some SerDes, so mismatched names can give NULLs. Test on a few rows first, and consider converting nested JSON into Parquet with typed columns once your queries stabilise.

By default Athena matches Parquet columns by name, so adding new columns at the end or in any position works: old files return NULL for the new column.

Renaming or dropping columns is riskier, because name-based mapping then fails to find the data. You can switch to position-based reading with parquet.column.index.access=true in table properties, but that breaks if column order changes.

Iceberg handles all of these properly through column IDs.

A practical habit is to agree on a schema contract with data producers. When Spark writes Parquet with inconsistent types across days, Athena can raise type errors for just some partitions, which are painful to track down after the fact.

Workgroups let you attach budgets and limits to a team or application.

• Per-query data limit cancels any query that scans more than the threshold.
• Workgroup data limit over a period (hourly or daily) can trigger SNS alerts.
• Cost allocation tags show spend per team in Cost Explorer.
• Enforced settings force everyone through the same result bucket and encryption.

A common pattern is one workgroup per team, with a low per-query limit for analysts and a higher one for ETL.

Limits act on data scanned, not on runtime. So a slow query that scans little is allowed, while a quick query that scans a huge table is cancelled. Set the limit to a sensible multiple of your largest legitimate scan.

When Lake Formation manages a database, Athena checks Lake Formation permissions in addition to IAM when someone queries a table. That makes it possible to grant access per database, table, column, or row without editing S3 bucket policies.

Lake Formation vends temporary credentials to Athena for the allowed data only. Column-level and row-level filters (data filters) are applied automatically, so analysts see a filtered view of the same table.

Remember that the S3 location must be registered in Lake Formation, and the user still needs IAM permission to call Athena.

In practice, this gives you a single place to manage permissions across Athena, Glue, EMR, and Redshift Spectrum. Tag-based access control (LF-tags) scales better than granting table by table once you have hundreds of tables.

Set encryption in the workgroup or query result configuration. Three options exist:

• SSE-S3 - S3-managed keys.
• SSE-KMS - your KMS key, with key policy control and audit logs.
• CSE-KMS - client-side encryption using KMS before upload.

Enforce the setting at workgroup level so users cannot override it. The caller also needs kms:GenerateDataKey and kms:Decrypt on the key. For source data, Athena reads SSE-S3, SSE-KMS, and CSE-KMS objects given suitable permissions.

Encryption of source data in S3 is separate from result encryption, so check both. A frequent error is "Access Denied" on the results bucket when the KMS key policy does not include the Athena caller or role.

Query result reuse lets Athena return the saved result of an identical earlier query instead of running it again, as long as the previous result is within the maximum age you set (up to 7 days).

A reused result scans zero data, so you are not billed for scanning, and it returns faster. It helps dashboards that fire the same query repeatedly.

Reuse only applies when the SQL is identical and the earlier query succeeded in the same workgroup. Because it can return stale data, pick the age to match how fresh the data must be.

Reuse is not available for every statement type, and queries on tables that have since changed may behave differently, so consider disabling it for pipelines that must always see fresh data. Dashboards reading hourly data can safely accept a 30-60 minute age.

Athena sends the request to a connector Lambda in stages. The connector tells Athena how to split the source, then reads each split.

sequenceDiagram
  participant U as User
  participant A as Athena
  participant L as Connector Lambda
  participant S as Data source
  U->>A: SQL query
  A->>L: GetTableLayout / GetSplits
  L-->>A: Splits
  A->>L: ReadRecords per split
  L->>S: Fetch rows
  S-->>L: Rows
  L-->>A: Arrow data (spill to S3 if large)
  A-->>U: Result

Large responses spill to an S3 spill bucket. Predicates can be pushed down to the source, so filter early. Lambda limits and concurrency affect speed, which is why federation suits light workloads.

To debug, check the connector Lambda's CloudWatch logs and the spill bucket permissions. Typical failures include Lambda timeouts, missing VPC access to the source, and a connector version that does not match the data source.

Athena broadcasts or hash-partitions data between workers, so join order and size matter.

1. Put the largest table on the left and the smaller on the right, since the right side is loaded into memory for hash joins.
2. Filter and project columns before joining, using subqueries or CTEs.
3. Join on partition or bucket columns where possible.
4. Prefer approx_distinct over COUNT(DISTINCT) when exactness is not needed.
5. Keep table statistics current if your engine version uses them (Iceberg, Glue column stats).

Check the plan with EXPLAIN ANALYZE and look for large exchanges or skewed stages.

Another useful trick is replacing a join with a filtered semi-join using IN (SELECT ...) when you only need rows that exist on the other side. It often reduces the amount of data moved between workers.

This error means the query needed more memory than the engine could give a worker, typically from huge sorts, wide joins, or COUNT(DISTINCT) on a big column.

1. Remove or limit ORDER BY on big results; add LIMIT.
2. Replace COUNT(DISTINCT) with approx_distinct.
3. Put the smaller table on the right side of the join.
4. Select fewer columns and filter earlier.
5. Split the work: run per partition and combine with INSERT INTO.
6. Use capacity reservations if the workload is regular and heavy.

Also check for skew, where one key holds most rows.

If the same query works on one day of data but fails on a year, the data volume is the issue, so process in slices. Check the query plan for stages with huge row counts, which often point to an accidental cross join or a skewed key.

Engine version 2 is based on an older Presto release. Engine version 3 is built on Trino, with newer SQL functions, better performance, and fixes. New workgroups use v3 by default.

You can set the engine version per workgroup, so test critical queries in a separate workgroup before upgrading. Some behaviour changes, such as type coercion or error messages, can affect old queries.

When upgrading, read the engine release notes and run your most important queries side by side. Pay attention to implicit casts, null handling in some functions, and date parsing, which are the areas where results sometimes differ.

A capacity reservation gives you dedicated query processing capacity measured in DPUs (data processing units), billed per DPU-hour instead of per TB scanned. The minimum reservation is 24 DPUs.

You assign one or more workgroups to a reservation, and their queries then run on that capacity. It gives predictable cost, higher concurrency, and no queueing from shared capacity.

It makes sense for steady, heavy, or business-critical workloads. For sporadic queries, on-demand per-TB pricing is usually cheaper. You can scale the DPU count up or down as needs change.

You can add or remove DPUs on demand, but changes can take a few minutes to apply. Monitor the reservation's DPU utilisation in CloudWatch, so you neither overpay for idle capacity nor starve queries.

UNLOAD writes the result of a SELECT directly to S3 in a chosen format, such as Parquet, ORC, Avro, or JSON, instead of the default CSV.

UNLOAD (SELECT * FROM sales WHERE region = 'eu')
TO 's3://my-bucket/exports/eu/'
WITH (format = 'PARQUET', compression = 'SNAPPY');

It is handy for exporting data for other tools or for downstream ETL without creating a table. Unlike CTAS, no table is created in the catalog. The destination prefix must be empty, and you pay for data scanned like any other query.

Use partitioned_by in the WITH clause if you want the exported data laid out by partition. Since UNLOAD writes files directly, you can later point a table at that prefix, or hand it to another system as is.

CloudTrail delivers JSON logs to S3. You can create the table from the CloudTrail console's Create Athena table button, or write the DDL with the CloudTrail SerDe.

SELECT eventtime, eventname, useridentity.arn, sourceipaddress
FROM cloudtrail_logs
WHERE eventname = 'DeleteBucket'
  AND eventtime > '2026-10-01'
LIMIT 50;

Logs grow quickly, so add partition projection on account, region, and date to keep scans small. Always filter by date. For long-term analysis, consider CloudTrail Lake or convert the logs to Parquet.

Useful queries include finding who deleted a resource, listing failed console logins (errorcode not null), and tracing activity from one access key. Remember CloudTrail timestamps are in UTC when comparing with local logs.

When too many requests hit the same S3 prefix, S3 returns 503 SlowDown, and Athena may fail with an error or retry and slow down. It is common with many small files under one prefix, or when several queries read the same data at once.

1. Reduce file count by compacting into larger files.
2. Spread data across more prefixes with partitioning.
3. Avoid running many heavy queries on the same data simultaneously, or stagger them.
4. Reuse results where possible to avoid repeated scans.
5. Check that other apps (Glue, EMR) are not hammering the same prefix.

S3 scales per prefix, so good partition design helps both speed and reliability.

In Athena, you may also see the issue as slow queries rather than hard errors, because the client retries with backoff. Looking at the query's engine execution time compared with the data scanned can reveal that throttling is the cause.

A solid design keeps raw logs cheap and queryable data compact.

flowchart LR
  A["App / ALB / CloudTrail logs"] --> B["S3 raw bucket"]
  B --> C["Glue job or Athena CTAS"]
  C --> D["S3 curated Parquet, partitioned by date"]
  D --> E["Athena workgroup with data limits"]
  E --> F["QuickSight / analysts"]

1. Land raw logs in S3 with a lifecycle rule to Glacier or deletion.
2. Convert to Parquet daily (or hourly) with CTAS or Glue, partitioned by date and compacted to large files.
3. Use partition projection so new days appear without crawlers.
4. Give each team a workgroup with per-query limits and tags.
5. Turn on result reuse for dashboards.

This keeps scans small, costs predictable, and access controlled, while raw data remains available for replay.

Add monitoring too: CloudWatch alarms on workgroup data scanned, and S3 storage metrics for the raw bucket. That way you notice cost spikes or exploding file counts before the bill arrives.