DDL

Data-definition statements through Tables.Execute. All of them ride the same RPC — DDL is a spec_json route to the compactor, so nothing here needs a separate endpoint. For the RPC contract, see the Execute hub.

Every statement below returns the ddl arm of ExecuteResponse — a DdlResult carrying table_name, version, column_count, columns_added, pk_columns, already_existed, replaced, objects_deleted and rows_backfilled. The tables HTTP door flattens it to {"kind":"ddl"}.

CREATE TABLE

Inline column-level and table-level PRIMARY KEYs both flow into the planner — later writes get keyed WAL routing for free.

CREATE TABLE events (
  id          BIGINT      NOT NULL,
  user_id     VARCHAR     NOT NULL,
  occurred_at TIMESTAMP   NOT NULL,
  event_type  VARCHAR     NOT NULL,
  payload     JSON,
  PRIMARY KEY (id, user_id)
) PARTITIONED BY (event_type);

Inline-PK form, with defaults:

CREATE TABLE users (
  id    VARCHAR PRIMARY KEY DEFAULT GENERATE_UUID(),
  email VARCHAR NOT NULL,
  tier  VARCHAR DEFAULT 'free'
);

GENERATE_UUID() / ULID() return strings. Declare the column VARCHAR, not BIGINT — a BIGINT column with a UUID default fails on the first insert (dodil-tables/crates/tables-common/src/defaults.rs:17-20).

IF NOT EXISTS and OR REPLACE are both accepted and reported back on DdlResult.already_existed / .replaced (crates/tables-sql/src/ddl.rs:127-145).

The declared integer family stores as long. INTEGER, SMALLINT, BIGINT all land as long so foreign keys cannot truncate against snowflake PKs (crates/htap-planner/src/ddl.rs:440-452).

CREATE TABLE AS SELECT (CTAS)

CREATE TABLE click_summary AS
SELECT user_id, COUNT(*) AS n, MAX(occurred_at) AS last_click
FROM events
WHERE event_type = 'click'
GROUP BY user_id;

The dispatcher creates the table, then materialises the SELECT into it; DdlResult.rows_backfilled carries the row count. Both variants work:

CREATE TABLE IF NOT EXISTS click_summary AS SELECT …;
CREATE OR REPLACE TABLE click_summary AS SELECT …;

You can declare columns and a primary key alongside the SELECT rather than inferring them:

CREATE TABLE click_summary (
  user_id VARCHAR,
  n       BIGINT,
  PRIMARY KEY (user_id)
) AS SELECT user_id, COUNT(*) FROM events GROUP BY user_id;

Type inference maps DuckDB’s result types onto the plane’s vocabulary and warns where it narrows (crates/coordinator/src/execute/ddl_stmt.rs:730-768):

Those warnings ride ExecuteResponse.warnings and are the only signal that a CTAS changed your types — read them.

A keyless CTAS still gets a key. When neither a PRIMARY KEY clause nor an inferrable key exists, the plane adds a hidden _rowid VARCHAR DEFAULT generate_ulid() primary key so the table has bag semantics and Postgres-wire parity. It is hidden from SELECT *, but it means keyed DML on your columns will not route as keyed (ddl_stmt.rs:27-47,434-437).

JOINs in the source SELECT work with plain table names — the reader registers an overlay ∪ Delta view for every referenced table, including CTEs, derived tables and WHERE subqueries (crates/reader/src/service.rs:1085-1098). This is why there is no lookup_tables request field: literal names resolve on their own.

There is no Materialize RPC. Persisting a SELECT into a table is always one of these statements — never a separate call. Each “mode” a materialize API would offer maps to a CTAS variant:

What you want	Statement
Create a new table from a query, fail if it exists	CREATE TABLE t AS SELECT …
Create it only if absent	CREATE TABLE IF NOT EXISTS t AS SELECT …
Replace it wholesale, no drop-then-create window	CREATE OR REPLACE TABLE t AS SELECT …
Append into an existing table	INSERT INTO t SELECT …

The append case is a plain INSERT … SELECT — the SELECT output maps to the target’s columns positionally, never by name:

INSERT INTO click_summary
SELECT user_id, COUNT(*), MAX(occurred_at)
FROM events WHERE event_type = 'click' GROUP BY user_id;

ALTER TABLE

Add, drop and retype columns. Multiple actions in one statement are supported.

ALTER TABLE events ADD COLUMN session_id VARCHAR;
ALTER TABLE events ADD COLUMN session_id VARCHAR, ADD COLUMN device VARCHAR;
ALTER TABLE events DROP COLUMN device;
ALTER TABLE events ALTER COLUMN n TYPE BIGINT;

Existing rows get NULL for added columns. DdlResult.columns_added lists what was added; column_count is the total after the statement, not the number added.

RENAME COLUMN and RENAME TO are refused — they parse cleanly via sqlparser but the executor rejects them (crates/htap-planner/src/ddl.rs:214-215). Recreate via CTAS with the new name.

CREATE INDEX

-- Non-unique: exactly one column (the index IS the `__idx_{table}_{column}` table)
CREATE INDEX ON events (user_id);
 
-- UNIQUE may be composite; enforced on the write path with SQLSTATE 23505
CREATE UNIQUE INDEX ON users (org_id, email);

A multi-column non-unique index is refused with a message naming the UNIQUE alternative. DdlResult.rows_backfilled reports the backfill. Source: crates/tables-sql/src/ddl.rs:160-186.

CREATE INDEX … USING hnsw | ivfflat routes to the vector ANN plan instead — see Vector → Indexes.

DROP TABLE [IF EXISTS]

DROP TABLE events;
DROP TABLE IF EXISTS events;

Removes the Delta directory, the sidecar and the un-drained WAL objects. DropTableResponse reports existed, objects_deleted, bytes_deleted, sidecar_deleted and wal_objects_deleted.

Pipeline-bound tables: DROP TABLE runs on the data plane and does not cascade to the K3 pipeline or ingest rule that feeds it. Clean those up separately via the Pipelines API.

TRUNCATE [TABLE]

TRUNCATE TABLE events;

Removes all rows while keeping the schema, primary key, secondary indexes (emptied), graph bindings and reservations. A single empty Delta commit plus a WAL/overlay purge, all-or-nothing. It is the sanctioned “delete everything” — DELETE without a WHERE clause is refused (crates/tables-sql/src/ddl.rs:96-103).

RESTORE

RESTORE TABLE events TO VERSION AS OF 40;
RESTORE TABLE events TO TIMESTAMP AS OF '2026-05-27T08:00:00Z';

DDL-class time travel — the only rollback surface the plane exposes. The timestamp form requires an offset (Z or +02:00). Full semantics, including the two things it destroys, are on Maintenance → RESTORE.

See also

• Execute — Overview — RPC contract, the one-statement rule, refusals
• Tables → Create — the typed CreateTable RPC
• Maintenance — OptimizeTable · VacuumTable · Compact · RESTORE
• SQL Compatibility — DuckDB dialect, types, JSON ops
• Recipes → Manual table + CTAS — runnable end-to-end flows