DuckDB read_parquet: When to Query Parquet in Place and When to Materialize

The lake-table dilemma

You have a directory of Parquet files—maybe partitioned by date, maybe dumped by an upstream job. You need to run SQL against them today. The question is whether to point DuckDB at the files directly with read_parquet, or to load them into a native table first. Querying in place avoids duplication and keeps a single source of truth, but every query re-opens and re-decodes Parquet metadata. Materializing with CREATE TABLE AS trades storage for faster iterative scans. The right answer depends on how often you query, how stable your schema is, and how your files are laid out.

read_parquet and the no-ETL path

DuckDB's read_parquet table function accepts glob patterns like 'data/*.parquet' or 's3://bucket/**/*.parquet' and infers schemas automatically. You do not need to define a catalog, register a database, or run an ETL pipeline. If your data follows a partitioned lake layout, a single SQL statement can span every file that matches the pattern. This is useful for exploratory analysis, one-off audits, and pipelines where the filesystem is the contract.

What pushdown actually does for you

DuckDB is columnar and vectorized. When you query Parquet directly, it does not read entire files into memory and filter afterward. Predicate and projection pushdown let DuckDB skip Parquet row groups—contiguous chunks of rows—that cannot satisfy your WHERE clause, and read only the columns your query touches. Queries that touch few columns or filter on sorted partition keys can scan terabytes in seconds. The engine uses the metadata embedded in each Parquet file to make these decisions without loading the full dataset.

The per-query tax: metadata, tiny files, and drift

Direct queries are not free. Every execution opens the matched files and decodes their metadata. If your lake contains many tiny files, that metadata overhead dominates. Network latency compounds the problem for remote object stores: the httpfs extension is required for s3:// or gs:// paths, and throughput is bounded by the network and by file size. Schema drift is another trap. If a new column appears in later files, positional alignment can silently misalign data or throw a hard error. DuckDB's union_by_name=true option aligns columns by name instead of position, preventing those failures.

A worked example: surviving schema drift

Suppose you have daily Parquet files in data/ and an upstream team adds a region column to the newest file. Run this in the DuckDB CLI or any SQL client connected to DuckDB; you need read access to the files.

-- Check that the Parquet reader is active
FROM duckdb_extensions() 
WHERE extension_name = 'parquet' AND loaded;

If the extension is not loaded, run INSTALL parquet; LOAD parquet; first. Then compare behavior:

-- May raise a schema mismatch or misalign columns
SELECT count(*) FROM read_parquet('data/*.parquet');

-- Aligns columns by name; missing values become NULL
SELECT count(*) 
FROM read_parquet('data/*.parquet', union_by_name=true);

With union_by_name=true, files that lack region return NULL for that column. Without it, DuckDB may raise a schema mismatch error or map columns incorrectly. This is a configuration decision, not a bug: choose union_by_name=true when producers can add fields independently, and keep strict positional alignment only when every file is guaranteed identical.

Limitations and when to materialize

External Parquet queries do not participate in DuckDB's ACID transactions. Consistency depends on the underlying filesystem or object store. If a concurrent writer is overwriting files mid-query, you may see partial or stale data. Native tables, by contrast, give you transactional guarantees inside DuckDB.

Automatic schema inference samples a subset of rows. Wide or heterogeneous files may need explicit columns or union_by_name to avoid runtime errors. If your workflow runs the same aggregation dozens of times against a stable dataset, the per-query metadata tax adds up. At that point, materialize:

CREATE TABLE hot_events AS
SELECT * FROM read_parquet('data/*.parquet');

This changes state: it writes a new table into your DuckDB database. You need write permissions and enough disk space. To reverse it, run DROP TABLE hot_events;—the original Parquet files remain untouched.

A practical check before you commit

Do not guess whether direct queries are fast enough for your workload. Measure. Run:

EXPLAIN ANALYZE 
SELECT count(*) FROM read_parquet('data/*.parquet');

Then run the same query against a materialized copy. Compare the timing and the scan operators. If the direct query spends most of its time in metadata binding rather than row-group scanning, materialization will pay off. If the scan itself dominates, keep querying the files directly and spend your effort on file layout instead.

Keep read_parquet as your default for exploration and for pipelines where the filesystem is the source of truth. Reach for CREATE TABLE AS when iteration speed matters more than storage, or when you need transactional isolation from upstream writers.