Core Concepts — Tables

Package: dodil.tables.v1 · Service: Tables (dodil-tables/proto/api/tables.proto). Wire encodings: gRPC follows proto types directly; HTTP uses pbjson (camelCase, int64 as JSON strings, enums as wire-name strings).

There is no Engine message, no Table message and no HistoryEntry message in the proto. A table is not a control-plane object you register — it is a Delta table in your bucket that SQL creates, describes and drops. What you actually need to understand is the two-tier storage model and how a statement is routed across it.

   bucket ─── database (db_id = "<org>--<bucket>")
                  │
                  └── tables ─── columns (ColumnDef: name, type, nullable, default_expr)
                        │
                        ├── Writes ─► WAL (S3 write-ahead log, keyed by PK)
                        │              │
                        │              └── compactor drains ──► Delta Lake commit log
                        │
                        └── Reads  ─► frontier check
                                       ├── backlog proven empty ──► Delta scan (analytical)
                                       └── otherwise ────────────► overlay ∪ Delta (strong)

Writes append to a WAL in the bucket; an asynchronous compactor drains the WAL into the table’s Delta Lake commit history. Reads are read-your-writes by default; the plane earns the cheaper analytical path only when it can prove the WAL is drained. Maintenance (OptimizeTable / VacuumTable / Compact, and RESTORE SQL) operates on the Delta side.

Freshness is not a client knob

This is the single most important thing to internalise, and it is the opposite of what older versions of this page said.

ExecuteRequest.freshness does not exist, and where a Freshness field survives on the wire for compatibility the runtime ignores the requested value. There is no FRESHNESS_STRONG to ask for and no QueryStrategy enum to inspect. The contract is chosen by state, never by query syntax or by a client flag.

Before every read the coordinator runs a frontier check — one in-memory call asking the writer whether the database’s WAL backlog is empty on an authoritative shard:

