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

The control plane of the [dialect series](https://dynostudio.dev/docs/dynostudio-partiql/): 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.

**You'll learn**

- Create a table, and understand why key **types** are required up front
- Add a **multi-key GSI** and read why DynamoDB auto-backfills it for free
- Drop a GSI without touching its base-table items
- Create a native **vector index**, wait for backfill, search it, and drop it without touching the table
- Take a DynamoDB-managed backup and restore it into a **new table**
- Provision an **S3 Tables analytics replica** when a report should move off the live table
- Provision and manage an eventually consistent **OpenSearch projection** for full-text, facets and highlights
- Understand why Kanject **marker** lifecycle is model-driven rather than DDL
- Drop a table safely

## CREATE TABLE

```sql
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

```sql
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](https://dynostudio.dev/docs/dynostudio-partiql-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`.

**Try it · CREATE GLOBAL INDEX**

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

_Executes as:_ UpdateTable · stage.Orders (+1 GSI) — Lowers to the control-plane UpdateTable call — disclosed before it runs. DynamoDB auto-backfills the index from existing attributes (no item rewrite); queryable once ACTIVE.

_Result:_ Index byRegionStatus · CREATING → ACTIVE · no item rewrite · a read-only stage would refuse this

_Run it live in DynoStudio:_ https://dynostudio.dev/dynostudio/

## DROP GSI — remove a native index

```sql
DROP GSI "byRegionStatus" ON "stage.Orders";

-- accepted aliases
DROP 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.

> **The index stops serving reads immediately:** The first Run arms the drop and the second unchanged Run confirms it. Once DynamoDB accepts the request, queries against the GSI fail immediately while its storage is deleted in the background. Recreating it later starts a fresh backfill. A read-only stage refuses the drop outright.

## CREATE / DROP VECTOR INDEX — native similarity search

```sql
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.

> **Backfill is part of the lifecycle:** The receipt and Background Tasks panel track create through descriptor appearance, backfill, and ready (`ACTIVE` with `Backfilling` cleared). Searches refuse until ready. Drop is tracked until the descriptor disappears, and never deletes a base-table item. Full syntax and examples: [CREATE / DROP VECTOR INDEX](https://dynostudio.dev/docs/dynostudio-vector-index/).

## BACKUP TABLE / RESTORE TABLE — managed snapshots

```sql
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.

> **Backup and restore do not need a destructive confirmation:** Backup adds a snapshot and restore creates a new table, so both run on the first press in the PartiQL console and Browse's PartiQL mode. A read-only stage still refuses them. See [BACKUP / RESTORE TABLE](https://dynostudio.dev/docs/dynostudio-backup-restore/) for the full contract.

## S3 Tables analytics replicas

```sql
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.

> **Replica first, routing explicit:** The launch flow provisions, manages, and monitors the analytics replica. DynoStudio does not silently reroute a live-table query: the execution plane must be visible, just like Query vs Scan is visible today. Replica reads carry their own engine and cost disclosure when routed there.

> **The cost moved, it did not vanish:** DynamoDB exports for the integration do not consume live-table RCUs, but the replica still has a bill: PITR storage, export GB, S3 Tables storage and compaction, plus read-side compute when you query it. The preview shows that trade before provisioning.

## OpenSearch projections

```sql
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.

> **Search is a separate Professional plane:** The stage needs a Search binding and the source needs Streams plus PITR. OpenSearch statements and reads are Professional, SigV4-signed, resume-safe, ownership-checked, and never silently adopt an existing foreign index or pipeline. Full lifecycle and query syntax: [OpenSearch indexes and queries](https://dynostudio.dev/docs/dynostudio-opensearch-index/).

## 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](https://dynostudio.dev/docs/dynostudio-marker-indexes/) for that contract.

## DROP TABLE

```sql
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](https://dynostudio.dev/docs/dynostudio-partiql-writes/), never a table drop — the two never collide.)

> **DROP TABLE is irreversible:** Dropping a table cannot be undone, so — like the AWS Console — the run opens a confirmation that stays **disarmed until you type the exact table name**. A read-only stage refuses it outright, at the dispatch layer, regardless of which surface issued it.

**AWS background**

- [Working with tables in DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/WorkingWithTables.Basics.html) — AWS docs: CreateTable, key schema, attribute types, and billing modes.
- [Managing global secondary indexes](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GSI.OnlineOps.html) — AWS docs: adding a GSI via UpdateTable, and how the index backfills before it goes ACTIVE.

**Recap**

- `CREATE TABLE` lowers to `CreateTable`; key **types are required** because DynamoDB fixes them at creation.
- `CREATE GLOBAL INDEX` lowers to `UpdateTable` for a native, multi-key GSI that DynamoDB **auto-backfills** with no item rewrite.
- `DROP GSI "index" ON "table"` lowers to an `UpdateTable` delete; it preserves base-table items, but index reads fail immediately.
- `CREATE / DROP VECTOR INDEX` lowers to `UpdateTable.VectorIndexUpdates`; searches wait for `ACTIVE` with backfill cleared, and drop preserves base-table items.
- `BACKUP TABLE` creates a managed snapshot and reports its ARN; `RESTORE TABLE` uses that ARN to create a new table.
- `CREATE ANALYTICS REPLICA` provisions and monitors a DynamoDB to S3 Tables analytics replica, with every native AWS call and recurring cost previewed before consent.
- `CREATE / ALTER / REINDEX / DROP OPENSEARCH INDEX` manages an eventually consistent derived search projection; DynamoDB remains authoritative.
- Kanject **markers** are model-declared reserved rows, not PartiQL DDL; lifecycle migrations must use the canonical Kanject model metadata.
- `DROP TABLE` lowers to `DeleteTable` and stays disarmed until you type the exact name.

**Try it yourself**

**1. Give a Scan its index**

Reads filtering `stage.Users` by `email` keep flagging amber. Create the index that turns them into Queries.

_Hint:_ The fix for a Scan is a GSI whose partition key *is* the attribute you filter on — the move from [Targeted reads](https://dynostudio.dev/docs/dynostudio-partiql-reads/).

_Solution:_ Lowers to `UpdateTable`; DynamoDB auto-backfills the index from existing attributes, and once it goes `ACTIVE`, `FROM "stage.Users"."byEmail" WHERE email = …` is a key-condition Query.

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

**2. Slow a replica down**

Your `stage.Orders` analytics replica refreshes hourly, but the report that reads it runs daily. Cut the refresh cadence to every 12 hours.

_Hint:_ `ALTER ANALYTICS REPLICA … SET` updates supported settings — and warns when a change can force a full resync.

_Solution:_ The alter runs through the same gated control-plane path as create, with the cost trade previewed before consent — a slower cadence usually means fewer export runs billed.

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

**3. Create the vector read path**

Product embeddings have 1,536 dimensions. Searches must stay within one category and may filter by brand and status.

_Hint:_ `PARTITION BY` declares the required equality attribute; `FILTER BY` declares optional equality filters. Neither clause projects result fields by itself.

_Solution:_ The definition lowers to one `UpdateTable` create. The receipt tracks backfill, and the matching `VECTOR_DISTANCE` query stays refused until the descriptor is ACTIVE and no longer backfilling.

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

> **The whole dialect, disclosed:** That's the whole dialect — from a first `SELECT` to the control plane that shapes and protects the data itself, every lowering disclosed along the way. Keep the [keyword reference](https://dynostudio.dev/docs/dynostudio-partiql-keywords/) open as you write, or head back to [the series overview](https://dynostudio.dev/docs/dynostudio-partiql/) for the use-case map.

---
_Source: https://dynostudio.dev/docs/dynostudio-partiql-ddl/ · DynoStudio Docs_