Data rarely arrives in one format. Customer records come out of a spreadsheet as CSV, an analytics pipeline drops order history as Parquet, a compact binary format for tables, and the app logs events as JSON, one object per line. The usual Python answer is to load each file into pandas, fix up the types, and merge the frames by hand. DuckDB lets you skip the loading: you write SQL against the files themselves, and a single query can join a CSV to a Parquet file to a JSON log.

DuckDB is an in-process analytical database. “In-process” means it is a library your Python program imports, like SQLite, not a server you install and connect to. “Analytical” means it is built for scans and aggregations over many rows rather than for many small transactional writes.

This article builds a uv project on your Mac that reads all three formats with DuckDB from Python, and you run it to produce a per-customer summary: revenue from a Parquet file, profile data from a CSV, and device activity from nested JSON, joined in one query and saved back out as Parquet. A Makefile drives every step and a pytest suite locks in the results. Along the way you will see a CSV date that DuckDB reads as the wrong month without any error, and a join that triples one customer’s revenue, because both mistakes look like correct output until you check.

What you end up with

  • duckdb-files/, a uv project with DuckDB as its only runtime dependency.
  • Three sample inputs: customers.csv, events.jsonl, and a generated orders.parquet.
  • One small script per format, each runnable on its own, plus one that shows the wrong way to join them.
  • report.py, which joins all three and writes out/customer_summary.parquet.
  • A four-test pytest suite and a Makefile whose default target prints help.

Prerequisites

  • macOS 13 or later, Apple silicon or Intel. Validated on macOS 26.7.1 on Apple silicon.
  • uv 0.6 or later (installation guide), for example brew install uv. Validated on uv 0.11.26. Earlier releases name uv init’s placeholder file hello.py rather than main.py.
  • Xcode Command Line Tools for make: xcode-select --install. macOS ships GNU Make 3.81, which is all the Makefile below needs.
  • Basic SQL: SELECT, GROUP BY, and JOIN. If those are new, read DuckDB’s SQL introduction first. Everything DuckDB-specific is explained where it appears.

You do not need Python installed in advance; uv downloads Python 3.13 if you do not have it. The versions used here are Python 3.13.14 and DuckDB 1.5.6.

Step 1: Create the project

Everything lives in one uv project, so the DuckDB version is recorded in uv.lock and every command runs against the same environment. The .gitignore comes first so that generated files (the virtual environment, the Parquet output, test caches) are never committed, even by accident.

Create the file

mkdir -p duckdb-files
cd duckdb-files
touch .gitignore

Add the code: .gitignore

.venv/
__pycache__/
.pytest_cache/
out/
data/orders.parquet
.DS_Store
*.log

Detailed breakdown

  • .venv/, __pycache__/, and .pytest_cache/ are the environment and caches that uv, Python, and pytest create.
  • out/ holds the report this article produces in Step 8.
  • data/orders.parquet is generated by a script in Step 3, so the script is the source of truth and the binary file stays out of version control.
  • .DS_Store and *.log keep Finder metadata and stray logs out.

Now initialize the project and add its dependencies. --app creates an application rather than a library, and --python 3.13 pins the interpreter so your results match the ones shown here.

uv init --app --python 3.13 --name duckdb-files
uv add duckdb
uv add --dev pytest
rm main.py

uv init keeps the .gitignore you just wrote, and it also runs git init unless the folder is already inside a git repository. main.py is a placeholder that this project does not use. Confirm DuckDB imports:

uv run python -c "import duckdb; print(duckdb.__version__)"

1.5.6

Step 2: Add the CSV and JSON sample files

The project needs data in each format. CSV and JSON are text, so you can type them; Parquet is binary and gets generated in the next step. The values are small enough to check every result in this article by hand.

Create the file

mkdir -p data
touch data/customers.csv

Add the code: data/customers.csv

customer_id,name,state,signup_date
1,Ada Lovelace,MA,01/05/2026
2,Grace Hopper,NY,02/03/2026
3,Alan Turing,CA,02/11/2026
4,Katherine Johnson,NJ,03/09/2026
5,Edsger Dijkstra,CA,04/02/2026
6,Barbara Liskov,MA,04/08/2026

Detailed breakdown

  • Six customers, each with an id that the other two files refer to.
  • signup_date is written month/day/year, the way a US spreadsheet exports it, so 01/05/2026 is January 5. Every day in this column is 12 or less, which makes each date readable either way round. Step 4 shows what DuckDB does with that.
  • Barbara Liskov (id 6) has no orders and no events. She tests that the final report keeps customers with no activity instead of dropping them.

The events file is newline-delimited JSON, often called NDJSON or JSON Lines: one complete JSON object per line, with no enclosing array. Logging systems write it because each event can be appended as a line without rewriting the file.

Create the file

touch data/events.jsonl

Add the code: data/events.jsonl

