CTAS & Derived Tables

Goal: build a new table from a SQL query over an existing one. This is K3’s in-database ETL — denormalize, sessionize, summarize, snapshot — without leaving the bucket.

Materialization is SQL. There is no Materialize RPC and no /materialize HTTP route; every shape below is a statement sent through Tables.Execute (or any wire that carries SQL — the Postgres door, dodil data sql, the tables HTTP door).

Shape:

  Source table(s)  ──►  SELECT (joins, aggregates, windows, JSON ops, …)
                                │
                                ▼
                       New / appended / replaced
                              target table
                       (own Delta history)

Prerequisites

• A bucket with at least one populated table. We’ll use the events table from Manual Table.
• A connection on any SQL-carrying wire — see Connect & wire adapters.

1. Simplest CTAS — summarize a source

Derive a click_summary table from events:

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;

-- paste the statement above, then:
SELECT * FROM click_summary ORDER BY n DESC LIMIT 5;

conn.execute("""
    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
""")
print(conn.execute("SELECT * FROM click_summary ORDER BY n DESC LIMIT 5").fetchall())

dodil data sql -b kb-prod "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"

# Canonical native REST on the tables door: the db is a PATH segment.
curl -sS -X POST "https://table.uk-lon-1.dodil.io/v1/databases/kb-prod/sql/execute" \
  -H "Authorization: Bearer $DODIL_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{"sql":"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"}'

{ "kind": "ddl" }

Over gRPC the same statement returns a DdlResult:

{
  "statement_kind": "create_table",
  "table_name": "click_summary",
  "version": 0,
  "column_count": 4,
  "pk_columns": ["_rowid"],
  "rows_backfilled": 342
}

Under the hood the dispatcher:

1. Runs DESCRIBE <your SELECT> to learn the result’s column names and types — even for a zero-row result, so an empty source still produces a correctly-typed table.
2. Creates the target from that inferred schema, with every column nullable.
3. Runs the SELECT as a strong materializing read and bulk-writes the rows in. rows_backfilled is the count.

FROM is required. A FROM-less CTAS (CREATE TABLE t AS SELECT 1) is refused — the planner needs a source table to register the read against.

The hidden _rowid key

A CTAS with no PRIMARY KEY gets one minted for it: a hidden _rowid VARCHAR DEFAULT generate_ulid() column, which becomes the table’s sole PK. This gives every result row its own key — bag semantics, matching Postgres, where CTAS preserves duplicates. (The alternative, keying on the whole row, would silently collapse duplicates.) _rowid is hidden from SELECT * on the Postgres wire, but it is the reason a keyless CTAS table won’t accept keyed UPDATE/DELETE on your columns — declare a real PK if you need those.

Type inference rules

The inferred type is DuckDB’s result type mapped onto the plane vocabulary:

Warnings ride back on the statement (warnings on ExecuteResponse; psql shows them as notices). Read them — a silent HUGEINT narrowing is exactly the kind of thing you want to know about.

Note the asymmetry with explicit DDL: an INTEGER column you declare stores as long (so a foreign key can’t truncate against a snowflake PK), but an INTEGER the CTAS infers stays int. If you care, declare the columns.

2. CTAS with explicit columns, PK and partitioning

When you don’t want inference surprises, declare everything. The plane accepts the full CREATE TABLE (…) PARTITIONED BY (…) AS SELECT … shape:

CREATE TABLE click_summary (
  user_id    VARCHAR   NOT NULL,
  n          BIGINT    NOT NULL,
  last_click TIMESTAMP NOT NULL,
  PRIMARY KEY (user_id)
)
AS
SELECT user_id, COUNT(*) AS n, MAX(occurred_at) AS last_click
FROM events
WHERE event_type = 'click'
GROUP BY user_id;

Two payoffs: the types are yours, and PRIMARY KEY (user_id) makes subsequent UPDATE / DELETE / MERGE against click_summary route through the keyed write path for free — no hidden _rowid.

To partition the target as well:

CREATE TABLE events_repartitioned (
  user_id    VARCHAR NOT NULL,
  event_type VARCHAR NOT NULL,
  occurred_at TIMESTAMP,
  payload    JSON,
  PRIMARY KEY (user_id, occurred_at)
)
PARTITIONED BY (event_type, user_id)
AS
SELECT user_id, event_type, occurred_at, payload FROM events;

Reads that filter on (event_type, user_id) prune both partitions — useful for shifting a hot read shape without rewriting the source. PARTITIONED BY references must name declared columns, or the statement is refused.

3. Incremental materialization — INSERT … SELECT

Build a daily summary table that grows over time. Create it once, then append a slice per run:

