## 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.

```text
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:

```sql
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.

2. Merge by column name instead of position:

```sql
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.

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

```sql
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.

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

```sql
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.

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

```python
# 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 union_by_name 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