{"event_id": 1, "customer_id": 1, "ts": "2026-05-01 09:15:00", "type": "page_view", "device": {"os": "macOS", "browser": "Safari"}, "tags": ["home"]}
{"event_id": 2, "customer_id": 1, "ts": "2026-05-01 09:17:30", "type": "add_to_cart", "device": {"os": "macOS", "browser": "Safari"}, "tags": ["cart", "promo"]}
{"event_id": 3, "customer_id": 2, "ts": "2026-05-02 14:02:11", "type": "page_view", "device": {"os": "iOS", "browser": "Safari"}, "tags": []}
{"event_id": 4, "customer_id": 3, "ts": "2026-05-03 08:45:00", "type": "page_view", "device": {"os": "Windows", "browser": "Edge"}, "tags": ["home"]}
{"event_id": 5, "customer_id": 3, "ts": "2026-05-03 08:47:42", "type": "search", "device": {"os": "Windows", "browser": "Edge"}, "tags": ["search"]}
{"event_id": 6, "customer_id": 3, "ts": "2026-05-03 08:50:05", "type": "add_to_cart", "device": {"os": "Windows", "browser": "Edge"}, "tags": ["cart"]}
{"event_id": 7, "customer_id": 4, "ts": "2026-05-04 19:30:00", "type": "page_view", "device": {"os": "Android", "browser": "Chrome"}, "tags": ["promo"]}
{"event_id": 8, "customer_id": 5, "ts": "2026-05-05 11:11:11", "type": "search", "device": {"os": "macOS", "browser": "Firefox"}, "tags": ["search", "promo"]}

Detailed breakdown

  • Eight events from five customers. Alan Turing (id 3) has three, which matters in Step 7.
  • device is a nested object (the operating system and browser an event came from) and tags is an array, the two shapes that flat CSV cannot hold. Event 3 has an empty tags array on purpose.
  • Keep each object on a single line. Pretty-print one across several lines and the file is no longer newline-delimited JSON.

Step 3: Generate the Parquet file

Parquet is a columnar file format: it stores all the values of one column together instead of storing whole rows, and it records each column’s type in the file itself. Columnar storage is why analytical tools favor it. A query that needs two columns out of fifty reads those two and skips the rest. Because it is binary, you create it with a program, and DuckDB can write Parquet as easily as it reads it.

Create the file

touch make_data.py

Add the code: make_data.py

"""Write data/orders.parquet. Parquet is binary, so it is generated, not typed."""

from pathlib import Path

import duckdb

ORDERS = Path(__file__).parent / "data" / "orders.parquet"


def main() -> None:
    con = duckdb.connect()
    orders = con.sql(
        """
        SELECT
            order_id::INTEGER         AS order_id,
            customer_id::INTEGER      AS customer_id,
            order_date::DATE          AS order_date,
            amount::DECIMAL(10, 2)    AS amount,
            status
        FROM (VALUES
            (1, 1, '2026-05-01', 120.00, 'completed'),
            (2, 1, '2026-05-09',  35.50, 'completed'),
            (3, 2, '2026-05-02',  89.99, 'completed'),
            (4, 3, '2026-05-03',  15.00, 'refunded'),
            (5, 3, '2026-05-06', 240.00, 'completed'),
            (6, 3, '2026-05-10',  60.25, 'completed'),
            (7, 4, '2026-05-04',  42.00, 'pending'),
            (8, 5, '2026-05-05', 310.40, 'completed'),
            (9, 5, '2026-05-12',  19.99, 'refunded')
        ) AS t(order_id, customer_id, order_date, amount, status)
        """
    )
    orders.write_parquet(str(ORDERS))

    count = con.execute("SELECT count(*) FROM read_parquet(?)", [str(ORDERS)]).fetchone()[0]
    print(f"Wrote {count} orders to data/{ORDERS.name}")


if __name__ == "__main__":
    main()

Detailed breakdown

  • duckdb.connect() with no argument opens an in-memory database. Nothing is saved to disk except files you write explicitly, which is all this project needs. Pass a filename such as duckdb.connect("shop.duckdb") when you want tables to persist between runs.
  • con.sql(...) returns a relation: DuckDB’s object for a query that has been checked but not yet run. DuckDB parses the SQL and confirms the input files exist when you build it, then executes it when you ask for results or call a method like write_parquet().
  • The :: casts fix each column’s type before the data is written: INTEGER ids, a real DATE, and DECIMAL(10, 2) for money: up to 10 digits, 2 of them after the point. DECIMAL stores exact cents, where a float approximates most of them. As a float, 89.99 is stored as 89.98999999999999.... The types travel inside the Parquet file, and Step 5 shows them coming back out.
  • write_parquet() writes the relation to a file. The path is passed as a string because DuckDB 1.5.6 rejects a pathlib.Path, both here and as a query parameter.
  • The final query reads the new file back with read_parquet(?). The ? is a prepared-statement parameter: DuckDB substitutes the value from the list that follows, so a path containing a quote or a space cannot break the SQL. Every script in this project passes file paths this way instead of formatting them into the query string.
  • Customer 6 has no orders, and customer 3 has one refunded order alongside two completed ones. Both cases show up in the final report.

