DEV Community

OmUniyal
OmUniyal

Posted on Edited on

I built a terminal SQL workspace for CSV, Parquet, PSV and JSON — and it catches bad data automatically

#python #opensource #dataengineering #beginners

Every time I get a new data file, I do the same three things.

Open it in Excel to get a feel for it. Write a quick pandas snippet to answer one question. Notice halfway through that a column has garbage values I wasn't expecting.

The notebook is already open, the virtual environment is already running, and I've already spent ten minutes on something that should have taken two.

I wanted something faster. Type a command, load a file, write SQL, done. No notebook, no imports, no setup.

So I built duckboard.

What it does

duckboard is a file-first local SQL workspace powered by DuckDB. Load CSV, TSV, PSV, Parquet, JSON or JSONL by name, query it with plain SQL, export the result — no notebook, no imports, no setup. Files are registered as DuckDB views, never copied into a database. On load it scans every row for structural errors and type anomalies and files them in an _errors_{name} table, so a bad row at 15,000 gets caught the same as one at 5. Then export just the clean rows.

pip install duckboard
Enter fullscreen mode Exit fullscreen mode

Start a session:

duckboard
Enter fullscreen mode Exit fullscreen mode

Load a file and query it immediately:

duckboard> :load sales.csv as sales
duckboard> SELECT region, SUM(amount) FROM sales GROUP BY 1;
┌────────┬─────────────┐
│ region │ sum(amount) │
├────────┼─────────────┤
│ eu     │      500.25 │
│ us     │      430.50 │
└────────┴─────────────┘
(2 rows)
duckboard> :save results.csv
Saved 2 rows to results.csv
Enter fullscreen mode Exit fullscreen mode

Parquet and JSON work exactly the same way — :load data.parquet as t just works.

The part I didn't plan: automatic validation

While testing duckboard, I loaded a real file from work. One of the rows had a number in the gender column — clearly a data entry error. DuckDB loaded it fine. I only noticed because I happened to run a GROUP BY on that column.

That made me think: what if duckboard flagged this automatically?

So I added validation on load. When you load a file, duckboard checks for:

Structural errors — rows whose field count doesn't match the header. A trailing comma, a missing value, a malformed export. This is the most common real-world problem — data in fields containing unquoted commas causes the row to look like it has more columns than it does.

Type anomalies — rows where a numeric value appears in a predominantly string column. A number in a name field. A code in a date field.

All-null columns — columns that loaded with no data at all. Often a sign of a delimiter mismatch or a schema change upstream.

JSONL line-level errors — for .jsonl and .ndjson files, every line is validated with json.loads(). A malformed line at position 50,000 gets caught just like one at position 2.

All issues get stored in an _errors_{name} table for the session:

duckboard> :load customers.csv as customers
Loaded 'customers' from customers.csv  (csv)
  1 validation error(s) found.
  → :export_errors customers  to inspect  |  :export_clean customers  for clean rows

duckboard> SELECT * FROM _errors_customers;
┌────────────┬──────────┬──────────────┬─────────────┬────────────────────────────────────────┐
│ row_number │ raw_line │ error_type   │ column_name │ reason                                 │
├────────────┼──────────┼──────────────┼─────────────┼────────────────────────────────────────┤
│          5 │ NULL     │ type_anomaly │ gender      │ column gender: expected non-numeric,   │
│            │          │              │             │ got '42'                               │
└────────────┴──────────┴──────────────┴─────────────┴────────────────────────────────────────┘
Enter fullscreen mode Exit fullscreen mode

There's also a residual cross-check: after loading, duckboard compares the raw line count against what DuckDB actually loaded. If DuckDB silently dropped rows — encoding issues, embedded nulls, type coercion — you get a warning with the exact counts.

The :tables command shows which tables have issues at a glance:

duckboard> :tables
┌───────────┬────────┬───────────────┬────────────┐
│ name      │ format │ path          │ errors     │
├───────────┼────────┼───────────────┼────────────┤
│ customers │ csv    │ customers.csv │ [!1]       │
│ events    │ parquet│ events.parquet│ [!?]       │
│ orders    │ csv    │ orders.csv    │ ok         │
└───────────┴────────┴───────────────┴────────────┘
Enter fullscreen mode Exit fullscreen mode

[!1] means one known error in the error table. [!?] means rows were dropped by DuckDB for an unknown reason. Once you've reviewed, export just the clean rows:

duckboard> :export_clean customers clean_customers.csv
duckboard> :export_errors customers bad_rows.csv
Enter fullscreen mode Exit fullscreen mode

Non-standard delimiters and quote characters

Not every file uses commas and double quotes. v0.4.0 adds --delimiter and --quotechar flags to :load:

duckboard> :load data.csv --delimiter semicolon
duckboard> :load data.csv --delimiter | --quotechar '
duckboard> :load data.tsv as tsv_data       ← tab delimiter inferred automatically
Enter fullscreen mode Exit fullscreen mode

