dodil data table — data

Read and mutate rows. Six subcommands. All of them build a SQL statement and send it through dodil.tables.v1.Tables/Execute — the typed shortcuts exist so you don’t have to template SQL strings, not because they reach a different API.

Persistent flag on the group: --bucket / -b.

For ad-hoc SQL, dodil data sql -b BUCKET "<sql>" is shorter than dodil data table query, and dodil data pg sends the same statement over the Postgres wire. Both are documented in the CLI Guide overview.

dodil data table query

dodil data table query [sql] -b BUCKET

Runs SQL. The statement is a positional argument — wrap it in quotes. Handles reads and writes alike.

# Aggregate
dodil data table query \
  "SELECT event_type, COUNT(*) AS n FROM events GROUP BY event_type ORDER BY n DESC" \
  -b kb-prod
 
# JSON column extraction
dodil data table query \
  "SELECT user_id, payload->>'sku' AS sku FROM events WHERE event_type = 'purchase'" \
  -b kb-prod -o json
 
# Window function
dodil data table query \
  "SELECT id, event_type, ROW_NUMBER() OVER (PARTITION BY event_type ORDER BY id DESC) AS rn FROM events" \
  -b kb-prod

--freshness is a deprecated no-op. The flag still parses, but running it prints Flag --freshness has been deprecated, reads are read-your-writes by default; this flag is ignored and the query runs unchanged. There is nothing to set: reads are read-your-writes, and the plane picks the physical path from a frontier check on the WAL backlog. See Core Concepts → Freshness is not a client knob.

Large results spool. Above a size threshold the plane writes result parts to object storage and returns a manifest of presigned GETs instead of inline rows. You’ll see:

(result spooled to object storage: N rows across M part(s); re-run with a LIMIT or fetch the presigned parts)

Add a LIMIT, or fetch the parts. See Execute → Spooled results.

High-value query patterns

Joins and window functions force a single-executor plan; aggregates over a large partitioned table can fan out. That’s a planner decision, not a request field. See Execute → SELECT.

dodil data table insert

dodil data table insert [name] -b BUCKET --row JSON [--row JSON ...] [--mode MODE]

dodil data table insert events -b kb-prod \
  --row '{"id":1,"user_id":"u-101","occurred_at":1779876000000000,"event_type":"click","payload":{"page":"/pricing"}}' \
  --row '{"id":2,"user_id":"u-101","occurred_at":1779876060000000,"event_type":"click","payload":{"page":"/signup"}}'

There is no --rows flag taking a JSON array. It is --row (or -r), once per row.

For a table with a primary key, rows route through the WAL and the compactor MERGEs them into Delta on the next drain. For a table with no declared PK, rows append directly to Delta. --mode overwrite replaces the table contents in a single Delta commit.

INSERT … SELECT has no flag form — run it as SQL.

dodil data table upsert

dodil data table upsert [name] -b BUCKET --row JSON [--row JSON ...] \
  [--match-column COL ...] [--merge]

The typed keyed write — insert-or-update by key. This is the CLI’s form of the Upsert RPC.

# Full-row upsert on the table's PK
dodil data table upsert events -b kb-prod \
  --row '{"id":1,"user_id":"u-101","occurred_at":1779876000000000,"event_type":"click_pricing","payload":{"page":"/pricing"}}'
 
# Partial merge — only event_type changes; payload and occurred_at are left alone
dodil data table upsert events -b kb-prod --merge \
  --row '{"id":1,"user_id":"u-101","event_type":"click_pricing"}'

--merge is the flag that decides whether an absent column means “set it to null” or “leave it”. Without it, an upsert is a full-row replace.

dodil data table merge

dodil data table merge [name] -b BUCKET \
  --row JSON [--row JSON ...] \
  --match-column COL [--match-column COL ...] \
  [--when-matched ACTION] [--when-not-matched ACTION]

General MERGE with explicit arms — use this when you need delete-on-match or skip semantics. For a plain upsert, table upsert is simpler.

# Upsert by composite key
dodil data table merge events -b kb-prod \
  --match-column id --match-column user_id \
  --row '{"id":1,"user_id":"u-101","event_type":"click_pricing","payload":{"page":"/pricing","variant":"B"}}' \
  --row '{"id":4,"user_id":"u-103","event_type":"signup","payload":{"plan":"pro"}}'
 
# Delete-on-match (cleanup pattern)
dodil data table merge events -b kb-prod \
  --match-column id --match-column user_id \
  --when-matched delete --when-not-matched skip \
  --row '{"id":5,"user_id":"u-104"}'

The no-match arm is skip, not ignore. The matched arm accepts skip too.

The returned row count counts WAL appends and tombstones — the asynchronous drain later refines the insert-vs-update split. For the post-drain breakdown, force a drain with dodil data table compact and read its counters.

dodil data table update

dodil data table update [name] -b BUCKET \
  --predicate "SQL_WHERE_CLAUSE" \
  --updates-json '{ "col": "value", ... }'

UPDATE events SET event_type = 'click_pricing'
WHERE id = 1 AND user_id = 'u-101';
 
-- A non-PK predicate is fine — see below.
UPDATE events SET event_type = 'archived'
WHERE occurred_at < TIMESTAMP '2025-01-01 00:00:00';

dodil data table update events -b kb-prod \
  --predicate "id = 1 AND user_id = 'u-101'" \
  --updates-json '{"event_type":"click_pricing"}'
 
dodil data table update events -b kb-prod \
  --predicate "occurred_at < TIMESTAMP '2025-01-01 00:00:00'" \
  --updates-json '{"event_type":"archived"}'

A WHERE clause is mandatory. UPDATE and DELETE without one are refused upfront with a BigQuery-style error. To clear a table, use TRUNCATE TABLE.

A non-PK predicate is not a hazard. The planner synthesises SELECT pk FROM events WHERE <predicate> and routes the write through the WAL as a keyed write, so every write on a table with a primary key is WAL-routed. Direct-to-Delta routing survives only for tables with no declared PK — where no WAL exists to race against in the first place. See Core Concepts → Write routing.

Wire-shape gotcha: --updates-json is a flat { col: value } map. Do not wrap it in a "fields" key — {"fields":{…}} parses as a single-column SET clause and the update is a no-op.

dodil data table delete-rows

dodil data table delete-rows [name] -b BUCKET --predicate "SQL_WHERE_CLAUSE"

dodil data table delete-rows events -b kb-prod \
  --predicate "id = 1 AND user_id = 'u-101'"
 
dodil data table delete-rows events -b kb-prod \
  --predicate "occurred_at < TIMESTAMP '2025-01-01 00:00:00'"

Same routing as update: a WHERE clause is mandatory, and a non-PK predicate resolves matching keys via a synthesised SELECT pk FROM target WHERE predicate before writing one tombstone per key into the WAL. The compactor materializes them as a Delta DELETE on drain.

Deleted rows still occupy space until optimize rewrites the files and vacuum expires the old versions — see maintenance.

See also

• Data — API Reference — Query · QueryStream · GetRow · BatchGetRows · Upsert · Delete · WriteStream · Commit
• Execute (SQL) — API Reference — full DuckDB SQL surface (DDL, CTAS, ALTER, DROP)
• SQL Compatibility — dialect details, JSON ops, the honest refusal list
• dodil data table — lifecycle — create / list / describe / delete
• dodil data table — maintenance — optimize / vacuum / compact