DuckDB Parquet Schema Consistency: The Complete Guide to Solving Column Order Mismatch Errors

Introduction: Have You Ever Encountered This Frustrating Error?

Invalid Input Error: Expected schema to have consistent columns

If you’ve ever batch-read Parquet files with DuckDB, you’ve almost certainly seen this error. Picture this scenario: your ETL pipeline produces Parquet files daily from multiple sources, distributed across different servers and maintained by different teams. One day you run a query and DuckDB throws an error. After hours of debugging, you discover the root cause — the column order in Parquet files from different batches is inconsistent.

This isn’t your fault, and it’s not a DuckDB bug. It’s a fundamental characteristic of the Parquet format: Parquet is a columnar storage format, and different tools may write columns in different orders. Apache Spark, PyArrow, fastparquet, and Pandas each have their own default behaviors. When they’re mixed together, schema inconsistency is almost inevitable.

In this article, I’ll share the complete solution for handling Parquet schema inconsistency in DuckDB — from quick fixes to root-cause governance — covering both SQL and Python approaches.

Root Cause: Why Do Parquet Files Have Inconsistent Column Orders?

The Columnar Nature of Parquet

Parquet is a columnar storage format, fundamentally different from row-based formats like CSV. In Parquet files, data for each column is stored contiguously. This design enables:

1. Read-only-needed-columns: If you only need user_id and amount, DuckDB skips all other columns, dramatically improving performance
2. Better compression: Data of the same type compresses more efficiently
3. Column order is tool-dependent: Different tools arrange columns according to their own preferences

Common Sources of Inconsistency

Solution 1: DuckDB Native union_by_name (Recommended)

This is the simplest and most recommended solution. DuckDB’s read_parquet() function provides a union_by_name parameter that automatically aligns data by column name, filling missing columns with NULL.

SQL Approach

-- Basic usage: auto-align by column name
SELECT * 
FROM read_parquet('data/*.parquet', union_by_name=true);

-- Explicitly specify needed columns (safer, avoids unexpected dirty data)
SELECT date, user_id, amount, category
FROM read_parquet('data/*.parquet', union_by_name=true);

-- Combine with hive_partitioning for partitioned directories
SELECT *
FROM read_parquet('data/year=2026/*.parquet', 
                  hive_partitioning=true,
                  union_by_name=true);

Python Approach

import duckdb

# Read all Parquet files with automatic column alignment
df = duckdb.sql("""
    SELECT * FROM read_parquet('data/*.parquet', 
                                union_by_name=true)
""").df()

# Only select needed columns
df = duckdb.sql("""
    SELECT date, user_id, amount, category
    FROM read_parquet('data/*.parquet', union_by_name=true)
""").df()

# Combined with partitioned paths
df = duckdb.sql("""
    SELECT * FROM read_parquet('data/year=2026/*.parquet', 
                                hive_partitioning=true,
                                union_by_name=true)
""").df()

How It Works

When union_by_name=true, DuckDB will:

1. Scan the schema of all Parquet files
2. Merge all column names (take the union)
3. Organize columns in a unified order
4. Fill missing columns with NULL for rows from files that don’t have them

Solution 2: Explicit Column Specification (Production Recommended)

In large-scale production environments, blindly reading all columns can lead to unexpected behavior. The safer approach is to explicitly declare the columns you need:

-- Explicitly specify columns, DuckDB auto-fills missing ones
SELECT 
    date, 
    user_id, 
    amount, 
    category
FROM read_parquet('data/*.parquet', union_by_name=true)
WHERE date >= DATE '2026-01-01'
  AND amount > 0;

Benefits of this approach:

• Better readability: Code is documentation — you clearly know which columns are needed
• Better performance: Only reads needed columns, leveraging Parquet predicate pushdown
• Defensive programming: Avoids downstream calculation errors from unexpected new columns

Solution 3: Enforce Unified Schema at Write Time (Python)

