DuckDB for the analysis that does not need a cluster

Guide, October 2026. Every number on the home page of this site was computed this way, from the public NYC taxi file for June 2025, on a laptop, in about 27 seconds.

What DuckDB is

An analytical SQL database that runs in-process, like SQLite, but built for columnar scans and aggregations. It reads Parquet and CSV files directly, including over HTTP, and it uses every core on the machine. There is nothing to install beyond a Python package, nothing to start, and nothing to pay for.

The whole thing, end to end

import duckdb

con = duckdb.connect()
con.execute("INSTALL httpfs; LOAD httpfs;")

trips = "https://d37ci6vzurychx.cloudfront.net/trip-data/yellow_tripdata_2025-06.parquet"
zones = "https://d37ci6vzurychx.cloudfront.net/misc/taxi_zone_lookup.csv"

con.execute(f"CREATE TABLE trips AS SELECT * FROM read_parquet('{trips}')")
con.execute(f"CREATE TABLE zones AS SELECT * FROM read_csv_auto('{zones}')")

print(con.execute("""
  SELECT z.Zone, count(*) AS trips
  FROM trips t JOIN zones z ON t.PULocationID = z.LocationID
  WHERE tpep_pickup_datetime >= '2025-06-01' AND tpep_pickup_datetime < '2025-07-01'
    AND fare_amount >= 0 AND tpep_dropoff_datetime >= tpep_pickup_datetime
  GROUP BY 1 ORDER BY trips DESC LIMIT 5
""").fetchall())

That is 4.3 million rows read over the network, joined to a lookup, filtered and ranked. JFK Airport comes first, ahead of every Manhattan neighbourhood. The full set of a dozen aggregates, including the data quality counts, ran in 27 seconds; the lakehouse walkthrough publishes the queries.

Where it fits

• Exploring a new source before deciding how to land it. Column names, types, null rates, the weird values, all in minutes.
• One-off questions from the business that do not need to be repeated: "how many of last month's rows had a negative fare?" (275,565, as it happens).
• Data quality checks in a workflow. A Python job that reads the day's files, runs the checks and posts the result does not need a cluster. The quality gate pattern is often just this.
• Prototyping the gold query that will later run on the platform. SQL is SQL; the query moves with small changes.

Where it stops fitting

• Data that does not fit in memory and on local disk. DuckDB spills to disk and handles tens of gigabytes on a decent laptop; it does not handle a billion rows a day with a deadline.
• Many people and many jobs on the same data needing governance, lineage and concurrent writes. That is what a lakehouse with Unity Catalog is for.
• Anything that must run reliably every night with monitoring and a person paged on failure. Possible with DuckDB and a scheduler; but by then the platform's job scheduler, alerts and audit tables are the point.

The honest rule: if one person wants one answer from data that fits on their machine, DuckDB. If many people need the same answers every day from data that keeps growing, the lakehouse. Most teams need both, and the mistake is using the second for the first.

Handing the result to the platform

DuckDB reads and writes Delta tables and Parquet, so the path from "I proved it on my laptop" to "it runs on the platform" is short. Save the prototype's output as Parquet, land it in the bronze layer like any other source, and port the SQL to a gold table. The query shape, the clean filter and the quality rules you wrote while exploring become the pipeline's expectations.

When to ask for help

If your team runs a cluster for questions a laptop could answer, an afternoon of showing the pattern usually changes habits more than any policy. If the opposite is true and a laptop script has quietly become the production pipeline, the lakehouse FAQ is the next read.

Related: DuckDB in the glossary, the Databricks bill guide for the other side of the same decision.