Amazon Athena SQL Cheat Sheet: Queries, Partitions, Cost

Amazon Athena bills you for every byte it reads from S3, so writing efficient queries is as much a cost discipline as a SQL one. This is the reference I keep open while working: the Athena-specific behaviour that catches people out first, then the Trino SQL surface worth having to hand. Engine v3 throughout.

Cost and engine mental model

You are billed by bytes scanned from S3, rounded up, with a 10 MB minimum per query. DDL, failed queries, and MSCK REPAIR cost nothing.
LIMIT does not reduce bytes scanned. Only partition pruning and columnar formats do.
Columnar formats (Parquet, ORC) scan only the columns you reference. SELECT * defeats this. CSV/JSON always scan the whole row.
Compression (Snappy, ZSTD) reduces bytes scanned and therefore cost, not just storage.
Engine v3 = Trino semantics. If a function exists in Trino docs, it almost certainly works here.

Identifiers and literals (the stuff that bites)

DDL quotes identifiers with backticks: `my_table`.
DML quotes identifiers with double quotes: "date", "timestamp", "order" (all reserved).
String literals are single quotes only. Double quotes = identifier.
Equality is =, never ==.
Glue lowercases column names. Reference them lowercase in queries.
Integer / integer truncates. Cast first: CAST(a AS double) / b.

SELECT "timestamp", count(*)
FROM logs
WHERE status = 'ERROR'
GROUP BY "timestamp";

DDL: external tables

CREATE EXTERNAL TABLE IF NOT EXISTS events (
  user_id   bigint,
  action    string,
  payload   string,
  amount    decimal(12,2)
)
PARTITIONED BY (dt string)
STORED AS PARQUET
LOCATION 's3://my-bucket/events/'
TBLPROPERTIES ('parquet.compression' = 'SNAPPY');

Common STORED AS targets: PARQUET, ORC, JSON, TEXTFILE. For CSV with quoting use OpenCSVSerde:

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ('separatorChar' = ',', 'quoteChar' = '"')

Note: OpenCSVSerde reads every column as string. Cast in queries.

Partitions

Three ways to make partitions visible, in increasing order of preference:

