Puzzleshot #010 - Combining many tools in one pipeline

raw data→unix tools→duckdb→result

Over the last few Puzzleshots we’ve built up a useful set of techniques — filtering malformed data with awk, querying remote files with DuckDB’s httpfs extension, and using DuckDB as both a query engine and format converter.

This time we’re combining them.

You have two data sources:

A bzip2 compressed local sales file with malformed rows (same as #007/#008)
A remote dealer reference file published as Parquet over HTTP (same as #009)

The challenge:

Using a single pipeline, filter the local sales file with awk and JOIN the clean output against the remote dealer reference data in DuckDB to produce:

Total order count and sales value per dealer name and region
Only include dealers with more than 100 orders

Constraints:

Single pipeline, no intermediate files
The sales data must be filtered before DuckDB sees it
The dealer data must be queried remotely — no local copy

Things to consider:

How do you pass awk output to DuckDB while also querying a remote source?
What does DuckDB need to handle two different source types in a single query?
Where are the potential bottlenecks in this pipeline?

Reveal solution

This puzzle combines everything we’ve built so far — bzip2 decompression, awk filtering, remote httpfs queries, and a cross-source JOIN in a single pipeline.

The full solution:

bzip2 -dc sales.tsv.bz2 | awk -F'\t' 'NF==4 && $4 ~ /^[0-9]+(\.[0-9]{2})?$/' | duckdb -c "
  LOAD httpfs;

  SELECT
    d.dealer_name,
    d.region_id,
    count(*) as order_count,
    sum(s.order_total) as total_value
  FROM read_csv('/dev/stdin', delim='\t') s
  JOIN read_parquet('https://example.com/dealers.parquet') d
    ON s.dealer_id = d.dealer_id
  GROUP BY d.dealer_name, d.region_id
  HAVING count(*) > 100
  ORDER BY total_value DESC
"

What’s happening at each stage:

bzip2 -dc — decompresses the local sales file to stdout. DuckDB still can’t read bzip2 natively so this stage remains necessary.
awk — filters malformed rows before DuckDB sees them. Same filter as #007 and #008 — wrong column counts and non-currency order_total values are dropped cleanly.
duckdb — simultaneously reads from two completely different sources in a single query:
- /dev/stdin — the clean filtered sales stream from awk
- https://example.com/dealers.parquet — the remote dealer reference file via httpfs
That JOIN across stdin and a remote HTTP source in a single query is the most interesting part of this Puzzleshot.

Note the use of HAVING vs WHERE:

HAVING filters after aggregation — WHERE filters before. Since the 100 order minimum applies to the aggregated result rather than individual rows, HAVING is what we should use. Using WHERE in this situation is a common issue.

On potential bottlenecks:

Three candidates depending on your environment:

Network — if the remote Parquet file(s) are large, accessing files over the network dominates
Decompression — bzip2 is single-threaded and slower than gzip or zstd
I/O — on older hardware reading the local bzip2 file may be the limiting factor

Always try to profile where you’re spending time before optimizing and remember that modern machines can make use of their hardware if you’re smart about the techniques used.

Thus far, we’ve gone from validating a single gzipped file with basic unix tools in #001 to joining a locally filtered stream against a remote Parquet source in #010. If you learn about your data, know your constraints, and pick the right tool for each part you can create sophisticated solutions without special (ie, Expensive) components.