duckdb parquet schema mismatch on read

TL;DR

Use union_by_name=true so files with different column sets merge by name, and align disagreeing types with explicit casts. Multi-file parquet reads assume one schema; the glob is where that assumption breaks.

duckdb parquet schema mismatch on read

Use this when

• read_parquet('dir/*.parquet') fails on schema mismatch
• Some files have columns others lack
• The same column has different types across files

Not for this skill when

• Reading a large CSV runs out of memory
• CSV type inference picks the wrong type
• A single parquet file is corrupt

Steps

1. See the actual disagreement. Read the schemas file by file:

SELECT file, column_name, column_type
FROM parquet_schema('data/*.parquet')
ORDER BY file, column_name;

Expected output: every file's columns and types side by side. The mismatch is usually one file with an extra column or an Int32 vs Int64 disagreement.

1. Merge by column name instead of position:

SELECT * FROM read_parquet('data/*.parquet', union_by_name=true);

Expected output: the read succeeds, with nulls where a file lacked a column. This is the fix for the "files have different columns" case.

1. For type disagreements, cast the odd files into line before the union:

SELECT * FROM read_parquet('data/2026-01.parquet')
UNION ALL BY NAME
SELECT id::BIGINT, * EXCLUDE (id) FROM read_parquet('data/2026-02.parquet');

Expected output: one consistent schema. Cast toward the wider type (Int32 to Int64, never the reverse) so no data is lost.

1. When the file set is messy beyond repair, normalize once into a clean copy:

COPY (SELECT id::BIGINT, name, amount::DOUBLE FROM read_parquet('data/*.parquet', union_by_name=true))
TO 'clean.parquet' (FORMAT PARQUET);

Expected output: a single clean parquet file with one schema. Point all downstream queries at the clean copy.

1. Prevent recurrence: validate schemas at write time in the producer:

# in the writer, assert the schema before writing each file
assert set(df.columns) == EXPECTED_COLUMNS

Expected output: the mismatch is caught where it is created instead of discovered at read time weeks later.

Variant phrasings

duckdb read_parquet column mismatch

union_by_name=true handles missing columns; explicit casts handle type disagreements (steps 2-3).

parquet files different schemas duckdb

Inspect with parquet_schema (step 1) first. Guessing the difference wastes more time than looking.

duckdb unionbyname not working

It only reconciles column sets, not conflicting types. A type conflict still needs the explicit cast (step 3).

Why it happens

Parquet files are self-describing, and a glob read must reconcile N schemas into one. DuckDB's default is positional union, which breaks the moment files disagree on column order, presence, or type. Schema drift across writers, backfills, or partitioned outputs is the usual source.

Edge cases

• Nested struct fields with different sub-schemas do not reconcile, normalize structs at write time.
• Hive-partitioned directories add partition columns automatically, which can look like a mismatch against non-partitioned files.
• Decimal precision differences (DECIMAL(10,2) vs DECIMAL(12,2)) count as type mismatches, cast to the wider precision.
• Filename-based filtering (read_parquet with a file list) lets you exclude the odd files when they are genuinely corrupt.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_itbCZBN6h5Lr8qpakDPquw