Puzzleshot #008 - Parquet writing

raw data→unix tools→duckdb→result

In Puzzleshot 007 we built a three-stage pipeline — bzip2 for decompression, awk for filtering, and DuckDB for analytics. This week we’re going to change what DuckDB does at the end of that pipeline.

Rather than querying the data directly, we want to persist the cleaned output as Parquet for downstream use.

Same source file as #007:

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

The challenge:

Using the same pipeline approach from #007, how would you:

Decompress, filter malformed rows, and write the clean output to a Parquet file using DuckDB
Query the resulting Parquet file to produce the same dealer aggregation from #006

Things to consider:

What syntax does DuckDB use to write Parquet?
What compression does DuckDB apply to Parquet output by default?
How does querying the Parquet file compare to querying the original TSV directly?

Reveal solution

This week we extended the pipeline from #007 to use DuckDB as a Parquet converter rather than a query engine. Two stages to cover.

Stage 1 — Decompress, filter, and write to Parquet:

bzip2 -dc sales.tsv.bz2 | awk -F'\t' 'NF==4 && $4 ~ /^[0-9]+(\.[0-9]{2})?$/' | duckdb -s "
  COPY (
    SELECT * FROM read_csv('/dev/stdin', delim='\t')
  ) TO 'sales_clean.parquet' (FORMAT PARQUET)
"

The pipeline is identical to #007 up to the DuckDB stage. The difference is COPY TO instead of a SELECT — DuckDB reads the clean TSV from stdin and writes it directly to Parquet without any intermediate files.

Stage 2 — Query the Parquet file:

SELECT
  dealer_id,
  count(*) as order_count,
  sum(order_total) as total_value
FROM 'sales_clean.parquet'
GROUP BY dealer_id
ORDER BY order_count DESC

Notice the ::DOUBLE cast from #007 is gone — Parquet stores typed data, so order_total is already numeric. No casting needed at query time.

On the compression question:

DuckDB writes Parquet with Snappy compression by default — a good balance of compression ratio and decompression speed. If you want to specify explicitly or use a different algorithm:

COPY (…) TO ‘sales_clean.parquet’ (FORMAT PARQUET, COMPRESSION ZSTD)

Zstd compresses better than Snappy at a small CPU cost — worth considering if storage is a concern.

On query performance:

Querying the Parquet file is generally faster than querying the original TSV for analytical workloads. Parquet is columnar — DuckDB only reads the columns your query actually needs rather than parsing every field in every row. On wide tables with many columns the difference can be significant.

The broader point:

DuckDB as a format converter is an underappreciated pattern. Anything you can pipe into it — from any source, filtered by any tool — can be saved as Parquet for fast downstream querying.