5. Reading files in place

This is the feature people actually adopt DuckDB for, and it is worth being precise about what it does and where it stops.

The basic move

SELECT * FROM 'events.parquet';
SELECT * FROM 'events.csv';
SELECT * FROM 'events.json';
SELECT * FROM 'data/*.parquet';
SELECT * FROM 'data/**/*.parquet';
SELECT * FROM 's3://bucket/events/**/*.parquet';
SELECT * FROM 'https://example.com/data.parquet';

Every one of these is a complete query. No CREATE TABLE, no load step, no schema declaration, no catalog entry. The replacement scan from chapter 2 sees a string that looks like a path, checks the extension, and substitutes the right reader function.

The explicit forms exist too, and you need them the moment you want options:

SELECT * FROM read_parquet('data/*.parquet', hive_partitioning = true);
SELECT * FROM read_csv('x.csv', delim = '|', header = true, types = {'id': 'BIGINT'});
SELECT * FROM read_json('x.json', format = 'newline_delimited');

Parquet — the one that works properly

Parquet is where the in-place model is not just convenient but genuinely fast, because Parquet’s layout was designed for exactly this.

   events.parquet

   ┌─────────────────────────────────────┐
   │ ROW GROUP 0                         │
   │  column chunk: id      [stats]      │
   │  column chunk: country [stats]      │
   │  column chunk: amount  [stats]      │
   │  column chunk: ts      [stats]      │
   ├─────────────────────────────────────┤
   │ ROW GROUP 1                         │
   │  ...                                │
   ├─────────────────────────────────────┤
   │ FOOTER                              │
   │  schema                             │
   │  row group locations + byte offsets │
   │  per-chunk min/max statistics       │
   └─────────────────────────────────────┘
                    ↑ read this FIRST

   SELECT country, sum(amount)
   FROM 'events.parquet'
   WHERE ts >= '2026-09-01'

   1. read the FOOTER  (a few KB)
   2. the query needs 3 columns of 40.
      → ignore 37 column chunks entirely.
   3. row group stats: ts max < 2026-09-01?
      → skip the whole row group.
   4. issue reads for only the surviving
      byte ranges of only the 3 columns.

On a 40-column file where the query touches three and the filter kills 90% of the row groups, DuckDB reads roughly 0.75% of the file. That is projection pushdown and predicate pushdown, and it is why “query the files directly” is a real strategy rather than a demo. Chapter 9 is the full account.

Over HTTPS or S3 the same thing happens with range requests. The footer is fetched, then only the needed byte ranges. A 2 GB Parquet file in S3, queried for two columns with a selective filter, might transfer twenty megabytes.

CSV — the sniffer, and its limits

CSV has no schema, so DuckDB guesses one. The CSV sniffer is genuinely good and it is also the source of a specific class of bug.

   THE SNIFFER, in order:

   1. DIALECT. Try combinations of delimiter,
      quote character and escape character.
      Pick the one that yields a consistent
      column count across the sample.

   2. TYPES. For each column, try the most
      specific type that all sampled values
      fit: BOOLEAN → BIGINT → DOUBLE →
      TIME → DATE → TIMESTAMP → VARCHAR.

   3. HEADER. Does row 1 have types that
      differ from rows 2..n? If so it is
      probably a header.

   4. Re-run type detection with the header
      excluded.

   Sample size: 20,480 rows by default
                (that is 10 vectors).

The failure mode is the value that appears after the sample.

   rows 1 – 20,480:   product_code
                      "1001", "1002", ...
                      → sniffer says BIGINT

   row 400,000:       "1002-B"
                      → CONVERSION ERROR

Three responses, in order of preference:

-- 1. Declare the columns you care about. Best.
SELECT * FROM read_csv('x.csv', types = {'product_code': 'VARCHAR'});

-- 2. Sniff the whole file. Correct, and slow on big files.
SELECT * FROM read_csv('x.csv', sample_size = -1);

