DEV Community

OmUniyal
OmUniyal

Posted on

I built a terminal SQL workspace for CSV files — and made it catch 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, PSV, Parquet, or JSON files by name, query them with plain SQL, export results — all from the terminal.

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

Files are registered as DuckDB views — never copied into a database. Load once, query as many times as you want. 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 CSV file, duckboard now scans the first 1,000 rows and checks for two things:

Structural errors — rows whose field count doesn't match the header. A trailing comma, a missing value, a malformed export.

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.

Both types get stored in a _errors_{name} table for the session:

duckboard> :load customers.csv as customers
Loaded 'customers' from customers.csv  (csv)
  1 validation error(s) found. Run 'SELECT * FROM _errors_customers' to inspect.

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

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

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

Once you've reviewed the errors, you can 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

A few other commands worth knowing

No-header files — some exports don't include a header row. 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

Rename a column without reloading the file:

duckboard> :rename_column sales cust_id customer_id
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. It's the same engine with less ceremony for the file-first use case.

Where to find it

It's at v0.2.0 — 67 tests, available now. If you work with structured files — CSV, Parquet, PSV, or JSON — and want something faster than spinning up a notebook, give it a try. Issues and feedback welcome.

Top comments (0)