Skip to Content
We are live but in Staging 🎉
TablesCLI Guidedodil k3 table (data)

dodil k3 table — data

Read and mutate rows. Five subcommands — query runs SQL through the Execute planner; the other four are typed shortcuts mapping to the Data RPCs.

Persistent flag on the group: --bucket / -b (required).

dodil k3 table query

dodil k3 table query [sql] -b BUCKET

Runs SQL through the planner. The sql is a positional argument — wrap in quotes. Routes reads and writes alike, but in practice you’ll use the typed shortcuts (insert, merge, update, delete-rows) for writes.

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

--freshness is not in the CLI yet. Default is EVENTUAL (Delta-only). For read-your-writes (FRESHNESS_STRONG) or OLTP-only freshness, hit the Execute API directly via curl.

High-value query patterns

PatternExampleStrategy
AggregationSELECT kind, COUNT(*) FROM events GROUP BY kindUNARY_WAREHOUSE
Time bucketsSELECT date_trunc('day', occurred_at), COUNT(*) FROM events GROUP BY 1UNARY_WAREHOUSE (or FEDERATED_AGGREGATE if partitioned)
Latest per groupSELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY occurred_at DESC) rn FROM eventsUNARY_WAREHOUSE
PercentilesSELECT quantile_cont(duration_ms, 0.95) AS p95 FROM eventsUNARY_WAREHOUSE
JSON extractionSELECT json_extract_string(payload, '$.sku') AS sku, COUNT(*) FROM events GROUP BY 1UNARY_WAREHOUSE
JoinsSELECT e.user_id, u.email FROM events e JOIN users u ON e.user_id = u.idUNARY_WAREHOUSE (joins resolve in DuckDB)

For the full strategy mapping — eventual vs strong reads, federated vs unary — see Execute → SELECT.

dodil k3 table insert

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

Bulk insert. Pass each row as its own --row JSON object; repeat the flag for batches.

FlagShortTypeDefaultDescription
--row-rrepeatable JSON—One JSON object per row. Pass multiple times to batch.
--mode—stringappendappend (default) or overwrite
dodil k3 table insert events --bucket kb-prod \
  --row '{"id":1,"user_id":"u-101","occurred_at":1716840000000000,"event_type":"click","payload":{"page":"/pricing"}}' \
  --row '{"id":2,"user_id":"u-101","occurred_at":1716840060000000,"event_type":"click","payload":{"page":"/signup"}}' \
  --row '{"id":3,"user_id":"u-102","occurred_at":1716840120000000,"event_type":"purchase","payload":{"sku":"A-12","amount":49.99}}'

For PK tables (table has merge_keys), rows route through the write log → compactor MERGE on next drain. For no-PK tables, rows append directly to Delta.

--mode overwrite drops the table contents then writes the rows (single Delta commit, bypasses the write log). Useful for periodic full-table refreshes.

dodil k3 table merge

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

Upsert by match-columns. Existing rows (matching all --match-columns) get updated (or deleted); new rows get inserted (or ignored).

FlagShortTypeDefaultDescription
--row-rrepeatable JSON—Source rows to merge
--match-column—string list (repeat)[]Columns used to find matching rows. Usually = merge_keys.
--when-matched—stringupdateupdate or delete
--when-not-matched—stringinsertinsert or ignore
# Upsert by composite key
dodil k3 table merge events --bucket 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 k3 table merge events --bucket kb-prod \
  --match-column id --match-column user_id \
  --when-matched delete --when-not-matched ignore \
  --row '{"id":5,"user_id":"u-104"}'

rows_written in the response counts source rows the write log accepted — not the pre-drain insert/update/delete classification. The post-drain breakdown surfaces via dodil k3 table describe’s lastDrain* counters.

dodil k3 table update

dodil k3 table update [name] -b BUCKET \
  --predicate "SQL_WHERE_CLAUSE" \
  --updates-json '{ "col": "value", ... }'
FlagTypeRequiredDescription
--predicatestringyesSQL WHERE clause selecting rows
--updates-jsonJSON objectyesFlat { column: literal } map of new values
# Keyed update (predicate matches merge_keys)
dodil k3 table update events --bucket kb-prod \
  --predicate "id = 1 AND user_id = 'u-101'" \
  --updates-json '{"event_type":"click_pricing"}'
 
# Non-keyed update (⚠️ predicate doesn't match merge_keys — bypasses write log)
dodil k3 table update events --bucket kb-prod \
  --predicate "occurred_at < TIMESTAMP '2025-01-01 00:00:00'" \
  --updates-json '{"event_type":"archived"}'

Keyed vs non-keyed routing: predicates matching the table’s merge_keys route through the write log (KEYED_UPDATE). Predicates that don’t go directly to Delta (NON_KEYED_UPDATE) — bypasses the write log; if pending log entries for affected rows haven’t compacted yet, the next drain may overwrite the change. See Execute → UPDATE for the two safe patterns.

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

dodil k3 table delete-rows

dodil k3 table delete-rows [name] -b BUCKET --predicate "SQL_WHERE_CLAUSE"
FlagTypeRequiredDescription
--predicatestringyesSQL WHERE clause selecting rows to delete
# Keyed delete
dodil k3 table delete-rows events --bucket kb-prod \
  --predicate "id = 1 AND user_id = 'u-101'"
 
# Non-keyed delete (⚠️)
dodil k3 table delete-rows events --bucket kb-prod \
  --predicate "occurred_at < TIMESTAMP '2025-01-01 00:00:00'"

Keyed delete resolves matching keys via SELECT pk FROM target WHERE predicate, writes one tombstone per key into the write log. Compactor materializes them as a Delta DELETE on drain.

See also

dodil k3 tabledodil k3 table (maintenance)