Run it:

uv run python make_data.py

Wrote 9 orders to data/orders.parquet

Step 4: Read the CSV and check the date format DuckDB detected

A CSV file carries no types. Every value is text, so DuckDB’s CSV reader runs a sniffer first: it samples the file to work out the delimiter, whether there is a header row, and a type for each column, including the format of any dates. Usually it gets everything right. When a date column is ambiguous, as signup_date is here, it has to pick an order, and it can pick one that is wrong for your data without raising any error. This script prints what the sniffer decided and then reads the file both ways.

Create the file

touch read_csv.py

Add the code: read_csv.py

"""Read data/customers.csv with DuckDB: first as detected, then with the date format pinned."""

from pathlib import Path

import duckdb

CUSTOMERS = Path(__file__).parent / "data" / "customers.csv"


def main() -> None:
    con = duckdb.connect()  # in-memory database: nothing is written to disk
    path = str(CUSTOMERS)

    detected = con.execute("SELECT DateFormat FROM sniff_csv(?)", [path]).fetchone()[0]
    print(f"Detected date format: {detected}")

    print("As detected:")
    con.sql("SELECT name, signup_date FROM read_csv(?)", params=[path]).show()

    print("With dateformat pinned to month/day/year:")
    con.sql(
        "SELECT name, signup_date FROM read_csv(?, dateformat = '%m/%d/%Y')",
        params=[path],
    ).show()


if __name__ == "__main__":
    main()

Detailed breakdown

  • sniff_csv(path) runs the sniffer on its own and returns its decisions as a single row. DateFormat is one column of that row; others include Delimiter, HasHeader, and Columns. Query it whenever a CSV reaches your pipeline from somewhere new.
  • con.execute(sql, params) runs a statement immediately, and fetchone() returns its first row as a tuple.
  • con.sql(sql, params=...) builds a relation, and .show() prints it as a table. show() is for looking at results; use fetchall() when your code needs the values.
  • read_csv(path) with no options uses everything the sniffer detected. Adding dateformat = '%m/%d/%Y' overrides only the date format and keeps every other detected setting. The codes are the strftime format codes: %m is the month, %d the day, and %Y the four-digit year.
  • If you need to fix a column’s type rather than its date format, read_csv also takes types = {'column': 'VARCHAR'}.

Run it:

uv run python read_csv.py

Detected date format: %d/%m/%Y
As detected:
┌───────────────────┬─────────────┐
│       name        │ signup_date │
│      varchar      │    date     │
├───────────────────┼─────────────┤
│ Ada Lovelace      │ 2026-05-01  │
│ Grace Hopper      │ 2026-03-02  │
│ Alan Turing       │ 2026-11-02  │
│ Katherine Johnson │ 2026-09-03  │
│ Edsger Dijkstra   │ 2026-02-04  │
│ Barbara Liskov    │ 2026-08-04  │
└───────────────────┴─────────────┘

With dateformat pinned to month/day/year:
┌───────────────────┬─────────────┐
│       name        │ signup_date │
│      varchar      │    date     │
├───────────────────┼─────────────┤
│ Ada Lovelace      │ 2026-01-05  │
│ Grace Hopper      │ 2026-02-03  │
│ Alan Turing       │ 2026-02-11  │
│ Katherine Johnson │ 2026-03-09  │
│ Edsger Dijkstra   │ 2026-04-02  │
│ Barbara Liskov    │ 2026-04-08  │
└───────────────────┴─────────────┘

The sniffer chose day/month/year (%d/%m/%Y), so 01/05/2026 became May 1 and 04/02/2026 became February 4. Every row parsed, the column has the correct date type, and nothing in the first table looks broken; all six dates are simply wrong. The second line of each table header is the column’s type as .show() prints it: varchar is DuckDB’s string type and date a calendar date.

The sniffer can tell the two orders apart only when some day in the sample is greater than 12, and in this file none is. Otherwise it falls back to a fixed preference list that ranks day-first above month-first. Other data can expose the mistake: read day-first, Alan Turing signed up on November 2, six months after the orders he placed in May. When you know how a file was produced, pin the format rather than trusting the guess.

Step 5: Query the Parquet file

Parquet needs no sniffing, because the file states its own schema: column names, types, and even the precision of a DECIMAL. The script below prints that schema and then aggregates the orders by status, a typical analytical query.

Create the file

touch read_parquet.py

Add the code: read_parquet.py

"""Query data/orders.parquet with DuckDB: inspect its schema, then aggregate it."""

from pathlib import Path

import duckdb

ORDERS = Path(__file__).parent / "data" / "orders.parquet"


def main() -> None:
    con = duckdb.connect()
    path = str(ORDERS)

    print("Schema stored in the file:")
    con.sql("SELECT column_name, column_type FROM (DESCRIBE SELECT * FROM read_parquet(?))", params=[path]).show()

    print("Orders by status:")
    con.sql(
        """
        SELECT status, count(*) AS orders, sum(amount) AS total
        FROM read_parquet(?)
        GROUP BY status
        ORDER BY total DESC
        """,
        params=[path],
    ).show()


