Amazon Athena & Federated Queries: Partition Projection, Iceberg & CTAS Cost Tuning

Amazon Athena bills you for exactly one thing — the bytes it reads off S3 to answer your query — and that single fact quietly decides whether your data lake is a cheap, fast query layer or a runaway line item that finance flags at the end of the quarter. Because Athena is serverless Presto/Trino with no cluster to size and no per-hour meter, the entire cost-and-latency story collapses into how much data scanned each query touches, and every tuning lever — how you lay out partitions, whether you store rows or columns, whether you compress, whether the engine can prune before it reads — exists to make that number smaller. A query that full-scans a terabyte of CSV and a query that reads forty gigabytes of partitioned, compressed Parquet return the same answer; one costs twenty-five times more and runs ten times slower.

This guide is the senior-data-engineering walkthrough for driving that bytes-scanned number down and extending Athena past the lake, framed the way interviewers actually probe it: partition projection that computes partition values at query time so you never wait on a Glue metastore round-trip, CTAS (CREATE TABLE AS SELECT) rewrites into partitioned and compressed Parquet, Apache Iceberg tables that add ACID MERGE upserts, time travel, and compaction on top of the lake, and federated query with Lambda connectors that let one SQL statement join S3 against a live RDS or DynamoDB table — all governed by workgroup cost limits that cap the bytes a single query is allowed to scan. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the SQL practice library →, sharpen the cost axis on the optimization practice library →, and rehearse pipeline layout on the ETL practice library →.

---

On this page

• Why Amazon Athena's bytes-scanned cost model determines everything
• Partition projection — kill the Glue partition bottleneck
• Columnar formats + CTAS cost tuning
• Apache Iceberg tables in Athena
• Federated queries + workgroup cost governance
• Cheat sheet — Amazon Athena cost-tuning recipes
• Frequently asked questions
• Practice on PipeCode

---

1. Why Amazon Athena's bytes-scanned cost model determines everything

Athena is priced on data scanned, not compute time — so every tuning lever exists to read fewer bytes

The one-sentence invariant: Amazon Athena is serverless Presto/Trino that charges per terabyte of data scanned off S3 (list price ~$5/TB in most regions), which means query cost and query latency are both dominated by a single number — bytes read — and every optimisation you will ever apply (partitioning, columnar formats, compression, projection, Iceberg metadata pruning) is a way to shrink that number rather than to buy a bigger cluster. There is no cluster to right-size, no autoscaling group, no reserved-instance math; the meter is the S3 read path, and the engineer who internalises "reduce bytes scanned" as the prime directive out-tunes the one who reaches for engine knobs.

The four axes interviewers actually probe.

• Data scanned (the bill). Athena rounds each query up to the nearest 10 MB and charges per TB. A single unpartitioned SELECT * over a year of raw JSON can scan multiple terabytes; the same answer over partitioned Parquet can scan a few gigabytes. Interviewers open here because "how is Athena priced?" separates people who have paid an Athena bill from people who have only read the console.
• Partition pruning. Can the engine skip whole S3 prefixes before reading them? With Hive-style partitions plus a WHERE on the partition key, Athena reads only the matching prefixes. Without partitioning — or without a partition predicate — it reads everything. Pruning is the single biggest lever, and partition projection (section 2) is how you get pruning without a Glue metastore that chokes at scale.
• Columnar format + compression. Row formats (CSV, JSON, raw text) force Athena to read every byte of every row even when the query needs two columns. Columnar formats (Parquet, ORC) plus compression (Snappy, ZSTD) let the engine read only the referenced columns and skip row-groups by min/max statistics. This is the CTAS/Iceberg payoff (sections 3 and 4).
• Concurrency and governance. Athena runs queries in workgroups. A workgroup enforces a per-query data scanned cutoff, isolates one team's queries and results from another's, enables query-result reuse, and emits CloudWatch cost metrics. Without workgroup limits, one careless SELECT * can scan (and bill) a fortune before anyone notices.

The 2026 reality — the cost-tuning stack is settled.

• Partition projection is the default for high-cardinality time-series layouts (logs, events, clickstream). It removes the MSCK REPAIR TABLE / ALTER TABLE ADD PARTITION maintenance burden and the metastore round-trip that dominates planning once a table has hundreds of thousands of partitions.
• CTAS + columnar is the default curation step. Raw ingest lands as CSV/JSON; a scheduled CTAS (or INSERT INTO) rewrites it to partitioned, compressed Parquet that downstream queries hit instead of the raw zone.
• Apache Iceberg is the default when the table needs row-level MERGE/UPDATE/DELETE, ACID guarantees, schema evolution, or time travel — things a plain Hive external table simply cannot offer. Athena speaks Iceberg natively.
• Federated query is the default when the answer lives partly outside S3 — a dimension in RDS, a lookup in DynamoDB, recent logs in CloudWatch — and building an ETL copy is not worth it. A Lambda connector lets Athena read those sources in place.

What interviewers listen for.

• Do you name the pricing unit — "per terabyte of data scanned, rounded up to 10 MB" — without prompting? — required answer.
• Do you say "reduce bytes scanned" is the prime directive rather than "make the cluster bigger"? — senior signal.
• Do you name partition projection as the pruning mechanism that scales past the Glue metastore? — senior signal.
• Do you distinguish CTAS-to-Parquet (append-only curation) from Iceberg (row-level ACID upserts) instead of treating them as interchangeable? — senior signal.
• Do you mention workgroups + per-query data-scanned limits as the governance layer? — required answer for any "how do you control Athena spend?" probe.

Worked example — the four-axis Athena cost model

Detailed explanation. The single most useful artifact for an Athena interview is a mental table that turns each layout decision into a bytes-scanned consequence. Every senior Athena discussion converges on it within the first ten minutes; carrying it in your head is the difference between a fluent cost answer and hand-waving about "it depends." Walk through building the table for a hypothetical events dataset queried daily.

• Dataset. One year of clickstream events, ~4 TB raw as gzipped JSON on S3.
• Typical query. "Count events by event_type for the last 7 days" — touches 2 columns and 7 days out of 365.
• Question the table answers. For each layout, how many bytes does that query scan, and therefore what does it cost?

Question. Build the layout-to-bytes-scanned table for the 7-day query and compute the relative cost of each option.

Input.

Layout	Partitioned by day?	Columnar?	Bytes scanned (7-day query)
Raw gzipped JSON, no partitions	no	no	~4 TB (whole dataset)
JSON partitioned by dt	yes	no	~77 GB (7 of 365 days)
Parquet + Snappy, partitioned by dt	yes	yes	~1.5 GB (2 cols × 7 days)
Parquet + partition + column projection	yes	yes	~0.9 GB (row-group skip too)

Code.

-- The same logical query against every layout
SELECT event_type, COUNT(*) AS n
FROM   events
WHERE  dt BETWEEN date '2026-08-11' AND date '2026-08-17'
GROUP  BY event_type;

-- Inspect what a query actually scanned (per-query, after it runs)
SELECT query_id,
       ROUND(data_scanned_in_bytes / 1e9, 2) AS gb_scanned,
       ROUND(data_scanned_in_bytes / 1e12 * 5, 4) AS approx_usd   -- ~$5/TB
FROM   information_schema.query_history          -- or the GetQueryExecution API
WHERE  query LIKE '%FROM events%'
ORDER  BY gb_scanned DESC;

Step-by-step explanation.

1. The unpartitioned raw layout has no way to skip data: Athena lists the whole prefix and reads all ~4 TB, because a WHERE dt BETWEEN ... predicate on a column that is not a partition key cannot prune S3 objects. At ~$5/TB that one query costs about $20 — and it runs every day.
2. Partitioning by dt lets Athena read only the 7 matching day-prefixes: ~4 TB / 365 × 7 ≈ 77 GB. The predicate now maps to S3 prefixes, so 358 days are never opened. Cost drops from ~$20 to ~$0.39 with a single layout change and no format change.
3. Switching to columnar Parquet lets the engine read only the two referenced columns (event_type, plus the implicit row count) instead of every field in each JSON object. Two narrow columns out of dozens is roughly a 50× reduction on top of partitioning — ~1.5 GB, ~$0.0075 per run.
4. Parquet's per-row-group min/max statistics add a second prune: within the 7 partitions, row-groups whose dt range falls outside the window are skipped without decompression. This is why the "column projection" row edges below the plain-Parquet row.
5. The information_schema.query_history / GetQueryExecution DataScannedInBytes field is the ground truth. Never estimate cost from row counts; read the scanned-bytes metric Athena reports for the exact query.

Output.

Layout	Bytes scanned	Approx cost/run	Cost vs raw
Raw JSON, no partitions	~4 TB	~$20.00	1× (baseline)
JSON partitioned by dt	~77 GB	~$0.39	~51× cheaper
Parquet + partition	~1.5 GB	~$0.0075	~2,600× cheaper
Parquet + partition + row-group skip	~0.9 GB	~$0.0045	~4,400× cheaper

Rule of thumb. Never reason about an Athena query by wall-clock latency alone — read the DataScannedInBytes it reports and multiply by your per-TB rate. Partitioning is the first 50×; columnar + compression is the next 50×; both together are the difference between a $20 query and a half-cent query.

Worked example — the "reduce bytes scanned" decision tree

Detailed explanation. Given any slow or expensive Athena query, the senior engineer runs a short decision tree to find the biggest lever first. Codifying it makes the interview answer reproducible: an interviewer hands you a "this query costs too much" scenario and you walk the tree out loud instead of guessing. Walk the tree against three canonical complaints.

• Q1 — Is the table partitioned on the column you filter on? If not, add partitioning (or projection) — this is the 50× lever.
• Q2 — Is the data columnar (Parquet/ORC) and compressed? If it is raw CSV/JSON, CTAS it to Parquet — the next 50× lever.
• Q3 — Does the query SELECT * when it needs three columns? Project only the columns you use so columnar skipping actually helps.
• Q4 — Are there millions of tiny files? Compact them (CTAS rewrite or Iceberg OPTIMIZE) so per-object overhead stops dominating.
• Q5 — Does the query re-run identically? Turn on workgroup query-result reuse so repeat runs scan zero bytes.

Question. For each of three complaints, name the first lever the tree selects and the expected order-of-magnitude win.

Input.

Complaint	Partitioned?	Format	Query shape	First lever
"Daily report scans 3 TB"	no	Parquet	filters on dt	add partitioning / projection
"Ad-hoc query on raw logs is slow + costly"	yes	gzip JSON	4 columns	CTAS → Parquet
"BI dashboard re-runs same query all day"	yes	Parquet	fixed	workgroup result reuse

Code.

# Illustrative decision helper — returns the highest-impact lever first
def biggest_athena_lever(partitioned_on_filter: bool,
                         columnar: bool,
                         selects_only_needed_cols: bool,
                         many_small_files: bool,
                         repeats_identically: bool) -> str:
    if not partitioned_on_filter:
        return "add partitioning or partition projection (~50x)"
    if not columnar:
        return "CTAS to Parquet + compression (~50x)"
    if not selects_only_needed_cols:
        return "project only used columns (enables column skipping)"
    if many_small_files:
        return "compact files: CTAS rewrite or Iceberg OPTIMIZE"
    if repeats_identically:
        return "enable workgroup query-result reuse (scans 0 bytes)"
    return "already well tuned; inspect row-group stats / sort order"


print(biggest_athena_lever(False, True,  True,  False, False))
# → add partitioning or partition projection (~50x)
print(biggest_athena_lever(True,  False, True,  False, False))
# → CTAS to Parquet + compression (~50x)
print(biggest_athena_lever(True,  True,  True,  False, True))
# → enable workgroup query-result reuse (scans 0 bytes)

Step-by-step explanation.

1. The tree is ordered by expected impact, not by convenience. Partitioning and columnar conversion are each roughly an order of magnitude; column projection and file compaction are meaningful but smaller; result reuse is a special case that zeros the bill for exact-repeat queries.
2. Complaint 1 — "3 TB daily report" on Parquet that filters on dt but is not partitioned on dt. Q1 fails: the format is already good, but with no partition on the filter column the engine reads all row-groups. Adding day partitioning (or projection) is the fix.
3. Complaint 2 — raw gzip JSON, four columns needed. Q1 passes (partitioned), Q2 fails (row format). CTAS to Parquet lets the four columns be read without the rest of each record, and compression shrinks the bytes further.
4. Complaint 3 — an identical dashboard query all day. Every earlier lever passes; the win is workgroup query-result reuse: Athena serves the cached result for up to the configured max age (up to 7 days) and reports zero bytes scanned for the reused runs.
5. The tree also prevents wasted effort: there is no point hand-tuning row-group sort order (Q-final) while the table is still unpartitioned raw JSON. Fix the order-of-magnitude levers before the percentage ones.