-- 3. Do not fail on bad rows. Use with care.
SELECT * FROM read_csv('x.csv', ignore_errors = true);
--    or, better, keep them:
SELECT * FROM read_csv('x.csv', store_rejects = true);
--    then inspect reject_errors / reject_scans

ignore_errors = true silently discards rows. store_rejects = true keeps them in a table you can look at, which is nearly always what you actually wanted. In any pipeline that runs unattended, declare the types. A sniffer is a convenience for exploration, not a contract.

CSV performance

   ✓ PARALLEL. DuckDB splits the file into
     byte ranges and each thread finds the
     next record boundary.

   ✗ NOT PARALLEL if gzipped. You cannot
     start decompressing in the middle.
     `.csv.gz` runs on one thread.

   ✗ NOT PARALLEL if a quoted field contains
     newlines and DuckDB cannot safely find
     boundaries.

   → and every value must be PARSED from
     text on every query.

If you query the same CSV more than about twice, convert it. One statement:

COPY (SELECT * FROM 'big.csv') TO 'big.parquet' (FORMAT parquet);

The difference is routinely ten to fifty times, and it compounds every time you run the query.

JSON

-- newline-delimited, one object per line: the easy case
SELECT * FROM read_json('logs.ndjson');

-- an array of objects in one file
SELECT * FROM read_json('data.json', format = 'array');

-- keep the nesting instead of flattening
SELECT * FROM read_json('data.json', columns = {'payload': 'JSON'});

DuckDB infers a schema by sampling, exactly as with CSV, and unifies nested objects into STRUCT types. That is powerful and it is also where JSON goes wrong: heterogeneous records produce a struct with every field any record had, mostly NULL.

-- pull out fields with the JSON path operators
SELECT
  payload->>'$.user.id'        AS user_id,
  payload->'$.items[0].sku'    AS first_sku
FROM read_json('events.ndjson');

-> returns JSON; ->> returns text. That is the PostgreSQL convention and DuckDB follows it deliberately.

For anything large, extract to columns once and store as Parquet. Querying raw JSON repeatedly means re-parsing it repeatedly, and JSON parsing is expensive in a way Parquet decoding is not.

Globs, and reading many files as one table

SELECT * FROM 'data/2026/*/*.parquet';
SELECT * FROM 'data/**/*.parquet';        -- recursive
SELECT * FROM read_parquet(['a.parquet', 'b.parquet']);

Two options that solve real problems:

-- files whose schemas differ: match on column NAME,
-- fill missing columns with NULL
SELECT * FROM read_parquet('data/*.parquet', union_by_name = true);

-- add a column holding which file each row came from
SELECT * FROM read_parquet('data/*.parquet', filename = true);

union_by_name is the answer to “the schema changed in March”. Without it, DuckDB matches columns positionally and either errors or — worse — lines up the wrong columns.

filename = true is the debugging tool for “which file has the bad row”, and it is also how you recover a partition value that is in the path but not in the data.

Hive partitioning

   s3://bucket/events/
     year=2026/
       month=09/
         day=01/  part-0.parquet
                  part-1.parquet
         day=02/  part-0.parquet
       month=10/
         day=01/  part-0.parquet

SELECT count(*)
FROM read_parquet('s3://bucket/events/**/*.parquet',
                  hive_partitioning = true)
WHERE year = 2026 AND month = 9;

year, month and day become real columns, derived from the directory names, and a filter on them skips whole directories without opening a single file. That is partition pruning, and it is the same mechanism Iceberg improves on and Trino relies on for Hive tables.

Writing partitioned output is symmetric:

COPY (SELECT * FROM events)
TO 's3://bucket/events'
(FORMAT parquet, PARTITION_BY (year, month), OVERWRITE_OR_IGNORE);

The classic mistake is partitioning too finely. PARTITION_BY (year, month, day, hour, country) produces hundreds of thousands of tiny files, and the cost of listing and opening them exceeds any pruning benefit. MinIO’s chapter 7 is the same lesson from the object-store side: many small objects is the expensive shape. Aim for partitions that hold hundreds of megabytes, not kilobytes.