-- 1. Hive-style layout (s3://.../dt=2026-06-25/), discover all at once
MSCK REPAIR TABLE events;

-- 2. Register one explicitly (non-Hive layout, or targeted add)
ALTER TABLE events ADD IF NOT EXISTS
  PARTITION (dt = '2026-06-25')
  LOCATION 's3://my-bucket/events/2026-06-25/';

MSCK REPAIR gets slow and expensive on tables with thousands of partitions because it lists all of S3 every run.

Partition projection (use this)

Eliminates the metastore round-trip entirely. Athena computes partition values from config instead of listing S3 or querying Glue. Massive speedup on high-cardinality partitions and no MSCK ever.

CREATE EXTERNAL TABLE events (...)
PARTITIONED BY (dt string)
STORED AS PARQUET
LOCATION 's3://my-bucket/events/'
TBLPROPERTIES (
  'projection.enabled' = 'true',
  'projection.dt.type' = 'date',
  'projection.dt.range' = '2024-01-01,NOW',
  'projection.dt.format' = 'yyyy-MM-dd',
  'projection.dt.interval' = '1',
  'projection.dt.interval.unit' = 'DAYS',
  'storage.location.template' = 's3://my-bucket/events/${dt}/'
);

Projection types: enum, integer, date, injected.

enum: fixed list, e.g. 'projection.region.values' = 'eu-west-1,us-east-1'
integer: 'projection.id.range' = '0,1000' with optional digits for zero-padding
date: as above; NOW and NOW-3DAYS style expressions allowed
injected: value must be supplied in the WHERE clause; Athena will not enumerate it

Gotcha: with projection, querying outside the declared range returns zero rows silently, not an error. Widen the range before backfilling history.

Always prune the partition

-- Good: partition column in WHERE, scans one day
SELECT * FROM events WHERE dt = '2026-06-25';

-- Bad: function on the partition column can defeat pruning
SELECT * FROM events WHERE date(from_iso8601_date(dt)) = current_date;

-- Range pruning works
WHERE dt BETWEEN '2026-06-01' AND '2026-06-25'

Keep partition columns as plain string comparisons where possible. Wrapping them in casts/functions can force a full scan.

SELECT essentials

WITH recent AS (
  SELECT user_id, amount, dt
  FROM events
  WHERE dt >= '2026-06-01'
)
SELECT user_id, sum(amount) AS total
FROM recent
GROUP BY user_id
HAVING sum(amount) > 100
ORDER BY total DESC
LIMIT 50;

DISTINCT, GROUP BY, HAVING, ORDER BY ... [ASC|DESC] [NULLS FIRST|LAST]
UNION / UNION ALL / INTERSECT / EXCEPT
GROUP BY GROUPING SETS ((a,b),(a),()), GROUP BY CUBE(a,b), GROUP BY ROLLUP(a,b)
GROUP BY 1, 2 references select positions

Conditionals and NULL handling

CASE WHEN amount > 100 THEN 'high'
     WHEN amount > 10  THEN 'mid'
     ELSE 'low' END
CASE status WHEN 'A' THEN 1 ELSE 0 END

coalesce(a, b, 0)        -- first non-null
nullif(a, 0)             -- null if a = 0 (guard division)
if(cond, then, else)     -- Trino shorthand
try(a / b)               -- swallow runtime errors, return null
try_cast(x AS integer)   -- cast or null instead of failing

try() and try_cast() are the difference between one bad row killing a query and getting your results.

Strings

length(s)                       concat(a, b, c)          a || b
lower(s)  upper(s)  trim(s)      lpad(s, 10, '0')
substr(s, 1, 5)                  replace(s, 'x', 'y')
split(s, ',')                    -- returns array
split_part(s, ',', 2)           -- 1-indexed
strpos(s, 'sub')                 -- 0 if not found
position('sub' IN s)
format('%s-%05d', name, id)     -- printf style

Regex (Java syntax)

regexp_like(s, '^ERROR')
regexp_extract(s, 'id=(\d+)', 1)        -- capture group 1
regexp_extract_all(s, '\d+')            -- array of matches
regexp_replace(s, '\s+', ' ')
regexp_split(s, '[,;]')

Dates and times

Trino is strict about types. timestamp vs date vs string matters.

current_date           current_timestamp        now()
date '2026-06-25'      timestamp '2026-06-25 14:30:00'

-- parsing strings (MySQL-style format codes)
date_parse('2026-06-25 14:30', '%Y-%m-%d %H:%i')
date_format(ts, '%Y-%m-%d')
from_iso8601_timestamp('2026-06-25T14:30:00Z')
from_unixtime(epoch_seconds)
to_unixtime(ts)

-- arithmetic
date_add('day', 7, ts)          date_diff('hour', a, b)
date_trunc('month', ts)
ts + interval '1' day           ts - interval '30' minute

-- extraction
year(ts)  month(ts)  day(ts)  hour(ts)  dow(ts)  day_of_week(ts)
extract(WEEK FROM ts)

Watch the format codes: date_parse/date_format use %Y %m %d %H %i %s (MySQL style), not yyyy-MM-dd. The yyyy-MM-dd style is only for partition projection config.

Aggregation

count(*)  count(col)  count(DISTINCT col)
sum  avg  min  max
array_agg(col ORDER BY ts)
array_agg(DISTINCT col)
map_agg(key, value)             multimap_agg(key, value)
string_agg / array_join(array_agg(x), ',')
count_if(status = 'ERROR')      -- count rows matching a predicate
sum(CASE WHEN x THEN 1 ELSE 0 END)

Approximate aggregates (much cheaper at scale)

approx_distinct(user_id)             -- HyperLogLog, ~2.3% error
approx_distinct(user_id, 0.01)       -- tighter error bound
approx_percentile(latency, 0.95)     -- p95
approx_percentile(latency, ARRAY[0.5, 0.9, 0.99])

Window functions

SELECT
  user_id,
  amount,
  row_number()   OVER (PARTITION BY user_id ORDER BY ts)            AS rn,
  rank()         OVER (PARTITION BY user_id ORDER BY amount DESC)   AS rnk,
  dense_rank()   OVER (...)                                          AS drnk,
  lag(amount, 1) OVER (PARTITION BY user_id ORDER BY ts)           AS prev,
  lead(amount)   OVER (...)                                          AS next,
  sum(amount)    OVER (PARTITION BY user_id ORDER BY ts
                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  ntile(4)       OVER (ORDER BY amount)                            AS quartile,
  first_value(amount) OVER (...),
  last_value(amount)  OVER (... ROWS BETWEEN UNBOUNDED PRECEDING
                                AND UNBOUNDED FOLLOWING)
FROM events;

Classic dedup pattern (keep latest row per key):

SELECT * FROM (
  SELECT *, row_number() OVER (PARTITION BY user_id ORDER BY ts DESC) AS rn
  FROM events
) WHERE rn = 1;

Arrays, maps, structs

ARRAY[1, 2, 3]                  arr[1]            -- 1-indexed!
cardinality(arr)                contains(arr, 5)
element_at(arr, 1)              -- safe, null on miss vs arr[i] error
array_distinct(arr)             array_sort(arr)
array_intersect(a, b)           array_union(a, b)
slice(arr, 2, 3)                concat(a, b)

MAP(ARRAY['k1','k2'], ARRAY[1,2])
element_at(m, 'k1')             map_keys(m)        map_values(m)

row.field                       -- struct field access
CAST(ROW(1,'x') AS ROW(id int, name varchar))

Lambda expressions

transform(arr, x -> x * 2)
filter(arr, x -> x > 0)
reduce(arr, 0, (s, x) -> s + x, s -> s)
array_sort(arr, (a, b) -> IF(a < b, -1, IF(a > b, 1, 0)))

UNNEST (flatten arrays into rows)

SELECT user_id, tag
FROM events
CROSS JOIN UNNEST(tags) AS t(tag);

-- with ordinality (position index)
CROSS JOIN UNNEST(tags) WITH ORDINALITY AS t(tag, pos)

-- unnest a map into key/value rows
CROSS JOIN UNNEST(attrs) AS t(k, v)

UNNEST against LEFT JOIN keeps rows with empty/null arrays; plain CROSS JOIN UNNEST drops them.

JSON

json_extract(payload, '$.user.id')          -- returns JSON
json_extract_scalar(payload, '$.user.id')   -- returns varchar (use this for values)
json_array_length(json_extract(payload, '$.items'))
json_parse(s)                                -- string -> JSON
json_format(j)                               -- JSON -> string
CAST(payload AS JSON)
CAST(json_extract(payload, '$.amount') AS double)

-- map a JSON object to a typed map
CAST(json_parse(payload) AS MAP(varchar, varchar))

Use json_extract_scalar when you want the value as text. json_extract keeps it as JSON (quotes included), which trips people up.

Joins

FROM a JOIN b ON a.id = b.id           -- inner
LEFT JOIN  /  RIGHT JOIN  /  FULL JOIN
CROSS JOIN                              -- cartesian (and UNNEST)
LEFT JOIN ... ON a.id = b.id AND b.x = 'y'   -- filter in ON, not WHERE, for outer joins

Put the larger table on the left, smaller on the right; Athena builds the hash table from the right side. Filter both sides on their partition columns inside subqueries before joining to cut bytes scanned.

CTAS and INSERT INTO

CTAS writes query results to a new table + S3 location in one shot. The standard way to build optimised Parquet tables and to materialise expensive intermediate results.

CREATE TABLE summary
WITH (
  format = 'PARQUET',
  parquet_compression = 'SNAPPY',
  partitioned_by = ARRAY['dt'],
  external_location = 's3://my-bucket/summary/'
) AS
SELECT user_id, sum(amount) AS total, dt
FROM events
GROUP BY user_id, dt;

-- append to an existing table
INSERT INTO summary
SELECT user_id, sum(amount), dt FROM events WHERE dt = '2026-06-25' GROUP BY 1, 3;

CTAS rules worth knowing:

Partition columns must be the last columns in the SELECT list, in order.
Bucketing: add bucketed_by = ARRAY['user_id'], bucket_count = 16.
CTAS and INSERT INTO ... SELECT each cap at 100 partitions written per statement. For more, batch by partition or use UNLOAD.
external_location must be empty or unspecified; Athena will not overwrite.

UNLOAD (export without a table)

UNLOAD (SELECT * FROM events WHERE dt = '2026-06-25')
TO 's3://my-bucket/exports/'
WITH (format = 'PARQUET', compression = 'SNAPPY');

Good for exporting query output in a chosen format/partitioning without the CTAS 100-partition limit and without leaving a catalog table behind.

Performance and cost checklist

Partition on your common filter column (usually date). Always include it in WHERE.
Use partition projection for high-cardinality or time-based partitions.
Store as Parquet/ORC with Snappy or ZSTD. Convert CSV/JSON landing data via CTAS.
Select only the columns you need. Avoid SELECT * on wide columnar tables.
Aim for files in the 128 MB to 1 GB range. Many tiny files = slow listing and overhead; use compaction or CTAS to consolidate.
Filter early, join late. Push predicates into subqueries before joins.
Prefer approx_distinct / approx_percentile over exact for dashboards.
Check Data scanned in the query stats after each run. That number is your bill.

Workgroups and results

Each workgroup can enforce a result location, encryption, and a per-query data-scanned limit (a cost guardrail worth setting).
Query results land as CSV in the workgroup’s S3 output location; reused result cache can return prior results free if enabled.
EXPLAIN and EXPLAIN ANALYZE show the plan and actual row/byte counts.

Quick gotcha list

Symptom	Cause	Fix
`Column 'date' cannot be resolved`	reserved word	double-quote it: `"date"`
Query returns 0 rows, no error	partition projection range too narrow, or partitions not loaded	widen projection range / `MSCK REPAIR`
Integer division gives 0	int / int truncates	`CAST(x AS double)`
Whole table scanned despite `WHERE dt=...`	function wrapping partition column	compare partition column raw
One bad value fails the whole query	strict casting	`try_cast` / `try`
`json_extract` value has quotes around it	returned JSON not scalar	use `json_extract_scalar`
CTAS fails on partitions	partition cols not last, or >100 partitions	reorder SELECT / batch / `UNLOAD`
Slow query, huge scan on small result	too many small files or row format	CTAS to Parquet, compact

Amazon Athena SQL Cheat Sheet

Cost and engine mental model

Identifiers and literals (the stuff that bites)

DDL: external tables

Partitions

Partition projection (use this)

Always prune the partition

SELECT essentials

Conditionals and NULL handling

Strings

Regex (Java syntax)

Dates and times

Aggregation

Approximate aggregates (much cheaper at scale)

Window functions

Arrays, maps, structs

Lambda expressions

UNNEST (flatten arrays into rows)

JSON

Joins

CTAS and INSERT INTO

UNLOAD (export without a table)

Performance and cost checklist

Workgroups and results

Quick gotcha list

Like this:

Leave a ReplyCancel reply

Cost and engine mental model

Identifiers and literals (the stuff that bites)

DDL: external tables

Partitions

Partition projection (use this)

Always prune the partition

SELECT essentials

Conditionals and NULL handling

Strings

Regex (Java syntax)

Dates and times

Aggregation

Approximate aggregates (much cheaper at scale)

Window functions

Arrays, maps, structs

Lambda expressions

UNNEST (flatten arrays into rows)

JSON

Joins

CTAS and INSERT INTO

UNLOAD (export without a table)

Performance and cost checklist

Workgroups and results

Quick gotcha list

Share this:

Like this:

Leave a ReplyCancel reply