Puzzleshot #015 - When a Duck eats from Python...

raw data→python→duckdb→result

Over the last few Puzzleshots we’ve been preprocessing data with awk before passing it to DuckDB. This week let’s try something different — handling the preprocessing entirely in Python, but using DuckDB internally rather than piping between tools.

DuckDB has a Python library that allows you to register Python objects — including generator functions — directly as queryable tables. This means you can use Python’s csv module to handle parsing, cleaning, and validation, then query the result with SQL.

Same ragged data as #011:

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 challenge:

Using Python and DuckDB’s Python library, try to handle the ragged schema problem from #011 — extract just the first 10 columns using Python’s csv module, register the result as a DuckDB table, and produce total order value per region.

Things to consider:

How do you register a Python generator as a DuckDB table?
How does Python’s csv module handle quoted fields with embedded commas compared to cut or awk?
What are the memory implications of this approach vs the streaming pipe approach from earlier in the series?
When would you choose this approach over the unix pipe approach?

Reveal solution

This puzzle we moved to preprocessing with Python while keeping DuckDB as the query engine — using DuckDB’s Python library for a really neat trick - use a generator function to generate a pandas dataframe as a queryable table.

A solution:

import duckdb
import csv
import gzip
import pandas as pd

def clean_rows(filename):
  with gzip.open(filename, 'rt') as f:
    reader = csv.reader(f)
    next(reader) # skip header
    for row in reader:
      if len(row) >= 10:
        yield row[:10]

conn = duckdb.connect()
conn.register('clean_data', pd.DataFrame(clean_rows('sales.csv.gz')))

result = conn.execute("""
  SELECT
    column3 as region,
    sum(CAST(column4 AS DOUBLE)) as total_value
  FROM clean_data
  GROUP BY region
  ORDER BY total_value DESC
""").fetchdf()

print(result)

What’s happening:

gzip.open with mode ‘rt’ handles decompression transparently — no separate zcat/gunzip step needed.
Python’s csv.reader correctly handles quoted fields with embedded commas — the problem that cut and naive awk splitting can’t solve cleanly. This closes the loop on the embedded comma gotcha from #011 and #012.
len(row) >= 10 filters out any rows with fewer than 10 columns before they reach DuckDB.
row[:10] discards the variable remainder beyond column 10.
conn.register('clean_data', pd.DataFrame(clean_rows('sales.csv.gz'))) registers the generator as a DuckDB virtual table via pandas. Since we skipped the header, DuckDB assigns positional column names — column0, column1, etc. We alias column3 and column4 in the query to give them meaningful names. Note that DuckDB can properly handle alias names in the GROUP BY statement - other SQL engines may not.
fetchdf() returns the result as a Pandas dataframe — swap for fetchall() if you prefer raw tuples.

Memory considerations:

This approach materializes the generator output into memory before DuckDB queries it. For very large files the unix pipe approach from earlier in the series has a lower memory footprint since it streams without fully materializing. Choose based on your data size and whether proper CSV parsing matters for your dataset.