Object storage — httpfs and secrets

INSTALL httpfs;   -- once
LOAD httpfs;      -- per session (auto-loaded in recent versions)

Credentials go through the secrets system rather than settings:

-- explicit
CREATE SECRET s3_creds (
  TYPE s3,
  KEY_ID 'AKIA...',
  SECRET '...',
  REGION 'eu-west-1'
);

-- from the environment, ~/.aws, IMDS, container role —
-- the normal AWS chain. Prefer this.
CREATE SECRET s3_creds (TYPE s3, PROVIDER credential_chain);

-- MinIO or any S3-compatible endpoint
CREATE SECRET minio (
  TYPE s3,
  ENDPOINT 'minio.internal:9000',
  URL_STYLE 'path',
  USE_SSL false,
  KEY_ID '...', SECRET '...'
);

URL_STYLE 'path' is the setting that trips everyone up with MinIO and other self-hosted S3 implementations — AWS uses virtual-host style addressing, most compatible servers use path style, and the error when you get it wrong is a confusing DNS failure rather than anything about URLs.

Add PERSISTENT to a CREATE SECRET to store it in ~/.duckdb/stored_secrets so it survives the session. That is a file on disk containing credentials; treat it accordingly.

The remote-read settings that matter

SET http_keep_alive = true;         -- default; do not disable
SET enable_http_metadata_cache = true;
SET enable_object_cache = true;     -- cache Parquet metadata
                                    -- across queries

enable_object_cache is the one people miss. Without it, every query re-reads the footer of every file. Over S3 with a thousand files that is a thousand round trips before any data is read, and it can dominate the query.

When in-place stops being the right answer

Being honest about the limits:

   ✗ CSV queried repeatedly → convert to
     Parquet. Always.

   ✗ Thousands of tiny files → the listing and
     the per-file overhead dominate. Compact
     them, or use a table format.

   ✗ You need updates, deletes or MERGE →
     files have no such thing. Use the DuckDB
     format, or [Iceberg](/iceberg/09-row-level-operations/).

   ✗ You need consistent reads while someone
     is writing → a glob over a directory has
     no atomicity. This is exactly the problem
     [Iceberg's chapter 1](/iceberg/01-what-and-why/)
     exists to describe.

   ✗ Many concurrent readers hitting the same
     remote files → each pays its own metadata
     cost. A server that caches would win.

The last two are the argument for a table format, and they are why DuckDB reading Iceberg (chapter 9’s extensions) is a more serious proposition than DuckDB reading a directory of Parquet.

What to take from this chapter

A path is a table. SELECT * FROM 'x.parquet' is complete — no create, no load, no schema. Use the explicit read_* functions the moment you need options.
Parquet is where this genuinely works: read the footer, ignore the columns you do not need, skip row groups by statistics, fetch only surviving byte ranges — often under 1% of the file, and over S3 as range requests.
The CSV sniffer samples 20,480 rows, and the failure mode is the odd value that appears after the sample. Declare types in anything unattended; prefer store_rejects to ignore_errors, which silently discards rows.
.csv.gz cannot be split and runs single-threaded. If you query a CSV more than twice, COPY it to Parquet — routinely 10–50× and it compounds.
JSON infers STRUCT types by sampling; -> returns JSON and ->> returns text. Extract to columns once rather than re-parsing per query.
union_by_name = true handles files whose schemas differ; filename = true tells you which file a row came from.
Hive partitioning turns directory names into columns and prunes whole directories. Do not partition too finely — hundreds of megabytes per partition, not kilobytes.
Credentials go through CREATE SECRET; prefer PROVIDER credential_chain. For MinIO and friends, URL_STYLE 'path' is the setting that fails confusingly.
enable_object_cache = true stops every query re-reading every footer — the most commonly missed remote-read setting.
In-place reading stops being right when you need updates, atomic reads while writing, or many concurrent readers — which is the argument for a table format.

Next: the SQL dialect — the deliberate small kindnesses, and the nested types that make it more than a query language.

Written by Sepuri Sai Krishna