Catch Schema Drift Before Combining Parquet Files

Version scope: DuckDB 2.0. The SQL in this article was run on DuckDB 2.0.0 against small synthetic fixtures.

A file collection rarely stays identical forever. One producer adds a field, another renames one, and a backfill writes an older representation. The dangerous case is a query that still runs while the population or meaning of its columns changes.

DuckDB can combine file schemas by name with union_by_name. Missing fields can become null in the combined result. That is a mechanism for reading evolving inputs, not a policy for deciding which changes are acceptable. DuckDB schema combination

Classify changes before accepting them

Treat an optional additive field differently from a missing required key. A new campaign_label may be acceptable with a documented null meaning. A missing order_id should usually stop an order-level pipeline. A renamed amount field requires a mapping decision, not blind acceptance.

Type compatibility is also insufficient. An integer that changes from cents to whole currency units can remain a valid integer. A timestamp that switches from UTC to local wall time can remain syntactically readable. Keep units, time-zone semantics, enum meanings and null policy in the contract alongside physical types.

For a reviewable import, enumerate files and inspect their schemas before the combined query. Record the producer version and schema identifier for each group. Decide whether to normalize known versions, quarantine unknown ones or reject the whole run.

Separate the raw landing view from the accepted model

The following is an illustration. It deliberately keeps a source filename for investigation and does not silently turn malformed values into accepted business data.

CREATE TEMP VIEW landed AS
SELECT filename, order_id, amount_minor, currency
FROM read_parquet(
  ['export-v1.parquet', 'export-v2.parquet'],
  union_by_name = true
);

SELECT filename, count(*) AS bad_rows
FROM landed
WHERE order_id IS NULL
   OR amount_minor IS NULL
   OR currency IS NULL
GROUP BY filename;

The virtual filename column is documented for Parquet reads. The file list here is fixed for the run. DuckDB Parquet reader

Have the harness block downstream work when required-field violations occur. If an optional field is absent, report its coverage rather than quietly treating null as a business value such as “organic” or “unknown customer.” Those substitutions can distort segmentation.

Review normalization as code

For known historical versions, use an explicit projection per version and combine only normalized results. For example, converting an old whole-unit amount to minor units requires a documented currency rule and overflow handling. Do not assume multiplying every numeric amount by one hundred works for every currency or historical representation.

Preserve raw files so a reviewer can trace normalization decisions. Record counts before and after each stage. When a conversion rejects rows, decide whether partial acceptance is allowed. A financial reconciliation may require an all-or-nothing run, while exploratory analysis may allow a clearly labeled rejected subset.

Add regression fixtures for reordered fields, an added optional column, a missing required column, an invalid type and a semantic unit change. The last case will not be caught by a schema reader alone; it needs contract metadata or business-level assertions.

Make evolution a controlled release

A producer change should arrive with a schema version, compatibility statement and consumer test. If those do not exist, the consuming team must avoid interpreting successful parsing as approval.

The dependable pattern is tolerant reading only where the contract permits it, strict validation where meaning depends on it, and a visible record of every normalization decision. Schema flexibility is useful when its boundaries are explicit.

Both known versions enter an explicit normalization stage. Unknown or incompatible inputs should be quarantined, not guessed.