Puzzleshot #006 - Multiple files in DuckDB

raw data→duckdb→result

In Puzzleshot 003 we processed 500 gzipped files using GNU parallel and awk to aggregate regional sales counts. It worked well, but required careful orchestration of parallel processes and a two-stage aggregation pipeline.

This week — same problem, different tool.

You have the same 500 gzipped TSV files from Puzzleshot 003:

sales_001.tsv.gz sales_002.tsv.gz … sales_500.tsv.gz

Each file has the same structure as before:

order_id customer_id dealer_id order_total

Using DuckDB, answer the following:

What is the total number of orders per dealer across all 500 files?
What is the total sales value per dealer across all 500 files?

Constraints:

No shell scripting
No loops
Single DuckDB query per question

Things to consider:

How does DuckDB handle multiple files?
How does this compare in complexity to the parallel awk solution from #003?
What are the tradeoffs between the two approaches?

Reveal solution

In Puzzleshot 003 we solved this with GNU parallel and a two-stage awk pipeline — one awk instance per file running in parallel, then a second awk instance aggregating the results. It worked, but took some orchestration to get right.

DuckDB collapses this to a single query, and using SQL instead of awk syntax.

Total orders and sales value per dealer, across all 500 files:

select
 dealer_id,
 count(*) as order_count,
 sum(order_total) as total_value
from read_csv('sales_*.tsv.gz')
group by dealer_id
order by order_count desc

A few things worth calling out:

The wildcard pattern ‘sales_*.tsv.gz’ tells DuckDB to treat all 500 matching files as a single table. DuckDB handles the gzip decompression automatically — no separate zcat step needed.
read_csv here is being explicit, but DuckDB’s automatic file-type detection means you could often just write from ‘sales_*.tsv.gz’ directly and get the same result, similar to what we saw in Puzzleshot 005.
Compared to #003: the parallel/awk solution required thinking about process counts, output formats between stages, and a second aggregation pass. The DuckDB version is one query with no orchestration. That’s a meaningful complexity difference.

So should we throw awk away? Not at all. A few situations where the #003 approach still wins:

DuckDB isn’t installed and you can’t install it (back to our original constraint - though there’s another workaround we may explore later)
The files are large enough that even DuckDB’s processing benefits from being split across more available cores than DuckDB will use by default
You need the result as part of a larger shell pipeline rather than a standalone query

The honest takeaway: DuckDB removes a lot of the orchestration overhead for this class of problem, but the unix tools aren’t obsolete — they’re what you reach for when DuckDB isn’t available or isn’t the right fit. Though there’s nothing to say you couldn’t mix the two…