Output.

Complaint	Tree stop	Lever applied	Expected win
3 TB daily report	Q1	partition / projection on dt	~50× fewer bytes
Slow raw-log query	Q2	CTAS → Parquet + Snappy	~10–50× fewer bytes
Repeating dashboard	Q5	workgroup result reuse	0 bytes on repeats

Rule of thumb. Walk the levers top-down and stop at the first one that applies — partition, then columnar, then project, then compact, then reuse. Fixing an order-of-magnitude lever before a percentage lever is what makes the tree fast to reason about under interview pressure.

Worked example — what interviewers actually probe

Detailed explanation. The senior Athena interview has a predictable arc: an ambiguous opener ("we query a data lake with Athena and the bill is climbing — what do you do?"), then progressive narrowing to test whether you know the axes. Candidates who name the bytes-scanned model in sentence one score highest; candidates who suggest "a bigger cluster" reveal they have never used Athena. Walk the grading rubric.

• Ambiguous opener. "Our Athena costs tripled this quarter." — invites you to name the bytes-scanned model.
• Follow-up 1. "The tables have 400k partitions and planning is slow." — probes partition projection.
• Follow-up 2. "Most data is CSV." — probes CTAS / columnar.
• Follow-up 3. "We need to upsert late-arriving corrections." — probes Iceberg.
• Follow-up 4. "Some dashboards need a live value from our RDS." — probes federated query.
• Follow-up 5. "How do we stop one analyst scanning 50 TB by accident?" — probes workgroups.

Question. Draft a five-minute senior Athena answer that pre-empts all five follow-ups.

Input.

Interview signal	Weak answer	Senior answer
Cost model	"run it on a bigger instance"	"Athena bills per TB scanned; cut bytes read"
Partition scale	"run MSCK nightly"	"partition projection — no metastore round-trip"
Format	"leave it as CSV"	"CTAS to partitioned Snappy Parquet"
Upserts	"overwrite the whole table"	"Iceberg MERGE for row-level upsert"
Live sources	"build an ETL copy"	"federated query via a Lambda connector"
Governance	"ask people to be careful"	"workgroup per-query data-scanned limit"

Code.

Senior Amazon Athena answer template (5 minutes)
================================================

Minute 1 — name the cost model
  "Athena is serverless Presto/Trino billed per TB scanned. The whole
   game is reading fewer bytes, not buying more compute."

Minute 2 — partitions at scale
  "With 400k partitions, MSCK/ADD PARTITION and the Glue metastore
   round-trip dominate planning. I'd switch time-series tables to
   partition projection so partition values are computed from table
   properties at query time — no metastore scan."

Minute 3 — format
  "Raw CSV/JSON scans every byte. I'd stand up a CTAS curation step
   that rewrites raw into partitioned, Snappy-compressed Parquet, and
   point dashboards at the curated table."

Minute 4 — upserts + live sources
  "For late-arriving corrections I'd use an Apache Iceberg table and
   MERGE INTO for row-level upserts, with OPTIMIZE + VACUUM for
   maintenance. For the live RDS value I'd use a federated query via
   the JDBC Lambda connector instead of copying the table."

Minute 5 — governance
  "I'd put each team in its own workgroup with a per-query
   data-scanned control limit, enable query-result reuse, and alert on
   the CloudWatch DataScannedInBytes metric so a runaway SELECT * is
   capped before it bills."

Step-by-step explanation.

1. Minute 1 frames everything around bytes scanned. Naming the pricing unit immediately signals you have operated Athena, not just read about it; the "bigger cluster" answer is disqualifying because there is no cluster.
2. Minute 2 addresses partition scale before being asked. "MSCK is slow at 400k partitions" is the pain; "partition projection removes the metastore round-trip" is the senior fix, and naming it unprompted is the differentiator.
3. Minute 3 is the format argument. CTAS-to-Parquet is the curation pattern every lake eventually adopts; pointing consumers at a curated table rather than the raw zone is the operational half of the answer.
4. Minute 4 splits two distinct needs that juniors conflate: row-level upserts (Iceberg MERGE) and reaching non-S3 data (federated query). Naming the right tool for each — and not "just overwrite the table" — is the seniority tell.
5. Minute 5 is governance. Workgroups with per-query data-scanned limits turn "please be careful" into an enforced ceiling; the CloudWatch metric turns cost into something you alert on rather than discover on the invoice.

Output.

Grading criterion	Weak score	Senior score
Names bytes-scanned model in minute 1	rare	mandatory
Names partition projection at scale	rare	senior signal
Names CTAS → Parquet curation	occasional	mandatory
Splits Iceberg upsert vs federated reach	rare	senior signal
Names workgroup data-scanned limit	rare	required

Rule of thumb. The senior Athena answer is a five-minute monologue that walks the bill down — cost model, projection, columnar, Iceberg/federated, workgroups — without waiting for the follow-ups. Rehearse it once; it covers 90% of Athena interview arcs.

Senior interview question on Athena cost tuning

A senior interviewer often opens with: "You inherit a data lake queried through Amazon Athena. Costs tripled last quarter; the biggest table is a year of clickstream stored as unpartitioned gzipped JSON, and analysts run SELECT *-style ad-hoc queries against it. Walk me through the target layout you would migrate to, the order you would apply the changes, and how you would prove the bytes scanned actually dropped."

Solution Using a partitioned columnar curation layer with measured before/after bytes scanned

-- Step 1 — measure the baseline: what does the current query scan?
--   (read DataScannedInBytes from GetQueryExecution or query_history)
-- Baseline: SELECT event_type, COUNT(*) ... over raw JSON  => ~4 TB scanned

-- Step 2 — build a curated, partitioned, columnar table with CTAS
CREATE TABLE curated.events_parquet
WITH (
    format             = 'PARQUET',
    parquet_compression = 'SNAPPY',
    partitioned_by     = ARRAY['dt'],
    external_location  = 's3://lake-curated/events_parquet/'
) AS
SELECT
    event_id,
    user_id,
    event_type,
    payload,
    -- derive the partition column LAST (CTAS requires partition cols last)
    date_format(from_unixtime(event_ts), '%Y-%m-%d') AS dt
FROM   raw.events_json
WHERE  from_unixtime(event_ts) >= date '2025-08-01';

