Validate Every CSV Column Before Trusting a DuckDB Import

A CSV import can succeed while a value that matters to your application never gets checked. With DuckDB, two useful conveniences explain why: automatic type inference can choose a permissive type, and projection pushdown can avoid converting columns your query does not use.

This tutorial builds a small, auditable import boundary. It fixes the expected schema, materializes every column, preserves rejected records, and checks that the record counts reconcile. The accompanying code was executed with DuckDB 1.5.6 and Python 3.12.14. All data is synthetic.

Disclosure: This is an AI-produced TajerStudio technical-writing sample, with executable examples and recorded tests. It is not a commissioned client case study.

Start with a deliberately awkward file

Save the following as fixtures/orders.csv, or use the supplied file:

order_id,amount,ordered_on
A100,19.95,2026-09-01
A101,not-a-number,2026-09-02
A102,7.50,not-a-date
A103,broken,also-broken
A104,,2026-09-05
A105,-3.00,2026-09-06
A106,0.00,2026-09-07

There are seven data records. Three contain values that cannot satisfy the intended numeric or date types. A103 contains two such values. Another record has a missing amount, while two have non-positive amounts. Keep those categories separate: successful conversion does not establish that an order meets your business rules.

The package pins the dependency:

python -m venv .venv
. .venv/bin/activate
python -m pip install -r requirements.txt
python sample.py
python -m unittest -v

The requirements file contains duckdb==1.5.6. Run the commands from the extracted package directory. The tutorial uses only local files after installation; it needs no database server, credentials, extensions, or paid service.

See what inference actually promises

Run this SQL through a DuckDB connection:

DESCRIBE SELECT *
FROM read_csv('fixtures/orders.csv', header=true, sample_size=-1);

In the recorded run, all three columns were inferred as VARCHAR. The strings not-a-number and not-a-date therefore remained ordinary text. Reading the whole file for inference did not enforce the desired contract.

DuckDB documents that inference tests candidate types and retains VARCHAR as a fallback. Sampling the entire file can improve the description of what is present, but your application still needs to state what is allowed. Use inference to explore an unfamiliar export; establish explicit types before accepting a recurring feed. CSV auto-detection documentation

Check the columns your query skips

An explicit schema is necessary here, but query shape still matters:

SELECT order_id
FROM read_csv(
    'fixtures/orders.csv',
    auto_detect=false, header=true, delim=',',
    columns={
        'order_id':'VARCHAR',
        'amount':'DECIMAL(10,2)',
        'ordered_on':'DATE'
    }
);

This returned every ID, A100 through A106. The recorded EXPLAIN plan showed only order_id under the CSV scan's projections. DuckDB did not need to produce either problematic typed column to answer that question. Its documentation explicitly describes this projection behavior. Reading faulty CSV files

The tests also verified two tempting shortcuts: COUNT(*) returned seven, and adding store_rejects=true to the ID-only query produced no reject entries. Neither result demonstrated that every amount and date had been converted. Conversely, selecting all columns without reject handling raised a conversion error on A101.

Materialize a complete typed staging table

Create a staging table before running downstream projections or aggregates:

CREATE TEMP TABLE typed_orders AS
SELECT * FROM read_csv(
    'fixtures/orders.csv',
    auto_detect=false, header=true,
    delim=',', quote='"', escape='"',
    columns={
        'order_id':'VARCHAR',
        'amount':'DECIMAL(10,2)',
        'ordered_on':'DATE'
    },
    dateformat='%Y-%m-%d', nullstr='',
    null_padding=false, strict_mode=true,
    parallel=false, store_rejects=true, rejects_limit=0
);

This statement requests every typed column and completes the materialization. The single-threaded scan makes this tiny demonstration easier to inspect; it is not a performance recommendation. Disabling padding also avoids turning absent fields into supplied values.

For this fixture, the table contains A100, A104, A105, and A106. The other three records appear in the reject diagnostics. store_rejects=true skips faulty records while recording diagnostics; it does not repair them. A zero reject limit requests uncapped diagnostics. DuckDB exposes both scan metadata and error details in temporary tables. Reject tables and options

The companion script first makes a byte-for-byte source copy and records its SHA-256 digest. It copies typed rows, temporary diagnostics, and business-rule failures into Python objects before closing the connection, then writes the JSON reports. Preserve the source alongside these reports: converted values and diagnostic text are not a replacement for the original bytes.

Reconcile records rather than error entries

Inspect how many source records were rejected:

SELECT count(*) AS rejected_records
FROM (
    SELECT DISTINCT scan_id, file_id, line_byte_position
    FROM reject_errors
);

The answer is three. COUNT(*) directly on reject_errors returns four, because A103 generated an amount error and a date error. The verified accounting is:

• Seven input records equal four typed records plus three rejected records
• Four diagnostic entries describe those three rejected records

The key includes the scan and file identifiers as well as the record's starting byte position. Counting distinct CSV text would merge two identical bad records. A dedicated duplicate-record fixture proves that case. Keep each run's diagnostics isolated, as the example does with a fresh connection.

For the independent input count, the script uses Python's CSV reader with the declared dialect and strict parsing. It counts records after the header, rather than counting newline characters. A quoted field can contain a newline; the multiline fixture contains three data records across five physical data lines. Python CSV reader documentation

Do not continue when the accounting fails. Setting the diagnostic limit to one makes the test fixture fail reconciliation: four typed records plus one logged reject cannot explain seven inputs. The script also refuses an unclosed quote, an unexpected header, and blank records during preflight. Those inputs need an explicit handling policy rather than an optimistic count.

Apply the rules conversion cannot express

The example's paid-order rule requires a present, positive amount, a present ID, and a present order date. Query the materialized table for failures:

SELECT order_id
FROM typed_orders
WHERE order_id IS NULL OR amount IS NULL
   OR amount <= 0 OR ordered_on IS NULL
ORDER BY order_id;

This returns A104, A105, and A106. A104's empty amount remains NULL; it is never filled with zero. The three business-invalid records stay in the typed output and receive a separate report. Only A100 satisfies this demonstration's rules, and the batch is flagged as not ready for downstream use.

There are further boundaries. The rounding fixture shows that DECIMAL(10,2) accepts 1.234 as 1.23; this schema does not enforce a two-decimal input spelling. An out-of-range amount is rejected. If exact lexical precision matters, retain text and add a separate precision check before approving the conversion. Duplicate IDs, unexpected currencies, and acceptable date ranges also require rules specific to your feed.

This is a tested pattern for small local UTF-8 inputs, not a universal CSV repair tool. It does not cover every encoding, compressed input, malformed quoting pattern, or large parallel scan. When adapting it, preserve the originals, rerun the negative fixtures on your pinned version, and require complete accounting before promoting a batch.