Manual Table

Goal: build a structured table from scratch — schema you control, data you push, queries you write — then keep it healthy with periodic maintenance. The canonical SQL-first workflow.

Shape:


CREATE TABLE   →   INSERT / MERGE / UPDATE / DELETE
                                   │
                        ┌──────────┼──────────┐
                        ▼          ▼          ▼
                     SELECT     Compact    Optimize
                   (read-your-  (drain WAL  (bin-pack
                    writes)      → Delta)    files)

Prerequisites

A bucket — kb-prod:


dodil data bucket create kb-prod -d "Events warehouse"

A credential for the wire you’re using — API key, service account, or a bearer JWT from dodil auth login. All three work on every wire; see Connect & wire adapters.
Tables are implicit per bucket. There is nothing to enable.

Connect once and reuse the session for the whole recipe:

psql


# sslmode=require — the pg door enters on a raw host port (5432) but does
# terminate TLS. Never `prefer`: it falls back to plaintext without telling you,
# and your password here is your API-key/service-account secret.
# dbname is the bare bucket name.
psql "host=pg.uk-lon-1.dodil.io port=5432 dbname=kb-prod \
      user=dk_XXXX password=$DODIL_SECRET sslmode=require"

Python (psycopg)


import os
import psycopg
 
conn = psycopg.connect(
    host="pg.uk-lon-1.dodil.io",
    port=5432,
    dbname="kb-prod",          # the bare bucket name
    user="dk_XXXX",
    password=os.environ["DODIL_SECRET"],
    sslmode="require",         # the pg wire terminates TLS; never "prefer"
    autocommit=True,
)

dodil data


# Runs SQL over the tables-gateway gRPC door (Tables.Execute).
dodil data sql -b kb-prod "SELECT 1"
 
# Or force the Postgres wire as the transport:
dodil data pg  -b kb-prod "SELECT 1"
 
# Print a ready connection URL for your own client:
dodil data connect kb-prod -o psql

1. Create the table

We’ll model an events table with a composite primary key, a partition column, and a JSON column for arbitrary payload:


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);

psql


-- paste the CREATE TABLE above straight into the psql prompt
\d events

Python (psycopg)


conn.execute("""
    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)
""")

dodil data


# Either send the DDL as SQL …
dodil data sql -b kb-prod "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)"
 
# … or use the structured shortcut (CreateTable RPC).
dodil data table create events --bucket kb-prod \
  --description "Click + purchase events" \
  --columns-json '[
    {"name":"id",          "type":"bigint",    "nullable":false},
    {"name":"user_id",     "type":"varchar",   "nullable":false},
    {"name":"occurred_at", "type":"timestamp", "nullable":false},
    {"name":"event_type",  "type":"varchar",   "nullable":false},
    {"name":"payload",     "type":"json",      "nullable":true}
  ]' \
  --partition-column event_type \
  --merge-key id --merge-key user_id

The two key choices:

Choice	Effect
Composite PK (`PRIMARY KEY (id, user_id)`, or `--merge-key` twice)	Writes whose predicate matches all PK columns route through the write-ahead log → drain → Delta MERGE, and every read observes them immediately. Without a PK, CTAS-style keyless tables get a hidden `_rowid` and keyed DML is unavailable.
Partition column (`PARTITIONED BY (event_type)`)	Delta physically groups rows by this column. Reads that filter on it prune whole partitions.

Type vocabulary. The plane accepts string, int, long, float, double, boolean, date, timestamp, binary, json, decimal(p,s), vector(dim), struct<…>, array<…> plus short. SQL spellings map onto it — and the whole integer family (INT, INTEGER, BIGINT, SERIAL) stores as long, deliberately, so a foreign key can’t truncate against a snowflake PK. See SQL Compatibility → Column types.

Verify the schema:


DESCRIBE events;

2. Insert rows


INSERT INTO events (id, user_id, occurred_at, event_type, payload) VALUES
  (1, 'u-101', TIMESTAMP '2026-05-27 10:00:00', 'click',    '{"page":"/pricing"}'),
  (2, 'u-101', TIMESTAMP '2026-05-27 10:01:00', 'click',    '{"page":"/signup"}'),
  (3, 'u-102', TIMESTAMP '2026-05-27 10:02:00', 'purchase', '{"sku":"A-12","amount":49.99}');

psql


-- Bulk load from a local file — COPY FROM STDIN is a Postgres-wire-only
-- capability (pg TEXT format, tab-separated, \N = NULL; CSV also parses).
\copy events (id, user_id, occurred_at, event_type, payload) FROM 'events.csv' CSV

Python (psycopg)


rows = [
    (1, "u-101", "2026-05-27 10:00:00", "click",    '{"page":"/pricing"}'),
    (2, "u-101", "2026-05-27 10:01:00", "click",    '{"page":"/signup"}'),
    (3, "u-102", "2026-05-27 10:02:00", "purchase", '{"sku":"A-12","amount":49.99}'),
]
 
# Prepared statements + executemany: Postgres-wire only.
conn.cursor().executemany(
    "INSERT INTO events (id, user_id, occurred_at, event_type, payload)"
    " VALUES (%s, %s, %s, %s, %s)",
    rows,
)
 