if __name__ == "__main__":
    main()

Detailed breakdown

  • DESCRIBE SELECT ... returns one row per column of the query’s result, with its name and type, without returning the data itself. Wrapping it in an outer SELECT keeps just the two columns worth reading.
  • read_parquet(path) is the Parquet counterpart of read_csv. It also accepts a glob, a wildcard pattern such as 'data/orders/*.parquet', and reads every matching file as one table.
  • The aggregate reads only status and amount. Because Parquet is columnar, DuckDB skips the other three columns on disk, which is much of the format’s speed on wide files.
  • int64 in the output is BIGINT under the name .show() uses. sum() over a DECIMAL(10, 2) column returns DECIMAL(38, 2): DuckDB widens the precision so a large total cannot overflow, and the cents stay exact.

Run it:

uv run python read_parquet.py

Schema stored in the file:
┌─────────────┬───────────────┐
│ column_name │  column_type  │
│   varchar   │    varchar    │
├─────────────┼───────────────┤
│ order_id    │ INTEGER       │
│ customer_id │ INTEGER       │
│ order_date  │ DATE          │
│ amount      │ DECIMAL(10,2) │
│ status      │ VARCHAR       │
└─────────────┴───────────────┘

Orders by status:
┌───────────┬────────┬───────────────┐
│  status   │ orders │     total     │
│  varchar  │ int64  │ decimal(38,2) │
├───────────┼────────┼───────────────┤
│ completed │      6 │        856.14 │
│ pending   │      1 │         42.00 │
│ refunded  │      2 │         34.99 │
└───────────┴────────┴───────────────┘

The schema is exactly what make_data.py cast to, with no guessing involved. The totals add up: the six completed orders are 120.00 + 35.50 + 89.99 + 240.00

  • 60.25 + 310.40 = 856.14, and the two refunds are 15.00 + 19.99 = 34.99.

Step 6: Query nested JSON

The JSON reader infers types the way the CSV sniffer does, but JSON can also nest. DuckDB maps a nested object to a STRUCT (a value with named fields, read with dot notation) and an array to a LIST (an ordered collection of values of one type). Knowing those two mappings is what lets you query the device and tags fields without flattening the file first.

Create the file

touch read_json.py

Add the code: read_json.py

"""Query data/events.jsonl with DuckDB: nested objects and arrays included."""

from pathlib import Path

import duckdb

EVENTS = Path(__file__).parent / "data" / "events.jsonl"


def main() -> None:
    con = duckdb.connect()
    path = str(EVENTS)

    print("Schema inferred from the JSON:")
    con.sql("SELECT column_name, column_type FROM (DESCRIBE SELECT * FROM read_json(?))", params=[path]).show()

    print("Events by operating system (a field inside the nested device object):")
    con.sql(
        """
        SELECT device.os AS os, count(*) AS events
        FROM read_json(?)
        GROUP BY os
        ORDER BY events DESC, os
        """,
        params=[path],
    ).show()

    print("Tag counts (one row per element of the tags array):")
    con.sql(
        """
        SELECT tag, count(*) AS events
        FROM (SELECT unnest(tags) AS tag FROM read_json(?))
        GROUP BY tag
        ORDER BY events DESC, tag
        """,
        params=[path],
    ).show()


if __name__ == "__main__":
    main()

Detailed breakdown

  • read_json(path) detects whether the file is newline-delimited or a single JSON array, then infers a type for each key. If detection ever guesses wrong, format = 'newline_delimited' states the layout explicitly.
  • device.os reads the os field of the device struct. Nested structs chain the same way, as in a.b.c.
  • unnest(tags) turns each list into one row per element, so an event tagged ["cart", "promo"] contributes one row to each tag. An event with an empty list contributes no rows, which is why event 3 does not appear in the tag counts.
  • Both aggregates sort by count and then by name, so ties print in a fixed order. String sorting is case-sensitive and uppercase letters sort first, which puts Windows ahead of macOS.

Run it:

uv run python read_json.py

Schema inferred from the JSON:
┌─────────────┬─────────────────────────────────────┐
│ column_name │             column_type             │
│   varchar   │               varchar               │
├─────────────┼─────────────────────────────────────┤
│ event_id    │ BIGINT                              │
│ customer_id │ BIGINT                              │
│ ts          │ TIMESTAMP                           │
│ type        │ VARCHAR                             │
│ device      │ STRUCT(os VARCHAR, browser VARCHAR) │
│ tags        │ VARCHAR[]                           │
└─────────────┴─────────────────────────────────────┘

Events by operating system (a field inside the nested device object):
┌─────────┬────────┐
│   os    │ events │
│ varchar │ int64  │
├─────────┼────────┤
│ Windows │      3 │
│ macOS   │      3 │
│ Android │      1 │
│ iOS     │      1 │
└─────────┴────────┘

