duckboard

File-first local SQL workspace for CSV, Parquet, PSV, and JSON — powered by DuckDB.

Load files once, query by name with plain SQL, export results. Terminal-native alternative to spinning up a notebook for quick file questions.

Status: v0.4.0 — available on PyPI.

Install

pip install duckboard

Development install:

git clone https://github.com/OmUniyal/duckboard
cd duckboard
python -m venv .venv
.\.venv\Scripts\Activate.ps1
pip install -e ".[dev]"

Quickstart

duckboard

duckboard> :load examples/sample.csv as sample
duckboard> SELECT color, COUNT(*) AS n FROM sample GROUP BY 1;
┌────────┬───┐
│ color  │ n │
├────────┼───┤
│ blue   │ 1 │
│ green  │ 1 │
│ red    │ 2 │
└────────┴───┘
(3 rows)
duckboard> :save results.csv
Saved 3 rows to results.csv
duckboard> :quit
Bye.

Supported file formats

Extension	Format
.csv, .tsv	CSV
.psv	PSV (pipe-separated)
.parquet	Parquet
.json, .jsonl, .ndjson	JSON

REPL commands

Command	Description
:load "path" [as name] [--no-header] [--delimiter <value>] [--quotechar <char>]	Load a file as a queryable table. Name defaults to filename stem. Use --no-header for files without a header row. Use --delimiter to set the field separator (aliases: tab, pipe, semicolon, caret, comma, or any single character). Use --quotechar to set the quote character (default "). .tsv files auto-detect tab delimiter.
:tables	List all loaded tables with format, path, and validation status.
:schema <table>	Show column names, types, and nullability. Includes load warnings when present.
:save "path" [--csv|--parquet|--json]	Save last query result to a file. Format auto-detected from extension; use flag to override.
:unload <table>	Remove a loaded table from the session.
:unload all	Remove every loaded table in one shot.
:rename_column table old_name new_name	Rename a column in a loaded table.
:export_errors table "path" [--csv|--parquet|--json]	Export rows that failed validation to a file.
:export_clean table "path" [--csv|--parquet|--json]	Export only validated rows to a file.
:pwd	Show current working directory.
:clear / :cls	Clear the terminal.
:quit / :q / exit / quit / Ctrl+D	Exit duckboard.

Vertical output

Wide results can be viewed one row at a time using vertical mode. Two ways to trigger it:

\G suffix — append immediately before the semicolon:

duckboard> SELECT * FROM orders WHERE id = 1\G;
*************************** 1. row ***************************
        id: 1
  customer: Alice
   revenue: 12345.67

(1 row)

Oracle-style hint — inline, with a custom display row cap:

SELECT /*+ vertical_result(5) */ * FROM orders;

This shows up to 5 rows vertically. The full result set is still held in memory for :save.

Tab autocomplete

duckboard supports tab completion (requires pyreadline3 on Windows, built-in readline on Linux/macOS):

• Command names — type :sch and press Tab to expand to :schema
• Table names — after :schema, :unload, :export_errors, and similar commands; also after FROM, JOIN, and related SQL keywords during query entry
• File paths — after :load; filtered to supported file extensions only
• SQL keywords — SEL → SELECT, FR → FROM, etc.

Tab completes to the longest common prefix when multiple matches exist. Type enough characters to reach a unique match for a full completion.

Install the readline extra on Windows:

pip install duckboard[readline]

Data quality

When a file is loaded, duckboard validates it and reports issues automatically.

Full-file structural validation (CSV/PSV/TSV): scans every row, flags column count mismatches into _errors_{name}. No row limit — a bad row at position 15,000 is caught just like one at position 5.

Type anomaly detection: flags rows where a numeric value appears in a predominantly string column (e.g. a number in a gender column).

Residual row-count cross-check: after loading, duckboard compares the raw line count against what DuckDB actually loaded. Any shortfall beyond known errors is surfaced as a warning with exact counts so the root cause can be tracked down.

:load output reflects what was found:

# Clean
Loaded 'orders' from orders.csv  (csv)

# Known validation errors
Loaded 'orders' from orders.csv  (csv)
  2 validation error(s) found.
  → :export_errors orders  to inspect  |  :export_clean orders  for clean rows

# DuckDB dropped rows silently (cause unknown)
Loaded 'orders' from orders.csv  (csv)
  2 row(s) were dropped by DuckDB but not captured in the error table
  (cause unknown — may be encoding, embedded nulls, or a DuckDB type coercion issue).
  Raw line count: 20000, loaded: 19998, known errors: 0.

All known errors are stored in _errors_{name} for the duration of the session:

SELECT * FROM _errors_orders;

The :tables command shows status for every loaded table:

┌────────┬────────┬────────────┬────────────┐
│ name   │ format │ path       │ errors     │
├────────┼────────┼────────────┼────────────┤
│ orders │ csv    │ orders.csv │ [!2]       │
│ events │ parquet│ events.prq │ [!?]       │
│ mixed  │ csv    │ mixed.csv  │ [!2 +?]    │
│ sample │ csv    │ sample.csv │ ok         │
└────────┴────────┴────────────┴────────────┘

[!N] — N known errors in the error table, exportable via :export_errors. [!?] — rows dropped by DuckDB, not in error table, cause TBD. [!N +?] — both.

Delimiter and quote char support (CSV/PSV/TSV): non-default delimiters are forwarded to both DuckDB and the structural validator, so validation is consistent with how the file actually parses:

duckboard> :load data.csv --delimiter semicolon
duckboard> :load data.csv --delimiter | --quotechar '
duckboard> :load data.tsv              ← tab delimiter inferred automatically

Notes

• Queries display a maximum of 50 rows in the terminal. Full results are always exported via :save.
• Large exports (2,000+ rows) prompt for confirmation before writing.
• On Windows, forward slashes are recommended in paths: :load data/sales.csv. Backslashes work but tab completion requires forward slashes to navigate directories.
• Multi-line SQL is supported — statements execute on semicolon.
• Structural validation runs on CSV/PSV/TSV files (full-file scan, no row cap). JSONL/NDJSON files are line-scanned with json.loads(). All formats receive an all-null column check. Parquet validation is limited to the residual row-count cross-check and all-null detection.
• python -m duckboard works as an alternative launch method if the script entry point is unavailable.

Read more

I built a terminal SQL workspace for CSV files — and made it catch bad data automatically — dev.to

Project layout

src/duckboard/
├── __main__.py     # Enables python -m duckboard
├── session.py      # DuckboardSession — owns DuckDB connection + state
├── catalog.py      # Registered file → view mappings, validation, cross-check
├── repl.py         # Interactive REPL loop with \G and hint intercept
├── commands.py     # All REPL commands
├── formatter.py    # Box-drawing output, truncation, and vertical mode
├── completer.py    # Tab autocomplete for commands, tables, paths, SQL keywords
├── cli.py          # CLI entry point
└── exceptions.py   # DuckboardError hierarchy

License

MIT