PartiQL for DynamoDB by Example

PartiQL is a SQL-compatible query language for DynamoDB. It's friendlier than the
raw API for ad-hoc work — but it runs on the same engine, so the same key rules
(and the same costs) apply underneath the familiar syntax.

How do you write PartiQL queries for DynamoDB?

PartiQL gives DynamoDB four SQL-shaped statements — SELECT, INSERT, UPDATE,
and DELETE — run through ExecuteStatement. Each one compiles to a native
operation underneath, so filtering on the partition key stays a
Query while omitting it becomes a full-table Scan. Writes still target one item
by its primary key; there's no relational JOIN, GROUP BY, or aggregate.

Every statement runs through the same ExecuteStatement API (or its batch/transaction
variants). Table and index names must be double-quoted when they contain special
characters; attribute names in expressions follow the same quoting rules as the
low-level API. The PartiQL docs in DynoTable mirror these
examples with schema-aware autocomplete and a searchable history of past runs.

SELECT

SELECT * FROM "AppData"
WHERE "PK" = 'CUSTOMER#42' AND begins_with("SK", 'ORDER#')

Filter on the partition key and this is a Query. Omit the partition key and
PartiQL silently runs a full-table Scan — same footgun,
just hidden behind SELECT *.

WHERE clause shape	Compiled operation	Typical cost driver
PK = ? (exact partition)	Query	Items in that partition (plus filter waste)
PK = ? AND begins_with(SK, ?)	Query	Sort-key range within one partition
GSI1PK = ? via FROM "Table"."GSI1"	Query on index	Items in the index partition
No partition-key equality on table or index	Scan	Every item in the target, before filters

Put numbers on the Scan path: a table with 500,000 items averaging 1 KB each meters
~125,000 read request units on on-demand billing for a full SELECT * (500,000 KB
÷ 4 KB per unit, eventually consistent at 0.5 RCU each). Add WHERE status = 'OPEN'
and the bill is identical — filtering happens after the read is metered. The keyed
SELECT in the example above touches only the CUSTOMER#42 partition; if that
collection holds 40 orders at 1 KB each, the same eventually-consistent read costs
5 units (40 KB total), not 125,000.

ExecuteStatement paginates like the native API: a non-empty NextToken means
more rows remain. Loop until the token is absent (pagination guide).

INSERT

INSERT INTO "AppData" VALUE {'PK': 'CUSTOMER#42', 'SK': 'PROFILE', 'plan': 'pro'}

UPDATE

UPDATE "AppData" SET "plan" = 'enterprise'
WHERE "PK" = 'CUSTOMER#42' AND "SK" = 'PROFILE'

DELETE

DELETE FROM "AppData"
WHERE "PK" = 'CUSTOMER#42' AND "SK" = 'ORDER#2026-001'

Querying an index

Use the index name in the FROM clause:

SELECT * FROM "AppData"."GSI1" WHERE "GSI1PK" = 'STATUS#OPEN'

WHERE also supports IN, contains() and begins_with():

SELECT * FROM "AppData"
WHERE "PK" = 'CUSTOMER#42' AND "SK" IN ['ORDER#1', 'ORDER#2']

Parameterized statements

Use ? placeholders instead of inlining values — it sidesteps quoting/injection
issues and lets the SDK marshal types for you:

SELECT * FROM "AppData" WHERE "PK" = ? AND begins_with("SK", ?)

Pass Parameters: [{ S: 'CUSTOMER#42' }, { S: 'ORDER#' }] to ExecuteStatement.

Prefer parameters in application code and reserve string interpolation for ad-hoc
console sessions. The SDK marshals types (N for numbers, BOOL for booleans)
so you avoid the classic "42" string where DynamoDB expected a number attribute.
Reserved attribute names still need aliases in PartiQL exactly as in
FilterExpression — run your names through the
reserved-words checker before baking them
into a stored statement.

Batch and transactions

• BatchExecuteStatement — up to 25 statements in one round trip. Faster, but no cross-item atomicity (each succeeds or fails on its own).
• ExecuteTransaction — up to 100 statements, all-or-nothing. Use it when several writes must commit together.

Batch and transaction ceilings are fixed service limits, not tuning knobs. A cart
checkout that touches 30 line items still needs chunking: two BatchExecuteStatement
calls (25 + 5) or one transaction if you need atomicity across all 30 (and each
statement still targets a single item by full primary key).

PartiQL vs Workbench SQL

PartiQL executes on DynamoDB — one keyed access pattern per statement. DynoTable's
SQL Workbench executes in the client over rows
you've already pulled, which unlocks JOIN, GROUP BY, and aggregates PartiQL
deliberately omits. The split is operational, not cosmetic:

Need	Reach for
Single-partition read/write by primary key	PartiQL SELECT / DML
Cross-partition filter you modeled a GSI for	PartiQL against the index
Ad-hoc join across two entity types	Workbench SQL over bounded queries
Monthly revenue roll-up by SKU	Workbench GROUP BY
Production hot-path microservice	Native Query / GetItem APIs

Workbench still respects DynamoDB's access rules: you fetch partitions with keyed
reads (or bounded Scans you accept consciously), then SQL shapes the result set
locally. See PartiQL vs SQL for the full comparison.

The PartiQL footgun

PartiQL looks like SQL but runs on the DynamoDB engine, so SQL habits backfire:

• A single UPDATE/DELETE must target one item by its full primary key — there's no UPDATE … WHERE status = 'x' mass update (loop with a batch instead).
• No JOIN, no GROUP BY, no aggregates (COUNT/SUM/AVG). See PartiQL vs SQL.
• Omitting the partition key turns any SELECT into a full-table Scan — bounded only by your bill.

When you genuinely need a JOIN, a GROUP BY, or an aggregate, DynoTable's SQL
Workbench runs them client-side over the rows you've pulled — the SQL PartiQL
can't speak, inside DynamoDB's access-pattern rules.

PartiQL doesn't change the underlying data types — values
still go over the wire as DynamoDB-JSON, which you can inspect with the
converter.

For UPDATE and DELETE statements, the WHERE clause must resolve to exactly one
item. Partial-key WHERE clauses that match many rows fail at compile time or runtime
rather than mass-updating — loop with a keyed Query, then issue per-item DML inside
BatchExecuteStatement or a transaction when you need atomicity.

Complex KeyConditionExpression shapes (nested begins_with, mixed AND/OR) are
easier to prototype in the
DynamoDB Expression Builder and translate into
PartiQL's WHERE syntax once the condition is correct.

Try DynoTable to run PartiQL statements with schema-aware autocomplete
and browse the results in a sortable table view — then pivot the same dataset into
Workbench when you need a join or aggregate the engine cannot push down.