Named aliases for --delimiter: tab, pipe, semicolon, caret, comma. Any single character or multi-char string also works. The delimiter is forwarded to both DuckDB and the Python structural validator, so validation stays consistent with how the file actually parses. :schema shows the active delimiter and quote char for every loaded table.

Wide tables and vertical output

Results auto-truncate to fit your terminal width — columns get proportionally shortened with … rather than overflowing. For very wide rows, vertical mode shows one field per line:

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

(1 row)
Enter fullscreen mode Exit fullscreen mode

You can also use an inline hint to cap how many rows display vertically without changing the underlying query:

SELECT /*+ vertical_result(5) */ * FROM orders;
Enter fullscreen mode Exit fullscreen mode

A few other commands worth knowing

No-header files — pass --no-header and duckboard will prompt you for column names or auto-generate them:

duckboard> :load dump.csv as dump --no-header
Enter column names (comma-separated) or press Enter for auto [col1, col2, col3]:
Enter fullscreen mode Exit fullscreen mode

Unload everything at once:

duckboard> :unload all
Unloaded 4 table(s): customers, events, orders, products.
Enter fullscreen mode Exit fullscreen mode

Rename a column without reloading the file:

duckboard> :rename_column sales cust_id customer_id
Enter fullscreen mode Exit fullscreen mode

Tab autocomplete — commands, table names, file paths, and SQL keywords all complete on Tab. On Windows, install the readline extra:

pip install duckboard[readline]
Enter fullscreen mode Exit fullscreen mode

Multi-line SQL works naturally — statements execute on semicolon:

duckboard> SELECT region,
        ->   COUNT(*) AS n,
        ->   SUM(amount) AS total
        -> FROM sales
        -> GROUP BY 1;
Enter fullscreen mode Exit fullscreen mode

Why not just use DuckDB directly?

You can — DuckDB has its own CLI. duckboard wraps it with a workflow built around files: named tables, validation on load, save/export commands, and a session that remembers what you've loaded. DuckDB's CLI won't tell you a number turned up in your gender column. That's the differentiator.

Where to find it

It's at v0.4.0 — 151 tests, available now. If you work with structured files and want something faster than spinning up a notebook, give it a try. Issues and feedback welcome.

Top comments (3)

Collapse
 
loudify profile image
Deadato •

You ask "why not just use DuckDB directly?" and answer it with workflow - but you'd already given the better answer two sections earlier. DuckDB's CLI won't tell you a number turned up in your gender column. Validation on load is the differentiator and you filed it under "the part I didn't plan." Your title says CSV, too, and the thing reads Parquet, PSV and JSON - that's undersold in the one place people decide whether to click.

Wrote you a launch post, free, use it or bin it:

"duckboard is a file-first local SQL workspace powered by DuckDB. Load CSV, PSV, Parquet or JSON by name, query it with plain SQL, export the result - no notebook, no imports, no setup. Files are registered as views, never copied into a database. On load it scans the first 1,000 rows for structural errors and type anomalies and files them in an errors table, so you find the number sitting in your gender column before a GROUP BY does. Then export just the clean rows."

Full disclosure so it isn't strange: I build a tool that writes these. Nothing to sign up for and nothing to click - the post is yours either way.

Collapse
 
omuniyal profile image
OmUniyal •

Hi @loudify,
Thank you — genuinely useful feedback. You're right that I buried the lead; the validation story is the actual differentiator and I spent too long on workflow justification.

The launch copy you wrote is sharper than my own description of the feature — I'm going to borrow that framing for the article and for the future ones too. One correction worth flagging: as of v0.3.0 it scans the full file, not just the first 1,000 rows, so a bad row at position 15,000 gets caught the same as one at position 5.

The title is on my list to fix — the tool reads CSV, TSV, PSV, Parquet, JSON and JSONL, which isn't obvious from "CSV files."

Appreciate you taking the time. 🩷

Collapse
 
loudify profile image
Deadato •

Good flag on v0.3.0 - that changes the strongest line in the copy, so here it is updated.

"duckboard is a file-first local SQL workspace powered by DuckDB. Load CSV, TSV, PSV, Parquet, JSON or JSONL by name, query it with plain SQL, export the result - no notebook, no imports, no setup. Files are registered as DuckDB views, never copied into a database. On load it scans every row for structural errors and type anomalies and files them in an _errors_ table, so a bad row at 15,000 gets caught the same as one at 5. Then export just the clean rows."

Three changes. Full-file scanning replaces the row count, and it is a much stronger claim - "first 1,000 rows" reads as sampling to anyone who works with data, which sells you short. The format list is now all six from your reply. And "a bad row at 15,000 gets caught the same as one at 5" is your sentence, not mine - it is the most concrete thing either of us has written about that feature, and it belongs in the copy rather than in a comment.

One thing worth knowing if you reuse it: my original comment wrote that table name with underscores and DEV.to ate them, so it rendered as italics. Backticks survive it.

Saw the new title. That's the version that earns the click.