DEV Community: OmUniyal

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

OmUniyal — Mon, 24 Aug 2026 03:37:20 +0000

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

Start a session:

duckboard

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

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'                               │
└────────────┴──────────┴──────────────┴─────────────┴────────────────────────────────────────┘

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         │
└───────────┴────────┴───────────────┴────────────┘

[!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

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

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)

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;

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]:

Unload everything at once:

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

Rename a column without reloading the file:

duckboard> :rename_column sales cust_id customer_id

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

pip install duckboard[readline]

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

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

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

PyPI: https://pypi.org/project/duckboard
GitHub: https://github.com/OmUniyal/duckboard

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.

I built a Python package to diff large data files — here's why existing tools weren't enough

OmUniyal — Sun, 09 Aug 2026 05:05:54 +0000

Every few months at work I run into the same problem.

Two systems are supposed to produce identical data exports. A CSV from the old pipeline, a CSV from the new one. Simple enough to check — until the file has 500,000 rows, three sources to compare instead of two, and columns that were renamed somewhere along the way.

The usual tools fall apart fast:

Excel — opens maybe 100k rows before giving up
diff / fc — order-dependent, one mismatch per line, useless for structured data
pandas — fine for two files that fit in memory, painful for anything larger, no built-in N-way support
Custom scripts — I've written three. None of them were reusable.

So I built duckdiff.

What it does

duckdiff is a Python package for N-way, order-independent comparison of large structured files — CSV, TSV, and Parquet. It's powered by DuckDB, which means comparisons stream off disk and aren't bounded by RAM.

Install it:

pip install duckdiff

Compare two files from the command line:

duckdiff compare old=export_v1.csv new=export_v2.csv --key transaction_id

Output:

Sources:
  old: 547,823 rows, 14 columns
  new: 547,823 rows, 14 columns

Matched:     541,200
Mismatched:  6,123
Only in old: 500
Only in new: 0

Not sure which columns to use as --key? There's a subcommand for that:

duckdiff keys a=export_v1.csv

It scans the file and tells you which column combinations uniquely identify each row — printing results as it goes so you're not staring at a blank screen:

Key column suggestions for 'a':

  ✓  transaction_id  (unique)

  Suggested: duckdiff compare ... --key "transaction_id"

A few things that make it different

N-way comparison. Compare 2, 3, or 20 sources in one pass. Not N pairwise diffs.

Order-independent. Rows don't need to be sorted. DuckDB handles it.

Fuzzy column mapping. If the new pipeline renamed cust_id to customer_id, duckdiff can suggest a mapping — but never applies one silently. You opt in explicitly.

Schema flexibility. If sources don't share all columns, --auto-intersect compares only the shared ones and tells you what was dropped.

Pre-flight dry-run. --dry-run checks schema compatibility and file sizes without scanning a single row. Useful before running a comparison on a large file.

Python API

The CLI is a thin wrapper around a clean Python API:

from duckdiff import ComparisonSession, ComparisonConfig

config = ComparisonConfig(key_columns=["transaction_id"])
with ComparisonSession(config) as session:
    session.add_source("old", "export_v1.csv")
    session.add_source("new", "export_v2.csv")
    result = session.compare()

print(result.matched_row_count)
print(result.mismatched_row_count)
print(result.only_in)

Where to find it

PyPI: https://pypi.org/project/duckdiff
GitHub: https://github.com/OmUniyal/duckdiff

It's at v0.1.0 — early, but tested (161 tests) and usable. Feedback and contributions are welcome — feel free to open an issue or star the repo on GitHub