Tag counts (one row per element of the tags array):
┌─────────┬────────┐
│   tag   │ events │
│ varchar │ int64  │
├─────────┼────────┤
│ promo   │      3 │
│ cart    │      2 │
│ home    │      2 │
│ search  │      2 │
└─────────┴────────┘

VARCHAR[] is DuckDB’s notation for a list of strings. The ts strings were recognized as timestamps, which here is safe because they are written in ISO 8601 order (year-month-day), the layout DuckDB tries first and one with no common day/month variant.

Step 7: See how a direct join inflates revenue

The report this project builds has one row per customer, with name and state from the CSV, completed revenue from the Parquet file, and event activity from the JSON. The obvious way to write it joins orders and events directly onto customers, and it gives wrong revenue. Run that version first, so you know what the mistake looks like on screen before Step 8 avoids it.

Create the file

touch naive_join.py

Add the code: naive_join.py

"""Join orders and events straight onto customers, and show the revenue that produces."""

from pathlib import Path

import duckdb

DATA = Path(__file__).parent / "data"

NAIVE_SQL = """
SELECT c.name, count(*) AS joined_rows, sum(o.amount) AS revenue
FROM read_csv($customers, dateformat = '%m/%d/%Y') AS c
JOIN read_parquet($orders) AS o USING (customer_id)
JOIN read_json($events)    AS e USING (customer_id)
WHERE o.status = 'completed'
GROUP BY c.name
ORDER BY revenue DESC, c.name
"""


def naive_revenue(con: duckdb.DuckDBPyConnection, data_dir: Path = DATA) -> duckdb.DuckDBPyRelation:
    """Return completed revenue per customer from the raw join (inflated on purpose)."""
    return con.sql(
        NAIVE_SQL,
        params={
            "customers": str(data_dir / "customers.csv"),
            "orders": str(data_dir / "orders.parquet"),
            "events": str(data_dir / "events.jsonl"),
        },
    )


def main() -> None:
    con = duckdb.connect()
    naive_revenue(con).show()


if __name__ == "__main__":
    main()

Detailed breakdown

  • Each JOIN ... USING (customer_id) pairs every row on the left with every row on the right that has the same customer_id. Joining orders to a customer repeats the customer once per order; joining events then repeats each of those rows once per event.
  • joined_rows counts the rows each customer ends up with, so the multiplication is visible next to the total it inflates.
  • naive_revenue returns the relation instead of printing it, so the Step 9 tests can check the wrong totals without copying the SQL.
  • Customers with no completed orders drop out entirely, because a plain JOIN keeps only rows that match on both sides.

Run it:

uv run python naive_join.py

┌─────────────────┬─────────────┬───────────────┐
│      name       │ joined_rows │    revenue    │
│     varchar     │    int64    │ decimal(38,2) │
├─────────────────┼─────────────┼───────────────┤
│ Alan Turing     │           6 │        900.75 │
│ Ada Lovelace    │           4 │        311.00 │
│ Edsger Dijkstra │           1 │        310.40 │
│ Grace Hopper    │           1 │         89.99 │
└─────────────────┴─────────────┴───────────────┘

Alan Turing has two completed orders and three events, so the join produces 2 × 3 = 6 rows and counts each order three times: 300.25 × 3 = 900.75. Ada Lovelace’s two orders are each counted twice, once per event, giving 311.00 instead of 155.50. Edsger Dijkstra and Grace Hopper have one event each, so their totals happen to be right, which is why a spot check of one customer can miss this. Barbara Liskov and Katherine Johnson are missing altogether.

Step 8: Join all three formats correctly and save the result

The fix is to reduce orders and events to one row per customer before joining, so there is nothing left to multiply. This is the query the project exists for, and it also keeps the customers that the plain join dropped.

Create the file

touch report.py

Add the code: report.py

"""Join the CSV, Parquet, and JSON files into one per-customer summary, then save it as Parquet."""

from pathlib import Path

import duckdb

ROOT = Path(__file__).parent
DATA = ROOT / "data"
OUT = ROOT / "out" / "customer_summary.parquet"

SUMMARY_SQL = """
WITH
customers AS (
    SELECT customer_id, name, state
    FROM read_csv($customers, dateformat = '%m/%d/%Y')
),
revenue AS (
    SELECT customer_id, count(*) AS completed_orders, sum(amount) AS revenue
    FROM read_parquet($orders)
    WHERE status = 'completed'
    GROUP BY customer_id
),
activity AS (
    SELECT customer_id, count(*) AS events, list_sort(list_distinct(list(device.os))) AS operating_systems
    FROM read_json($events)
    GROUP BY customer_id
)
SELECT
    c.name,
    c.state,
    coalesce(r.completed_orders, 0)        AS completed_orders,
    coalesce(r.revenue, 0)::DECIMAL(10, 2) AS revenue,
    coalesce(a.events, 0)                  AS events,
    coalesce(a.operating_systems, [])      AS operating_systems
FROM customers AS c
LEFT JOIN revenue  AS r USING (customer_id)
LEFT JOIN activity AS a USING (customer_id)
ORDER BY revenue DESC, c.name
"""


