Skip to Content
We are live but in Staging 🎉
TablesAPI ReferenceExecute (SQL)SELECT

SELECT

For SELECT plans, you control freshness via ExecuteRequest.freshness; the planner picks the strategy based on table size + partitioning + the query shape. Both come back on ExecuteQueryResult.strategy and served_by. For the RPC contract and enum reference, see the Execute hub.

Freshness requestTypical strategyserved_byWhat it does
EVENTUAL (default)UNARY_WAREHOUSE or FEDERATED_*WAREHOUSEDelta-only — OLAP-optimized, may miss recent un-drained writes
STRONGUNARY_MERGED_STRONGMERGEDWrite log + Delta merged on read — read-your-writes
OLTP_ONLY(single-pod write-log scan)OLTPWrite log only — niche; for point lookups during heavy backlog

Read strategies

UNARY_WAREHOUSE — default analytical read

Plain SELECT, no special freshness. Reads Delta only. Fast.

curl -sS -X POST "https://k3.dev.dodil.io/kb-prod/tables/_execute" \
  -H "Authorization: Bearer $DODIL_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "bucket": "kb-prod",
    "sql": "SELECT event_type, COUNT(*) AS n FROM events GROUP BY event_type ORDER BY n DESC"
  }'

UNARY_MERGED_STRONG — read-your-writes

Set freshness: "FRESHNESS_STRONG" — K3 runs a single-pod query that merges the write log with Delta. Pays a small overhead vs EVENTUAL; use it sparingly on hot paths, freely for “did my write land?” checks.

curl -sS -X POST "https://k3.dev.dodil.io/kb-prod/tables/_execute" \
  -H "Authorization: Bearer $DODIL_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "bucket": "kb-prod",
    "sql": "SELECT * FROM events WHERE user_id = '\''u-101'\'' ORDER BY occurred_at",
    "freshness": "FRESHNESS_STRONG"
  }'

FEDERATED_AGGREGATE — partitioned reads with aggregates

Large partitioned tables + aggregate queries → planner fans the query out across partitions in parallel, then reduces. You don’t request this — the planner picks it from shape + size.

curl -sS -X POST "https://k3.dev.dodil.io/kb-prod/tables/_execute" \
  -H "Authorization: Bearer $DODIL_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "bucket": "kb-prod",
    "sql": "SELECT event_type, DATE_TRUNC('\''day'\'', occurred_at) AS day, COUNT(*) AS n FROM events GROUP BY event_type, day ORDER BY day"
  }'

FEDERATED_SCAN — partitioned reads without aggregates

Same fan-out, but for non-aggregate SELECTs — results are concatenated instead of reduced.

-- e.g. a partitioned full-table scan with a filter
SELECT id, user_id, occurred_at, payload
FROM events
WHERE event_type IN ('click', 'purchase')
ORDER BY occurred_at DESC
LIMIT 10000

FEDERATED_TO_SINGLE — planner downgraded to single

When the federated plan isn’t worth it (table too small / no partitions / etc.), the planner downgrades to a single call and explains in strategy_reason:

{
  "query": {
    "strategy": "QUERY_STRATEGY_FEDERATED_TO_SINGLE",
    "strategyReason": "table is not partitioned"
  }
}

JSON column reads — DuckDB-native operators

json columns work with every DuckDB JSON operator. K3 stores them as canonical JSON text tagged for DuckDB recognition.

curl -sS -X POST "https://k3.dev.dodil.io/kb-prod/tables/_execute" \
  -H "Authorization: Bearer $DODIL_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "bucket": "kb-prod",
    "sql": "SELECT user_id, payload->>'\''sku'\'' AS sku, (payload->>'\''amount'\'')::double AS amount FROM events WHERE event_type = '\''purchase'\'' ORDER BY amount DESC LIMIT 100"
  }'

->> extracts as text. Cast as needed: (payload->>'amount')::double. Other ops also work — ->, json_extract, json_extract_string, json_each, from_json.

Joins and CTEs

Any DuckDB-supported SQL works. Joins read both tables from the same bucket; the planner picks UNARY_WAREHOUSE or FEDERATED_* per table size.

-- CTE + JOIN + aggregate
WITH purchases AS (
  SELECT user_id, payload->>'sku' AS sku, (payload->>'amount')::double AS amount
  FROM events WHERE event_type = 'purchase'
)
SELECT u.email, SUM(p.amount) AS total
FROM purchases p JOIN users u ON p.user_id = u.id
WHERE p.amount >= 50
GROUP BY u.email
ORDER BY total DESC;
 
-- Window function
SELECT user_id, occurred_at,
       LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at) AS prev_event,
       occurred_at - LAG(occurred_at) OVER (PARTITION BY user_id ORDER BY occurred_at) AS gap_us
FROM events
ORDER BY user_id, occurred_at;
 
-- Set ops
(SELECT user_id FROM events WHERE event_type = 'click')
INTERSECT
(SELECT user_id FROM events WHERE event_type = 'purchase');

See also

DDLINSERT