PartiQL: tables, indexes, backups, replicas & search projections (DDL)

The control plane of the dialect series: create and shape tables, native GSIs, vector indexes, managed backups, S3 Tables analytics replicas, and OpenSearch projections around your workload. DynamoDB's own PartiQL has no DDL — these statements lower to the native AWS calls and ordered provisioning plans the services actually use. The "Executes as" strip shows that work before you run, and a read-only stage refuses mutating control-plane operations.

CREATE TABLE

CREATE TABLE "stage.Ledger" (  PARTITION KEY pk STRING,  SORT KEY sk STRING) WITH BILLING = ON_DEMAND

Lowers to CreateTable. Each key attribute's type is required — STRING, NUMBER, or BINARY (or S / N / B): DynamoDB fixes key types at creation and can't change them later, so the studio refuses a key with no explicit type rather than guess one. Billing is ON_DEMAND (the default) or PROVISIONED (RCU n, WCU n). The table is CREATING until DynamoDB brings it ACTIVE, and the status line says so.

CREATE GLOBAL INDEX — native, multi-key aware

CREATE GLOBAL INDEX "byRegionStatus" ON "stage.Orders"  PARTITION KEY (region, tenant)  SORT KEY (status, createdAt)  PROJECT ALL

Lowers to UpdateTable with a new global secondary index. CREATE GSI, CREATE GLOBAL SECONDARY INDEX, and bare CREATE INDEX remain accepted compatibility aliases; autocomplete and generated SQL use CREATE GLOBAL INDEX. A native GSI carries up to four partition and four sort attributes — the multi-key shape from Targeted reads — with types defaulting to string and nameable per attribute (ExpiresAt N). PROJECT ALL | KEYS | INCLUDE (a, b) chooses what rides along. DynamoDB auto-backfills the index from the table's existing attributes — no item rewrite — so it's queryable once it goes ACTIVE.

CREATE GLOBAL INDEX "byRegionStatus" ON "stage.Orders"
  PARTITION KEY (region, tenant)
  SORT KEY (status, createdAt)
  PROJECT ALL

DROP GSI — remove a native index

DROP GSI "byRegionStatus" ON "stage.Orders";​-- accepted aliasesDROP GLOBAL SECONDARY INDEX "byRegionStatus" ON "stage.Orders";DROP INDEX "byRegionStatus" ON "stage.Orders";

Lowers to UpdateTable with a GlobalSecondaryIndexUpdates delete. DROP GSI is the clearest DynamoDB spelling; DROP GLOBAL SECONDARY INDEX and SQL-flavoured DROP INDEX are equivalent aliases. The explicit ON "table" keeps the target unambiguous. This removes only the index — every item in the base table remains intact.

CREATE / DROP VECTOR INDEX — native similarity search

CREATE VECTOR INDEX "ProductEmbeddingIndex" ON "Products" (Embedding)  DIMENSIONS 1536  DISTANCE COSINE  PARTITION BY (Category)  FILTER BY (Brand, Status)  PROJECT ALL;​DROP VECTOR INDEX "ProductEmbeddingIndex" ON "Products";

CREATE VECTOR INDEX lowers to UpdateTable.VectorIndexUpdates.Create; DROP VECTOR INDEX lowers to the matching delete. The definition names exactly one vector attribute, 1–4096 dimensions, and COSINE, EUCLIDEAN, or DOT_PRODUCT distance. PARTITION BY adds at most one HASH search-schema attribute; FILTER BY adds up to 18 INLINE_FILTER attributes; PROJECT is ALL, KEYS, or an attribute list.

Creation is Professional and validates against a fresh DescribeTable: the source table must use ON_DEMAND billing, the name must be new, and the table must be below its five-vector-index limit. Indexes that share the same vector attribute must agree on DIMENSIONS. Both create and drop use the standard Run, then unchanged Run again DDL confirmation.

BACKUP TABLE / RESTORE TABLE — managed snapshots

BACKUP TABLE "stage.Orders" AS 'orders-2026-06-19';​RESTORE TABLE "stage.OrdersRestored"  FROM BACKUP 'arn:aws:dynamodb:…:table/Orders/backup/…';