If you need to guarantee consistency at the write端, you can use PyArrow to enforce a schema:

import pyarrow as pa
import pyarrow.parquet as pq
from datetime import date

# Define unified schema
schema = pa.schema([
    ('date', pa.date32()),
    ('user_id', pa.int64()),
    ('amount', pa.float64()),
    ('category', pa.string())
])

# Write with forced schema
table = pa.table({
    'date': [pa.scalar(date(2026, 8, 22), type=pa.date32())],
    'user_id': [12345],
    'amount': [99.9],
    'category': ['electronics']
}, schema=schema)

pq.write_table(table, 'output.parquet')

# Verify: DuckDB reads without any special parameters
import duckdb
df = duckdb.sql("SELECT * FROM read_parquet('output.parquet')").df()
print(df)

Batch Normalize Existing Files

If you have a batch of already-written Parquet files that need schema unification:

import pyarrow as pa
import pyarrow.parquet as pq
import glob
from pathlib import Path

TARGET_SCHEMA = pa.schema([
    ('date', pa.date32()),
    ('user_id', pa.int64()),
    ('amount', pa.float64()),
    ('category', pa.string())
])

def normalize_parquet_schema(input_dir, output_dir):
    """Batch normalize Parquet file schemas"""
    Path(output_dir).mkdir(exist_ok=True)
    
    for filepath in glob.glob(f'{input_dir}/*.parquet'):
        table = pq.read_table(filepath)
        normalized = table.cast(TARGET_SCHEMA, safe=False)
        out_path = Path(output_dir) / Path(filepath).name
        pq.write_table(normalized, out_path)
        print(f'Normalized: {filepath} -> {out_path}')

normalize_parquet_schema('raw_data/', 'normalized_data/')

Diagnostic Tool: The parquet_schema() Function

DuckDB provides a powerful diagnostic function parquet_schema() that lets you inspect Parquet file schema structures without reading any data:

-- View schema of a single file
DESCRIBE SELECT * FROM read_parquet('data/file.parquet');

-- Batch view schema structure of all files
SELECT 
    file_name, 
    column_name, 
    column_type,
    ordinal_position
FROM parquet_schema('data/*.parquet')
ORDER BY file_name, ordinal_position;

-- Find files with inconsistent schemas
WITH schemas AS (
    SELECT 
        file_name,
        list(zip(column_name, column_type)) as col_types
    FROM parquet_schema('data/*.parquet')
    GROUP BY file_name
)
SELECT 
    a.file_name as file_a,
    b.file_name as file_b,
    a.col_types as schema_a,
    b.col_types as schema_b
FROM schemas a
JOIN schemas b ON a.file_name < b.file_name
WHERE a.col_types != b.col_types;

Typical Output Example

┌─────────────────────────┬───────────────┬──────────────┬──────────────────────┐
│ file_name               │ column_name   │ column_type  │ ordinal_position     │
├─────────────────────────┼───────────────┼──────────────┼──────────────────────┤
│ data/001.parquet        │ date          │ DATE         │ 0                    │
│ data/001.parquet        │ user_id       │ BIGINT       │ 1                    │
│ data/001.parquet        │ amount        │ DOUBLE       │ 2                    │
│ data/002.parquet        │ user_id       │ BIGINT       │ 0                    │
│ data/002.parquet        │ date          │ DATE         │ 1                    │
│ data/002.parquet        │ amount        │ DOUBLE       │ 2                    │
│ data/003.parquet        │ date          │ DATE         │ 0                    │
│ data/003.parquet        │ user_id       │ BIGINT       │ 1                    │
│ data/003.parquet        │ amount        │ DOUBLE       │ 2                    │
│ data/003.parquet        │ category      │ VARCHAR      │ 3                    │
└─────────────────────────┴───────────────┴──────────────┴──────────────────────┘

You can see that 002.parquet has a different column order than 001.parquet, and 003.parquet has an extra category column. This is the root cause of the read error.