-- Step 3 — incremental catch-up for each new day (append, don't rebuild)
INSERT INTO curated.events_parquet
SELECT event_id, user_id, event_type, payload,
       date_format(from_unixtime(event_ts), '%Y-%m-%d') AS dt
FROM   raw.events_json
WHERE  from_unixtime(event_ts) >= current_date - interval '1' day;

-- Step 4 — repoint the analyst query at the curated table
SELECT event_type, COUNT(*) AS n
FROM   curated.events_parquet
WHERE  dt BETWEEN '2026-08-11' AND '2026-08-17'    -- partition prune
GROUP  BY event_type;

-- Step 5 — prove it: compare scanned bytes before vs after
SELECT 'before' AS phase, 4.0e12  AS bytes_scanned
UNION ALL
SELECT 'after'  AS phase, data_scanned_in_bytes
FROM   information_schema.query_history
WHERE  query LIKE '%FROM curated.events_parquet%'
ORDER  BY 1;

Step-by-step trace.

Step	Before (raw JSON)	After (curated Parquet)
Layout	one flat prefix, gzipped JSON	dt-partitioned Snappy Parquet
Partition prune on dt	none (dt not a partition key)	7 of 365 day-prefixes
Columns read (2-col query)	all fields per record	1–2 columns only
Bytes scanned (7-day query)	~4 TB	~1.5 GB
Cost per run (~$5/TB)	~$20.00	~$0.0075
Maintenance	none	daily INSERT INTO catch-up

After the migration, the analyst query reads 7 day-partitions of 2 columns instead of the whole year of every field. The DataScannedInBytes metric drops from terabytes to low gigabytes, and the daily report cost falls by three orders of magnitude — proven by the reported scanned-bytes number, not estimated.

Output:

Metric	Before	After
Bytes scanned (7-day query)	~4 TB	~1.5 GB
Approx cost per run	~$20.00	~$0.0075
Query latency	tens of minutes	seconds
Partition pruning	none	day-level
Column pruning	none	referenced columns only

Why this works — concept by concept:

• Data scanned is the meter — Athena bills per TB read off S3, so the entire migration is engineered to make one query read fewer bytes; the answer is identical, the invoice is not.
• CTAS to partitioned Parquet — CREATE TABLE AS SELECT rewrites the raw zone once into a columnar, compressed, dt-partitioned table that downstream queries hit; the partition key is derived last because CTAS requires partition columns at the end of the SELECT.
• Partition pruning — a WHERE dt BETWEEN ... predicate on the partition key maps to S3 prefixes, so Athena opens only the 7 matching day-folders and never lists the other 358.
• Columnar projection — Parquet stores columns contiguously, so a two-column query reads two column-chunks per row-group instead of every field; row-group min/max stats prune further within a partition.
• INSERT INTO catch-up — the curated table stays fresh with a small daily append instead of a full rebuild, keeping the rewrite cost O(one day) rather than O(whole history).
• Cost — one-time CTAS scan of the history (O(dataset), paid once) plus a daily append (O(one day)); every subsequent analyst query is O(bytes it actually needs). Net: a ~2,600× drop on the repeating query for a one-time rewrite cost.

OPTIMIZATION
Topic — optimization
Query cost and scan-reduction optimization problems

Practice →

SQL Topic — sql SQL analytics and aggregation problems

Practice →

---

2. Partition projection — kill the Glue partition bottleneck

partition projection computes partition values at query time from table properties — so you never wait on a Glue metastore round-trip

The mental model in one line: partition projection is the Athena feature where partition values are calculated from table properties (a type, a range, and an S3 location template) at query-planning time instead of being looked up in the Glue Data Catalog — which means you skip MSCK REPAIR TABLE and ALTER TABLE ADD PARTITION entirely, you skip the metastore GetPartitions round-trip that dominates planning once a table has hundreds of thousands of partitions, and you get partition pruning as long as your WHERE predicate stays inside the projected range. It is the single biggest planning-latency and maintenance win for high-cardinality time-series tables, and every senior Athena engineer reaches for it on logs, events, and clickstream layouts.

The four axes for partition projection.

• Permission / setup. Table properties only — no Lambda, no extra IAM beyond the S3 read the table already needs. You set projection.enabled = true, a per-column projection type, and (usually) a storage.location.template. The lowest-effort pruning mechanism Athena offers.
• Planning latency. Constant. Projection never calls GetPartitions, so planning does not degrade as partition count grows. A Glue-partitioned table with 400k partitions can spend seconds (or time out) just enumerating partitions; a projected table plans the same at 400 or 400k.
• Pruning quality. As good as Hive partitioning inside the projected range. A predicate on the projection column maps to a bounded set of computed S3 prefixes. Outside the range, projection generates prefixes that may not exist (wasted list attempts) — so the range bounds are load-bearing.
• Maintenance. Zero ongoing. New partitions "appear" the moment data lands at the templated prefix, because they are computed, not registered. No nightly MSCK, no per-partition ADD PARTITION, no crawler.

The projection types — one per partition column.

• date. For time partitions. Set projection.<col>.type = date, a format (e.g. yyyy-MM-dd), a range (NOW-3YEARS,NOW or explicit dates), and an interval + interval.unit (e.g. 1,DAYS). Athena generates one prefix per date in range that overlaps the predicate.
• integer. For numeric partitions (hour 0..23, shard 0..99). Set type = integer, range = 0,23, optional digits for zero-padding.
• enum. For a small fixed set (region us-east-1,eu-west-1,...). Set type = enum and values. Best when the domain is known and stable.
• injected. For high-cardinality values that cannot be enumerated (a UUID tenant id). The value must be supplied in the WHERE clause as equality; Athena injects it into the template. No range scan possible — the query must name the value.

The storage.location.template — how computed values become S3 prefixes.

• What it is. A template like s3://bucket/events/${dt}/ where ${dt} is substituted with each computed partition value. It tells Athena where the data for a computed value lives.
• When you can omit it. If your S3 layout already matches Hive style (.../dt=2026-08-17/) and the partition column name matches the key, projection can infer locations — but explicit templates are safer and required when the on-disk layout differs from key=value.
• The classic bug. A template that does not match the real prefix (trailing slash, wrong key name, wrong date format) makes every query return zero rows silently — the computed prefixes point at nothing.

Common interview probes on partition projection.

• "Why is MSCK REPAIR slow and how do you avoid it?" — required answer: it enumerates every partition in the metastore; projection computes them instead.
• "What happens if the projected range is wider than the data?" — Athena generates prefixes that do not exist; harmless for correctness but wastes list/plan effort.
• "When can't you use projection?" — when partition values are unbounded and not supplied as equality predicates (use injected only if the query always names the value).
• "Does projection change bytes scanned?" — no; it changes planning, not the read path. Pruning quality equals Hive partitioning inside the range.

Worked example — date-projected S3 access logs

Detailed explanation. The canonical projection layout: application logs land in S3 under year=/month=/day= prefixes, and instead of registering a partition per day (thousands per year across many services), you declare date projection so Athena computes the day-prefixes for any queried window. Build the table.

• Layout. s3://logs/app/year=2026/month=08/day=17/part-*.parquet.
• Projection columns. year, month, day as integers, or a single dt date column — here we use three integer columns to match the on-disk keys.
• Range. year 2023..2027, month 1..12, day 1..31.

Question. Write the CREATE EXTERNAL TABLE with partition projection so a single day's query prunes to one prefix with no metastore call.

Input.

Property	Value
projection.enabled	true
projection.year.type	integer, range 2023,2027
projection.month.type	integer, range 1,12, digits 2
projection.day.type	integer, range 1,31, digits 2
storage.location.template	s3://logs/app/year=${year}/month=${month}/day=${day}

Code.

CREATE EXTERNAL TABLE logs.app_access (
    ts        BIGINT,
    level     STRING,
    service   STRING,
    message   STRING,
    latency_ms INT
)
PARTITIONED BY (year INT, month INT, day INT)
STORED AS PARQUET
LOCATION 's3://logs/app/'
TBLPROPERTIES (
    'projection.enabled'            = 'true',
    'projection.year.type'          = 'integer',
    'projection.year.range'         = '2023,2027',
    'projection.month.type'         = 'integer',
    'projection.month.range'        = '1,12',
    'projection.month.digits'       = '2',
    'projection.day.type'           = 'integer',
    'projection.day.range'          = '1,31',
    'projection.day.digits'         = '2',
    'storage.location.template'     = 's3://logs/app/year=${year}/month=${month}/day=${day}'
);

-- Query one day — prunes to exactly one S3 prefix, no GetPartitions call
SELECT service, COUNT(*) AS errors
FROM   logs.app_access
WHERE  year = 2026 AND month = 8 AND day = 17
  AND  level = 'ERROR'
GROUP  BY service;

Step-by-step explanation.

1. PARTITIONED BY (year, month, day) declares the partition columns exactly as they appear in the S3 keys. Projection does not change the DDL shape; it changes how the values are resolved.
2. projection.enabled = 'true' flips the table from metastore-lookup to computed partitions. From this point Athena never calls Glue GetPartitions for this table; there is nothing to MSCK.
3. Each projection.<col>.type + range (+ digits) tells Athena the domain of that column. For the query's WHERE year=2026 AND month=8 AND day=17, Athena computes the single tuple (2026, 08, 17) and substitutes it into the template.
4. digits = '2' zero-pads month and day so the computed prefix is month=08/day=17, matching the on-disk month=08 rather than month=8. A digit mismatch here is the most common "returns zero rows" bug.
5. The storage.location.template resolves (2026, 08, 17) to s3://logs/app/year=2026/month=08/day=17 and Athena reads only that prefix. Planning is constant-time regardless of how many days of data exist.

Output.

Query window	Prefixes computed	Metastore calls	Prefixes read
single day	1	0	1
one month (day unbounded)	~31	0	~31
full year (month,day unbounded)	~365	0	~365
no partition predicate	full range (~1,825)	0	all (avoid this)

Rule of thumb. Match the projection digits and format to the exact on-disk key strings, always set storage.location.template explicitly, and keep the range as tight as the real data. Zero-row results from a projected table are almost always a template or padding mismatch, not missing data.

Worked example — enum + injected projection for tenant sharding

Detailed explanation. Not every partition is a date. A multi-tenant events table is partitioned by region (a small known set) and tenant_id (a high-cardinality UUID that cannot be enumerated). Use enum for region and injected for tenant, which forces every query to name the tenant — exactly what you want for isolation and pruning. Build it.

• region. enum over us-east-1,eu-west-1,ap-south-1.
• tenant_id. injected — no range; the query must supply tenant_id = '...'.
• Consequence. A query without a tenant_id equality predicate fails fast (projection cannot enumerate injected values), which is a feature: it prevents accidental cross-tenant full scans.

Question. Configure projection so region is enumerated and tenant_id is injected, and show a valid vs an invalid query.

Input.

Column	Type	Config
region	enum	values = us-east-1,eu-west-1,ap-south-1
tenant_id	injected	must appear as tenant_id = '<uuid>'
template	—	s3://ev/region=${region}/tenant=${tenant_id}

Code.

CREATE EXTERNAL TABLE ev.tenant_events (
    event_id  STRING,
    event_ts  BIGINT,
    payload   STRING
)
PARTITIONED BY (region STRING, tenant_id STRING)
STORED AS PARQUET
LOCATION 's3://ev/'
TBLPROPERTIES (
    'projection.enabled'          = 'true',
    'projection.region.type'      = 'enum',
    'projection.region.values'    = 'us-east-1,eu-west-1,ap-south-1',
    'projection.tenant_id.type'   = 'injected',
    'storage.location.template'   = 's3://ev/region=${region}/tenant=${tenant_id}'
);

-- VALID — names the injected tenant; prunes to one region/tenant prefix
SELECT COUNT(*)
FROM   ev.tenant_events
WHERE  region = 'eu-west-1'
  AND  tenant_id = 'a3f1c9e2-77b4-4d0e-9c11-2b8e6f0a1d55'
  AND  event_ts >= 1723852800;

-- INVALID — no tenant_id equality; injected projection cannot enumerate
-- Athena errors: "column projected as INJECTED must be in the WHERE clause"
SELECT COUNT(*) FROM ev.tenant_events WHERE region = 'eu-west-1';

Step-by-step explanation.

1. region uses enum because the set of regions is small, known, and stable. Athena can enumerate all three values, so a query without a region predicate simply projects all three region prefixes — bounded and safe.
2. tenant_id uses injected because a UUID space cannot be enumerated — a range would be meaningless and an enum would need millions of values. Injected means "I will always tell you the value."
3. The valid query supplies both region = 'eu-west-1' and tenant_id = '<uuid>'. Athena substitutes both into the template, producing exactly one prefix, and reads only that tenant's data in that region.
4. The invalid query omits tenant_id. Because the column is injected, Athena has no set of values to compute and raises an error rather than scanning. This is the isolation guarantee: you cannot accidentally scan every tenant.
5. The event_ts filter in the valid query is a normal column predicate (not a partition), so it prunes row-groups by Parquet stats but not S3 prefixes. Partition pruning and row-group pruning stack.

Output.

Query	region resolved	tenant resolved	Result
region + tenant + ts	1	1	one prefix scanned
region only	1	—	error (injected requires equality)
tenant only	all 3	1	3 prefixes (one per region)
neither	all 3	—	error (injected requires equality)

Rule of thumb. Use enum for small known domains, date/integer for bounded ranges, and injected for unbounded values you can force the query to name. Injected columns double as an accidental-full-scan guardrail — the query must name the value or it fails, which is exactly the behaviour you want for tenant isolation.

Worked example — the range-too-wide failure mode

Detailed explanation. Projection's one sharp edge is the range. If you declare projection.year.range = '2000,2030' but only have data from 2024 onward, an unbounded-year query makes Athena compute (and attempt to list) prefixes for 25 empty years. It stays correct — empty prefixes contribute zero rows — but planning and S3 listing waste effort, and a SELECT *-style query with no partition predicate becomes a slow, wide fan-out. Walk the diagnosis and fix.

• Symptom. A projected table plans slowly and issues thousands of S3 list calls for a query that returns few rows.
• Root cause. The projection range is far wider than the actual data, and the query lacks a tight partition predicate, so Athena expands the full range.
• Fix. Tighten the range to the real data window (or NOW-2YEARS,NOW for rolling), and require a partition predicate in the query.

Question. Show the before/after range settings and quantify the prefix fan-out for a no-predicate query.

Input.

Setting	Before (too wide)	After (tight)
projection.dt.range	2000-01-01,2030-12-31	NOW-2YEARS,NOW
interval	1,DAYS	1,DAYS
prefixes for no-predicate query	~11,300 days	~730 days
plan-time list attempts	~11,300	~730

Code.

-- BEFORE — range spans 30 years; a bare COUNT(*) fans out to ~11,300 prefixes
ALTER TABLE logs.app_access SET TBLPROPERTIES (
    'projection.dt.type'     = 'date',
    'projection.dt.format'   = 'yyyy-MM-dd',
    'projection.dt.range'    = '2000-01-01,2030-12-31',
    'projection.dt.interval' = '1',
    'projection.dt.interval.unit' = 'DAYS'
);

-- AFTER — rolling 2-year window; NOW keyword tracks the current date
ALTER TABLE logs.app_access SET TBLPROPERTIES (
    'projection.dt.range'    = 'NOW-2YEARS,NOW'
);

-- And require a partition predicate so the range never fully expands
SELECT service, COUNT(*)
FROM   logs.app_access
WHERE  dt BETWEEN date '2026-08-01' AND date '2026-08-17'   -- 17 prefixes
GROUP  BY service;

Step-by-step explanation.

1. The too-wide 2000,2030 range means any query that does not constrain dt forces Athena to compute one prefix per day across 30 years — roughly 11,300 prefixes — and probe S3 for each. Correct, but slow to plan and list-heavy.
2. Changing the range to NOW-2YEARS,NOW uses Athena's relative-date keywords so the window rolls forward automatically and never spans more than ~730 days. NOW is evaluated at query time, so you never edit the table when the calendar turns over.
3. Even with a tight range, a query with no dt predicate still expands the whole window. The durable fix pairs a tight range and a required partition predicate — enforced by convention, or by an injected-style discipline where the dashboard always passes a date window.
4. The BETWEEN date '2026-08-01' AND date '2026-08-17' predicate resolves to 17 computed prefixes; Athena reads only those. Planning is proportional to the queried window, not the declared range, once a predicate is present.
5. The net effect: the range bounds the worst case, and the query predicate bounds the actual case. Keep both tight and projection never fans out.

Output.

Query	Range setting	Prefixes computed	Plan cost
no dt predicate	2000,2030	~11,300	slow, list-heavy
no dt predicate	NOW-2YEARS,NOW	~730	bounded
dt 17-day window	either	17	fast
single day	either	1	fastest

Rule of thumb. Set the projection range to the real data window (prefer rolling NOW-NYEARS,NOW), and treat a partition predicate as mandatory on projected tables. The range caps the blast radius; the predicate is what keeps everyday queries fast.

Senior interview question on partition projection

A senior interviewer might ask: "A logs table registered in Glue has grown to 400,000 partitions across many services and years. MSCK REPAIR TABLE takes 20 minutes, new-partition registration lags ingestion, and even simple single-day queries spend seconds in planning. Redesign the table to remove the metastore bottleneck without moving the data, and explain what changes for correctness and for cost."

Solution Using date partition projection to replace MSCK and the GetPartitions round-trip

-- 1. The data already lives at s3://logs/svc/dt=YYYY-MM-DD/ — do NOT move it.
--    Recreate the table definition with projection instead of Glue partitions.
CREATE EXTERNAL TABLE logs.svc_events (
    ts         BIGINT,
    service    STRING,
    level      STRING,
    trace_id   STRING,
    message    STRING
)
PARTITIONED BY (dt STRING)
STORED AS PARQUET
LOCATION 's3://logs/svc/'
TBLPROPERTIES (
    'projection.enabled'          = 'true',
    'projection.dt.type'          = 'date',
    'projection.dt.format'        = 'yyyy-MM-dd',
    'projection.dt.range'         = 'NOW-3YEARS,NOW',
    'projection.dt.interval'      = '1',
    'projection.dt.interval.unit' = 'DAYS',
    'storage.location.template'   = 's3://logs/svc/dt=${dt}'
);

-- 2. Everyday query — single day. Zero metastore calls; one prefix.
SELECT service, COUNT(*) AS errors
FROM   logs.svc_events
WHERE  dt = '2026-08-17' AND level = 'ERROR'
GROUP  BY service
ORDER  BY errors DESC;

-- 3. Range query — 7 days. Seven computed prefixes; still no GetPartitions.
SELECT dt, COUNT(*) AS n
FROM   logs.svc_events
WHERE  dt BETWEEN '2026-08-11' AND '2026-08-17'
GROUP  BY dt;

# 4. Operational change: delete the nightly MSCK job entirely.
#    Before: Airflow task `msck_repair_svc_events` (runs 20 min, lags ingest)
#    After:  no job — partitions are computed; new days are queryable the
#            instant data lands at s3://logs/svc/dt=<new-day>/

Step-by-step trace.

Step	Before (Glue partitions)	After (projection)
Partition source	Glue Data Catalog (400k rows)	computed from table properties
New-partition upkeep	nightly MSCK REPAIR (~20 min)	none (computed)
Planning per query	GetPartitions round-trip, seconds	constant, no catalog call
Single-day query prefixes	1 (after MSCK caught up)	1 (immediately)
Data movement	—	none; same S3 layout
Bytes scanned	unchanged	unchanged

After the redesign, the 400k Glue partitions are irrelevant — Athena computes the one prefix a single-day query needs directly from dt, planning is constant-time, and the 20-minute nightly MSCK job is deleted. New days become queryable the instant their data lands, closing the ingestion-to-queryable lag. Nothing about the read path or bytes scanned changes, so cost per query is identical — the win is planning latency and zero maintenance.

Output:

Metric	Before	After
Partition resolution	GetPartitions (400k)	computed
MSCK maintenance	~20 min nightly	none
Planning latency (1-day query)	seconds	milliseconds
Ingestion-to-queryable lag	up to a day	none
Bytes scanned	baseline	identical

Why this works — concept by concept:

• Partition projection — declaring projection.enabled moves partition resolution from a Glue catalog lookup to an arithmetic computation over table properties, so planning cost is independent of partition count.
• Date projection range + interval — type=date with format, range=NOW-3YEARS,NOW, and interval=1 DAYS tells Athena the exact set of day-values it may compute; NOW rolls the window without DDL edits.
• storage.location.template — s3://logs/svc/dt=${dt} maps each computed dt back to its S3 prefix, so the existing on-disk layout is reused with no data movement.
• No GetPartitions round-trip — because values are computed, Athena never calls the catalog, which is what removes both the per-query planning latency and the nightly MSCK maintenance.
• Read path unchanged — projection changes planning, not reading; bytes scanned and therefore cost per query are identical to the Glue-partitioned table, so this is a pure latency/maintenance win.
• Cost — O(1) planning regardless of partition count, versus O(partitions) for a catalog enumeration; maintenance drops from a 20-minute nightly job to zero. The read cost stays O(bytes the predicate selects).

OPTIMIZATION
Topic — optimization
Partition-pruning and planning-cost optimization problems

Practice →

SQL Topic — sql SQL partitioning and DDL problems

Practice →

---

3. Columnar formats + CTAS cost tuning

CTAS rewrites raw rows into partitioned, compressed Parquet — so a query reads only the columns and row-groups it needs

The mental model in one line: row formats (CSV, JSON) force Athena to read every byte of every row even when a query touches two columns, while columnar formats (Parquet, ORC) plus compression store each column contiguously with per-row-group min/max statistics — so CTAS (CREATE TABLE AS SELECT) is the curation step that rewrites the raw zone once into partitioned, bucketed, compressed Parquet that downstream queries hit instead, turning a full-file scan into a read of just the referenced columns in just the matching row-groups. It is the second order-of-magnitude lever after partitioning, and INSERT INTO keeps the curated table fresh without a full rebuild.

Why columnar beats row format for analytics.

• Column pruning. A SELECT a, b over a 40-column Parquet table reads two column-chunks per row-group; the same query over CSV reads every column of every row because the engine cannot seek to a column mid-line. This alone is often 10–20×.
• Row-group skipping. Parquet stores min/max per column per row-group. A WHERE amount > 1000 predicate skips whole row-groups whose max is ≤ 1000 without decompressing them. Effectiveness depends on data being sorted/clustered on the predicate column.
• Compression. Snappy (fast, moderate ratio) or ZSTD (slower, better ratio) shrink the bytes on disk, and Athena bills the compressed bytes read. Columnar data compresses far better than row data because each column is homogeneous.
• Predicate + projection pushdown. Athena pushes both the column list and simple predicates down into the Parquet reader, so pruning happens before bytes leave S3.

The CTAS knobs — what every curation query sets.

• format. 'PARQUET' (default choice) or 'ORC'. Both columnar; Parquet is the lake default.
• parquet_compression. 'SNAPPY' (default, balanced) or 'ZSTD' / 'GZIP' for smaller files at more CPU.
• partitioned_by. ARRAY['dt'] — partition columns must be the last columns in the SELECT. CTAS writes at most 100 partitions per statement; more requires INSERT INTO batches or a bucketed approach.
• bucketed_by + bucket_count. Hash-bucket a high-cardinality join/filter key (e.g. user_id) into a fixed file count so equality lookups read one bucket instead of all files.
• external_location. Where the new files land; must be an empty/new prefix for CTAS.

Common interview probes on CTAS.

• "Why CTAS instead of querying raw?" — required answer: columnar + partition + compression cuts bytes scanned by orders of magnitude on repeating queries.
• "What is the CTAS partition limit?" — 100 partitions per CTAS statement; use INSERT INTO for the rest.
• "How do you keep the curated table fresh?" — INSERT INTO ... SELECT ... WHERE dt = new_day incremental append.
• "When do you bucket?" — high-cardinality equality-join or filter keys, to avoid scanning all files for one value.
• "What's the small-files trap?" — thousands of tiny files add per-object overhead and defeat row-group skipping; compact via CTAS rewrite or Iceberg OPTIMIZE.

Worked example — CTAS from raw CSV to partitioned Parquet

Detailed explanation. The canonical curation step: raw orders land as CSV, and a CTAS rewrites them into dt-partitioned, Snappy-compressed Parquet. Measure the bytes-scanned difference for a typical two-column aggregation. Build the CTAS and the before/after query.

• Raw. s3://lake-raw/orders_csv/ — one year, ~800 GB CSV, 25 columns.
• Curated. s3://lake-curated/orders_parquet/ — Parquet, Snappy, partitioned by dt.
• Test query. "Daily revenue for last 30 days" — touches dt and amount.

Question. Write the CTAS and quantify the bytes scanned before (CSV) and after (Parquet) for the 30-day revenue query.

Input.

Property	Value
Source	raw.orders_csv (CSV, 25 cols, ~800 GB)
Target format	Parquet + Snappy
Partition	dt (date string)
Test query columns	dt, amount
Window	30 days of 365

Code.

-- Curate: CSV -> partitioned, compressed Parquet (partition col LAST)
CREATE TABLE curated.orders_parquet
WITH (
    format              = 'PARQUET',
    parquet_compression = 'SNAPPY',
    partitioned_by      = ARRAY['dt'],
    external_location   = 's3://lake-curated/orders_parquet/'
) AS
SELECT
    order_id,
    customer_id,
    amount,
    status,
    -- ... other columns ...
    order_date AS dt          -- partition column must be last
FROM   raw.orders_csv
WHERE  order_date >= '2025-08-01' AND order_date < '2025-11-01';  -- <= 100 partitions

-- Same revenue query, both layouts
-- BEFORE (CSV): scans all 25 columns of ~30/365 of 800 GB (no col prune) => ~66 GB
SELECT dt, SUM(amount) AS revenue
FROM   raw.orders_csv
WHERE  order_date BETWEEN '2026-07-18' AND '2026-08-17'
GROUP  BY dt;

-- AFTER (Parquet): scans 2 columns of 30 day-partitions => ~0.4 GB
SELECT dt, SUM(amount) AS revenue
FROM   curated.orders_parquet
WHERE  dt BETWEEN '2026-07-18' AND '2026-08-17'
GROUP  BY dt;

Step-by-step explanation.

1. The CTAS lists dt (aliased from order_date) as the final column because partitioned_by requires partition columns at the end of the SELECT. Getting the order wrong is a hard error, not a silent bug.
2. The WHERE order_date >= ... AND < ... in the CTAS keeps the statement under the 100-partition limit — three months is ~92 day-partitions. To curate a full year you run three or four CTAS/INSERT INTO batches, not one CTAS.
3. The CSV query cannot prune columns: even though it needs only dt and amount, Athena reads all 25 columns of every row in the 30 matching days (~66 GB). CSV has no column boundaries to seek to.
4. The Parquet query reads only the amount column-chunks (plus the partition value, which is free — it is encoded in the path) across the 30 day-partitions, roughly 0.4 GB. Two columns of 30 days versus 25 columns of 30 days is the ~150× column-and-compression win.
5. Both queries return identical daily revenue; only the bytes scanned — and therefore the cost and latency — differ. The CTAS is paid once; the query saving repeats every run.

Output.

Query	Format	Columns read	Bytes scanned	Approx cost
revenue, 30 days	CSV	25 of 25	~66 GB	~$0.33
revenue, 30 days	Parquet+Snappy	1 of 25	~0.4 GB	~$0.002

Rule of thumb. Curate raw text into partitioned Snappy Parquet with CTAS, keep each CTAS under 100 partitions (batch the rest with INSERT INTO), and always list partition columns last. Point dashboards at the curated table; leave the raw zone for reprocessing only.

Worked example — bucketing a high-cardinality lookup key

Detailed explanation. Partitioning by a high-cardinality key (like user_id) would create millions of tiny partitions — a disaster. Instead, bucket the key: hash it into a fixed number of files so an equality lookup reads one bucket instead of scanning every file in the partition. Build a bucketed sessions table and show the single-user lookup win.

• Table. sessions(user_id, session_id, dt, ...), partitioned by dt, bucketed by user_id into 64 buckets.
• Query. "All sessions for one user_id on one day."
• Win. Reads 1 of 64 bucket-files in that day-partition instead of all 64.

Question. Write the bucketed CTAS and show how a single-user lookup prunes to one bucket file.

Input.

Property	Value
Partition	dt
Bucket key	user_id
Bucket count	64
Lookup	one user_id, one dt

Code.

CREATE TABLE curated.sessions_bucketed
WITH (
    format              = 'PARQUET',
    parquet_compression = 'SNAPPY',
    partitioned_by      = ARRAY['dt'],
    bucketed_by         = ARRAY['user_id'],
    bucket_count        = 64,
    external_location   = 's3://lake-curated/sessions_bucketed/'
) AS
SELECT session_id, user_id, duration_s, device, dt
FROM   raw.sessions
WHERE  dt = '2026-08-17';

-- Single-user lookup: prunes to 1 day-partition AND 1 of 64 buckets
SELECT session_id, duration_s
FROM   curated.sessions_bucketed
WHERE  dt = '2026-08-17'
  AND  user_id = 918273;       -- hashes to exactly one bucket file

Step-by-step explanation.

1. bucketed_by = ARRAY['user_id'] with bucket_count = 64 hashes each row's user_id into one of 64 files per partition. All rows for a given user in a given day live in exactly one bucket file.
2. The lookup filters on both dt (partition prune to one day) and user_id (bucket prune to one of 64 files). Athena computes the bucket from the equality predicate and opens only that file.
3. Without bucketing, the same lookup would open all files in the day-partition and rely only on Parquet row-group stats — much weaker when users are interleaved across files.
4. Bucketing shines for equality predicates and bucket-aligned joins (two tables bucketed the same way join without a shuffle). It does not help range predicates on the bucket key, since hashing scatters ranges across buckets.
5. Choose bucket_count so each bucket file is a healthy size (hundreds of MB, not KB). Too many buckets recreates the small-files problem; too few defeats the pruning.

Output.

Query	Partition prune	Bucket prune	Files read
single user, single day (bucketed)	1 day	1 of 64	1
single user, single day (not bucketed)	1 day	none	all 64
range on user_id (bucketed)	1 day	none (hash scatters)	all 64

Rule of thumb. Bucket a high-cardinality equality key (user, account, device) into a count that keeps each bucket file a few hundred MB; never "partition" by such a key. Bucketing plus partitioning stacks: partition prunes prefixes, bucketing prunes files within the prefix.

Worked example — the small-files problem and INSERT INTO compaction

Detailed explanation. Streaming ingestion or many concurrent INSERT INTO statements can produce thousands of tiny Parquet files. Athena pays per-object open overhead and loses row-group skipping when files are smaller than a row-group, so a "well-formatted" table can still scan slowly. The fix is periodic compaction — a CTAS/INSERT INTO rewrite that coalesces small files into fewer large ones. Walk the diagnosis and fix.

• Symptom. A partitioned Parquet table queries slower than its bytes-scanned suggests; the partition holds ~5,000 files averaging 200 KB.
• Root cause. Per-file open overhead and sub-row-group files defeat columnar skipping.
• Fix. Rewrite the partition with a single INSERT INTO ... SELECT (or CTAS to a new location) so files land at ~128–512 MB.

Question. Compact a small-files partition and show the file-count and effective-scan improvement.

Input.

Metric	Before	After
Files in partition	~5,000	~8
Avg file size	~200 KB	~256 MB
Row-group skipping	ineffective	effective
Per-object overhead	high	low

Code.

-- 1. Rewrite one day's partition into a few large files.
--    Stage into a fresh table, then swap the partition location.
CREATE TABLE curated.orders_compact_20260817
WITH (
    format              = 'PARQUET',
    parquet_compression = 'ZSTD',
    external_location   = 's3://lake-curated/orders_compact/dt=2026-08-17/'
) AS
SELECT order_id, customer_id, amount, status
FROM   curated.orders_parquet
WHERE  dt = '2026-08-17';

-- 2. Point the partition at the compacted location (metadata-only)
ALTER TABLE curated.orders_parquet
  PARTITION (dt = '2026-08-17')
  SET LOCATION 's3://lake-curated/orders_compact/dt=2026-08-17/';

-- 3. Drop the tiny source files after verifying counts match.

# Rule of thumb for target file size / count per partition
def target_file_count(partition_bytes: int, target_file_mb: int = 256) -> int:
    target = target_file_mb * 1024 * 1024
    return max(1, round(partition_bytes / target))

print(target_file_count(2_147_483_648))   # ~2 GB partition -> 8 files
# → 8

Step-by-step explanation.

1. The CTAS re-reads the fragmented partition and writes it back as a handful of large ZSTD files. Reading 5,000 tiny files sequentially is dominated by open/close overhead; reading 8 large files is not.
2. Large files carry full-size row-groups, so min/max statistics regain their pruning power — a WHERE amount > 1000 can skip row-groups it could not skip when each file was smaller than a row-group.
3. ALTER TABLE ... PARTITION ... SET LOCATION repoints the partition to the compacted prefix as a metadata operation; no data is copied by the swap itself.
4. The target_file_count helper sizes the rewrite: aim for ~128–512 MB files. Too-large files hurt parallelism; too-small files recreate the problem.
5. This manual compaction is exactly the maintenance that Apache Iceberg automates with OPTIMIZE (section 4) — which is a strong reason to move mutable, frequently-appended tables to Iceberg.

Output.

Metric	Before	After
Files scanned (1 day)	~5,000	~8
Open/close overhead	dominant	negligible
Row-group skipping	defeated	restored
Query latency	slow for the bytes	proportional to bytes

Rule of thumb. Keep Parquet files in the ~128–512 MB range; compact small-file partitions with a periodic CTAS/INSERT INTO rewrite, or move the table to Iceberg and let OPTIMIZE compact for you. Small files are the silent tax that makes a columnar table scan like a row one.

Senior interview question on CTAS cost tuning

A senior interviewer might ask: "Analysts query a raw CSV events table directly and each dashboard refresh scans over a terabyte. Design a curated layer with CTAS that cuts scan cost by at least 30×, keep it fresh as new data lands, and explain how you would handle the CTAS 100-partition limit and the small-files risk from incremental loads."

Solution Using a CTAS curated layer with INSERT INTO catch-up and periodic compaction

-- 1. Initial curation — batch by quarter to stay under 100 partitions/CTAS
CREATE TABLE curated.events
WITH (
    format              = 'PARQUET',
    parquet_compression = 'SNAPPY',
    partitioned_by      = ARRAY['dt'],
    external_location   = 's3://lake-curated/events/'
) AS
SELECT event_id, user_id, event_type, amount, dt
FROM   raw.events_csv
WHERE  dt >= '2026-01-01' AND dt < '2026-04-01';    -- ~90 partitions

-- Repeat INSERT INTO for each subsequent quarter (INSERT INTO has no 100 cap)
INSERT INTO curated.events
SELECT event_id, user_id, event_type, amount, dt
FROM   raw.events_csv
WHERE  dt >= '2026-04-01' AND dt < '2026-07-01';

-- 2. Daily incremental catch-up (one partition; cheap append)
INSERT INTO curated.events
SELECT event_id, user_id, event_type, amount, dt
FROM   raw.events_csv
WHERE  dt = date_format(current_date - interval '1' day, '%Y-%m-%d');

-- 3. Weekly compaction of the last 7 days into few large files
CREATE TABLE curated.events_compact_tmp
WITH (format='PARQUET', parquet_compression='ZSTD',
      partitioned_by=ARRAY['dt'],
      external_location='s3://lake-curated/events_compact/') AS
SELECT event_id, user_id, event_type, amount, dt
FROM   curated.events
WHERE  dt >= date_format(current_date - interval '7' day, '%Y-%m-%d');
-- then ALTER each rewritten partition's LOCATION to the compacted prefix

Step-by-step trace.

Layer	Component	Purpose
Initial CTAS	quarter batches, Parquet+Snappy	rewrite history under 100-partition cap
Backfill	INSERT INTO per quarter	no partition cap; completes the history
Daily catch-up	INSERT INTO one dt	keep curated table fresh, O(one day)
Weekly compaction	CTAS rewrite + SET LOCATION	coalesce small daily files into ~256 MB
Consumer queries	SELECT ... WHERE dt ... on curated	partition + column prune

After the build, dashboards read the curated Parquet table: a query that scanned ~1.2 TB of CSV now scans ~30–40 GB of partitioned columnar data — a >30× cut — while the daily INSERT INTO keeps it current and the weekly compaction stops small files from creeping back in. The CTAS 100-partition cap is handled by batching the initial load; the small-files risk is handled by the compaction job.

Output:

Metric	Before (raw CSV)	After (curated)
Bytes scanned per dashboard refresh	~1.2 TB	~30–40 GB
Approx cost per refresh	~$6.00	~$0.18
Freshness	live-on-raw	+1 day (nightly catch-up)
Files per partition	n/a	~4–8 (post-compaction)
Scan-cost reduction	1×	>30×

Why this works — concept by concept:

• CTAS curation — one rewrite turns row-format CSV into columnar, compressed, partitioned Parquet; the cost is paid once and every downstream query benefits.
• 100-partition batching — CTAS writes at most 100 partitions per statement, so the initial history is loaded in quarter-sized batches and completed with uncapped INSERT INTO statements.
• INSERT INTO catch-up — appending only yesterday's partition keeps the curated table fresh at O(one day) cost instead of rebuilding the whole table nightly.
• Periodic compaction — a weekly CTAS rewrite coalesces the many small daily files into a few ~256 MB files, restoring row-group skipping and removing per-object overhead.
• Partition + column pruning on read — consumer queries filter on dt (prefix prune) and select few columns (column-chunk prune), so they read tens of GB instead of over a TB.
• Cost — one-time O(history) CTAS + O(one day) daily append + O(7 days) weekly compaction; every dashboard refresh drops from O(all columns of all rows) to O(few columns of the queried window), a >30× steady-state saving.

SQL
Topic — sql
SQL CTAS and table-creation problems

Practice →

DATA TRANSFORMATION Topic — data-transformation Data transformation and reformatting problems

Practice →

---

4. Apache Iceberg tables in Athena

Apache Iceberg adds ACID transactions, row-level MERGE, and time travel on top of the lake — the things a Hive external table cannot do

The mental model in one line: Apache Iceberg is an open table format that layers a transactional metadata tree (snapshots, manifests, data files) over Parquet in S3, and Athena speaks it natively — so a plain external table's "read-only, append-by-rewrite, no history" limitations are replaced by ACID commits, row-level MERGE/UPDATE/DELETE, hidden partitioning, schema evolution, and snapshot time travel, all while queries still prune by partition and column exactly like a well-tuned Parquet table. It is the answer whenever a lake table must be mutated correctly — CDC apply, GDPR deletes, late-arriving corrections — rather than only appended.

What Iceberg gives you that Hive external tables do not.

• ACID commits. Each write produces a new immutable snapshot committed atomically; readers always see a consistent snapshot, never a half-written state. Concurrent writers are serialised by optimistic concurrency on the metadata pointer.
• Row-level MERGE/UPDATE/DELETE. You can upsert and delete individual rows. Iceberg records the change as new data files plus delete files (position or equality deletes) and merges them at read time — no full-table rewrite per change.
• Hidden partitioning. You declare a partition transform (day(ts), bucket(16, id)) once; Athena derives the partition value automatically, so queries filter on the raw column (WHERE ts >= ...) and still prune. No more dt-as-a-separate-column ceremony, and repartitioning does not rewrite old data.
• Snapshot time travel. Query the table FOR TIMESTAMP AS OF or FOR VERSION AS OF to read a past snapshot, and roll back to one if a bad write lands.
• Schema evolution. Add, drop, rename, or reorder columns by column id, not position, so old files stay readable without rewrite.

The maintenance operations — non-optional at volume.

• OPTIMIZE ... REWRITE DATA USING BIN_PACK. Compacts small data files and merges delete files into the data (removing the read-time merge cost). This is the automated version of the manual compaction from section 3.
• VACUUM. Expires snapshots older than the table's retention and deletes the now-unreferenced data/manifest files. Without VACUUM, every historical snapshot's files linger, storage grows, and metadata planning slows.
• The cadence. OPTIMIZE after heavy write bursts (or on a schedule); VACUUM on a retention that matches how far back you need time travel (e.g. keep 7 days).

Common interview probes on Iceberg.

• "Why Iceberg over a Hive external table?" — required answer: ACID + row-level upsert/delete + time travel; Hive tables are append-only and read-inconsistent under concurrent writes.
• "How does Iceberg do updates without rewriting everything?" — new data files + delete files, merged at read time; OPTIMIZE later collapses them.
• "What does VACUUM do and why is it required?" — expires old snapshots and reclaims files; skipping it bloats storage and slows planning.
• "How does hidden partitioning differ from Hive?" — you filter on the source column and Iceberg prunes via the recorded transform; no separate partition column, and partitioning can evolve without rewriting history.

Worked example — MERGE upsert for a CDC apply

Detailed explanation. The signature Iceberg operation: apply a batch of change-data-capture rows (inserts + updates) into a target dimension with a single MERGE INTO. This is impossible on a Hive external table without rewriting whole partitions. Build the Iceberg table and the merge.

• Target. dw.customers Iceberg table (customer dimension).
• Source. staging.customers_cdc — a batch of changed rows with the latest values.
• Rule. Match on customer_id; update on match, insert on no-match.

Question. Create the Iceberg table and write the MERGE INTO that upserts the CDC batch.

Input.

Table	Role	Key
dw.customers	Iceberg target dimension	customer_id
staging.customers_cdc	change batch (insert/update rows)	customer_id
Match action	update all columns	—
No-match action	insert row	—

Code.

-- 1. Create the Iceberg target (note TABLE_TYPE + hidden partition transform)
CREATE TABLE dw.customers (
    customer_id  BIGINT,
    name         STRING,
    tier         STRING,
    country      STRING,
    updated_at   TIMESTAMP
)
PARTITIONED BY (bucket(16, customer_id))          -- hidden partitioning transform
LOCATION 's3://lake-curated/iceberg/customers/'
TBLPROPERTIES (
    'table_type'            = 'ICEBERG',
    'format'                = 'PARQUET',
    'write_compression'     = 'ZSTD'
);

-- 2. Upsert the CDC batch in one atomic MERGE
MERGE INTO dw.customers AS t
USING staging.customers_cdc AS s
   ON t.customer_id = s.customer_id
WHEN MATCHED THEN UPDATE SET
        name       = s.name,
        tier       = s.tier,
        country    = s.country,
        updated_at = s.updated_at
WHEN NOT MATCHED THEN INSERT (customer_id, name, tier, country, updated_at)
        VALUES (s.customer_id, s.name, s.tier, s.country, s.updated_at);

Step-by-step explanation.

1. table_type = 'ICEBERG' in TBLPROPERTIES is what makes this an Iceberg table rather than a Hive external one; it unlocks MERGE, UPDATE, DELETE, time travel, and OPTIMIZE/VACUUM.
2. PARTITIONED BY (bucket(16, customer_id)) is a hidden-partitioning transform: Iceberg buckets rows by customer_id internally, and queries that filter on customer_id prune automatically — you never manage a separate partition column.
3. The MERGE INTO ... ON t.customer_id = s.customer_id joins target to source on the business key. WHEN MATCHED updates existing customers; WHEN NOT MATCHED inserts new ones. The whole statement commits as one atomic snapshot.
4. Under the hood, matched updates are written as new data files plus delete files marking the superseded rows; readers merge them at query time so they always see the latest version. No partition is rewritten wholesale.
5. Because the commit is atomic, a reader running concurrently sees either the pre-merge snapshot or the post-merge snapshot — never a partially-applied batch. This is the ACID guarantee a Hive table cannot make.

Output.

customer_id	Pre-merge	CDC batch	Post-merge
101	tier=silver	tier=gold	tier=gold (updated)
102	(absent)	insert	present (inserted)
103	tier=bronze	(not in batch)	tier=bronze (unchanged)

Rule of thumb. For any lake table that must absorb updates or deletes — CDC dimensions, GDPR erasure, correction batches — use an Iceberg table and MERGE INTO, not a Hive external table with partition overwrites. The MERGE is atomic, row-level, and does not rewrite untouched partitions.

Worked example — OPTIMIZE compaction and VACUUM snapshot expiry

Detailed explanation. Every MERGE/INSERT produces new files and (for merges) delete files. Left alone, an actively-written Iceberg table accumulates many small data files and delete files that must be merged at read time — scan cost creeps up. OPTIMIZE compacts and resolves deletes; VACUUM expires old snapshots and reclaims their files. Run both on a schedule.

• OPTIMIZE. Bin-packs small files into large ones and applies pending deletes so reads stop paying the merge cost.
• VACUUM. Removes snapshots older than vacuum_max_snapshot_age_seconds and deletes files no live snapshot references.
• Retention. Set retention to the time-travel window you actually need (e.g. 7 days).

Question. Write the maintenance statements and show how scan cost and storage change after each.

Input.

Operation	Effect	When
OPTIMIZE ... REWRITE DATA	compact small files, apply deletes	after write bursts / hourly
set retention property	bound snapshot history	once
VACUUM	expire old snapshots, reclaim files	daily

Code.

-- 1. Compact small data files and merge pending delete files
OPTIMIZE dw.customers REWRITE DATA USING BIN_PACK;

-- 2. Bound how long snapshots (and thus time travel) are retained
ALTER TABLE dw.customers SET TBLPROPERTIES (
    'vacuum_max_snapshot_age_seconds' = '604800'   -- keep 7 days of snapshots
);

-- 3. Expire old snapshots and delete unreferenced files
VACUUM dw.customers;

-- Inspect what maintenance did (Iceberg metadata tables)
SELECT COUNT(*) AS data_files      FROM "dw"."customers$files";
SELECT COUNT(*) AS live_snapshots  FROM "dw"."customers$snapshots";

Step-by-step explanation.

1. OPTIMIZE ... REWRITE DATA USING BIN_PACK reads many small files and pending delete files for each partition and writes a few large, delete-resolved data files. After it runs, reads no longer pay the position/equality-delete merge cost, and row-group skipping works on the large files.
2. Setting vacuum_max_snapshot_age_seconds = 604800 declares a 7-day retention: snapshots older than that become eligible for expiry. This is the knob that trades time-travel depth against storage and metadata size.
3. VACUUM expires snapshots past the retention and physically deletes the data/manifest files no remaining snapshot references. Without it, every MERGE you have ever run keeps its superseded files forever.
4. The $files and $snapshots metadata tables let you verify the effect: file count drops sharply after OPTIMIZE; live-snapshot count drops to the retention window after VACUUM.
5. Cadence matters: OPTIMIZE too rarely lets small files/deletes pile up (slow reads); VACUUM too rarely bloats storage and slows planning. Both too often waste write cost. Tie OPTIMIZE to write volume and VACUUM to the retention you promised.

Output.

Metric	Before maintenance	After OPTIMIZE + VACUUM
Data files (partition)	~1,200 small + deletes	~6 large
Read-time delete merge	required	none (applied)
Live snapshots	all history	last 7 days
Reclaimed storage	0	superseded files freed

Rule of thumb. Treat OPTIMIZE and VACUUM as mandatory scheduled jobs for any written Iceberg table — OPTIMIZE keyed to write volume, VACUUM keyed to your time-travel retention. An unmaintained Iceberg table slowly degrades into the small-files problem it was supposed to solve.

Worked example — time travel and rollback after a bad write

Detailed explanation. A batch job accidentally double-applies a CDC batch, corrupting tier for thousands of customers. On a Hive table this is a restore-from-backup incident; on Iceberg it is a two-minute rollback, because every commit is a snapshot you can read and revert to. Walk the recovery.

• Detect. Compare current counts against a pre-batch snapshot with FOR TIMESTAMP AS OF.
• Recover. FOR VERSION AS OF (or the console) to read the good snapshot; roll back the table pointer to it.
• Guarantee. Rollback is a metadata pointer move — instant, no data copy — as long as VACUUM has not yet expired the good snapshot.

Question. Diagnose the corruption against a prior snapshot and roll the table back.

Input.

Step	Mechanism
find good snapshot	SELECT * FROM "dw"."customers$snapshots"
read past state	FOR TIMESTAMP AS OF
revert	ALTER TABLE ... EXECUTE ROLLBACK (or set current snapshot)

Code.

-- 1. List snapshots to find the last good one (committed_at before the bad batch)
SELECT snapshot_id, committed_at, operation
FROM   "dw"."customers$snapshots"
ORDER  BY committed_at DESC
LIMIT  10;

-- 2. Confirm the good snapshot has the correct data (time travel by timestamp)
SELECT tier, COUNT(*) AS n
FROM   dw.customers FOR TIMESTAMP AS OF TIMESTAMP '2026-08-17 02:00:00 UTC'
GROUP  BY tier;

-- 3. Compare against 'now' to quantify the corruption
SELECT tier, COUNT(*) AS n
FROM   dw.customers
GROUP  BY tier;

-- 4. Roll the table back to the good snapshot (metadata pointer move)
ALTER TABLE dw.customers EXECUTE ROLLBACK (7412559835021003456);

Step-by-step explanation.

1. The $snapshots metadata table lists every commit with its snapshot_id, committed_at, and operation (append, overwrite, delete). You find the snapshot committed just before the bad batch ran.
2. FOR TIMESTAMP AS OF TIMESTAMP '...' reads the table exactly as it was at that instant, so you can validate the good state without touching the current table — the corrupted rows are not visible in that snapshot.
3. Comparing the time-travel counts against the current counts quantifies the blast radius (e.g. 4,000 customers wrongly flipped to gold), which you record for the incident review.
4. ALTER TABLE ... EXECUTE ROLLBACK (<snapshot_id>) repoints the table's current snapshot to the good one. Because Iceberg snapshots are immutable and the good data files still exist, this is a metadata-only operation — instant, no data movement.
5. The one precondition: the good snapshot must still be live, i.e. VACUUM has not expired it. This is why your VACUUM retention must be at least as long as your realistic detect-and-recover window.

Output.

Snapshot	tier=gold count	State
pre-batch (good)	1,020	correct
current (corrupted)	5,020	+4,000 wrong
after rollback	1,020	restored

Rule of thumb. Iceberg turns "bad write" from a restore-from-backup incident into a snapshot rollback — but only within your VACUUM retention window. Size that retention to cover how long a bad write can plausibly go undetected, and keep the $snapshots query in your runbook.

Senior interview question on Apache Iceberg in Athena

A senior interviewer might ask: "You need a lake table that ingests a CDC feed with inserts, updates, and deletes, supports GDPR row deletion, lets analysts query yesterday's state after a bad load, and still prunes efficiently. Design it on Apache Iceberg in Athena — the DDL, the upsert, the maintenance schedule, and the recovery story — and explain what would break if you used a Hive external table instead."

Solution Using an Iceberg table with MERGE upsert, scheduled OPTIMIZE/VACUUM, and time-travel recovery

-- 1. Iceberg table with a hidden day() partition transform on the event time
CREATE TABLE dw.orders (
    order_id     BIGINT,
    customer_id  BIGINT,
    status       STRING,
    amount       DECIMAL(12,2),
    event_ts     TIMESTAMP,
    is_deleted   BOOLEAN
)
PARTITIONED BY (day(event_ts))                    -- filter on event_ts, prune automatically
LOCATION 's3://lake-curated/iceberg/orders/'
TBLPROPERTIES (
    'table_type'                      = 'ICEBERG',
    'format'                          = 'PARQUET',
    'write_compression'               = 'ZSTD',
    'vacuum_max_snapshot_age_seconds' = '604800'   -- 7-day time-travel window
);

-- 2. Apply the CDC batch: upsert changed rows, hard-delete tombstones
MERGE INTO dw.orders AS t
USING staging.orders_cdc AS s
   ON t.order_id = s.order_id
WHEN MATCHED AND s.op = 'D' THEN DELETE
WHEN MATCHED AND s.op IN ('I','U') THEN UPDATE SET
        status = s.status, amount = s.amount, event_ts = s.event_ts
WHEN NOT MATCHED AND s.op IN ('I','U') THEN
        INSERT (order_id, customer_id, status, amount, event_ts, is_deleted)
        VALUES (s.order_id, s.customer_id, s.status, s.amount, s.event_ts, false);

-- 3. GDPR erasure — a real row-level delete (impossible on Hive external)
DELETE FROM dw.orders WHERE customer_id = 55123;

-- 4. Scheduled maintenance
OPTIMIZE dw.orders REWRITE DATA USING BIN_PACK;    -- hourly after write bursts
VACUUM   dw.orders;                                -- daily; honours 7-day retention

-- 5. Recovery after a bad load — read yesterday, then roll back if needed
SELECT COUNT(*) FROM dw.orders
  FOR TIMESTAMP AS OF TIMESTAMP '2026-08-17 00:00:00 UTC';
ALTER TABLE dw.orders EXECUTE ROLLBACK (<good_snapshot_id>);

Step-by-step trace.

Layer	Component	Purpose
DDL	table_type=ICEBERG + day(event_ts)	ACID table, hidden day partitioning
Upsert	MERGE with op-branch	insert/update/delete in one atomic commit
Erasure	DELETE ... WHERE customer_id	row-level GDPR delete
Maintenance	OPTIMIZE + VACUUM	compact files, bound snapshots
Recovery	FOR TIMESTAMP AS OF + ROLLBACK	inspect and revert a bad load
Read	WHERE event_ts >= ...	prunes via the hidden transform

After deployment, the CDC feed applies atomically every batch, GDPR deletes remove individual rows, analysts read any state within the 7-day window, and a bad load is a rollback rather than a restore. A Hive external table would break on all four fronts: no atomic upsert (partition overwrite races), no row-level delete (only whole-partition rewrite), no time travel, and read-inconsistency under concurrent writes.

Output:

Capability	Iceberg table	Hive external table
Row-level upsert (MERGE)	yes, atomic	no (partition overwrite)
Row-level delete (GDPR)	yes	no (rewrite partition)
Time travel / rollback	yes (snapshots)	no
Concurrent-write consistency	ACID snapshots	read-inconsistent
Partition evolution	yes (hidden transform)	rewrite required

Why this works — concept by concept:

• Iceberg table type — table_type=ICEBERG layers a snapshot/manifest metadata tree over Parquet, which is what makes atomic commits, row-level DML, and time travel possible at all.
• MERGE with op-branch — one MERGE INTO applies insert, update, and delete from a CDC batch in a single atomic snapshot, so readers never see a partially-applied batch.
• Hidden day() partitioning — declaring day(event_ts) lets queries filter on the raw event_ts and still prune, and lets partitioning evolve later without rewriting historical data.
• OPTIMIZE + VACUUM — OPTIMIZE compacts small files and resolves delete files so reads stay cheap; VACUUM expires snapshots past the 7-day retention and reclaims storage.
• Time-travel rollback — immutable snapshots make FOR TIMESTAMP AS OF inspection and EXECUTE ROLLBACK a metadata pointer move, turning a bad load into an instant revert within the retention window.
• Cost — writes are O(changed rows) plus periodic O(partition) compaction, versus O(whole partition) rewrite per change on Hive; reads stay O(bytes the predicate selects). The metadata overhead is small and bounded by VACUUM.

DATA TRANSFORMATION
Topic — data-transformation
Upsert, merge, and slowly-changing-data problems

Practice →

ETL Topic — etl ETL problems on CDC apply and table maintenance

Practice →

---

5. Federated queries + workgroup cost governance

federated query reaches live sources beyond S3 through Lambda connectors; workgroups cap the bytes each query is allowed to scan

The mental model in one line: a federated query runs a purpose-built AWS Lambda data source connector that lets one Athena SQL statement read (and join) data living outside S3 — a dimension in RDS, a lookup in DynamoDB, recent events in CloudWatch Logs, a table in Redshift — without first ETL-copying it into the lake, while a workgroup is the governance boundary that isolates a team's queries and results and enforces cost through a per-query data scanned control limit, query-result reuse, and CloudWatch metrics. Federation extends Athena's reach; workgroups bound its spend; senior engineers are expected to wire both.

How federated query works.

• The connector. A Lambda function (deployed from the Athena Federated Query SDK or a prebuilt connector) that translates Athena's requests into the source's API and streams rows back. One connector per source type (JDBC for RDS/MySQL/Postgres, DynamoDB, DocumentDB, CloudWatch, Redshift, etc.).
• The catalog. You register the connector as a data source / catalog in Athena; tables under it are referenced as "lambda:<catalog>".schema.table (or via a named catalog).
• Predicate + projection pushdown. Good connectors push WHERE filters and column lists into the source so only the needed rows/columns cross the wire. A connector that cannot push down forces a full source scan — the federated equivalent of the small-files trap.
• Cost note. Federated scans are billed differently (connector Lambda + data transfer), and S3 spill for large intermediate results counts too. Federation is for reach, not for moving big data — join a small live dimension to a big S3 fact, not the reverse.

What a workgroup controls.

• Per-query data-scanned limit. A control limit that cancels any single query scanning more than N bytes — the hard ceiling that stops an accidental SELECT * from billing a fortune.
• Result location + encryption. Every query's results land in the workgroup's configured S3 location with its encryption settings; results are isolated per team.
• Query-result reuse. Re-running an identical query within the configured max age (up to 7 days) returns the cached result and reports zero bytes scanned.
• Metrics + enforcement. Per-workgroup CloudWatch metrics (DataScannedInBytes, query counts) for alerting, plus the option to force workgroup settings so users cannot override the result location or limits.

Common interview probes on federation + workgroups.

• "When do you federate vs ETL-copy?" — required answer: federate a small, live source you need occasionally; ETL-copy when you query it heavily or it is large.
• "What is the federated performance risk?" — a connector without predicate pushdown scans the whole source; verify pushdown before relying on it.
• "How do you stop a runaway query?" — workgroup per-query data-scanned control limit; it cancels the query at the ceiling.
• "How do you attribute Athena cost per team?" — separate workgroups + tags + per-workgroup CloudWatch metrics.
• "How do you make repeated dashboard queries free?" — enable query-result reuse in the workgroup.

Worked example — join an S3 fact to a live DynamoDB dimension

Detailed explanation. A fraud dashboard needs to join a big S3 transactions fact against the current account-status dimension, which lives in DynamoDB and changes constantly. Copying DynamoDB into the lake would make the status stale; a federated query reads it live. Wire the connector and write the join.

• Fact. lake.transactions — partitioned Parquet in S3 (large).
• Dimension. accounts in DynamoDB (small, live) via the DynamoDB connector.
• Join. transactions.account_id = accounts.account_id, filter to flagged accounts.

Question. Register the DynamoDB connector as a catalog and write the federated join, ensuring the small side is DynamoDB and the predicate pushes down.

Input.

Component	Value
S3 fact	lake.transactions (Parquet, partitioned)
Live dimension	DynamoDB table accounts
Connector	Athena DynamoDB connector (Lambda)
Catalog name	ddb
Pushdown	filter status = 'FLAGGED' into DynamoDB

Code.

-- (One-time) the DynamoDB connector Lambda is deployed and registered
-- as an Athena data source / catalog named "ddb".

-- Federated join: big S3 fact  ×  small live DynamoDB dimension
SELECT t.dt,
       t.account_id,
       COUNT(*)        AS txns,
       SUM(t.amount)   AS total
FROM   lake.transactions AS t
JOIN   "ddb".default.accounts AS a
       ON a.account_id = t.account_id
WHERE  t.dt = '2026-08-17'                 -- S3 partition prune
  AND  a.status = 'FLAGGED'                -- pushed down into DynamoDB
GROUP  BY t.dt, t.account_id
ORDER  BY total DESC;

Step-by-step explanation.

1. "ddb".default.accounts references the DynamoDB table through the registered connector catalog. Athena calls the connector Lambda, which queries DynamoDB and streams matching items back as rows.
2. The a.status = 'FLAGGED' predicate is pushed down into DynamoDB by the connector, so only flagged accounts cross the wire — not the whole table. This is the difference between a cheap federated join and a full-table drag.
3. The t.dt = '2026-08-17' predicate prunes the S3 fact to one day-partition as usual; federation does not change how the S3 side is scanned.
4. The join keeps the small, live side in DynamoDB and the big side in S3. Athena streams the small flagged-accounts set and joins it against the pruned S3 partition — the correct shape for federation.
5. Because the dimension is read live, the dashboard reflects the current account status with no ETL lag; a copied dimension would be as stale as the last sync.

Output.

Source	Rows crossing the wire	Why
DynamoDB accounts	only status='FLAGGED'	predicate pushdown
S3 transactions	one day-partition	partition prune
Join result	flagged accounts × their day's txns	small × pruned

Rule of thumb. Federate the small, live, frequently-changing side and keep the big side in S3; always confirm the connector pushes down your filter before shipping. Federation is for reach and freshness, not for moving large tables — if you query a source heavily, ETL-copy it into the lake instead.

Worked example — a workgroup with a data-scanned cutoff and result reuse

Detailed explanation. The analytics team keeps triggering surprise costs. Put them in a dedicated workgroup with a per-query data-scanned control limit (hard cancel above the ceiling) and query-result reuse (repeat dashboard queries scan zero bytes). Configure it and show both behaviours.

• Per-query limit. Cancel any query scanning > 100 GB.
• Result reuse. Reuse identical query results for up to 60 minutes.
• Result location. Team-isolated S3 prefix, settings forced.

Question. Create the workgroup with a data-scanned control limit and result reuse, and show a query being cancelled and a query being reused.

Input.

Setting	Value
Per-query data-scanned limit	100 GB (cancel above)
Query-result reuse	enabled, max age 60 min
Result location	s3://athena-results/analytics/
Enforce workgroup config	true

Code.

# Create the governed workgroup (boto3)
import boto3
athena = boto3.client("athena")

athena.create_work_group(
    Name="analytics",
    Configuration={
        "ResultConfiguration": {
            "OutputLocation": "s3://athena-results/analytics/"
        },
        "EnforceWorkGroupConfiguration": True,          # users cannot override
        "PublishCloudWatchMetricsEnabled": True,
        "BytesScannedCutoffPerQuery": 100 * 1024**3,    # 100 GB hard cancel
    },
)

-- (A) Runaway query — cancelled by the 100 GB cutoff before it bills
--     Error: "Bytes scanned limit was exceeded" (query cancelled)
SELECT * FROM lake.transactions;          -- would scan multiple TB

-- (B) Enable result reuse for repeat dashboard queries (per-query)
--     Second identical run within 60 min scans 0 bytes.
SELECT dt, SUM(amount)
FROM   curated.orders_parquet
WHERE  dt BETWEEN '2026-07-18' AND '2026-08-17'
GROUP  BY dt;

// Result-reuse setting sent with the second, identical query (StartQueryExecution)
{
  "ResultReuseConfiguration": {
    "ResultReuseByAgeConfiguration": { "Enabled": true, "MaxAgeInMinutes": 60 }
  }
}

Step-by-step explanation.

1. create_work_group provisions the analytics workgroup with a result location, forced configuration (EnforceWorkGroupConfiguration=True so users cannot silently redirect results or dodge limits), and CloudWatch metrics.
2. BytesScannedCutoffPerQuery = 100 GB is the hard ceiling: Athena tracks bytes as the query runs and cancels it the instant it crosses 100 GB, so a SELECT * over a multi-TB table is stopped before it can bill more than the cutoff.
3. Query (A) — the bare SELECT * — trips the cutoff and is cancelled with a "bytes scanned limit exceeded" error. The team gets a clear signal instead of a surprise invoice line.
4. Query (B) with ResultReuseByAgeConfiguration enabled: the first run scans and caches; a byte-identical re-run within 60 minutes returns the cached result and reports 0 bytes scanned — the repeating-dashboard win from section 1's decision tree, enforced here at the workgroup/query level.
5. PublishCloudWatchMetricsEnabled emits per-workgroup DataScannedInBytes, so you can alert on the team's spend trend and attribute cost per workgroup for chargeback.

Output.

Query	Data-scanned behaviour	Outcome
SELECT * (multi-TB)	exceeds 100 GB cutoff	cancelled, minimal bill
dashboard query, first run	scans ~0.4 GB	cached
dashboard query, re-run < 60 min	reuses cache	0 bytes, 0 cost
any query	metric published	CloudWatch alert / chargeback

Rule of thumb. Give every team its own workgroup with a per-query data-scanned cutoff sized to its real workload, enable result reuse for dashboards, and force the workgroup configuration so limits cannot be bypassed. The cutoff is your circuit breaker; result reuse is free money on repeat queries; CloudWatch metrics are your per-team cost attribution.

Worked example — federated performance guardrails (pushdown check)

Detailed explanation. The subtle federated failure is a connector that cannot push down a predicate, so a "filtered" federated query actually drags the entire source table across the wire and into an S3 spill. Verify pushdown with EXPLAIN and guard against unbounded federated joins. Walk the check.

• Check. EXPLAIN the federated query and confirm the filter appears at the source scan, not as a post-scan filter.
• Guard. Constrain federated scans (always filter the federated side), and prefer connectors that document predicate pushdown.
• Escape hatch. If a source cannot push down and you query it heavily, ETL-copy it into partitioned Parquet instead.

Question. Show how to confirm pushdown and what to do when a connector cannot push a predicate.

Input.

Situation	Signal	Action
Filter pushed down	filter at source scan node	federate safely
Filter not pushed down	full source scan + spill	add explicit filter / ETL-copy
Heavy repeated federation	connector Lambda cost climbs	ETL-copy into lake

Code.

-- 1. Confirm the predicate reaches the source, not a post-scan filter
EXPLAIN
SELECT a.account_id, a.status
FROM   "ddb".default.accounts AS a
WHERE  a.status = 'FLAGGED';
-- Look for the filter/limit attached to the DynamoDB scan operator.
-- If the plan shows a full scan then a separate Filter node, pushdown FAILED.

-- 2. If pushdown fails and the source is queried often, materialise it:
CREATE TABLE curated.accounts_snapshot
WITH (format='PARQUET', parquet_compression='SNAPPY',
      external_location='s3://lake-curated/accounts_snapshot/') AS
SELECT account_id, status, tier, updated_at
FROM   "ddb".default.accounts;             -- one controlled full read, then query S3

Step-by-step explanation.

1. EXPLAIN reveals whether the status = 'FLAGGED' predicate is attached to the source scan (pushed down) or applied as a separate Filter node after a full scan (not pushed down). This is the single check that separates a cheap federated query from an expensive one.
2. When pushdown works, only flagged rows leave DynamoDB; when it fails, the whole table is read into Athena, filtered, and possibly spilled to S3 — slow and costly, and it grows with the source table.
3. If the connector cannot push down and you query the source repeatedly, the fix is to materialise it: one controlled full read into partitioned Parquet, then query the S3 copy. This trades freshness for cost and predictability.
4. For occasional, freshness-critical reads of a small source, live federation is still right even without perfect pushdown — the source is small enough that a full read is cheap.
5. The decision is the same "federate vs ETL-copy" axis: reach and freshness favour federation; volume and query frequency favour a materialised lake copy.

Output.

Federation case	Pushdown	Right call
small live dimension, filtered	yes	federate
small live dimension, occasional	partial	federate (source is small)
large source, no pushdown	no	ETL-copy to Parquet
heavily-queried source	any	ETL-copy to Parquet

Rule of thumb. Always EXPLAIN a new federated query to confirm the filter pushes down to the source; federate small, live, filtered reads, and materialise anything large or frequently queried into the lake. A federated query without pushdown is a full table copy wearing a WHERE clause.

Senior interview question on federated queries and workgroups

A senior interviewer might ask: "You run a multi-team Amazon Athena account. One team needs to join lake facts against a live RDS dimension; another keeps triggering surprise costs with SELECT *; finance wants per-team cost attribution. Design the workgroup and federation setup — the connectors, the per-query limits, result reuse, and the governance — and explain how you would stop a federated query from dragging an entire source table."

Solution Using per-team workgroups, a JDBC connector with pushdown, and enforced data-scanned limits

# 1. Per-team workgroups with distinct limits + forced config (boto3)
import boto3
athena = boto3.client("athena")

def make_workgroup(name, cutoff_gb, prefix):
    athena.create_work_group(
        Name=name,
        Configuration={
            "ResultConfiguration": {"OutputLocation": f"s3://athena-results/{prefix}/"},
            "EnforceWorkGroupConfiguration": True,
            "PublishCloudWatchMetricsEnabled": True,
            "BytesScannedCutoffPerQuery": cutoff_gb * 1024**3,
        },
        Tags=[{"Key": "team", "Value": name}, {"Key": "cost-center", "Value": prefix}],
    )

make_workgroup("analytics", cutoff_gb=100, prefix="analytics")   # heavy but capped
make_workgroup("bi",        cutoff_gb=20,  prefix="bi")          # dashboards; small

-- 2. Register the RDS Postgres JDBC connector as catalog "rds", then join live
SELECT f.dt, d.segment, SUM(f.amount) AS revenue
FROM   lake.sales AS f
JOIN   "rds".public.customer_dim AS d
       ON d.customer_id = f.customer_id
WHERE  f.dt BETWEEN '2026-08-01' AND '2026-08-17'   -- S3 partition prune
  AND  d.region = 'EU'                              -- pushed down into RDS
GROUP  BY f.dt, d.segment;

-- 3. Repeat BI query with result reuse (0 bytes on re-run within the window)
--    (StartQueryExecution ResultReuseConfiguration MaxAgeInMinutes = 60)
SELECT segment, SUM(revenue) FROM curated.daily_revenue
WHERE dt >= date_add('day', -7, current_date) GROUP BY segment;

# 4. Governance wiring
#  - EnforceWorkGroupConfiguration=true  -> users cannot dodge the cutoff/result loc
#  - BytesScannedCutoffPerQuery          -> hard cancel above the ceiling
#  - Tags (team, cost-center)            -> per-team cost attribution in Cost Explorer
#  - CloudWatch DataScannedInBytes alarm -> alert before month-end surprises
#  - EXPLAIN every federated query       -> confirm predicate pushdown to RDS

Step-by-step trace.

Layer	Component	Purpose
Workgroups	analytics (100 GB) + bi (20 GB)	isolate teams; per-team hard cutoffs
Enforcement	EnforceWorkGroupConfiguration=true	limits + result location cannot be bypassed
Federation	RDS JDBC connector catalog rds	live dimension join, filter pushed down
Reuse	result reuse 60 min on BI queries	repeat dashboards scan 0 bytes
Attribution	workgroup tags + CloudWatch	per-team cost + spend alarms
Pushdown guard	EXPLAIN federated queries	stop full-source drags

After the setup, each team's spend is capped by its workgroup's per-query cutoff and attributed by tag; the analytics team joins lake facts to a live RDS dimension with the region='EU' filter pushed into Postgres so only European customers cross the wire; the BI team's repeating dashboards reuse results at zero cost; and a SELECT * is cancelled at the cutoff before it bills. Finance reads per-workgroup CloudWatch metrics and Cost Explorer tags for chargeback.

Output:

Concern	Mechanism	Result
Surprise SELECT * cost	per-query data-scanned cutoff	query cancelled at ceiling
Live dimension join	JDBC federated connector + pushdown	fresh, small-side-only wire
Repeat dashboard cost	query-result reuse	0 bytes on re-run
Per-team attribution	workgroup tags + CloudWatch	chargeback + alarms
Federated full-scan risk	EXPLAIN pushdown check	drag caught before ship

Why this works — concept by concept:

• Per-team workgroups — separate workgroups isolate query history, results, and limits per team, which is the unit of both governance and cost attribution.
• BytesScannedCutoffPerQuery — a hard per-query ceiling that Athena enforces mid-flight, cancelling a runaway SELECT * before it can bill more than the cutoff.
• EnforceWorkGroupConfiguration — forcing the config means users cannot override the result location or the cutoff, so the governance is real rather than advisory.
• Federated connector + predicate pushdown — a JDBC connector reads the live RDS dimension in place, and pushing the region filter into Postgres keeps the federated read small; EXPLAIN verifies the pushdown.
• Query-result reuse — identical repeat queries return the cached result and scan zero bytes, which is the cheapest possible dashboard.
• Cost — governance is O(config), not O(query); the cutoff bounds worst-case spend per query, tags make attribution O(1), and pushdown keeps federated reads O(matching rows) instead of O(source table).

ETL
Topic — etl
ETL problems on cross-source integration

Practice →

DESIGN
Topic — design
Design problems on query governance and cost control

Practice →

---

Cheat sheet — Amazon Athena cost-tuning recipes

• The one rule. Amazon Athena bills per terabyte of data scanned off S3 (list ~$5/TB, rounded up to 10 MB per query), with no cluster to size — so every optimisation is a way to read fewer bytes, and you measure success with the DataScannedInBytes field from GetQueryExecution / information_schema.query_history, never by guessing from row counts.
• Reduce-bytes decision tree. In impact order: (1) partition on the filter column (or use projection) ~50×; (2) convert row format to Parquet/ORC + compression ~50×; (3) project only the columns you use; (4) compact small files; (5) enable workgroup result reuse for exact repeats (0 bytes). Fix order-of-magnitude levers before percentage levers.
• Partition projection template. TBLPROPERTIES('projection.enabled'='true', 'projection.dt.type'='date', 'projection.dt.format'='yyyy-MM-dd', 'projection.dt.range'='NOW-3YEARS,NOW', 'projection.dt.interval'='1', 'projection.dt.interval.unit'='DAYS', 'storage.location.template'='s3://bucket/tbl/dt=${dt}'). Computes partitions at query time — no MSCK REPAIR, no GetPartitions round-trip. Match digits/format to the exact on-disk keys; keep the range tight; require a partition predicate.
• Projection types. date (time ranges), integer (range=0,23 + digits), enum (values=a,b,c for small known sets), injected (unbounded values the query must name as equality — doubles as an accidental-full-scan guard).
• CTAS → Parquet template. CREATE TABLE t WITH (format='PARQUET', parquet_compression='SNAPPY', partitioned_by=ARRAY['dt'], external_location='s3://...') AS SELECT cols, dt FROM raw — partition columns last; CTAS writes ≤ 100 partitions per statement; complete the history with uncapped INSERT INTO; keep files ~128–512 MB.
• Bucketing. bucketed_by=ARRAY['user_id'], bucket_count=64 hash-buckets a high-cardinality equality key so a single-value lookup reads one bucket file, not all. Never "partition" by such a key; bucketing stacks on top of partitioning.
• Iceberg table template. CREATE TABLE t (...) PARTITIONED BY (day(event_ts)) LOCATION 's3://...' TBLPROPERTIES('table_type'='ICEBERG', 'format'='PARQUET', 'write_compression'='ZSTD', 'vacuum_max_snapshot_age_seconds'='604800'). Unlocks MERGE INTO, UPDATE, DELETE, hidden partitioning, and time travel.
• Iceberg upsert + maintenance. MERGE INTO t USING s ON t.id=s.id WHEN MATCHED [AND s.op='D'] THEN [DELETE|UPDATE SET ...] WHEN NOT MATCHED THEN INSERT .... Schedule OPTIMIZE t REWRITE DATA USING BIN_PACK (compact + resolve deletes) keyed to write volume and VACUUM t (expire snapshots) keyed to your time-travel retention. Recover a bad load with ... FOR TIMESTAMP AS OF TIMESTAMP '...' then ALTER TABLE t EXECUTE ROLLBACK (<snapshot_id>).
• Federated query. Deploy a Lambda data-source connector, register it as a catalog, reference tables as "catalog".schema.table. Federate the small, live, filtered side and keep the big side in S3; always EXPLAIN to confirm predicate pushdown reaches the source. Materialise into Parquet anything large or heavily queried.
• Workgroup governance. One workgroup per team with BytesScannedCutoffPerQuery (hard cancel above the ceiling), EnforceWorkGroupConfiguration=true (no bypass), PublishCloudWatchMetricsEnabled=true (alerts + chargeback), tags for cost attribution, and query-result reuse (ResultReuseByAgeConfiguration, up to 7 days) so repeat dashboards scan 0 bytes.
• Cost decision matrix. Row vs columnar: columnar always for analytics. Glue partitions vs projection: projection for high-cardinality time-series (removes the metastore round-trip). Hive external vs Iceberg: Iceberg whenever you need row-level upsert/delete, ACID, or time travel. S3 vs federated: federate small/live/occasional sources, ETL-copy large/frequent ones. Print this matrix; use it in every Athena interview.

Frequently asked questions

What is Amazon Athena in one sentence?

Amazon Athena is a serverless, interactive query service that runs standard SQL (built on Presto/Trino) directly over data in Amazon S3 — no clusters to provision, no infrastructure to manage — and it bills you per terabyte of data scanned to answer each query rather than per hour of compute. Because the meter is the bytes read off S3, every performance and cost technique — partitioning, partition projection, columnar formats via CTAS, Apache Iceberg metadata pruning, and workgroup limits — exists to make a single query read fewer bytes. Athena also supports federated query through Lambda connectors so one SQL statement can reach data outside S3, such as RDS, DynamoDB, or CloudWatch.

How does Athena pricing work and how do I reduce cost?

Athena's standard pricing is roughly $5 per terabyte of data scanned, rounded up to 10 MB per query, with no charge for DDL or failed queries. You reduce cost by reducing bytes scanned, in impact order: partition on the columns you filter on (or use partition projection), store data as compressed columnar Parquet/ORC via CTAS so queries read only referenced columns, select only the columns you need, compact small files, and enable workgroup query-result reuse so identical repeat queries scan zero bytes. Always verify the win by reading the DataScannedInBytes value Athena reports for the query — a partitioned columnar layout routinely cuts a query's scan from terabytes to gigabytes, which is orders of magnitude cheaper for the identical answer.

Partition projection vs Glue/Hive partitions — when do I use each?

Traditional Glue/Hive partitions register each partition in the Data Catalog, so Athena resolves them with a GetPartitions lookup and you must run MSCK REPAIR TABLE or ALTER TABLE ADD PARTITION as new data lands — which becomes a real bottleneck once a table has hundreds of thousands of partitions. partition projection instead computes partition values at query time from table properties (a type, a range, and a storage.location.template), so there is no metastore round-trip, no MSCK, and planning stays constant-time regardless of partition count. Use projection for high-cardinality, predictable layouts like date/hour/region-partitioned logs and events; stick with catalogued partitions when partition values are irregular, unpredictable, or already well-managed at low cardinality. Projection changes planning, not the read path — bytes scanned and pruning quality are the same as Hive partitioning inside the projected range.

CTAS vs Iceberg in Athena — which do I pick?

Pick CTAS (plus INSERT INTO) when you need an append-only curated layer: it rewrites raw CSV/JSON into partitioned, compressed Parquet once, and you keep it fresh with incremental appends. It is the cheapest way to cut scan cost on data that only grows. Pick Apache Iceberg when the table must be mutated correctly — row-level MERGE upserts for a CDC feed, DELETE for GDPR erasure, ACID guarantees under concurrent writes, schema evolution, or snapshot time travel and rollback. Iceberg adds a transactional metadata layer (with OPTIMIZE and VACUUM maintenance) that a plain Hive external table cannot offer, at the cost of that maintenance. A common pattern is both: CTAS-curated Parquet for immutable historical facts, and Iceberg for dimensions and any table that receives updates.

What is an Athena federated query?

An Athena federated query lets a single SQL statement read and join data that lives outside S3 — a table in RDS or Aurora, an item collection in DynamoDB, logs in CloudWatch, a Redshift table — by running an AWS Lambda data source connector that translates Athena's requests into the source's API and streams rows back. You deploy the connector (from the Athena Federated Query SDK or a prebuilt one), register it as a data source/catalog, and reference its tables as "catalog".schema.table. It is the right tool when you need a small, live value joined to lake data without building an ETL copy — for example joining a big S3 fact to a constantly-changing dimension in DynamoDB. The key caveat is predicate pushdown: confirm with EXPLAIN that your WHERE filter is pushed into the source, or a "filtered" federated query will drag the whole source table across the wire.

What do Athena workgroups control?

An Athena workgroup is the isolation and governance boundary for queries. It sets the S3 result location and encryption, can enforce a per-query data scanned control limit (BytesScannedCutoffPerQuery) that cancels any single query exceeding the ceiling — your circuit breaker against an accidental SELECT * — and can enable query-result reuse so identical repeat queries return cached results and scan zero bytes. Workgroups also publish per-workgroup CloudWatch metrics such as DataScannedInBytes for alerting and cost attribution, and with EnforceWorkGroupConfiguration you can prevent users from overriding those settings. Giving each team its own tagged workgroup with a right-sized cutoff is the standard way to cap and attribute Athena spend across an account.

Practice on PipeCode

• Drill the SQL practice library → for the CTAS, partitioning, MERGE, and analytic-aggregation problems an Amazon Athena interview leans on.
• Sharpen the cost axis on the optimization practice library → for scan-reduction, partition-pruning, columnar-layout, and query-cost scenarios.
• Rehearse the rewrites on the data transformation practice library → for raw-to-Parquet curation, upserts, and CDC-apply patterns.
• Stack the prerequisites against PipeCode's broader 450+ data-engineering catalogue to anchor the bytes-scanned decision tree against real graded inputs — partition, columnar, Iceberg, federated, and workgroup governance.

Lock in Amazon Athena cost-tuning muscle memory

Docs explain the features. PipeCode drills explain the decision — when partition projection beats an `MSCK` metastore, when CTAS-to-Parquet cuts a query from terabytes to gigabytes, when Apache Iceberg's `MERGE` and time travel earn their maintenance, and when a workgroup cutoff is the only thing standing between a `SELECT *` and the invoice. Pipecode.ai is Leetcode for Data Engineering — cost-first practice tuned for the production trade-offs senior data engineers actually face.

Practice SQL problems →
Practice optimization problems →