-- Day 1: create the target
CREATE TABLE daily_event_counts (
  day        DATE    NOT NULL,
  event_type VARCHAR NOT NULL,
  n          BIGINT  NOT NULL,
  PRIMARY KEY (day, event_type)
)
PARTITIONED BY (day)
AS
SELECT DATE_TRUNC('day', occurred_at)::DATE AS day, event_type, COUNT(*) AS n
FROM events
WHERE occurred_at >= TIMESTAMP '2026-05-26'
  AND occurred_at <  TIMESTAMP '2026-05-27'
GROUP BY 1, 2;

-- Every following night: load one day, idempotently
INSERT INTO daily_event_counts (day, event_type, n)
SELECT DATE_TRUNC('day', occurred_at)::DATE AS day, event_type, COUNT(*) AS n
FROM events
WHERE occurred_at >= TIMESTAMP '2026-05-27'
  AND occurred_at <  TIMESTAMP '2026-05-28'
GROUP BY 1, 2
ON CONFLICT (day, event_type) DO UPDATE SET n = EXCLUDED.n;

INSERT … SELECT requires the target to already exist and the projected columns to line up with the explicit column list. The ON CONFLICT DO UPDATE clause is what makes the nightly job safely idempotent: a plain INSERT of a (day, event_type) pair already loaded is a unique violation (SQLSTATE 23505, by design — see Writing rows), so a bare re-run of the same day would fail, not upsert. Drop the PK if you genuinely want append-only bag semantics.

4. Snapshot refresh — CREATE OR REPLACE TABLE

For a dashboard view that should always reflect the current state of the source rather than a growing log:

CREATE OR REPLACE TABLE top_users_24h AS
SELECT user_id, COUNT(*) AS events_24h
FROM events
WHERE occurred_at > NOW() - INTERVAL 1 DAY
GROUP BY user_id
ORDER BY events_24h DESC
LIMIT 100;

Without OR REPLACE (or IF NOT EXISTS), a CREATE TABLE onto a name that already exists is refused — the SQL surface rejects it rather than silently succeeding. IF NOT EXISTS is the opposite choice: keep whatever is there, do nothing.

Each OR REPLACE run is a fresh Delta table generation, so history accumulates on the target. Vacuum reclaims it after the retention window — see Time Travel & Restore.

5. JOINs — just name the tables

You do not need to register lookup tables or alias them into the call. The reader registers an overlay ∪ Delta view for every table the statement references — joined tables, CTE bodies, derived tables and subqueries in the WHERE clause — so a plain literal name resolves, and it sees un-drained writes on both sides:

CREATE OR REPLACE TABLE user_click_counts AS
SELECT e.user_id, u.email, COUNT(*) AS clicks
FROM events e
JOIN users u ON e.user_id = u.id
WHERE e.event_type = 'click'
GROUP BY e.user_id, u.email;

6. Sessionization — a worked window-function example

Compute session boundaries with LAG() — a common preparation for analytics dashboards. Sessions are gaps greater than 30 minutes:

CREATE OR REPLACE TABLE events_sessionized (
  user_id        VARCHAR   NOT NULL,
  occurred_at    TIMESTAMP NOT NULL,
  event_type     VARCHAR,
  payload        JSON,
  session_number BIGINT,
  PRIMARY KEY (user_id, occurred_at)
)
PARTITIONED BY (user_id)
AS
WITH gaps AS (
  SELECT user_id, occurred_at, event_type, payload,
         occurred_at - LAG(occurred_at)
           OVER (PARTITION BY user_id ORDER BY occurred_at) AS gap
  FROM events
)
SELECT user_id, occurred_at, event_type, payload,
       SUM(CASE WHEN gap IS NULL OR gap > INTERVAL 30 MINUTE THEN 1 ELSE 0 END)
         OVER (PARTITION BY user_id ORDER BY occurred_at
               ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS session_number
FROM gaps;

The result carries the original event rows plus a session_number per user — partitioned by user_id so per-user session queries prune efficiently.

7. Verify the new table

DESCRIBE click_summary;
 
SELECT * FROM click_summary ORDER BY n DESC LIMIT 5;

\d click_summary
SELECT COUNT(*) FROM click_summary;

dodil data table describe click_summary --bucket kb-prod
dodil data table query "SELECT * FROM click_summary ORDER BY n DESC LIMIT 5" --bucket kb-prod

Common gotchas

See also

• Execute → DDL — CREATE TABLE + CTAS spec
• Time Travel & Restore — pin a restore point by materializing a snapshot
• Manual Table — the source events table this recipe runs against
• SQL Compatibility → DML — reads — window functions / CTEs / JOIN reference