Advanced Tip: The fastparquet Pitfall

The fastparquet library has a known behavior: when writing DataFrames with special characters in column names or missing column names, it automatically appends .0, .1 suffixes. This causes DuckDB read errors.

Detection and Fix

import duckdb
import fastparquet
import pandas as pd

# Read fastparquet-written files with auto type promotion
df = duckdb.read_parquet('fastparquet_output.parquet', promote_types=True)

# Or handle directly in DuckDB
result = duckdb.sql("""
    SELECT * 
    FROM read_parquet('fastparquet_output.parquet', 
                      promote_types=true)
""").df()

-- Use promote_types in DuckDB to auto-handle type promotion
SELECT * 
FROM read_parquet('data/*.parquet', promote_types=true);

promote_types=true lets DuckDB attempt to promote different types to a common type (e.g., both INTEGER and BIGINT promote to BIGINT), avoiding type mismatch errors.

DuckDB vs Traditional Solutions: Parquet Processing Comparison

💡 Key Insight: For small-to-medium scale Parquet processing tasks, DuckDB delivers 5-10x faster processing than Pandas with zero运维 cost, and is far more lightweight and simpler than Spark.

Production-Ready ETL Template

Here’s a production-grade Parquet reading template that combines all best practices:

import duckdb
from datetime import datetime

def read_parquet_robust(pattern, required_columns, start_date=None):
    """
    Build a robust Parquet reading function
    
    Args:
        pattern: Parquet file path pattern, e.g. 'data/*.parquet'
        required_columns: List of required column names
        start_date: Optional, only read data after this date
    
    Returns:
        DuckDB DataFrame
    """
    query = f"""
        SELECT {', '.join(required_columns)}
        FROM read_parquet('{pattern}', 
                          union_by_name=true,
                          hive_partitioning=true)
    """
    
    if start_date:
        query += f" WHERE date >= DATE '{start_date}'"
    
    return duckdb.sql(query)

# Usage example
df = read_parquet_robust(
    pattern='s3://my-bucket/parquet/2026/*.parquet',
    required_columns=['date', 'user_id', 'amount', 'category'],
    start_date='2026-01-01'
)

# Aggregate directly without loading into memory
result = df.aggregate([
    ('SUM(amount)', 'total_amount'),
    ('COUNT(*)', 'record_count'),
    ('AVG(amount)', 'avg_amount')
])
print(result)

Monetization Suggestions

After mastering Parquet schema consistency handling, you can衍生 the following monetization paths:

1. Data Cleaning SaaS: Build an online Parquet file validation and repair service. Users upload files, and the system automatically detects schema inconsistency issues and generates repair reports. Free tier handles 10 files, paid tier $29/month unlimited.

2. ETL Template Library: Package the above production-grade templates into reusable ETL code libraries. Open-source the core version on GitHub, offer a paid “Enterprise Template Pack” (with Airflow, Prefect, dbt integrations) at $49-199.

3. Data Quality Monitoring Tool: Build a Parquet schema drift monitoring service that automatically scans data directories and sends alerts when new/missing/type-changed columns are detected. Charge by data volume: $0.001/file/month.

4. Online Courses: Create a “DuckDB Data Engineering in Practice” online course covering Parquet processing, schema management, and performance tuning, priced at $49-199/person.

5. Enterprise Consulting: Provide Parquet governance consulting services for data teams, helping establish unified schema standards. Charge per project: $5,000-20,000.

Summary

Parquet schema inconsistency is a common pain point in data engineering, but DuckDB provides an elegant solution through the union_by_name parameter. Remember three key principles:

1. Use union_by_name=true when reading to quickly align columns
2. Enforce unified schema when writing to solve the problem at the source
3. Use parquet_schema() for diagnostics to quickly identify problematic files

These techniques will not only help you eliminate errors but also significantly improve data processing efficiency and code maintainability.

Original link: https://duckdblab.org/en/post/duckdb-parquet-schema-consistency