PartiQL: tables, indexes & analytics replicas (DDL)

View .md

The control plane of the dialect series: 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 — 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 GSISample
Run it live in DynoStudio →
Executes asUpdateTable · stage.Orders (+1 GSI)
UpdateTable GlobalSecondaryIndexUpdates: [ Create byRegionStatus · keys (region, tenant)/(status, createdAt) · Projection ALL ]
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.
Index byRegionStatus · CREATING → ACTIVE · no item rewrite · a read-only stage would refuse this

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.

Kanject marker indexes

sql 
-- UNIQUE: one reserved row per distinct value; backfill refuses on collisionsCREATE 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 markerCREATE 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.

ALTER & DROP a marker

sql 
ALTER UNIQUE INDEX ON "stage.Users" (email) REFRESH                 -- reconcile to current dataALTER UNIQUE INDEX ON "stage.Users" (email) SET WHERE status = 'active'  -- re-scope a partial markerDROP  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, never a table drop — the two never collide.)

AWS background
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 3
1 Give a Scan its index
Reads filtering stage.Users by email keep flagging amber. Create the index that turns them into Queries.
The fix for a Scan is a GSI whose partition key is the attribute you filter on — the move from Targeted reads.
Show 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.
A trailing WHERE makes a marker partial — a partial UNIQUE only conflicts within that scope.
Show 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.
ALTER ANALYTICS REPLICA … SET updates supported settings — and warns when a change can force a full resync.
Show 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
Was this page helpful?