Can You Use SQL with DynamoDB? PartiQL and Analytics

Yes, DynamoDB supports SQL-style reads and writes through PartiQL, a subset of the PartiQL language adapted to DynamoDB. It supports SELECT, INSERT, UPDATE and DELETE, but does not turn the database into a relational engine with JOIN, GROUP BY or general SQL aggregation. For analytical SQL, use an external engine such as Athena over exported data or Amazon Redshift through a zero-ETL integration.

Two meanings of SQL on DynamoDB

Operational queries read or change application items using their keys. Analytical queries combine large datasets, calculate totals and answer questions that were not known when the application schema was designed. Both can use SQL syntax, but they have different execution paths.

The DynamoDB PartiQL documentation describes an alternative data-plane interface. Athena is a separate service, not another editor for that same API.

Start with a key-based PartiQL query

Assume Orders has String partition key customerId and String sort key orderId. In the DynamoDB console's PartiQL editor, this reads one customer's orders:

SELECT "customerId", "orderId", "status"
FROM "Orders"
WHERE "customerId" = 'customer-123'
SELECT "customerId", "orderId", "status"
FROM "Orders"
WHERE "customerId" = 'customer-123'

To read one order, specify both keys:

SELECT * FROM "Orders"
WHERE "customerId" = 'customer-123' AND "orderId" = 'order-42'
SELECT * FROM "Orders"
WHERE "customerId" = 'customer-123' AND "orderId" = 'order-42'

Use positional parameters in application code. In AWS CLI v2:

aws dynamodb execute-statement \
  --statement 'SELECT * FROM "Orders" WHERE "customerId" = ? AND "orderId" = ?' \
  --parameters '[{"S":"customer-123"},{"S":"order-42"}]' \
  --return-consumed-capacity TOTAL
aws dynamodb execute-statement \
  --statement 'SELECT * FROM "Orders" WHERE "customerId" = ? AND "orderId" = ?' \
  --parameters '[{"S":"customer-123"},{"S":"order-42"}]' \
  --return-consumed-capacity TOTAL

The values use DynamoDB JSON because the CLI calls the low-level API. Do not concatenate user input into the statement. The PartiQL implementation guide provides JavaScript v3 and boto3 code, batch writes, transactions and continuation-token handling.

Why a SELECT can become a full scan

SQL syntax does not remove DynamoDB's key-based access rules. A SELECT without a usable partition-key equality or IN condition can require a full scan. This statement has a WHERE clause, but status is not the table's partition key:

SELECT * FROM "Orders" WHERE "status" = 'OPEN'
SELECT * FROM "Orders" WHERE "status" = 'OPEN'

It evaluates table items and filters them. Returning a few matches does not mean it read only a few items. Adding a projection such as SELECT "orderId" reduces returned attributes, not the table items evaluated.

If the access pattern needs an index, create an appropriate index and explicitly select it. Merely mentioning an attribute indexed elsewhere does not make a base-table SELECT use that index automatically. For an index named status-createdAt-index whose partition key is status:

SELECT * FROM "Orders"."status-createdAt-index"
WHERE "status" = 'OPEN'
SELECT * FROM "Orders"."status-createdAt-index"
WHERE "status" = 'OPEN'

That index must already exist and project the attributes you need. A GSI also brings storage, write costs and eventual consistency. The AWS SELECT reference describes key predicates and explicit index selection; Query vs Scan explains the underlying access patterns.

What SQL features does DynamoDB support?

A batch API can submit several individual updates or deletes, so it is inaccurate to say PartiQL has no batching. What it cannot do is turn UPDATE Orders SET status = 'CLOSED' WHERE status = 'OPEN' into a bulk relational update over every match. Discover the target keys first and apply bounded writes with failure handling.

The language supports only the functions in AWS's PartiQL function reference. Familiar SQL syntax is not evidence that an arbitrary SQL function will work. For relationship reads in the application, see modeling joins in DynamoDB.

