Redshift or Athena: When a Warehouse Earns Its Keep

Business Challenge

The last two posts were about making files behave like a table: a catalog that describes them, then a table format that owns them. The reward for that work is that the same data is now legible to more than one engine, which turns a question that used to be settled by the storage into an open one. The table is on S3 either way. Something still has to run the SQL.

The way this gets argued is price per query, and the argument never resolves, because the two candidates do not meter the same thing. Athena bills the bytes a query reads — $5/TB scanned, rounded up to the nearest megabyte, with a 10 megabyte minimum per query. Amazon Redshift bills the time a warehouse is awake — $0.375 per RPU-hour on Redshift Serverless, metered per second with a 60-second minimum charge, or a flat hourly rate per node on a provisioned cluster. Those are not two prices for one thing. They are two units.

A unit mismatch of that kind is usually harmless, because you can convert. Here you can too, but the conversion needs a fact nobody has at the point of decision: how much of the day the warehouse will be idle, and how many queries will arrive while it is not. Data volume, the figure everyone brings to the meeting, barely enters into it. A hundred-terabyte table queried twice a week and a hundred-gigabyte table backing a dashboard that thirty people refresh all morning point at opposite answers, and the larger table is the one that wants Athena.

So this post does the conversion properly, in both directions, and then argues that the conversion is not actually the decision. Both services now sell both meters — Athena has capacity reservations billed by the hour, Redshift Serverless bills by the second — which means price per query has stopped separating them. What separates them is everything the warehouse does besides running SQL, and whether the workload needs any of it.

The question that actually decides it

Not "how much data do we have". It is what does the demand curve look like: how many queries, how close together, how long each is allowed to take, and how many hours a day nothing arrives at all. Athena is priced for a curve with gaps in it. A warehouse is priced for one without.

Architecture

Start with the arithmetic, because it is short and it is the part most often done wrong.

Diagram: the two meters behind the Redshift or Athena decision, showing Athena billing bytes scanned with a ten megabyte floor per query against Redshift Serverless billing RPU-seconds with a sixty second floor, the exchange rate of seventy five gigabytes of scanning per RPU-hour with hourly equivalents for four, eight and one hundred and twenty eight RPUs and for four ra3.4xlarge nodes, the fact that Athena also sells reserved capacity by the hour, and three ways each meter runs away from you

One exchange rate, and what it buys

$0.375 per RPU-hour divided by $5 per TB scanned is 0.075 TB. So one RPU-hour costs the same money as scanning 75 GB. That single constant converts every warehouse configuration into a scanning budget, which is the only way to compare them honestly:

Configuration	Cost per hour awake	Same money, in Athena
4 RPUs — the smallest base capacity	$1.50	0.3 TB scanned
8 RPUs	$3.00	0.6 TB scanned
128 RPUs — the console default	$48.00	9.6 TB scanned
4 × ra3.4xlarge provisioned, at $3.26 per node-hour	$13.04	2.608 TB scanned
Athena capacity reservation, 4 DPUs at $0.30 per DPU hour	$1.20	0.24 TB scanned

Read the right-hand column as a budget rather than a benchmark. It does not say the warehouse scans that much; it says that is what the same money buys on the other meter. An estate scanning under 300 GB an hour, spread across a day, is spending less on Athena than the smallest serverless warehouse would cost simply for existing during that hour. An estate scanning ten terabytes an hour has already paid for the default 128-RPU warehouse twice over.

That third row deserves a sentence of its own. The default base capacity for Redshift Serverless is 128 RPUs. Left alone, that is $48 an hour whenever the warehouse is awake, and the two most common first workloads — a proof of concept and a nightly load — are exactly the ones nobody sizes first. The floor is 4 RPUs, and 4 RPUs is a real configuration: 16 GB of memory per RPU, so 64 GB, supporting up to 32 TB of managed storage.

Both meters have a floor, and floors are where money actually goes

Averages hide the interesting behaviour. Both services charge a minimum per unit of work, and the minimums are what a real workload hits.

Athena's floor is 10 MB per query. A query that reads a single row of a single small file is charged as if it read 10 MB, which is $0.00005. That is invisible once and material at volume: a dashboard that fires two thousand small queries an hour is paying the floor two thousand times, and the bill is a function of query count rather than data size — which is the one thing the "$5/TB is cheap" reasoning cannot see, because no terabytes were involved.

