Skip to content
Engineering

DuckDB for Developers: SQL on CSV, JSON and Parquet Files Without a Server

AIDToolStack EditorialNovember 7, 20266 min read
duckdbsqlparquetanalyticsmotherduck
Share:Twitter

Someone drops an 8 MB CSV export in Slack and asks which country brought in the most revenue last quarter. You could import it into Postgres, or open it in a spreadsheet and wait. Or you could run one SQL query against the file where it sits. That's what DuckDB is for: an analytical SQL engine that runs inside your process, with no server, and reads CSV, JSON and Parquet directly.

This post uses real output. I installed the Python package with pip install duckdb into a virtualenv, which gave me 2.0.0, generated a 200,000-row orders file, and ran everything below against it. I didn't run the S3, MotherDuck or client-server examples, and I say so where they appear.

DuckDB 2.0, and what it means for upgrades

As of November 2026 the current release is DuckDB 2.0.0, released 21 October 2026. It's a long-term support release, supported until at least October 2027. Version 1.4 is supported until 17 November 2026 and 1.5 was extended to March 2027.

The upgrade note that matters: database files written by 2.0 can't be opened by versions before 2.0. Existing files stay readable by older versions as long as they aren't converted. The announcement suggests testing on a shadow copy first, and waiting for the first patch release (promised for November) if the workload is critical. Other breaking changes include the new PEG parser becoming the default (the old one is available via SET enable_legacy_parser = true and the legacy_parser extension), single-arrow lambda syntax being disabled, and macOS on Intel being phased out.

If you only query CSV and Parquet files and never persist a .duckdb file, the format change doesn't touch you.

DuckDB runs inside your process and reads local CSV, JSON and Parquet files, s3:// and https:// URLs through httpfs, .duckdb database files, MotherDuck through ATTACH md:, and other databases through extensions
DuckDB runs inside your process and reads local CSV, JSON and Parquet files, s3:// and https:// URLs through httpfs, .duckdb database files, MotherDuck through ATTACH md:, and other databases through extensions

Querying files where they sit

A file path in FROM is enough. DuckDB's CSV sniffer detects the delimiter, quoting, header row and column types from a sample (20,480 rows by default):

D DESCRIBE SELECT * FROM 'orders.csv'
┌─────────────┬─────────────┬─────────┐
│ column_name │ column_type │  null   │
├─────────────┼─────────────┼─────────┤
│ order_id    │ BIGINT      │ YES     │
│ customer_id │ BIGINT      │ YES     │
│ ordered_at  │ TIMESTAMP   │ YES     │
│ total       │ DOUBLE      │ YES     │
│ channel     │ VARCHAR     │ YES     │
└─────────────┴─────────────┴─────────┘

(I trimmed three empty columns.) ordered_at came out as TIMESTAMP because the values had a 00:00:00 time part. When a guess is wrong, for example zip codes read as integers and losing leading zeros, override that one column with read_csv('orders.csv', types = {'zip': 'VARCHAR'}).

Joins across formats just work. Here the customers are a JSON-lines file:

SELECT c.country, count(*) AS orders, round(sum(o.total), 2) AS revenue
FROM 'orders.csv' o
JOIN 'customers.json' c ON c.id = o.customer_id
GROUP BY ALL
ORDER BY revenue DESC;
┌─────────┬────────┬────────────┐
│ country │ orders │  revenue   │
├─────────┼────────┼────────────┤
│ US      │  52000 │ 5473644.58 │
│ IN      │  52000 │ 5461910.37 │
│ DE      │  48000 │  5040747.5 │
│ BR      │  48000 │ 5036960.06 │
└─────────┴────────┴────────────┘

GROUP BY ALL saved me listing the grouping column twice. If your analytical SQL is rusty, our window functions guide is a good companion. For JSON quirks before a file reaches DuckDB (big integers, JSON Lines), see our JSON tips.

Parquet: why it's worth converting

If you'll query a file more than a couple of times, convert it once:

COPY (FROM 'orders.csv') TO 'orders.parquet' (FORMAT parquet);

