DuckDB Data Analyst Plus

Use the bundled CLI instead of ad hoc pandas code for local tabular analysis:

python scripts/duckdb_analyzer.py --file_path ./data.csv --mode describe --ai_context --json

Install the runtime dependencies before first use:

python -m pip install -r requirements.txt

Core workflow

1. Run describe --ai_context --profile_level compact --json to discover the schema and safe SQL references.
2. Generate one read-only DuckDB SQL statement from result.ai_context.
3. Run query --json; the CLI defaults to 1,000 rows, a 30-second timeout, and a medium risk ceiling.
4. Revise failed SQL using diagnosis.available_columns, diagnosis.table_sql_ref, and diagnosis.hint.
5. Run analyze --analysis_templates ... --json for a broad first-pass report.
6. For multiple files, register --table_files and inspect catalog --ai_context --json before joining.
7. Add --export_task ./scheduled_analysis.py to a successful run when the analysis must be repeated by an external scheduler.

Common commands

Profile one file

python scripts/duckdb_analyzer.py \
  --file_path ./data.csv \
  --mode describe \
  --ai_context \
  --profile_level compact \
  --json

Use --simple for schema, row count, and samples only. Use --profile_level full only when the richer profile is worth the larger payload.

Run a controlled query

python scripts/duckdb_analyzer.py \
  --file_path ./data.csv \
  --mode query \
  --sql "SELECT category, COUNT(*) AS n FROM data GROUP BY category ORDER BY n DESC" \
  --json \
  --compress_json

Defaults:

• --max_rows 1000
• --timeout_seconds 30
• --max_query_risk medium
• read-only SQL enforcement

Use --estimate_only to return the EXPLAIN-based risk assessment without executing the query. Use --max_query_risk low for stricter execution.

--sample_fraction samples rows from the completed query result. It does not reduce the work performed by the inner query. For source-level sampling, write DuckDB SQL with USING SAMPLE explicitly.

Run standard analysis templates

python scripts/duckdb_analyzer.py \
  --file_path ./data.csv \
  --mode analyze \
  --analysis_templates missing,topn,distribution,trend,outliers,correlation \
  --json

Available templates:

• missing: null counts and rates
• topn: frequent dimension values
• distribution: count, range, quartiles, mean, and standard deviation
• trend: monthly time trends
• outliers: IQR-based potential outliers
• correlation: pairwise numeric correlations

Analyze multiple tables

python scripts/duckdb_analyzer.py \
  --table_files orders=./orders.csv,customers=./customers.csv \
  --mode catalog \
  --ai_context \
  --profile_level compact \
  --json

Use result.multi_ai_context.tables[*].table_sql_ref and join_candidates when generating a JOIN. The catalog also reports approximate cardinality and match rates. Automatic multi-table analysis returns summary, join_quality, dimension_rollups, insights, and chart_hints.

Persist registered tables when repeated work would otherwise reload large files:

python scripts/duckdb_analyzer.py \
  --table_files orders=./orders.csv,customers=./customers.csv \
  --mode catalog \
  --persist_db_path ./analysis.duckdb \
  --json

Inspect an existing database with --persist_db_path ./analysis.duckdb --mode catalog --json and no source file.

Export a reusable analysis task

Add --export_task to a successful describe, query, analyze, or catalog run:

python scripts/duckdb_analyzer.py \
  --file_path ./sales.csv \
  --mode query \
  --sql "SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue FROM data GROUP BY month" \
  --json \
  --export_task ./monthly_sales.py

The generated file is a standalone standard-library launcher. It embeds the normalized task arguments and backend path, runs the backend with shell=False, writes JSON atomically, supports --dry-run, and documents invocation examples in --help. It does not create or manage schedules; an external scheduler owns timing, retries, and alerts.

python ./monthly_sales.py --help
python ./monthly_sales.py --dry-run
python ./monthly_sales.py --output ./latest.json

The launcher replaces its output with the current result on every run. Backend failures produce success: false plus exit_code, preventing an earlier success payload from remaining current. Callers must check both the process exit code and JSON success. The launcher does not copy source data, the backend, or Python dependencies. Ensure the execution host can access the captured paths and install requirements.txt. Override the Python executable or backend with --python and --backend. Export refuses unsafe SQL and will not overwrite an existing launcher unless --overwrite_export_task is present.

Excel handling

• --excel_mode auto streams .xlsx files above --excel_stream_threshold_mb 50.
• --excel_mode pandas loads a workbook through pandas.
• --excel_mode stream reads .xlsx rows with openpyxl in batches controlled by --excel_chunk_size.
• --excel_sheet "Sheet1" selects a sheet.
• .xls input uses pandas and xlrd; large legacy workbooks should be converted to .xlsx, CSV, or Parquet.
• Query results may be exported to .xlsx; legacy .xls output is not supported.

Source column names are preserved. Always use the sql_ref values returned by the profile for names containing spaces, punctuation, reserved words, or non-Latin characters.

Safety and SQL rules

• Generate one read-only statement.
• Use data unless --table_name or the returned table_sql_ref says otherwise.
• Quote identifiers through returned sql_ref values.
• Prefer aggregation, filters, and explicit limits over raw detail rows.
• Avoid SELECT * on large data.
• Use explicit JOIN ... ON or JOIN ... USING clauses.
• Do not use --allow_unsafe_sql for AI-generated SQL.
• External file-reading table functions are rejected inside user SQL; register files through CLI options instead.

JSON and Agent integration

JSON failures contain success: false, error, and a structured diagnosis. Compressed query output includes row counts, truncation status, columns, and rows.

For repeated Agent calls, use --agent_run_id, --agent_state_path, --agent_trace_path, --max_agent_query_units, and --max_agent_elapsed_seconds. These controls persist lightweight budgets and append JSONL trace events; they are intentionally omitted from exported scheduler launchers.

Resources

• scripts/duckdb_analyzer.py: execution backend and task exporter
• references/data-formats.md: format-specific behavior and limitations
• requirements.txt: runtime dependencies