Redshift Serverless's floor is 60 seconds, metered per second above that. On 8 RPUs, 60 seconds is $0.05 — a thousand times the cost of a minimum-charge Athena query. And it is charged per usage, not per query: what triggers it is the warehouse being woken up. The docs are explicit that Redshift Serverless treats all incoming queries as billable user activity, including lightweight health-check queries sent by connection pools, so maintaining open connection pools can generate costs even when no actual user workloads are running. A pool sending SELECT 1 every thirty seconds keeps the meter running through a night in which no human asked anything.

Three billing behaviours that surprise people, all documented

A cancelled query is billed for the time it ran. Querying a system table is billed like any other query. And an open transaction goes on consuming RPUs until SESSION TIMEOUT ends it — 3600 seconds for an idle session, 21600 seconds for an open, inactive transaction, which is six billable hours from one missing COMMIT.

Neither service is one meter any more

Athena capacity reservations put Athena on a clock. You reserve DPUs — one DPU is typically 4 vCPUs and 16 GB of memory — from a minimum of 4 per reservation, at $0.30 per DPU hour, and queries in the assigned workgroups run on that capacity instead of being billed per byte. The smallest reservation is $1.20 an hour, held whether anyone queries or not, which is the same shape of commitment as a warehouse and about a third of the price of the smallest one.

What the reservation buys is not a faster engine. It is control over concurrency: queries on reserved capacity do not count towards the account's DDL and DML query quotas, up to 20 workgroups can share one reservation, and a query that finds the capacity busy queues rather than failing — for up to 10 hours. Athena's per-query allocation is otherwise automatic and invisible: it allocates between 4 and 124 DPUs to a DML query based on complexity, and 4 DPUs to each DDL query. Reserving capacity is how you stop that being the service's decision.

In the other direction, Redshift Serverless removed the thing that made a warehouse expensive for intermittent work: per-second billing and shutdown during inactivity. So the old rule of thumb — lake engine for spiky, warehouse for steady — is now an argument about minutes rather than about architecture, and it is no longer the interesting part of the decision.

What the warehouse sells that the lake engine does not

This is the part that should decide it, and it is not about price at all.

Capability	Redshift	Athena
Physical layout you control	Sort keys and distribution keys, so a join can be co-located	The file layout on S3, and nothing else
Precomputation	Materialized views, refreshed on the cluster	A CTAS table you rebuild and manage yourself
Concurrency under burst	Concurrency scaling — one hour of credits accrued every 24 hours, accumulating to 30	Account-level query quotas, or a capacity reservation
Long queries	Up to 86,399 seconds for a running query	DML timeout raisable to a maximum of 240 minutes
Repeat queries	Result caching and materialized views	Result reuse — default 60 minutes, maximum 7 days
The connection	A JDBC endpoint a BI tool expects, with sessions and transactions	An API that returns a result file on S3

The row that decides most real cases is the first one. Athena can read a well-organised Parquet dataset extremely fast, but it cannot be told to store two tables so that their join keys land together, because it does not own the storage. A warehouse does, and a query pattern built on a wide join across two large tables is the pattern where that ownership shows up as a different order of magnitude rather than a percentage.

Concurrency scaling is worth reading carefully rather than assuming. It covers COPY, INSERT, DELETE, UPDATE, CTAS and VACUUM, which is most of a load pipeline, but it does not cover queries on tables with interleaved sort keys or on temporary tables, and it does not cover write operations against a target table whose DISTSTYLE is ALL or which has an identity column. The default number of concurrency scaling clusters is one. A burst plan resting on it should be checked against those exclusions, because the tables most likely to be excluded are dimension tables, which are exactly the ones a burst of dashboard traffic joins to.

What the lake engine sells that the warehouse does not

Zero cost when nothing is happening is the obvious one, and it is a genuine architectural property rather than a discount: there is no endpoint to keep warm, so there is nothing to forget to pause. The subtler one is that the data was already on S3 and stays there, readable by everything else, with no copy to keep in step and no second storage bill. Redshift Managed Storage is $0.024/GB-Month — $24 per decimal terabyte per month — and that is charged on top of the S3 copy the rest of the estate reads, not instead of it.

