TopolearnReal datasets are folders of files; reading them as one table with pruning is what makes DuckDB scale.
A glob in the path reads many files as one table: FROM 'logs/*.parquet' or 'data/**/*.csv' (** recurses). You can also pass a list: read_parquet(['a.parquet', 'b.parquet']). Add filename = true to know which file each row came from, and union_by_name = true when files have different column sets.
Data lakes often use Hive partitioning: folders named key=value, e.g. sales/year=2025/month=03/part-0.parquet. With hive_partitioning = true (auto-detected for such paths) the folder keys become columns, and a filter like WHERE year = 2025 skips whole folders without opening them.
Parquet makes queries cheap in two more ways:
- projection pushdown: only the columns in the query are read
- filter pushdown: each row group stores min/max statistics, so groups that cannot match a WHERE condition are skipped
parquet_metadata('file.parquet') and parquet_schema(...) show row groups, statistics and types. All of this works the same on S3 or HTTPS paths, where skipping data saves network transfer as well as CPU.
Example
import duckdb, os, tempfile
os.chdir(tempfile.mkdtemp())
duckdb.sql("""
COPY (SELECT i AS id, i % 3 AS shop, 2024 + i % 2 AS year FROM range(12) t(i))
TO 'sales' (FORMAT parquet, PARTITION_BY (year))
""")
print(sorted(os.listdir("sales"))) # ['year=2024', 'year=2025']
print(duckdb.sql("""
SELECT year, count(*) AS n, sum(id) AS ids
FROM read_parquet('sales/*/*.parquet', hive_partitioning = true)
WHERE year = 2025 GROUP BY year
""").fetchall()) # [(2025, 6, 36)]Remember
- Globs (
*,**) and lists read many files as one table - Hive
key=valuefolders become columns and allow partition pruning - Parquet reads only needed columns and skips row groups using min/max stats
Watch out for
- Using thousands of tiny Parquet files; per-file overhead dominates. Aim for files of tens to hundreds of MB.