Yesterday’s export had an ID and an amount. Today’s adds a coupon column—and one amount says “pending.” You want all four rows without silently losing the new field or pretending every value is usable.
We will solve the two separate problems: combine the schemas, then validate the values. The browser creates real CSV and Parquet files from the sample tables, so the queries exercise the file readers.
Explore the idea
A new column meets an old file
First align the fields. Then decide which values meet the analytical contract.
4
rows, with coupon preserved
Older rows get a NULL coupon
union_by_name keeps the new field. It does not explain why a field is missing or validate the amount text.
3 + 1
numeric amounts + review row
Pending is not a numeric amount
TRY_CAST produces NULL for row 4. Keep its original text and review status so an aggregate cannot silently hide the problem.
Two exports, one evolving contract
early_export2 rows
The earlier export
Two columns; the coupon field did not yet exist.
| id | amount_text |
|---|---|
| 1 | 10.00 |
| 2 | 20.00 |
later_export2 rows
The later export
A new optional column and an amount that still needs review.
| id | amount_text | coupon |
|---|---|---|
| 3 | 30.50 | SAVE |
| 4 | pending | NULL |
The earlier rows have no coupon field. The later rows do, but one coupon is itself NULL. Once combined, those cases both appear as NULL; the source filename lets us recover which schema supplied a row.
Download the exact files used by the examples:
- CSV: earlier export · later export
- Parquet: earlier export · later export
The full setup writes these names into your DuckDB working directory. In the browser, they live in the example’s virtual filesystem. No upload or remote storage account is needed.
Ask for the union of column names
Keep both CSV schemas and retain file provenance
SELECT id::INTEGER AS id, amount_text, coupon,
parse_filename(filename) AS source_file
FROM read_csv(['schema-early.csv','schema-later.csv'],
union_by_name = true, all_varchar = true)
ORDER BY id;
| id | amount_text | coupon | source_file |
|---|---|---|---|
| 1 | 10.00 | NULL | schema-early.csv |
| 2 | 20.00 | NULL | schema-early.csv |
| 3 | 30.50 | SAVE | schema-later.csv |
| 4 | pending | NULL | schema-later.csv |
Scroll for more columns →
union_by_name asks the reader to combine the discovered column names. Missing fields become NULL. The explicit file list makes this small experiment reproducible; a glob can cover a larger export directory.
For CSV, all_varchar = true preserves the source fields as text instead of relying on type inference. We cast the known-good IDs deliberately. parse_filename(filename) reduces the reader’s provenance column to a readable basename.
This aligns fields, not business meanings. If the exporter renames amount_text to total, both names become separate columns. Decide whether the fields mean the same thing, alias each source to a common contract, and then combine them.
Use the same idea with Parquet
Combine the Parquet equivalents with a deliberate amount type
SELECT id, try_cast(amount_text AS DECIMAL(10,2)) AS amount,
coupon, parse_filename(filename) AS source_file
FROM read_parquet(['schema-early.parquet','schema-later.parquet'], union_by_name = true)
ORDER BY id;
| id | amount | coupon | source_file |
|---|---|---|---|
| 1 | 10 | NULL | schema-early.parquet |
| 2 | 20 | NULL | schema-early.parquet |
| 3 | 30.5 | SAVE | schema-later.parquet |
| 4 | NULL | NULL | schema-later.parquet |
Scroll for more columns →
Parquet already stores column types. Our fixtures deliberately store amount_text as VARCHAR in both files, so schema alignment succeeds. We then choose DECIMAL(10,2) as the analytical amount type.
try_cast converts pending to NULL instead of aborting the query. That makes the query run, but it also introduces a review obligation. A later sum would ignore this amount; it would not prove the source value was zero.
union_by_name is not a promise that arbitrary incompatible Parquet types can always be reconciled. For incompatible source contracts, read and normalize each source separately before combining the results.
Keep a conversion audit
Keep the original value beside the conversion decision
SELECT id::INTEGER AS id, amount_text,
try_cast(amount_text AS DECIMAL(10,2)) AS amount,
CASE WHEN amount_text IS NULL THEN 'missing'
WHEN try_cast(amount_text AS DECIMAL(10,2)) IS NULL THEN 'review'
ELSE 'accepted' END AS amount_status
FROM read_csv(['schema-early.csv','schema-later.csv'],
union_by_name = true, all_varchar = true)
ORDER BY id;
| id | amount_text | amount | amount_status |
|---|---|---|---|
| 1 | 10.00 | 10 | accepted |
| 2 | 20.00 | 20 | accepted |
| 3 | 30.50 | 30.5 | accepted |
| 4 | pending | NULL | review |
Scroll for more columns →
The original text remains beside the converted amount. ID 4 is marked review; the other three rows are accepted under this narrow amount rule. A missing amount would have its own status.
This is the same principle as our messy CSV lesson: preserve the evidence while making data usable. Avoid passing every row through an aggregate and hoping the total exposes a bad value.
Your turn · check your answer
Find values that failed numeric conversion
Return id and amount_text only for non-NULL source amounts that cannot become DECIMAL(10,2). Read both CSV files.
See the expected result
| id | amount_text |
|---|---|
| 4 | pending |
Preparing the editor…
Your result
Need a hint?
Use TRY_CAST to test conversion, and keep the original text for diagnosis.
Show a solution
SELECT id::INTEGER AS id, amount_text FROM read_csv(['schema-early.csv','schema-later.csv'], union_by_name=true, all_varchar=true) WHERE amount_text IS NOT NULL AND try_cast(amount_text AS DECIMAL(10,2)) IS NULL ORDER BY id;
A small contract goes a long way
For a recurring import, record the expected key, units, field meanings, and required columns. Adding an optional coupon is different from changing dollars to cents. Retain source provenance, audit rejected values, and check duplicate IDs before treating the combined dataset as one authoritative table.