Puzzleshot #007 - The Data was not enough

raw data→unix tools→duckdb→result

You’ve received another delivery of tractor sales data — this time compressed with bzip2. Unfortunately two problems have emerged:

DuckDB doesn’t natively support bzip2 compression
The file contains a number of malformed rows — some have the wrong number of columns, others have non-numeric values in the order_total field

A sample of what you’re dealing with:

order_id	customer_id	dealer_id	order_total
5001	8801	1001	124500.00
5002	8802	1003	87250.00
5003	8803	1001	pending
5004	8804	1002	156750.00
5005	8805	1001	203000.00	extra_column

You need to produce the same aggregation as Puzzleshot 006:

Total order count per dealer
Total sales value per dealer

Constraints:

The source file is bzip2 compressed
Malformed rows must be explicitly filtered, not silently ignored
The final aggregation must be done in DuckDB

Things to consider:

How do you chain these tools together?
How do you validate that your row filter is working correctly?
What does each stage of your pipeline contribute that the others can’t?

Reveal solution

This one requires chaining three tools together, each earning its place in the pipeline.

The full solution:

bzip2 -dc sales.tsv.bz2 | awk -F'\t' 'NF==4 && $4 ~ /^[0-9]+(\.[0-9]{2})?$/' | duckdb -s "
  SELECT
    dealer_id,
    count(*) as order_count,
    sum(order_total::DOUBLE) as total_value
  FROM read_csv('/dev/stdin', delim='\t')
  GROUP BY dealer_id
  ORDER BY order_count DESC
"

What each stage does:

bzip2 -dc — decompresses the file to stdout without writing anything to disk. DuckDB has no native bzip2 support so this stage is non-negotiable.
awk — filters the stream before DuckDB ever sees it. The single condition handles three things simultaneously:
- Rows with the wrong number of columns are dropped via NF==4
- Rows with non-numeric or malformed order_total values are dropped via the regex
- The header row is dropped naturally — “order_total” doesn’t match a numeric currency pattern
The regex /^[0-9]+(\.[0-9]{2})?$/ is worth noting — it validates currency format specifically, not just any numeric value. It accepts whole numbers or values with exactly two decimal places. Stricter than a general numeric check, but appropriate when you know you’re dealing with currency data.
duckdb — reads the clean TSV from stdin and handles the aggregation. The ::DOUBLE cast ensures order_total is treated as numeric and delim='\t' makes the tab delimiter explicit.

The broader point:

Each tool did what it does best — bzip2 for decompression, awk for stream filtering, DuckDB for analytics. Knowing when to chain tools rather than forcing one tool to do everything is a core data engineering skill.