Workflow: Table Lifecycle and SQL Flow

This workflow covers table engine setup, table creation, writes, reads, and maintenance.

When To Use

Creating analytics-ready datasets in K3.
Validating SQL/write behavior for operational reporting.
Operating table maintenance routines.

Step 1: Enable table engine


dodil k3 \
  engine enable --bucket "$K3_BUCKET"

Step 2: Create manual table


dodil k3 \
  table create events --bucket "$K3_BUCKET" \
  --columns-json '[{"name":"id","type":3,"nullable":false},{"name":"kind","type":1,"nullable":true}]' \
  --merge-key id

Step 3: Insert and query


dodil k3 \
  table insert events --bucket "$K3_BUCKET" \
  --row '{"id":1,"kind":"deploy"}' \
  --row '{"id":2,"kind":"incident"}'
 
dodil k3 \
  table query "SELECT kind, COUNT(*) FROM events GROUP BY kind" --bucket "$K3_BUCKET"

DuckDB-oriented SQL patterns you can run immediately:


# Time bucket trend (advantage: fast time-series aggregation)
dodil k3 \
  table query "SELECT date_trunc('hour', created_at) AS hour_bucket, COUNT(*) FROM events GROUP BY 1 ORDER BY 1" --bucket "$K3_BUCKET"
 
# Window ranking (advantage: latest-per-category and dedup logic)
dodil k3 \
  table query "SELECT id, kind, ROW_NUMBER() OVER (PARTITION BY kind ORDER BY created_at DESC) AS rn FROM events" --bucket "$K3_BUCKET"
 
# Percentile analytics (advantage: SLO quality checks)
dodil k3 \
  table query "SELECT quantile_cont(duration_ms, 0.95) AS p95_ms FROM events" --bucket "$K3_BUCKET"

Step 4: Merge/update/delete rows


dodil k3 \
  table merge events --bucket "$K3_BUCKET" \
  --row '{"id":2,"kind":"incident-critical"}' \
  --match-column id
 
dodil k3 \
  table update events --bucket "$K3_BUCKET" \
  --predicate "id = 1" \
  --updates-json '{"kind":"deploy-success"}'
 
dodil k3 \
  table delete-rows events --bucket "$K3_BUCKET" --predicate "id = 2"

Step 5: Maintenance


dodil k3 \
  table optimize events --bucket "$K3_BUCKET" --target-file-size-mb 128
 
dodil k3 \
  table vacuum events --bucket "$K3_BUCKET" --dry-run
 
dodil k3 \
  table compact events --bucket "$K3_BUCKET" --batch-size 20000

Step 6: Pipeline-generated table (API fallback)


curl -sS -X POST "https://k3.dev.dodil.io/$K3_BUCKET/tables/pipelines" "${AUTH[@]}" "${JSON[@]}" \
  -d '{
    "bucket": "'$K3_BUCKET'",
    "name": "lease_extracts",
    "template_id": "lease_extraction",
    "folder_prefix": "leases"
  }'

Outcome Checks

table describe shows schema and stats.
table query returns expected row counts.
_compact reduces WAL backlog over time.

Next: OAuth Source Onboarding