Two Athena features are worth designing for rather than discovering. Partition projection lets Athena compute partition values from table properties instead of calling GetPartitions against the Glue Data Catalog, which is what makes a heavily partitioned table answer quickly; the caveat is that if more than half of the projected partitions are empty, the documentation recommends going back to traditional partitions. Query result reuse turns a repeated dashboard query into no scan at all, with a default age of 60 minutes and a maximum of 7 days — but not for queries touching more than 20 tables, and only for exact matches once the query string passes 100 KB.

And the two are not symmetric on partition scale. Athena cannot read more than 1 million partitions in a single scan, which is a ceiling a large log estate can genuinely reach.

The middle ground has moved

Redshift reads S3 directly, and how it does that has just changed in a way worth knowing. On DC2 and RA3 provisioned clusters, data lake queries run on Redshift Spectrum — dedicated Amazon Redshift servers independent of your cluster, billed separately at $5/TB scanned with a 10 MB minimum per query, the same rate and the same floor as Athena. On the newer RG node types, data lake queries run on the cluster's own compute resources and share those resources with other workloads.

That is a real change in cost shape, not a detail. On RA3 the data lake query has its own meter and its own capacity; on RG it competes with the warehouse workload for the nodes you already pay for. AWS says as much directly: for workloads with heavy data lake query usage, consider this when sizing an RG cluster. On Redshift Serverless the same collapse has already happened — there is no separate charge for data-lake queries, and a query on S3 data is charged by transaction time exactly like a query on local data.

The practical consequence is that "keep the cold history on S3 and the hot tables in the warehouse" is a good design and no longer a pricing arbitrage. On serverless and on RG, reading the cold data costs warehouse time.

Both cost guardrails default to a notification

Each service can be given a limit, and on each service the limit that is easiest to set is the one that does not stop anything.

Athena's per-query control limit caps the data scanned by any single query in a workgroup, anywhere between 10 MB and 7 EB, and it cancels the query — the action cannot be changed to anything softer, which is the right default. But a workgroup can hold only one of them, and the per-workgroup data usage alerts, which are the ones that aggregate across users, only raise an SNS notification: queries are not automatically cancelled when a threshold is reached.

Redshift Serverless usage limits are set in RPU hours with a Daily, Weekly or Monthly frequency, and offer three actions: log to a system table, alert through SNS, or turn off user queries. The documentation states plainly that the first two are informational and only the last turns off query processing.

So on both services the guardrail that actually stops spend has to be chosen deliberately, and on both it is the one with a consequence somebody has to accept in advance. A limit that emails an administrator at 3am is a limit in name.

The parts that are still sharp

Redshift Serverless does not scale back to 4 RPUs. Once the warehouse scales beyond 4 RPUs it continues to use more, and Amazon Redshift will not bring it back down to 4. The smallest configuration is a decision you make once, not a floor it returns to.
Storage tiers bound the compute floor. 4 base RPUs support up to 32 TB of managed storage; 8 or 16 support up to 128 TB; past that the base cannot be set below 32 RPUs. Data growth raises the minimum bill even if query volume does not move.
Provisioned clusters take maintenance. Amazon Redshift assigns a 30-minute maintenance window at random from an 8-hour block per Region and terminates queries in progress while it works. It can be deferred by up to 60 days, which is a scheduling tool, not an exemption.
Athena's query string ceiling is not adjustable. 262144 bytes, and generated SQL from a BI tool over a wide table gets closer to that than anyone expects.
An ra3.4xlarge is 12 vCPU and 96 GiB with a 128 TB managed storage limit per node, from 2 nodes up. There is no single-node ra3.4xlarge, so the smallest provisioned cluster at that size is two nodes' worth of hourly cost.

Why This Architecture Holds Up

The reason this decision goes wrong is that it is made once, at the beginning, on the only evidence available at the beginning — the size of the data — and the size of the data is the input with the weakest relationship to the bill. Both meters are driven by demand shape. Athena's by how many queries and how much each reads; Redshift's by how many hours the thing is awake. A dataset can grow tenfold without moving either number much, and a new dashboard can move both without the dataset changing at all.