def build_summary(con: duckdb.DuckDBPyConnection, data_dir: Path = DATA) -> duckdb.DuckDBPyRelation:
    """Return the per-customer summary as a lazy DuckDB relation."""
    return con.sql(
        SUMMARY_SQL,
        params={
            "customers": str(data_dir / "customers.csv"),
            "orders": str(data_dir / "orders.parquet"),
            "events": str(data_dir / "events.jsonl"),
        },
    )


def main() -> None:
    con = duckdb.connect()
    summary = build_summary(con)
    summary.show()

    OUT.parent.mkdir(exist_ok=True)
    summary.write_parquet(str(OUT))
    rows = con.execute("SELECT count(*) FROM read_parquet(?)", [str(OUT)]).fetchone()[0]
    print(f"Saved {rows} rows to {OUT.relative_to(ROOT)}")


if __name__ == "__main__":
    main()

Detailed breakdown

  • The WITH clause defines three common table expressions (CTEs): named subqueries that the final SELECT uses like tables. Each one reads a different file format.
  • customers reads the CSV with the date format pinned, as Step 4 showed it must be. The report does not select signup_date, but pinning the format keeps the CTE correct for whoever adds that column later.
  • revenue and activity each GROUP BY customer_id, so they hold at most one row per customer. Joining one-row-per-customer tables onto customers cannot multiply rows, so revenue stays correct.
  • list(device.os) collects every event’s operating system into a list, list_distinct removes repeats, and list_sort puts the result in a fixed order so the output does not vary between runs.
  • LEFT JOIN keeps every customer even when there is no matching row in revenue or activity. The missing values arrive as NULL, and coalesce replaces them with 0 or an empty list [], so Barbara Liskov appears with zeros instead of disappearing.
  • $customers, $orders, and $events are named parameters, filled from the params dictionary. Named parameters read better than three ? markers when one query uses several values.
  • build_summary returns the relation without running it, and takes the data directory as an argument. That makes it easy to test (Step 9): a test can call it, fetch the rows, and compare.
  • ORDER BY revenue refers to the output column, the coalesced value, so customers with no orders sort as 0.00, and ties are broken by name.

Run it:

uv run python report.py

┌───────────────────┬─────────┬──────────────────┬───────────────┬────────┬───────────────────┐
│       name        │  state  │ completed_orders │    revenue    │ events │ operating_systems │
│      varchar      │ varchar │      int64       │ decimal(10,2) │ int64  │     varchar[]     │
├───────────────────┼─────────┼──────────────────┼───────────────┼────────┼───────────────────┤
│ Edsger Dijkstra   │ CA      │                1 │        310.40 │      1 │ [macOS]           │
│ Alan Turing       │ CA      │                2 │        300.25 │      3 │ [Windows]         │
│ Ada Lovelace      │ MA      │                2 │        155.50 │      2 │ [macOS]           │
│ Grace Hopper      │ NY      │                1 │         89.99 │      1 │ [iOS]             │
│ Barbara Liskov    │ MA      │                0 │          0.00 │      0 │ []                │
│ Katherine Johnson │ NJ      │                0 │          0.00 │      1 │ [Android]         │
└───────────────────┴─────────┴──────────────────┴───────────────┴────────┴───────────────────┘

Saved 6 rows to out/customer_summary.parquet

One query read a CSV, a Parquet file, and a JSON log and joined them. Check a row against the inputs: Alan Turing’s completed orders are 240.00 and 60.25, totalling 300.25, and his refunded 15.00 order is excluded. Katherine Johnson has an event but only a pending order, so she shows activity with no revenue. The six revenue figures add up to 856.14, the completed total from Step 5, where the naive join’s added up to 1,612.14. The summary is now a typed Parquet file, ready for the next tool to read.

Step 9: Lock in the results with pytest

The two traps this article demonstrates, the swapped dates and the multiplied revenue, both produce output that looks fine. Tests are how you make sure they stay fixed. pytest needs one setting first: the scripts live at the project root, not in an installed package, so pytest must put the root on Python’s import path for from report import ... to work.

Create the file

uv init already created pyproject.toml, so touch changes nothing here. Open the file and add the [tool.pytest.ini_options] table at the end.

touch pyproject.toml

Add the code: pyproject.toml

[project]
name = "duckdb-files"
version = "0.1.0"
description = "Add your description here"
readme = "README.md"
requires-python = ">=3.13"
dependencies = [
    "duckdb>=1.5.6",
]

[dependency-groups]
dev = [
    "pytest>=9.1.1",
]

[tool.pytest.ini_options]
pythonpath = ["."]

