Skip to content
DuckDB 2.0 CLI Agent Mode: Truncation Warnings & Structured Errors for AI Agents
Databases 10 min read

DuckDB 2.0 CLI Agent Mode: Truncation Warnings & Structured Errors for AI Agents

The article tests DuckDB 2.0 CLI's Agent Mode with a 1,205-row CSV, showing how it explicitly marks omitted rows and returns JSON-structured errors for missing columns, but emphasizes that statistical accuracy still requires aggregation queries like COUNT and FILTER rather than relying on displayed samples.

Data STUDIO
Data STUDIO
Data STUDIO

01 Same query, which rows are omitted

The author generates a CSV file sample_orders.csv with 1,205 rows using a Python script. The CSV has four columns: order_id, region, amount_yuan, status. Every 10th row has status "refunded", others "paid".

import csv

regions = ("East", "North", "South", "West")
with open("sample_orders.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.writer(f)
    writer.writerow(("order_id", "region", "amount_yuan", "status"))
    for order_id in range(1, 1206):
        writer.writerow((
            order_id,
            regions[(order_id - 1) % 4],
            100 + (order_id * 37) % 900,
            "refunded" if order_id % 10 == 0 else "paid",
        ))

Run with DuckDB 2.0 CLI in normal mode ( -no-agent) and Agent Mode ( -agent):

duckdb -no-agent -c "SELECT order_id, region, amount_yuan, status FROM read_csv('sample_orders.csv') ORDER BY order_id;"
duckdb -agent   -c "SELECT order_id, region, amount_yuan, status FROM read_csv('sample_orders.csv') ORDER BY order_id;"

Normal mode renders a bordered table showing first 20 and last 20 rows, with a footer line 1205 rows (40 shown). The query returns all rows, but the display truncates. If an agent only sees the visible 40 rows, it may compute wrong statistics.

Agent Mode outputs compact Markdown. Between the first and last 20 rows it inserts an explicit omission marker: … 1165 rows omitted … At the end it prints a summary line:

first 20 and last 20 of 1205 rows (.maxbytes 0 for all), hash 9cf275c5fce5ce5f

The hash corresponds to the full result set and can detect changes, but does not prove the query, filter, or business logic is correct. Both modes execute the same query; the difference is how they communicate incomplete display to downstream consumers.

02 What Agent receives when column name is wrong

Introduce a typo: amunt_yuan instead of amount_yuan.

duckdb -no-agent -c "SELECT amunt_yuan FROM read_csv('sample_orders.csv') LIMIT 1;"
duckdb -agent   -c "SELECT amunt_yuan FROM read_csv('sample_orders.csv') LIMIT 1;"

Normal mode writes a human-readable Binder error to stderr, suggesting the candidate column amount_yuan.

Agent Mode writes a JSON object to stderr with structured fields:

{
  "exception_type": "Binder",
  "error_subtype": "COLUMN_NOT_FOUND",
  "name": "amunt_yuan",
  "exception_message": "Referenced column \"amunt_yuan\" not found in FROM clause!
Candidate bindings: \"amount_yuan\""
}

Both commands exit with code 1. Agent Mode also prints an enablement notice on stderr before the JSON, so parsers must not treat the entire stderr as a single JSON object. The JSON provides error type, offending field, and candidate column; whether the agent can correct the SQL depends on whether it also checks the data schema and the original question.

03 How to give verifiable answers after truncation

If the task is "How many rows in this file, and how many are paid?", the displayed 40 rows cannot answer. Instead, push aggregation to the database:

duckdb -agent -c "SELECT COUNT(*) AS total_rows, COUNT(*) FILTER (WHERE status = 'paid') AS paid_rows FROM read_csv('sample_orders.csv');"

Result: total_rows = 1205, paid_rows = 1085. This matches the generation rule: among 1–1205, every 10th row is refunded (120 rows), the rest paid (1085). The verification is against this file and this SQL; with other data one must still check the loaded file, the meaning of status, nulls, and filter criteria.

The article contrasts two denominators: the CLI display shows 40 rows vs 1,165 omitted; the SQL aggregation shows 1,085 paid vs 120 refunded, both over the same 1,205 total rows but answering different questions.

According to DuckDB 2.0 Agent Mode documentation (https://duckdb.org/docs/2.0/clients/cli/agent_mode), defaults are: max 1,000 rows, 10,000 bytes, 500 characters per cell. Exceeding limits triggers first/last rows plus total count. Settings .maxrows, .maxbytes, .maxcellwidth adjust thresholds. Raising limits suits detail-reading tasks; for statistics, writing aggregations is more direct. When omission markers or truncated cells appear, do not compute full proportions from the visible sample.

Agent Mode can auto-enable when: an Agent-recognized environment variable exists, stdout is not a TTY, and no explicit output format ( -csv, -json, etc.) is given. Example: AI_AGENT=1 duckdb -c "SELECT 42 AS answer;" produces Markdown in non-TTY output. To force mode explicitly, use -agent or -no-agent.

04 Conclusion

DuckDB 2.0 CLI Agent Mode helps coding agents detect omitted results and parse machine-readable errors. The reliable workflow remains: confirm CLI mode, check for omitted rows or truncated cells, then use COUNT, filters, and aggregation queries to verify the question, and finally audit which file the SQL reads and what business definitions it uses.

The DuckDB team reported a 59% reduction in tokens read by the model across 22 TPC-H queries in their official benchmark, but noted total cost and latency did not necessarily drop. That figure is from their controlled experiment, not from this article's CSV test, and should not be generalized to all agent tasks. This article validates DuckDB 2.0.0 CLI output behavior against a fixed-rule CSV.

Original Source

Signed-in readers can open the original source through BestHub's protected redirect.

Sign in to view source
Republication Notice

This article has been distilled and summarized from source material, then republished for learning and reference. If you believe it infringes your rights, please contactadmin@besthub.devand we will review it promptly.

CLISQLerror handlingCSVaggregationDuckDBAgent Modetruncation
Reader feedback

How this landed with the community

Sign in to like

Rate this article

Was this worth your time?

Sign in to rate
Discussion

0 Comments

Thoughtful readers leave field notes, pushback, and hard-won operational detail here.