That is why the exchange rate is worth carrying around. 75 GB of scanning per RPU-hour is a number you can hold in your head, and it turns an argument about philosophy into an observation: at this rate of scanning, and this many idle hours, one of these is cheaper by a factor you can state. Most estates find the answer is not close, in one direction or the other, and the ones where it is close are the ones where the decision should be made on capability instead.

There is also a migration cost hiding in the framing. Moving from Athena to Redshift is not changing an engine; it is acquiring storage, a loading pipeline, a vacuum and analyze regime, sort and distribution key decisions, WLM configuration, and an endpoint with a security group. Moving the other way means giving up the sort keys and materialized views the queries were tuned against. Neither is a swap, so the initial choice carries more than its share of weight — which is an argument for making it on the demand curve you expect in a year rather than the data volume you have this month.

Finally: the floors. Two of the three most expensive surprises in this post — the 10 MB per-query minimum and the 60-second wake-up charge — are invisible in any per-terabyte comparison, and both are driven by query count. Any workload described as "lots of small queries" should be priced on its floor first and its scan volume second.

Key Architecture Decisions

Decision	Take this	Because
How to compare the two	Convert at 75 GB of scanning per RPU-hour, then argue	$0.375 per RPU-hour and $5/TB are different units; without the conversion the comparison is not a comparison
What decides it	The demand curve — query count, concurrency, latency target, idle hours	Data volume barely moves either meter; query count and awake hours are what both bill on
Serverless base capacity	Set it explicitly on day one; never accept the default	The default is 128 RPUs, which is $48 an hour awake, and it does not scale back down to 4
Many small queries	Price the 10 MB floor times the query count before anything else	A per-terabyte comparison cannot see a bill driven entirely by query count
Connection pools on serverless	Turn off validation queries, or accept a bill with no users behind it	Every query reaching the endpoint is billable, health checks included, at a 60-second minimum
Transaction hygiene	Every `BEGIN` gets a `COMMIT` or `ROLLBACK`, enforced in code	An open, inactive transaction bills RPUs for 21600 seconds — six hours — before the session times out
Wide joins across large tables	Warehouse, for the sort and distribution keys	Athena does not own the storage, so co-locating join keys is not something it can be told to do
Heavily partitioned log data	Athena with partition projection, unless most projected partitions are empty	It removes the `GetPartitions` call entirely; above half-empty, AWS recommends traditional partitions
Data lake reads from Redshift	Size for them explicitly on RG and on Serverless	Only DC2 and RA3 push them to a separate Spectrum fleet; elsewhere they consume the compute you already pay for
Cost guardrails	Set the one that stops queries, not only the one that notifies	Athena's workgroup alert does not cancel, and two of Redshift's three limit actions are informational
Bursty dashboard traffic on Redshift	Check the concurrency scaling exclusions against your actual tables	Interleaved sort keys, temp tables, DISTSTYLE ALL targets and identity columns are excluded, and the default is one cluster

Closing Thought

A warehouse earns its keep when the workload needs something a query engine cannot offer: storage it can lay out, precomputation it can maintain, transactions, sessions, and an endpoint that answers in the time a dashboard tile takes to paint. That is a capability argument, and it is decidable. What is not decidable is the argument the decision usually gets, which compares a price per terabyte against a price per hour and concludes whichever way the person doing the comparing was already leaning.

The convergence makes this easier rather than harder. Athena sells reserved hours; Redshift Serverless sells seconds; Redshift reads S3 directly and, on its newest node types, does so on the same compute as everything else. The pricing gap that used to do the deciding has narrowed to an exchange rate you can compute in one line, and once you have computed it, the money usually stops being the interesting variable.

What is left is the shape of the demand and the shape of the queries — and, quietly, the floors. Both services charge a minimum for the smallest possible unit of work, and both minimums are invisible in the comparison everyone makes. The estate that gets this right is not the one that picked the cheaper engine. It is the one that knew which meter it was running, and set the limit that actually stops it.

Next in this series

#33 — Lake Formation and governed access. Three posts have been about making the data legible to more than one engine. The next one asks who is allowed to read it, and what happens to a permission model when the table can be reached from a warehouse, a query engine and a Spark job at the same time.

AWS Architecture Series #32 — Redshift or Athena: When a Warehouse Earns Its Keep