# Or stream a large load through COPY.
with conn.cursor().copy(
    "COPY events (id, user_id, occurred_at, event_type, payload) FROM STDIN"
) as copy:
    for r in rows:
        copy.write_row(r)

dodil data


# The typed shortcut — each --row is one JSON object, lowered to an INSERT.
dodil data table insert events --bucket kb-prod \
  --row '{"id":1,"user_id":"u-101","occurred_at":"2026-05-27 10:00:00","event_type":"click",   "payload":{"page":"/pricing"}}' \
  --row '{"id":2,"user_id":"u-101","occurred_at":"2026-05-27 10:01:00","event_type":"click",   "payload":{"page":"/signup"}}' \
  --row '{"id":3,"user_id":"u-102","occurred_at":"2026-05-27 10:02:00","event_type":"purchase","payload":{"sku":"A-12","amount":49.99}}'


{ "rows_affected": 3 }

--mode overwrite lowers to INSERT OR REPLACE INTO instead of INSERT INTO — but note the OR REPLACE modifier is inert: the statement lowers as a plain INSERT and still fails with SQLSTATE 23505 on an existing key (details). Use dodil data table upsert when you mean replace.

timestamp columns accept both a TIMESTAMP '…' literal and microseconds since the Unix epoch, UTC.

3. Query — reads are read-your-writes


SELECT event_type, COUNT(*) AS n
FROM events
GROUP BY event_type
ORDER BY n DESC;


event_type  n
click       2
purchase    1

There is no freshness knob. A write lands in the table’s write-ahead log and every subsequent read folds that log over the Delta snapshot, so your own writes are visible immediately. The older Freshness / FRESHNESS_STRONG request field is gone from the plane, and dodil data table query --freshness is a deprecated no-op that prints a warning.

What does exist is an explicit session watermark for read-your-writes across separate connections or processes: a keyed write returns a ULID (WriteAck.wal_ulid, ExecuteResponse.max_wal_ulid), and passing it back as the next read’s min_ulid guarantees that read observes at least that write — or fails over to S3-WAL truth rather than serving a silently stale overlay. On one psql or psycopg connection you never need it; it matters for a fan-out where a different process must see a write it did not make.

4. Upsert with `MERGE`

Replace an existing row and add a new one in one statement:


MERGE INTO events AS t
USING (VALUES
    (1, 'u-101', TIMESTAMP '2026-05-27 10:00:00', 'click_pricing', '{"page":"/pricing","variant":"B"}'),
    (4, 'u-103', TIMESTAMP '2026-05-27 10:03:00', 'signup',        '{"plan":"pro"}')
  ) AS s (id, user_id, occurred_at, event_type, payload)
  ON t.id = s.id AND t.user_id = s.user_id
WHEN MATCHED THEN UPDATE SET
    occurred_at = s.occurred_at, event_type = s.event_type, payload = s.payload
WHEN NOT MATCHED THEN INSERT (id, user_id, occurred_at, event_type, payload)
    VALUES (s.id, s.user_id, s.occurred_at, s.event_type, s.payload);

psql


-- Then confirm both arms landed:
SELECT id, user_id, event_type FROM events
WHERE user_id IN ('u-101', 'u-103')
ORDER BY id;

Python (psycopg)


conn.execute(MERGE_SQL)
print(conn.execute(
    "SELECT id, user_id, event_type FROM events"
    " WHERE user_id IN (%s, %s) ORDER BY id", ("u-101", "u-103"),
).fetchall())

dodil data


# General MERGE with explicit arms (lowered to the SQL above).
dodil data table merge events --bucket kb-prod \
  --match-column id --match-column user_id \
  --when-matched update --when-not-matched insert \
  --row '{"id":1,"user_id":"u-101","occurred_at":"2026-05-27 10:00:00","event_type":"click_pricing","payload":{"page":"/pricing","variant":"B"}}' \
  --row '{"id":4,"user_id":"u-103","occurred_at":"2026-05-27 10:03:00","event_type":"signup","payload":{"plan":"pro"}}'
 
# Or the keyed typed write path — matches the PK by default, returns the
# session watermark (Tables.Upsert, not a SQL lowering).
dodil data table upsert events --bucket kb-prod \
  --row '{"id":4,"user_id":"u-103","event_type":"signup"}' --merge


{ "wal_written": true, "wal_ulid": "01JX…" }

--merge makes it a partial-column merge: columns absent from the row stay untouched. Without it the row replaces the whole tuple.

--when-matched accepts update | delete | skip; --when-not-matched accepts insert | skip.

5. Update + delete


UPDATE events SET event_type = 'click_signup'
WHERE id = 2 AND user_id = 'u-101';
 
DELETE FROM events
WHERE id = 3 AND user_id = 'u-102';

A WHERE clause is mandatory. UPDATE and DELETE are refused without one — the plane will not lower an unbounded write, and dodil data table update / delete-rows both require --predicate for the same reason. To empty a table on purpose, use TRUNCATE TABLE events, which keeps the schema, PK, indexes, graph bindings and reservations.