Source: dodil-tables/crates/coordinator/src/service.rs:1690-1800. The planner comment is explicit that a syntax rule (“has SUM( → eventual”) was rejected because INSERT; SELECT SUM(…) would then silently miss the just-acked write.

dodil data table query --freshness still parses but is inert. The CLI prints Flag --freshness has been deprecated, reads are read-your-writes by default; this flag is ignored and runs the query anyway.

Watermarks — the explicit handle you do get

The plane is stateless: it holds no session, so the watermark is the session. Every write returns a ULID:

Pass it as the next read’s min_ulid (ExecuteRequest.min_ulid, QueryRequest.min_ulid, GetRowRequest.min_ulid) and the read observes at least that write — or fails over to the S3 WAL. Never a silently-stale overlay. You rarely need it, precisely because strong is already the default.

ServedBy — which tier answered

Returned on ExecuteResponse.served_by, QueryResponse.served_by and friends. Seven values, from dodil-tables/proto/api/types.proto:177-185:

enum ServedBy {
  SERVED_BY_UNSPECIFIED = 0;
  SERVED_BY_HOT         = 1;   // RAM slot
  SERVED_BY_WARM        = 2;   // disk slot
  SERVED_BY_MERGED      = 3;   // overlay ∪ Delta (strong)
  SERVED_BY_WAREHOUSE   = 4;   // Delta scan (analytical)
  SERVED_BY_WAL         = 5;   // overlay only (internal freshness)
  SERVED_BY_HOT_SHARDED = 6;   // sharded hot-set fan-out
}

There is no SERVED_BY_OLTP. HOT / WARM / HOT_SHARDED reflect residency — whether a reader holds the data in RAM or on local disk — which you influence with LOAD TABLE / RELEASE TABLE, not with a freshness request. See SQL Compatibility → Residency verbs.

Strong reads abort rather than lie

A strong read builds its overlay from un-drained WAL entries, and that overlay has a cap. If it truncates, the merged result would be incomplete — so the read is aborted, not served:

FAILED_PRECONDITION: strong read aborted: the WAL overlay truncated at N entries,
so the result would be incomplete — compaction is catching up; retry shortly

Source: dodil-tables/crates/reader/src/service.rs:1341-1354. You never get a silent partial result. The drain hints fire at a fraction of the overlay cap, so by the time truncation is possible a drain is already scheduled — a retry converges. Eventual reads keep serving (stale-tolerant by contract) with a warning on warnings.

Table shape — TableSpec and ColumnDef

The only typed table structure in the proto is the creation spec. There is no Table message describing an existing table; you read a table’s shape back with SQL (DESCRIBE, SHOW TABLES) or with GetDatabaseStats.

message TableSpec {
  string table_name = 1;
  repeated ColumnDef columns = 2;
  repeated string partition_columns = 3;
  repeated string pk_columns = 4;      // composite PK supported
  optional string description = 5;
  bool if_not_exists = 6;
  bool or_replace = 7;
}
 
message ColumnDef {
  string name = 1;
  string type = 2;                     // BIGINT, VARCHAR, DOUBLE, TIMESTAMP…
  bool nullable = 3;
  optional string default_expr = 4;    // GENERATE_UUID(), CURRENT_TIMESTAMP(), literal…
}

ColumnDef.type is a SQL type string, not an enum. There is no ColumnType enum and no COLUMN_TYPE_* names anywhere in the plane — anything that told you to write COLUMN_TYPE_LONG was fiction. The vocabulary is fifteen types, and the whole integer family stores as 64-bit long:

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

Full type table, pg OIDs and wire shapes: SQL Compatibility → Column types. Default expressions (GENERATE_UUID(), CURRENT_TIMESTAMP(), SNOWFLAKE_ID(), literals) are validated at table creation, never on first insert — see default_expression.

Key facts:

• pk_columns (the CLI’s --merge-key) is the table’s primary key, composite supported. It drives write routing — see below — and read-time dedup on the overlay.
• partition_columns controls physical layout in Delta. Reads that filter on a partition column prune whole directories.
• JSON columns are first-class — DuckDB’s ->, ->>, json_extract, json_each, from_json all work. For high-volume read paths promote hot fields to typed columns; reserve json for the long tail.
• A table with no declared PK has no WAL prefix at all — HTAP routing only kicks in for tables with a primary key. DML on such a table goes straight to Delta.

Two creation modes

Creating a pipeline does not start ingesting. CreateTablePipeline binds a template to a table; it does not create an ingest rule. Scoping a rule at the pipeline is a separate, mandatory step — see Templates → Using a template. The one exception is dodil data recipe install, which expands to a collection and a bound rule for you.

Write routing — WriteStrategy

Every DML statement is classified by the planner before it executes, and the strategy names exactly which executor primitive runs. The strategies are a Rust enum serialized in snake_case (dodil-tables/crates/htap-planner/src/ir.rs:108-300) — there is no WRITE_STRATEGY_* proto enum.

Two consequences worth stating plainly:

1. A non-PK predicate is not a footgun any more. UPDATE t SET … WHERE tier = 'pro' synthesises SELECT pk FROM t WHERE tier = 'pro' and routes through keyed_from_subquery, so every write on a keyed table goes through the WAL. non_keyed_update / non_keyed_delete now survive for exactly one case: the target has no pk_columns, so no WAL exists to race against. The old “WAL-bypass” hazard, and the “safe patterns” prescribed to dodge it, no longer apply (dodil-tables/crates/htap-planner/src/dml.rs:775-830).
2. UPDATE and DELETE are refused without a WHERE clause. The no-WHERE branch was deliberately removed; plan_update / plan_delete reject that shape upfront with a BigQuery-style error. Use TRUNCATE TABLE to clear a table.

Non-PK conjuncts alongside a PK predicate (WHERE id = 42 AND tier = 'pro') ride along as extra_filter and are applied after the key lookup. Range predicates on a composite PK are refused rather than guessed at.

Read shape — unary vs federated

Independently of tier, a read plan carries a Shape: unary (one executor call) or federated (N parallel calls over partition specs, then aggregate). Window functions and joins force unary. This is a plan detail, not a request field — you cannot ask for federation, and the planner downgrades when the table is too small or unpartitioned to pay for it.

What a statement returns

ExecuteResponse is a oneof over three arms plus common metadata:

message ExecuteResponse {
  oneof result {
    RowSet rows = 1;            // SELECT / SHOW / DESCRIBE / EXPLAIN
    uint64 rows_affected = 2;   // INSERT / UPSERT / UPDATE / DELETE / MERGE
    DdlResult ddl = 3;          // CREATE / ALTER / DROP / CTAS
  }
  string statement_kind = 4;    // "select" | "insert" | "merge" | "create_table" | …
  string max_wal_ulid = 5;      // session watermark after a write
  ServedBy served_by = 6;
  ResultManifest manifest = 7;  // spooled SELECT tail — rows empty, pull presigned parts
  repeated string warnings = 8;
}

rows_affected on a write counts WAL appends and tombstones; the asynchronous drain later refines the insert-vs-update split. For the truthful post-drain breakdown, force a drain and read Compact’s counters.

Time travel

Delta Lake keeps the commit history, so SELECT … VERSION AS OF n and RESTORE TABLE t TO VERSION AS OF n both work. Two facts that decide whether you can use them:

• History is not an RPC and Restore is not an RPC or a route. Restore is SQL, run through Execute.
• A restore destroys the tail. It rolls the table back and wipes the WAL, and indexes are not rolled back. See Maintenance → RESTORE.
• The retention floor is 168 h with no bypass. VacuumTable can destroy the very history a time-travel query needs; there is no --disable-retention-check.

When ingest runs (pipeline-bound tables)

Pipeline-bound tables ingest on the same trigger model as Pipelines — once a rule is scoped at the pipeline:

• Direct upload of a matching object via S3 → the template runs → rows land in the table
• Source sync (Preview) → discovered objects → template runs → rows land
• One-shot manual via TriggerIngest

See also

• Quickstart — the whole model in five minutes of SQL
• SQL Compatibility — DuckDB dialect, the fifteen types, statement shapes, the honest refusal list
• API Reference → Execute — every statement shape with examples
• API Reference → Data — Query / Upsert / Delete / WriteStream / Commit
• Capacity — reservations and residency, which is what HOT / WARM actually mean