Editors and permissions

The AWS console, NoSQL Workbench, CLI and SDKs can execute PartiQL. In the console, select PartiQL editor, choose a table, and start with the Query table template. Replace its key placeholders before running it. AWS documents the current workflow in Getting started with PartiQL.

Dynobase's PartiQL workspace supports PartiQL writes and transactions for desktop workflows.

The screenshot shows an earlier editor version; use the current app's controls rather than treating it as a current UI walkthrough.

Permissions use dynamodb:PartiQLSelect, dynamodb:PartiQLInsert, dynamodb:PartiQLUpdate and dynamodb:PartiQLDelete. Grant the operations and table or index resources needed by the caller. A read-only workflow does not need a wildcard granting every PartiQL write action.

IAM can deny statements that perform full scans using the dynamodb:FullTableScan condition on dynamodb:PartiQLSelect. Denying only dynamodb:Scan does not cover PartiQL SELECT permissions. The PartiQL IAM examples show the relevant policy.

Export to S3 and query with Athena

For a point-in-time report or repeatable analysis, export DynamoDB data to S3, define its schema for Athena, and perform joins and aggregation there. A practical flow is:

1. Enable point-in-time recovery on the source table.
2. Request an export at a point within the recovery window.
3. Wait for export completion and identify its data files.
4. Configure an appropriate Glue Data Catalog schema, or transform the files into an analytics-friendly layout.
5. Query with Athena and record the source snapshot time alongside the results.

Exports support DynamoDB JSON and Amazon Ion. DynamoDB JSON includes type wrappers, so do not assume the export is a flat JSON record set that Athena can infer without configuration. Transforming the data into a columnar format can also make repeated analytical reads more efficient.

Export does not consume table read capacity, but export, S3 storage and requests, transformation, and Athena queries have their own charges. Full, incremental and filtered exports are available. Incremental output must be applied according to its change semantics; appending changes as if they were independent current rows can double-count entities. The S3 export documentation explains formats and behavior, and the export walkthrough covers the workflow.

Zero-ETL analytics options

DynamoDB's zero-ETL integration with Amazon Redshift copies an initial dataset and refreshes it with incremental exports. It requires PITR and the documented source and target permissions. Updates typically arrive every 15 to 30 minutes, so this is a reporting copy rather than a read-after-write path for an application.

The SageMaker Lakehouse integration offers another managed analytics path, with data typically kept fresh within 15 to 30 minutes and without consuming source-table capacity. AWS Glue zero-ETL integrations also support an Amazon S3 Tables target. Select the destination based on the analytical tools, governance and table format your team already uses.

Zero-ETL reduces custom pipeline work; it does not eliminate destination storage, compute, permissions or freshness requirements. Define what happens when a source schema changes and how reports distinguish data freshness from query execution time.

Athena's live DynamoDB connector

The Athena DynamoDB connector provides federated SQL access to a live source. It supports reads, not INSERT writes back into DynamoDB. The connector can push supported predicates into DynamoDB, but broad queries still consume source read capacity and can affect production traffic.

Use it when querying live data through Athena is the requirement and the source load is acceptable. Use export or replication when analytics should run against a separate copy. A connector is not an automatic way to make full scans cheap, and the SQL execution cost is not limited to a DynamoDB bill.

Should you use PartiQL or native APIs?

Choose based on the team's workflow and the operation, not an assumption that SQL syntax is slower or cheaper. AWS states that PartiQL operations provide the same availability, latency and performance as other DynamoDB data-plane operations. The access pattern and consistency determine the real read work.

PartiQL is useful for familiar ad hoc syntax and parameterized statements. Native APIs make Query, Scan and key conditions explicit and expose operation-specific helpers. Whichever interface you use, inspect consumed capacity, paginate all required results, and test representative data sizes. Moving an application from a relational database still requires redesigning its access patterns and integrity rules; switching the query language alone is insufficient.