A predicate that matches all PK columns routes through the write log (keyed). A predicate on non-PK columns is a non-keyed write straight to Delta — see Execute → UPDATE.

SQL


UPDATE events SET event_type = 'click_signup'
WHERE id = 2 AND user_id = 'u-101';

6. Transactions

BEGIN / COMMIT / ROLLBACK are supported. On the Postgres wire they are a real buffered session: statements accumulate and flush as one atomic Delta commit at COMMIT, and savepoints work. Over Tables.Execute a whole BEGIN; …; COMMIT; block lowers to one atomic commit in a single call.


BEGIN;
  UPDATE events SET event_type = 'archived' WHERE id = 1 AND user_id = 'u-101';
  DELETE FROM events WHERE id = 2 AND user_id = 'u-101';
COMMIT;

If a concurrent write touches a key this transaction already observed, the commit aborts with a serialization failure (SQLSTATE 40001) rather than silently overwriting.

7. Maintenance — compact + optimize

Compact drains the write-ahead log into the Delta table; OptimizeTable bin-packs (and optionally Z-orders) the resulting Parquet files. Both are RPCs, not SQL:


# Drain the write log into Delta
dodil data table compact events --bucket kb-prod -o json


{
  "wal_entries_processed": 6,
  "wal_unique_keys": 4,
  "drained_high_ulid": "01JX…"
}


# Bin-pack small Delta files (add --z-order-column to cluster)
dodil data table optimize events --bucket kb-prod -o json


{
  "table_name": "events",
  "version": 5,
  "optimize_type": "COMPACTION",
  "files_added": 1,
  "files_removed": 12
}

You don’t usually need to run these by hand — a background maintenance loop drains and optimizes on its own — but explicit calls are useful:

After large bulk writes, to collapse the log before an analytical scan
As the canonical Compact → Optimize post-batch sequence
In tests / e2e, to assert a deterministic Delta version

Compaction does not gate visibility: rows are readable the moment they are written, drained or not. Draining changes how they are stored, not whether they are seen.

Cleanup

SQL


DROP TABLE IF EXISTS events;

Common gotchas

Symptom	Cause	Fix
`UPDATE`/`DELETE` rejected outright	No `WHERE` clause — the plane refuses unbounded writes	Add a predicate, or use `TRUNCATE TABLE` if you really mean “all rows”
`sslmode=verify-full` fails with “root certificate file … does not exist”	Client-side only: libpq doesn’t read the OS trust store, so psql/psycopg/SQLAlchemy need to be pointed at one. The server’s certificate is a public Let’s Encrypt one and verifies fine	Add `sslrootcert=system` (libpq 16+), or point `sslrootcert` at the system bundle (e.g. `/etc/ssl/cert.pem` on macOS). Go, Node and JVM drivers need nothing — see Choosing an sslmode
Connection refused with a bucket-qualified dbname	The gateway qualifies the db id itself from your authenticated org	Send the bare bucket name as `dbname`
`--freshness` prints a deprecation warning	The freshness knob was removed; reads are read-your-writes	Drop the flag
A second process can’t see a write the first just made	Cross-process read-your-writes needs the watermark	Carry the writer’s `wal_ulid` as the reader’s `min_ulid`
`optimize` reports `files_added: 0`	Table was already well-packed	Not an error — a no-op is the expected steady state
CTAS-created table rejects keyed `UPDATE`	A keyless CTAS mints a hidden `_rowid` PK, not your columns	Declare an explicit `PRIMARY KEY` in the CTAS column list

Variations

Variation	What changes
No-PK table (audit-log style)	Omit `PRIMARY KEY`. Appends are fast; keyed upserts and keyed DML are unavailable.
Multi-column partition	`PARTITIONED BY (event_type, user_id)`. Reads that filter on all partition columns prune most aggressively.
JSON-heavy schema	Lean on `payload`. Trade-off: JSON extraction is slower than a typed-column predicate. Promote hot JSON fields to typed columns when you query them often.
Secondary index	`CREATE INDEX ON events (user_id)` — a non-unique index is single-column. `CREATE UNIQUE INDEX ON events (a, b)` may span columns and enforces `23505` on the write path.
Default expressions	`id VARCHAR DEFAULT GENERATE_UUID()`, `created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP()`, `sort_key VARCHAR DEFAULT ULID()`, or a literal — applied at commit when the column is absent. `GENERATE_UUID()`/`ULID()` produce strings, so the column must be `VARCHAR`. See SQL Compatibility → default_expression.

Manual Table

Prerequisites

psql

Python (psycopg)

dodil data

1. Create the table

psql

Python (psycopg)

dodil data

2. Insert rows

psql

Python (psycopg)

dodil data

3. Query — reads are read-your-writes

4. Upsert with MERGE

psql

Python (psycopg)

dodil data

5. Update + delete

SQL

dodil data

6. Transactions

7. Maintenance — compact + optimize

Cleanup

SQL

dodil data

Common gotchas

Variations

See also

4. Upsert with `MERGE`