Puzzleshot #011 - Handling variable column counts in unix

raw data→unix tools→duckdb→result

A common real-world data problem — you receive a file where the first N columns are consistent and well-defined, but beyond that the structure is variable. Maybe optional attributes, legacy fields, or an embedded list that got appended over time.

Sample rows to illustrate:

order_id,customer_id,dealer_id,region,order_total,order_date,product_id,quantity,status,sales_rep,tag1,tag2,tag3
5001,8801,1001,North,124500.00,2024-01-01,X9100,2,complete,Jones,preferred,volume
5002,8802,1003,South,87250.00,2024-01-02,CH95,1,pending,Smith
5003,8803,1001,East,203000.00,2024-01-03,X9100,3,complete,Davis,preferred,volume,new_account,referral
5004,8804,1002,West,156750.00,2024-01-04,CH95,1,complete,Jones

The first 10 columns are consistent across all rows. Everything after is variable and unpredictable.

The challenge:

The file is gzipped. You need to query just the first 10 columns using DuckDB to produce total order value per region — but DuckDB’s CSV reader struggles with the inconsistent column count.

How do you get clean data into DuckDB without writing a custom parser?

Things to consider:

What unix tool handles column truncation cleanly before DuckDB sees the file?
What happens if any of the first 10 columns contain embedded commas?
How would your approach change if the consistent column count was 100 instead of 10?

Reveal solution

The core challenge here is getting ragged data — with a consistent first 10 columns, variable remainder — into DuckDB cleanly without writing a custom parser.

One solution:

zcat sales.csv.gz | cut -d',' -f1-10 | duckdb -c "
  SELECT
    region,
    sum(order_total) as total_value
  FROM read_csv('/dev/stdin')
  GROUP BY region
  ORDER BY total_value DESC
"

What each stage does:

zcat — decompresses the gzipped file to stdout. No intermediate file written to disk.
cut -d',' -f1-10 — slices the first 10 columns before DuckDB sees the data. The ragged variable columns never enter the pipeline. Clean, fast, single pass.
duckdb — receives clean consistent CSV via stdin and handles the aggregation without any schema complaints.

Why cut works here:

cut is ideal when your consistent columns contain no embedded commas. It’s fast, simple, and requires no awk logic. One flag for the delimiter, one flag for the field range.

The embedded comma gotcha:

If any of the first 10 columns contain quoted fields with embedded commas — like “Acme Corp, North Division” — cut will miscount the columns and slice incorrectly. In that case a tool with proper CSV handling is better suited for this one. It’s a bit more in-depth, so we’ll look at that later on.

The 100 column question:

If the consistent portion was 100 columns instead of 10, cut -f1-100 handles it identically — no additional complexity. The approach scales without changes.

The overall point:

DuckDB’s CSV reader is powerful but expects consistent structure. When your data is ragged, pre-process the stream before DuckDB sees it rather than trying to force DuckDB to handle inconsistency it wasn’t designed for. Unix tools handle the mess, DuckDB handles the analytics. Though perhaps we should consider some ways to do this within DuckDB itself…