Introduction
Amazon Athena handles day-based and hour-based partitions well until one window gets so big that the job fails with "query exhausted resources" at this scale factor. Running it again usually fails the same way. This article shows how to split large queries in Athena with a simple modulo on a busy id column, store Parquet under that extra key, and run smaller jobs side by side into one output path.
One practical fix is id % 4 (or another N). N is the number of slices you choose up front. Each job only handles its slice. Point another table at the parent folder so readers see one dataset. The work finishes sooner. Keep the time filter and the slice filter tight, and you still scan about the same amount of data, so cost stays in the same range. The same folder idea shows up in older Hive-style partitioning write-ups; here you add a slice key on top of day and hour.
When a Time Filter Is Not Enough
Athena will skip unused day or hour folders when your WHERE clause is right. Missing partitions are often not the issue. One busy window can still need more memory than the query cluster has for joins and grouping. AWS explains the error here: How do I resolve the "Query exhausted resources at this scale factor" Athena query error?. More background sits in Optimize service use.
Dropping unused columns, fixing joins, and using Parquet all help. Sometimes that still is not enough for a heavy multi-table job. When polish is not enough, you need another way to break up the work.
Splitting Large Queries with a Modulo
First pick how many slices you want. Call that number N. In the examples below, N = 4, so each row lands in slice 0, 1, 2, or 3 based on column_value % 4. Pick a primary key, or another stable id that uniquely identifies the row (or comes close) at the grain you join. Event id, account id, or order id usually work: they appear on every table in the join, take many different values, and can use the same rule on every side of the join. Skip a yes/no flag or a region field with only a few values. Those dump most rows into one or two buckets.
Names like placement id or content id are fine only when they behave like that kind of key. Here the sample column is placement_id. Read it as a stand-in for a busy integer id shared by the tables you join.
Before you lock the design, check how even the buckets are for one busy hour:
SELECT coalesce(placement_id, 0) % 4 AS slice_id, count(*) AS row_count FROM analytics_lake.page_views WHERE year = '2026' AND month = '09' AND day = '14' AND hour = '13' GROUP BY 1 ORDER BY 1;
You should get four rows (slice_id 0 through 3) with counts that are close to each other. If slice 0 has most of the rows, raise N, try another key, or handle the hot id in its own job. That result is how you choose N and the column. Skipping the check is how one slice stays too big.
Store the Slice Data
Next, store the data with the slice column. We keep placement_id and N = 4. The new column is slice_id. The formula is coalesce(placement_id, 0) % 4. Null ids become 0 before the modulo so they still get a slice. When you convert CSV to Parquet, add slice_id and write it into the folder path. After the write, one hour on S3 looks like this:
s3://analytics-lake/curated/page_views/ year=2026/month=09/day=14/hour=13/slice_id=0/ year=2026/month=09/day=14/hour=13/slice_id=1/ year=2026/month=09/day=14/hour=13/slice_id=2/ year=2026/month=09/day=14/hour=13/slice_id=3/
This insert builds those folders for one hour:
INSERT INTO analytics_lake.page_views_parquet SELECT event_id, placement_id, year, month, day, hour, (coalesce(placement_id, 0) % 4) AS slice_id FROM analytics_lake.page_views_csv WHERE year = '2026' AND month = '09' AND day = '14' AND hour = '13';
Use the same formula and the same N on every related table. If views use % 4 and clicks use something else, the join will be wrong.
Run One Job Per Slice
You do not replace the big window with a single new insert. You schedule N copies of the same SQL. With N = 4, that means four jobs. Only the slice_id filter and the output folder change. That is the core move when you split large queries this way. Number the slices from 0 through N-1. With N = 4 the set is 0, 1, 2, 3. There is no slice 4. Do not run i + 1 as an extra job; i already is the slice id. The SQL below is one of those four jobs: the job for slice_id = 2. Your scheduler runs the same statement again for 0, 1, and 3.
-- Example: only the job for slice_id = 2 (one of N = 4 jobs)
INSERT INTO analytics_lake.window_agg
SELECT
date_parse(
concat(year, '-', month, '-', day, ' ', hour, ':00:00'),
'%Y-%m-%d %H:%i:%s'
) AS event_ts,
placement_id,
domain,
sum(is_view) AS views,
sum(is_click) AS clicks,
count(*) AS row_count,
year,
month,
day,
hour,
slice_id
FROM (
SELECT
v.event_id,
v.placement_id,
v.domain,
v.is_view,
c.is_click,
v.year,
v.month,
v.day,
v.hour,
v.slice_id
FROM analytics_lake.page_views_parquet v
LEFT JOIN analytics_lake.page_clicks_parquet c
ON v.event_id = c.event_id
AND v.slice_id = c.slice_id
WHERE v.year = '2026'
AND v.month = '09'
AND v.day = '14'
AND v.hour = '13'
AND v.slice_id = 2
)
GROUP BY
1, placement_id, domain, year, month, day, hour, slice_id;The filter that shrinks the work is AND v.slice_id = 2. For the other jobs, change that value and the output path.
In your scheduler, loop i from 0 to N-1, run the query for that slice, and write here:
.../year=2026/month=09/day=14/hour=13/slice_id={i}/Delete that slice's old files before you write again. Run the N jobs at the same time. Each one holds less data in memory, which is usually what stops the failure.
Read Everything as One Result
Writers care about slice_id. Most readers do not. Point a second Athena table at the parent folder for that hour:
s3://analytics-lake/curated/window_agg/year=.../month=.../day=.../hour=.../
Everything under that folder is the full result across all slices. Keep slice_id if you need it for debugging. Hide it in a view if you do not.
The Costs
Athena mostly charges for how much data you read. Four slice jobs that each read about a quarter of the window should add up to roughly the same scan as one full job. You add more steps in the pipeline, not automatically 4x the bill.
That only works if every job still filters on the time folders and the slice. If each job reads the whole window, you pay about N times. After the first try, check "data scanned" on each query in the Athena console. AWS covers partitioning and columnar formats in the same resource error article and in Partitioning data in Athena.
When to Split Large Queries
Use this when:
- One window keeps failing with the Athena resource error above
- You can compute each slice on its own and put the files together afterward
- Every table in the join can use the same modulo
Skip it when:
- The job truly needs every row of the window at once (some global distincts, ranks, or window functions)
- You cannot find a shared column with enough different values
- You are still reading wide CSV for the heavy step; fix that first
Athena capacity reservations are still an option for hard cases. Splitting with modulo is often cheaper when each slice can stand alone.
Things That Go Wrong
Uneven slices. If one bucket holds most of the rows, that job can still run out of memory. Run the row-count check from Pick a Column before you rely on this in production. Raise N or change the key when the spread looks bad.
Mismatched formulas. Every table in the join must use the same expression, the same N, and the same null handling (coalesce to 0, or whatever you chose). If one side differs, related rows land in different slices and the join misses them.
Writing twice. Do not land a full-window job and the slice jobs in the same folder. Readers will double-count, and you will not know which files belong to which method.
Too many slices. A large N means more tasks and more small files, which slows listing and planning. Start with 4 or 8 and only go higher if a slice is still too big.
Too many parallel Athena jobs. Running all slices at once can hit workgroup concurrency or account service limits. Cap how many run together, or stagger starts, if submissions fail.
One slice fails. If slices 0, 1, and 3 succeed and 2 fails, the parent folder is incomplete. Fix or rerun slice 2 before anyone treats the window as done.
Summary
Day and hour partitions get you far in Athena. When a single job for one window runs out of memory, split large queries with a modulo on a busy id column, store that key with the Parquet files, and run N smaller jobs into one output path. Readers see one result. Jobs finish faster. Scan cost stays close to the original if your filters stay tight.