Detailed breakdown

  • Everything above [tool.pytest.ini_options] was written by uv init and uv add. Your version floors may be newer if newer releases exist; leave them as uv wrote them.
  • pythonpath = ["."] adds the project root to the import path when pytest runs, so tests can import report.py. Without it, the tests fail to collect with ModuleNotFoundError: No module named 'report'.

Create the file

mkdir -p tests
touch tests/test_report.py

Add the code: tests/test_report.py

"""Tests for the DuckDB queries. Run with `make test` (which generates the Parquet file first)."""

import datetime
from decimal import Decimal

import duckdb
import pytest

from naive_join import naive_revenue
from report import DATA, build_summary


@pytest.fixture
def con():
    connection = duckdb.connect()
    yield connection
    connection.close()


def test_csv_dates_need_a_pinned_format(con):
    path = str(DATA / "customers.csv")
    sql = "SELECT signup_date FROM read_csv(?{}) WHERE name = 'Edsger Dijkstra'"

    detected = con.execute(sql.format(""), [path]).fetchone()[0]
    pinned = con.execute(sql.format(", dateformat = '%m/%d/%Y'"), [path]).fetchone()[0]

    assert detected == datetime.date(2026, 2, 4)  # 04/02 read as day/month
    assert pinned == datetime.date(2026, 4, 2)


def test_summary_rows(con):
    rows = build_summary(con).fetchall()
    assert rows == [
        ("Edsger Dijkstra", "CA", 1, Decimal("310.40"), 1, ["macOS"]),
        ("Alan Turing", "CA", 2, Decimal("300.25"), 3, ["Windows"]),
        ("Ada Lovelace", "MA", 2, Decimal("155.50"), 2, ["macOS"]),
        ("Grace Hopper", "NY", 1, Decimal("89.99"), 1, ["iOS"]),
        ("Barbara Liskov", "MA", 0, Decimal("0.00"), 0, []),
        ("Katherine Johnson", "NJ", 0, Decimal("0.00"), 1, ["Android"]),
    ]


def test_joining_raw_tables_inflates_revenue(con):
    naive = {name: revenue for name, _, revenue in naive_revenue(con).fetchall()}
    assert naive["Alan Turing"] == Decimal("900.75")  # 300.25 once per event: three times
    assert naive["Ada Lovelace"] == Decimal("311.00")  # 155.50 twice


def test_summary_round_trips_through_parquet(con, tmp_path):
    out = tmp_path / "summary.parquet"
    summary = build_summary(con)
    summary.write_parquet(str(out))

    assert con.execute("SELECT * FROM read_parquet(?)", [str(out)]).fetchall() == summary.fetchall()

Detailed breakdown

  • con is a pytest fixture: a function pytest runs for each test that names it as a parameter. It gives each test a fresh in-memory connection and closes it afterwards, so no test can see another’s state.
  • test_csv_dates_need_a_pinned_format records the Step 4 behavior: detected, 04/02/2026 is February 4; pinned, it is April 2. If a future DuckDB release changes how the sniffer handles ambiguous dates, this test fails and tells you, instead of the change slipping through.
  • test_summary_rows compares the full report. DuckDB returns DECIMAL values as Python Decimal and lists as Python lists, so the expected rows use those types. Comparing to the float 310.4 would fail.
  • test_joining_raw_tables_inflates_revenue imports naive_revenue from Step 7 and confirms the two wrong totals, 900.75 and 311.00. Keeping the wrong query under test documents why report.py aggregates before joining.
  • test_summary_round_trips_through_parquet writes the summary to pytest’s temporary directory and reads it back, proving the saved file holds exactly the rows the query produced, types included.
  • The tests read data/orders.parquet, so it must exist before pytest runs. The Makefile in the next step makes that automatic.

Run the suite:

uv run pytest -v

tests/test_report.py::test_csv_dates_need_a_pinned_format PASSED         [ 25%]
tests/test_report.py::test_summary_rows PASSED                           [ 50%]
tests/test_report.py::test_joining_raw_tables_inflates_revenue PASSED    [ 75%]
tests/test_report.py::test_summary_round_trips_through_parquet PASSED    [100%]

============================== 4 passed in 0.04s ===============================

pytest also prints a header with your platform and versions above these lines, and the timing on the last line varies from run to run.

Step 10: Drive everything with make

Six scripts and a test suite are easy to run in the wrong order, for example running the tests before the Parquet file exists. A Makefile records the order once: each target names what it depends on, and make builds those first. A target is a name you pass to make, such as report. Running make with no target prints the list of targets for whoever opens the project next.

Create the file

touch Makefile

Add the code: Makefile

.DEFAULT_GOAL := help
.PHONY: help data csv parquet json naive report test clean

help:  ## Show this help screen
	@grep -E '^[a-z]+:.*## ' $(MAKEFILE_LIST) | awk 'BEGIN {FS = ":.*## "} {printf "  %-8s %s\n", $$1, $$2}'

data: data/orders.parquet  ## Generate data/orders.parquet

data/orders.parquet: make_data.py
	uv run python make_data.py