On my synthetic data the CSV was 8,199,713 bytes and the Parquet file 1,558,529, about a fifth. Your ratio will differ; repetitive columns compress best. The Parquet docs say the default codec is Snappy, which parquet_metadata() confirmed:

D SELECT path_in_schema, compression, num_values
  FROM parquet_metadata('orders.parquet') WHERE row_group_id = 0;
┌────────────────┬─────────────┬────────────┐
│ path_in_schema │ compression │ num_values │
├────────────────┼─────────────┼────────────┤
│ order_id       │ SNAPPY      │     122880 │
│ customer_id    │ SNAPPY      │     122880 │
│ ordered_at     │ SNAPPY      │     122880 │
│ total          │ SNAPPY      │     122880 │
│ channel        │ SNAPPY      │     122880 │
└────────────────┴─────────────┴────────────┘

The bigger win is that DuckDB pushes column selection and filters into the Parquet reader. EXPLAIN shows both:

╭─ Parquet Scan ─────────────────────────────────╮
│ Function: PARQUET_SCAN                         │
│ Projections: channel, total                    │
│ Filters:                                       │
│ ordered_at >= '2026-10-01 00:00:00'::TIMESTAMP │
│ ~40,000 rows                                   │
╰────────────────────────────────────────────────╯

Only two of five columns are read. The filter can also skip whole row groups using their min/max statistics, but only if the data is laid out so the ranges don't overlap. My file had dates cycling through the year, so both row groups covered January to October and nothing could be skipped. Sorting before writing changed that:

Unsorted file: both row groups span 2026-01-01 to 2026-10-27, so both are read. Sorted file: row group 0 spans January to July 4 and is skipped for a filter on October dates; only row group 1 is read
Unsorted file: both row groups span 2026-01-01 to 2026-10-27, so both are read. Sorted file: row group 0 spans January to July 4 and is skipped for a filter on October dates; only row group 1 is read
COPY (FROM 'orders.parquet' ORDER BY ordered_at)
  TO 'orders_sorted.parquet' (FORMAT parquet);

For data you'll filter by a low-cardinality column, partitioned writes give you Hive-style folders:

D COPY (FROM 'orders.parquet')
  TO 'orders_by_channel' (FORMAT parquet, PARTITION_BY (channel), OVERWRITE_OR_IGNORE);
orders_by_channel/channel=android/data_0.parquet
orders_by_channel/channel=ios/data_0.parquet
orders_by_channel/channel=web/data_0.parquet

Reading them back with read_parquet('orders_by_channel/*/*.parquet', hive_partitioning = true) restores channel as a column.

Files on S3 (not run here)

The httpfs extension reads s3:// and https:// paths. The S3 docs show a secret that uses the normal AWS credential chain:

CREATE OR REPLACE SECRET aws (TYPE s3, PROVIDER credential_chain);
SELECT channel, count(*) FROM read_parquet('s3://your-bucket/orders/*.parquet') GROUP BY channel;

Globs use ListObjectsV2, and COPY ... TO 's3://...' writes CSV or Parquet via multipart upload. DuckDB 2.0 added asynchronous I/O that keeps several reads in flight, which the announcement credits with "dramatically faster" queries on object storage. Everything above about pushdown applies doubly here, because skipped row groups are bytes you never download.