BACKUP TABLE … AS 'name' lowers to CreateBackup: an on-demand, DynamoDB-managed snapshot with no PITR prerequisite and no S3 bucket. The run reports the backup ARN. RESTORE TABLE "new" FROM BACKUP 'arn' lowers to RestoreTableFromBackup, creates a new table, and tracks it from CREATING to ACTIVE; the target name must not already exist.

S3 Tables analytics replicas

CREATE ANALYTICS REPLICA IF NOT EXISTS FOR TABLE "stage.Orders"  WITH (refresh_interval = INTERVAL '1' HOUR, unnest = FULL);​ALTER ANALYTICS REPLICA FOR TABLE "stage.Orders"  SET refresh_interval = INTERVAL '6' HOUR;​DROP ANALYTICS REPLICA FOR TABLE "stage.Orders";

CREATE ANALYTICS REPLICA provisions a DynamoDB to Amazon S3 Tables zero-ETL integration for the named source table. DynoStudio expands the statement into the ordered AWS setup plan: DynamoDB PITR / resource-policy checks, the S3 Tables bucket and analytics catalog, Lake Formation and Glue wiring, the IAM service role, and the Glue integration itself. Every native call, likely permission gap, and recurring cost line is previewed before consent.

IF NOT EXISTS makes the create path resume-friendly: if a previous provision stopped partway through, DynoStudio resumes from the saved step instead of re-minting resources. ALTER ANALYTICS REPLICA updates supported settings such as refresh cadence, while warning when a change can force a full resync. DROP ANALYTICS REPLICA tears down the integration through the same gated control-plane path.

OpenSearch projections

CREATE OPENSEARCH INDEX "open-orders" ON "stage.Orders"  PROJECT (orderId, notes, status, total)  MAPPING (notes SEARCH, status FILTER)  ON INSERT, MODIFY, REMOVE  WHERE status <> 'ARCHIVED';​DESCRIBE OPENSEARCH INDEX "open-orders";ALTER OPENSEARCH INDEX "open-orders" DROP WHERE;REINDEX OPENSEARCH INDEX "open-orders";DROP OPENSEARCH INDEX "open-orders" INCLUDING DOCUMENTS;

CREATE OPENSEARCH INDEX provisions a derived search projection: a PITR export seeds existing rows, then DynamoDB Streams and OpenSearch Ingestion keep it current. PROJECT shapes documents, MAPPING assigns SEARCH, FILTER, GEO, or VECTOR roles, ON selects propagated events, and an optional bounded WHERE keeps only an explicit subset. DynamoDB remains the system of record; search is eventually consistent.

DESCRIBE / SHOW report registration and pipeline health. One-action ALTER changes future ingestion; REINDEX fully replaces and reseeds documents so historical rows match the current contract. Plain DROP unwires the feed but retains documents; INCLUDING DOCUMENTS waits for the writer to disappear, then deletes the physical index behind a type-the-index-name confirmation.

Kanject marker indexes are not DDL

A Kanject marker index is not a GSI or an AWS control-plane resource. It is a model-declared access path materialized as reserved rows in the base table. Its exact keys can depend on annotation names, template and rangeTemplate expressions, several entity properties, and the Kanject generator contract — more information than a column-only CREATE INDEX statement contains.

DynoStudio therefore does not create, alter, refresh, or drop marker rows through PartiQL DDL. Use DynoStudio to understand the model, generated access paths, reserved rows, and drift. Kanject's generated index-aware write methods maintain markers for new application writes; existing-data backfill or reconciliation belongs to a separate durable migration built from the same model metadata. See Kanject marker indexes for that contract.

DROP TABLE

DROP TABLE "stage.Ledger"   -- irreversible; the run stays disarmed                            -- until you type the exact table name

Lowers to DeleteTable. DELETE TABLE is an accepted spelling. (A set-based DELETE FROM … WHERE is a bulk data write from Writes & transactions, never a table drop — the two never collide.)

CREATE GLOBAL INDEX "byEmail" ON "stage.Users"  PARTITION KEY (email)  PROJECT ALL

ALTER ANALYTICS REPLICA FOR TABLE "stage.Orders"  SET refresh_interval = INTERVAL '12' HOUR

CREATE VECTOR INDEX "ProductEmbeddingIndex" ON "Products" (Embedding)  DIMENSIONS 1536 DISTANCE COSINE  PARTITION BY (Category) FILTER BY (Brand, Status)  PROJECT (ProductId, Title)