# PartiQL: tables, indexes & analytics replicas (DDL)

The control plane of the [dialect series](https://www.kanject.com/docs/dynostudio-partiql/): create and shape the tables, indexes, Kanject markers, and S3 Tables analytics replicas around your workload. DynamoDB's own PartiQL has **no DDL** — you can't `CREATE` or `DROP` through a statement — so DynoStudio adds a SQL-shaped DDL surface that **lowers to the native AWS calls the services actually use**. As everywhere in the dialect, the "Executes as" strip shows that plan before you run, and a read-only stage refuses all mutating control-plane work.

**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
- Build a Kanject **marker** index, and understand its backfill and collision refusal
- Provision an **S3 Tables analytics replica** when a report should move off the live table
- Reconcile a marker with `ALTER`, and 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 GSI — native, multi-key aware

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

Lowers to `UpdateTable` with a new global secondary index. `CREATE GLOBAL SECONDARY INDEX` and a bare `CREATE INDEX` are accepted spellings (the console autocompletes toward `CREATE GSI`). A native GSI carries **up to four partition and four sort attributes** — the multi-key shape from [Targeted reads](https://www.kanject.com/docs/dynostudio-partiql-reads/) — with types defaulting to string and nameable per attribute (`createdAt 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 GSI**

```sql
CREATE GSI "byRegionStatus" ON "stage.Orders"
  PARTITION KEY (region, tenant)
  SORT KEY (status, createdAt N)
  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://www.kanject.com/dynostudio/

## 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.

## Kanject marker indexes

```sql
-- UNIQUE: one reserved row per distinct value; backfill refuses on collisions
CREATE UNIQUE INDEX ON "stage.Users" (email)
CREATE UNIQUE INDEX "uxTenantEmail" ON "stage.Users" (tenantId, email)

-- COLLECTION / RANGE: one reserved row per item (need a sort key)
CREATE COLLECTION INDEX ON "stage.Orders" (status)
CREATE RANGE      INDEX ON "stage.Scores" (points)

-- a trailing WHERE makes it a PARTIAL marker
CREATE UNIQUE INDEX ON "stage.Users" (email) WHERE status = 'active'
```

A Kanject **marker** index is *not* a GSI. It's a set of **reserved rows in the base table itself** that the Kanject Core library reads to enforce and resolve a constraint DynamoDB has no native concept of. Creating one backfills those rows from existing data, in one of two shapes:

- **`UNIQUE`** (single- or multi-column) writes **one reserved row per distinct value**. The studio scans, groups by the value, and **if a value repeats it refuses and lists the collisions** — it never picks a winner; you resolve them and re-run. A multi-column (composite) unique needs a name matching the entity's `[CompositeUnique("…")]` group.
- **`COLLECTION` / `RANGE`** write **one reserved row per item** (a membership marker, no uniqueness check): `COLLECTION` buckets items by the value, `RANGE` pools them into one value-sorted bucket for range scans. Both need the base table to have a **sort key**.
- **Partial markers** — a trailing `WHERE` indexes only the matching rows (a partial `UNIQUE` only conflicts *within* that scope). The predicate is applied per scanned item during the backfill; an unparseable one **refuses** rather than silently index everything.

> **Note:** Markers work **in conjunction with the Kanject Core library**: DynoStudio backfills the reserved rows, and the matching annotation (`[Unique]`, `[Collection]`, …) is what *enforces* the constraint on every future write. So a backfill nudges you to add that annotation — the backfill alone is point-in-time. It runs from the PartiQL console and Browse's PartiQL mode for **bounded tables** (the in-app path is scan-capped); larger tables route to an AWS Glue job.

## ALTER & DROP a marker

```sql
ALTER UNIQUE INDEX ON "stage.Users" (email) REFRESH                 -- reconcile to current data
ALTER UNIQUE INDEX ON "stage.Users" (email) SET WHERE status = 'active'  -- re-scope a partial marker
DROP  UNIQUE INDEX ON "stage.Users" (email)                        -- delete the reserved rows
```

`ALTER … REFRESH` **reconciles** — one scan recomputes the reserved rows from current data and diffs them against what's stored: it adds the missing and removes the stale, reporting the diff. A `UNIQUE` reconcile can surface **new** conflicts and refuses if so, just like create. `SET WHERE` reconciles to a new predicate scope (items now outside it lose their rows). `DROP … INDEX` deletes the marker's reserved rows — and if the entity is still annotated, the SDK re-creates them on the next write, so drop the annotation too.

## 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://www.kanject.com/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 GSI` lowers to `UpdateTable` for a native, multi-key index that DynamoDB **auto-backfills** with no item rewrite.
- `CREATE ANALYTICS REPLICA` provisions and monitors a DynamoDB to S3 Tables analytics replica, with every native AWS call and recurring cost previewed before consent.
- Kanject **markers** are reserved base-table rows — `UNIQUE` refuses on collisions, `COLLECTION` / `RANGE` need a sort key, a trailing `WHERE` makes them partial.
- `ALTER … REFRESH` reconciles a marker; `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://www.kanject.com/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 GSI "byEmail" ON "stage.Users"
  PARTITION KEY (email)
  PROJECT ALL
```

**2. Scope a uniqueness rule**

Enforce that *active* users have unique emails — deactivated accounts may share one.

_Hint:_ A trailing `WHERE` makes a marker partial — a partial `UNIQUE` only conflicts *within* that scope.

_Solution:_ The backfill scans, groups by value within the scope, and refuses with the collisions listed if any exist. Pair it with the `[Unique]` annotation in Kanject Core so future writes stay enforced — the backfill alone is point-in-time.

```sql
CREATE UNIQUE INDEX ON "stage.Users" (email)
WHERE status = 'active'
```

**3. 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
```

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

---
_Source: https://www.kanject.com/docs/dynostudio-partiql-ddl/ · Kanject Docs_