One writer, many readers (and what's new)

DuckDB's concurrency model is simple: one process can open a database file read-write, or many processes can open it with access_mode = 'READ_ONLY'. Not both. A web app with several worker processes all writing to one .duckdb file won't work; that's an OLTP job, and Postgres or SQLite are better at it (our SQLite in production post covers that end).

Version 2.0 polishes Quack, a native protocol first released in 1.5.3 that turns a DuckDB process into a server. From the announcement: CALL quack_serve(token = 'my_token'); on one side, ATTACH 'quack:localhost' AS qk (TOKEN 'my_token'); on the other. It listens on localhost only by default, and the docs recommend a TLS-terminating reverse proxy for remote clients. The other route to many writers is DuckLake with a Postgres catalog. I haven't run either, so treat them as options to evaluate.

Two smaller things in the box: duckdb -ui opens a local web UI on port 4213 (UI docs); queries run locally, but the UI's own files are fetched from ui.duckdb.org. And the 2.0 CLI detects when a coding agent is driving it and switches to compact Markdown output and JSON errors, which you can force with -agent or turn off with -no-agent.

Where MotherDuck fits

MotherDuck is a hosted DuckDB service. From the CLI you run ATTACH 'md:'; (or start with duckdb "md:"), sign in through the browser or set motherduck_token, and query cloud databases alongside local files.

One timing problem: as of November 2026 MotherDuck's version schedule lists support for DuckDB clients 1.4.x to 1.5.6, with no mention of 2.0. If you use MotherDuck, pin your client to 1.5.6 until that page lists 2.0.

For AI-assisted analysis, the MotherDuck MCP server works with plain local DuckDB too, read-only by default when pointed at a file; our data warehouse MCP roundup compares it with BigQuery, Snowflake and ClickHouse servers.

A short checklist

  • Ad hoc questions about a file: duckdb, then FROM 'file.csv'. No import step.
  • Querying the same data repeatedly: convert to Parquet, sorted by the column you filter on.
  • Many files: glob them, and partition by a column you filter by.
  • Persisting a .duckdb file: decide on 1.5 or 2.0 deliberately, since 2.0 files don't open in 1.x.
  • Several processes writing: not a plain DuckDB file. Look at Quack or DuckLake, or use a database built for it.
  • Using MotherDuck: stay on a client version its schedule lists.
AE

Written by AIDToolStack Editorial

Researched from official documentation, specifications and vendor pages, listed under Sources below. Last checked November 2026.

Sources

  1. github.com/duckdb/duckdb/releases/tag/v2.0.0
  2. duckdb.org/2026/10/21/announcing-duckdb-200.html
  3. duckdb.org/docs/2.0/data/csv/auto_detection.html
  4. duckdb.org/docs/2.0/data/json/overview.html
  5. duckdb.org/docs/2.0/data/parquet/overview.html
  6. duckdb.org/docs/2.0/core_extensions/httpfs/s3api.html
  7. duckdb.org/docs/2.0/connect/concurrency.html
  8. duckdb.org/docs/2.0/core_extensions/ui.html
  9. duckdb.org/docs/2.0/core_extensions/overview.html
  10. motherduck.com/docs/getting-started/interfaces/connect-query-from-duckdb-cli/
  11. motherduck.com/docs/troubleshooting/version-lifecycle-schedules/
  12. github.com/motherduckdb/mcp-server-motherduck

Related Posts

Explore on AIDToolStack

Discover tools and servers featured in our directory.

MCP Servers

Trigger.dev Jobs MCP ServerManage Trigger.dev background jobs from your AI coding assistant. Monitor job runs, trigger tasks manually, inspect execution logs, manage schedules, and debug failed runs. The server provides operational visibility into your background job infrastructure — see which tasks are running, queued, or failed, and take action. Your AI can help diagnose job failures from execution logs, retry failed tasks, and optimize concurrency configurations for better throughput.Tailscale Network MCP ServerManage your Tailscale mesh VPN network from your AI coding assistant. View connected devices, manage ACLs, configure exit nodes, check device status, and manage DNS settings. The server provides visibility into your Tailscale network — see which machines are online, troubleshoot connectivity issues, manage network access policies, and configure routes. Useful for developers managing distributed infrastructure where Tailscale provides secure networking between services.Portainer Docker MCP ServerManage Docker environments through Portainer's API from your AI coding assistant. Control containers, images, networks, and volumes across multiple Docker hosts and Kubernetes clusters. The server provides tools for container lifecycle management, image pulling and building, stack deployment, and resource monitoring. Portainer's multi-environment management makes this server particularly valuable for teams managing containers across development, staging, and production environments.

Reviews

Sign in to leave a review.

No reviews yet. Be the first!

Comments

Sign in to leave a comment.

No comments yet. Be the first!

Sponsor Our Newsletter

Reach thousands of developers who are actively evaluating AI tools, MCP servers, and dev infrastructure. Our weekly newsletter goes to engaged technical decision-makers.

Growing subscriber baseHigh open rates

All sponsored content is clearly labeled per our editorial policy.