DynamoDB Cost Model: Why SQL Can Hide the Bill

SQL over DynamoDB gives you JOIN, GROUP BY, and arbitrary WHERE clauses over a database that natively offers none of them. A relational engine answers those with a planner and the indexes you created; DynamoDB indexes only the keys you designed.

A perfectly legal SELECT can compile to a full-table Scan that reads, and bills you for, every item in the table.

Why does SQL over DynamoDB cost more than it looks?

A relational database lets you add an index on any column, and its planner uses it to answer WHERE status = 'active' without reading every row.

DynamoDB answers efficiently on exactly one thing: the partition key (optionally narrowed by a sort key), on the table or on a secondary index you defined. Anything else is a Scan.

• A partition-key equality is a Query. DynamoDB jumps straight to the items under that key and reads only those. Cost scales with that partition's items.
• Anything else is a Scan + Filter. DynamoDB reads every item in the table, then applies your WHERE as a FilterExpression, after the read. You are billed for everything it scanned, not for the handful of rows returned.

A WHERE clause looks like it narrows the work. On a non-key attribute, it only narrows the output; the read cost is already spent.

Which SQL constructs compile to Scans?

• WHERE with no partition-key equality → a full Scan with a post-read filter.
• JOIN → no server-side join exists. The client does one key lookup per joined row, each billed as an item read. Base-table keys batch up to 100 per BatchGetItem; a side keyed on a GSI is one Query per key. For a join you run often, store both entities in one item collection (same partition key, different sort-key prefixes) so one Query returns both.
• COUNT, SUM, GROUP BY, aggregates → the only server-side aggregate is a count (Select=COUNT), and it still reads and bills every item it scans. For an aggregate you need often, keep a running total item updated with UpdateItem ADD as data changes (AWS calls this materialized aggregation).
• ORDER BY or DISTINCT on a non-key attribute → sorting and de-duplication happen client-side over whatever was scanned. For a table-wide top-N, add a GSI with a constant partition key (GSI1PK = "ALL" on every ranked item) and that attribute as its sort key, then Query it with ScanIndexForward=false and a Limit. For top-N per tenant or per month, key the GSI on that instead. A constant key takes every index write; one partition serves at most 1,000 write units/s, and with a sort key that keeps rising, DynamoDB can't split the load away. Past that, shard it (ALL#0 to ALL#9) and merge each shard's top N client-side (see hot partitions).

None of these are wrong to run; sometimes a Scan is exactly what you want. The point is to know when you are running one.

What one full Scan costs

A Scan bills every 4 KB it reads, at half a read unit per 4 KB with eventually consistent reads. A 1 GB table is about 131,000 read units per full pass (262,144 strongly consistent), about $0.016 on on-demand at us-east-1 list price.

You pay that on every pass, and a FilterExpression doesn't lower it. The unit-by-unit comparison lives in Query vs Scan; price your own data with the item size calculator and the pricing calculator.

The curve below prices a read-heavy Scan workload under both capacity modes. Slide the average utilization to see when paying for provisioned throughput beats paying per request:

How do I keep SQL queries on the key?

• Design keys around your access patterns. The cheapest query is the one your key schema already answers. Plan it with the single-table design tool and the query vs scan guide.
• Put a partition-key equality in the WHERE to get a Query instead of a Scan.
• Add a GSI for a second access pattern rather than scanning-and-filtering.
• Read the plan before you run. DynoTable's Workbench compiles your SQL to the DynamoDB operation and shows it under the editor: Scan or Query, the index it uses, and an estimate of the items read when it has one. A cross-table JOIN gets an inline warning about its per-row lookups.
• Treat the estimate as a hint. The RCU estimate appears only when table metadata supports it; an ordinary partition Query shows none. See also why scans are slow and expensive and on-demand vs provisioned capacity.

Does DynoTable's SQL make the cost problem worse?

SQL over DynamoDB can hide a Scan behind a short SELECT, which is a fair critique. DynoTable keeps the SQL and shows the compiled plan before you run it, so you see a Scan before you pay for one. Treat any number it shows as a pre-run hint, not a bill.

Try it

Download DynoTable and open the SQL Workbench against your own table. The plan under the editor shows whether each query is a Scan or a Query, and on which index, before you run it.