csv:  ## Read data/customers.csv, as detected and with the date format pinned
	uv run python read_csv.py

parquet: data  ## Query data/orders.parquet
	uv run python read_parquet.py

json:  ## Query data/events.jsonl, including nested fields
	uv run python read_json.py

naive: data  ## Join the raw files directly and show the inflated revenue
	uv run python naive_join.py

report: data  ## Join all three files and save out/customer_summary.parquet
	uv run python report.py

test: data  ## Run the pytest suite
	uv run pytest -v

clean:  ## Remove generated files
	rm -rf out data/orders.parquet .pytest_cache

Detailed breakdown

  • .DEFAULT_GOAL := help makes a bare make run the help target.
  • help finds every line shaped like target: ... ## description and prints the target and its description. Adding a target with a ## comment adds it to the help screen automatically.
  • data/orders.parquet is a file target that depends on make_data.py. make regenerates the Parquet file only when it is missing or older than the script, so make data does nothing on a second run.
  • parquet, naive, report, and test depend on data, so they generate the Parquet file first when it is missing. csv and json read only text files and need nothing generated.
  • Recipe lines, the commands under each target, must be indented with a tab, not spaces. If make reports missing separator, an editor has replaced the tab.
  • .PHONY marks targets that are command names rather than files. It matters most for data: without it, make would see the data/ directory, decide the target already exists, and never run it.

Check the help screen:

make

  help     Show this help screen
  data     Generate data/orders.parquet
  csv      Read data/customers.csv, as detected and with the date format pinned
  parquet  Query data/orders.parquet
  json     Query data/events.jsonl, including nested fields
  naive    Join the raw files directly and show the inflated revenue
  report   Join all three files and save out/customer_summary.parquet
  test     Run the pytest suite
  clean    Remove generated files

Now prove the dependency chain from a clean state. make clean deletes the Parquet input and the report, and make report rebuilds both:

make clean
make report

rm -rf out data/orders.parquet .pytest_cache
uv run python make_data.py
Wrote 9 orders to data/orders.parquet
uv run python report.py
┌───────────────────┬─────────┬──────────────────┬───────────────┬────────┬───────────────────┐
│       name        │  state  │ completed_orders │    revenue    │ events │ operating_systems │
│      varchar      │ varchar │      int64       │ decimal(10,2) │ int64  │     varchar[]     │
├───────────────────┼─────────┼──────────────────┼───────────────┼────────┼───────────────────┤
│ Edsger Dijkstra   │ CA      │                1 │        310.40 │      1 │ [macOS]           │
│ Alan Turing       │ CA      │                2 │        300.25 │      3 │ [Windows]         │
│ Ada Lovelace      │ MA      │                2 │        155.50 │      2 │ [macOS]           │
│ Grace Hopper      │ NY      │                1 │         89.99 │      1 │ [iOS]             │
│ Barbara Liskov    │ MA      │                0 │          0.00 │      0 │ []                │
│ Katherine Johnson │ NJ      │                0 │          0.00 │      1 │ [Android]         │
└───────────────────┴─────────┴──────────────────┴───────────────┴────────┴───────────────────┘

Saved 6 rows to out/customer_summary.parquet

make generated the missing Parquet file before running the report. make test follows the same chain and ends with 4 passed.

Troubleshooting

  • Dates are off by months. The CSV sniffer picked the wrong day/month order. Check its choice with SELECT DateFormat FROM sniff_csv('file.csv') and pass dateformat to read_csv.
  • Conversion Error: CSV Error on Line: .... A column looked numeric in the rows the sniffer sampled and then contained text further down. The error message names the column; pass types = {'that_column': 'VARCHAR'} to read_csv.
  • IO Error: No files found that match the pattern. The path is wrong for the directory you ran from. The scripts build paths from Path(__file__) so they work from anywhere; check any path you typed by hand.
  • ModuleNotFoundError: No module named 'report' under pytest. The pythonpath = ["."] setting from Step 9 is missing from pyproject.toml.
  • A total is several times too large. A join matched more than one row per customer on both sides, as in Step 7. Aggregate each side to one row per key before joining, as report.py does.

Recap

You built a uv project that queries Parquet, CSV, and JSON files in place with DuckDB, without loading any of them into a separate database first. You saw that a Parquet file carries its own types, that the CSV sniffer guesses types and can guess a date order wrong without raising an error, and that DuckDB maps nested JSON onto STRUCT and LIST values you can query with dot notation and unnest. You joined all three formats in one query, avoided the row multiplication that inflates totals, saved the result as Parquet, and pinned the behavior with pytest and a Makefile.

Natural next steps:

  • Point read_parquet at a glob such as 'exports/*.parquet' to query a whole directory of files as one table.
  • Open a persistent database with duckdb.connect("shop.duckdb") and CREATE TABLE ... AS SELECT from the files when the same data is queried repeatedly.
  • Call .pl() or .df() on a relation to hand results to Polars or pandas. Add the libraries first: uv add polars pyarrow or uv add pandas.