DuckDB Read the CSV. Which Records Reached the Table?

A CSV query that returns rows proves only that DuckDB produced a result. It does not prove that every declared source record reached a usable table. A reduced projection can avoid evaluating an unused typed column, a malformed record can be skipped when rejection storage is enabled, and a failed conversion can appear as NULL when TRY_CAST is used. Import acceptance therefore requires evidence from the source bytes through parsing, materialization, conversion, rejection handling, and final reconciliation.

The practical goal is a defensible decision: accept the import, quarantine a file, or hold and reimport it. The workflow below uses immutable synthetic fixtures, an approved schema, complete-column reads, persisted reject evidence, and an independent manifest of expected record identifiers and integer amounts. The examples are suitable for a disposable lab and should be rerun with the exact DuckDB and client versions used by the ingestion owner.

Understand What DuckDB Can Infer

DuckDB can discover CSV dialect, headers, and candidate types through its CSV auto-detection process. The default type-inference sample is 20,480 rows, and sample_size = -1 requests whole-file sampling. Sampling locations depend on the input: regular seekable files can be sampled at multiple positions, while non-seekable inputs such as compressed streams or standard input are sampled from the beginning. This makes inference useful for discovery, but not a committed schema contract.

Freeze the reader behavior that matters to acceptance. The DuckDB CSV reader options document settings such as delimiter, quote, escape, header handling, encoding, explicit column types, rejection storage, strict parsing, and schema combination. Record the exact values rather than relying on whichever defaults happen to be installed.

Freeze Source Files, Reader Options, and the Approved Schema

Treat every import run as a versioned evidence package. The source object, bytes, schema, and reader configuration must be fixed before the first query. If any of those inputs change, start a new run rather than overwriting the previous evidence.

Record the installed engine version as part of the run rather than inserting an illustrative version number into the contract:

SELECT version() AS duckdb_version;

For the examples below, a compact contract is id VARCHAR, amount BIGINT, note VARCHAR. The identifier and amount are required. The note can be empty. The independent manifest, not the imported table, defines the expected record count and the amount total.

Build Synthetic Files With Independently Known Records

Use a small immutable fixture set with no personal information. Each fixture should exercise one behavior, and its expected logical records should be authored separately from the import under test. Do not compute the expected denominator from DuckDB output.

Separate Typed-Value Defects From CSV Structure Defects

Do not mix an invalid numeric value with broken quoting in the same fixture. A typed-value defect should form a valid logical record that can be staged as text. A structural defect tests whether the parser can form the expected fields and record boundaries at all. Keeping these cases separate makes the evidence classifiable and the recovery decision reviewable.

Preserve Identifier and File-Schema Intent

The manifest should list each stable identifier, its expected integer amount, and the source file that owns the record. It should also state which file-level differences are permitted. Column reordering may be approved when names are authoritative; a missing required column is not automatically acceptable just because a reader can fill the value with NULL.

Materialize Every Contracted Column Before Acceptance

A preview query is not an acceptance scan. DuckDB documents that projection pushdown can avoid evaluating a faulty column when the query selects only other columns. The faulty CSV guidance demonstrates that a row with an invalid typed value may appear valid when the failing column is not projected. Materialize every required column over the full selected input.

These queries answer different questions:

-- Preview only: does not validate the wider contract.
SELECT id
FROM read_csv(
    'bad_type.csv',
    header = true,
    columns = {'id': 'VARCHAR', 'amount': 'BIGINT', 'note': 'VARCHAR'}
);

Run the Complete Projection

-- Acceptance scan: evaluates every contracted column.
CREATE OR REPLACE TABLE imported_typed AS
SELECT id, amount, note
FROM read_csv(
    'bad_type.csv',
    header = true,
    columns = {'id': 'VARCHAR', 'amount': 'BIGINT', 'note': 'VARCHAR'},
    store_rejects = true,
    rejects_limit = 0
);

A COUNT(*) result, a successful LIMIT preview, or a reduced column list does not establish that all required conversions were evaluated. Acceptance requires a persisted full projection, followed by checks against the independent manifest.

Separate Parse Admission From Type Conversion

An all-text staging read can help distinguish structural admission from business type conversion. It does not make malformed quoting valid. It simply avoids asking the CSV scan to convert accepted fields to numeric or date types before the staging table is available.

CREATE OR REPLACE TABLE staged_text AS
SELECT *
FROM read_csv(
    'bad_type.csv',
    header = true,
    all_varchar = true,
    store_rejects = true,
    rejects_limit = 0
);

After the structural scan, classify conversions explicitly. Freeze null-related reader settings such as allow_quoted_nulls and force_not_null because they affect whether source text becomes SQL NULL. DuckDB documents that TRY_CAST returns NULL when a conversion is not possible. That behavior is useful only when the audit preserves the original text and records whether the source value was empty, allowed to be null, or invalid for the target type.

CREATE OR REPLACE TABLE converted AS
SELECT
    id,
    amount AS amount_text,
    TRY_CAST(amount AS BIGINT) AS amount_value,
    amount IS NOT NULL
        AND TRIM(amount) <> ''
        AND TRY_CAST(amount AS BIGINT) IS NULL AS amount_conversion_failed,
    note
FROM staged_text;

A NULL in amount_value is not self-explanatory. The boolean classification distinguishes a failed conversion from a permitted empty source value. Required fields with invalid text remain unresolved and must not be silently counted as zero.

Capture Reject Evidence Before the Session Ends

With store_rejects = true, DuckDB creates temporary reject_scans and reject_errors tables. The CSV rejects-table documentation describes scan metadata, source coordinates, error types, the original CSV line, and the error message. Copy both tables to durable storage before closing the connection.

CREATE OR REPLACE TABLE saved_reject_scans AS
SELECT FROM reject_scans;

CREATE OR REPLACE TABLE saved_reject_errors AS
SELECT FROM reject_errors;

Count Rejected Records, Not Error Messages

One input record can create several entries in reject_errors. Therefore, the number of error rows is not a rejected-record count. Deduplicate with coordinates that identify the same source record in the pinned DuckDB version. A practical starting key is (scan_id, file_id, line_byte_position); keep the detailed errors for diagnosis.

SELECT COUNT(*) AS rejected_record_count
FROM (
    SELECT DISTINCT scan_id, file_id, line_byte_position
    FROM saved_reject_errors
) AS rejected_records;

Record rejects_limit in the run manifest. The documented value 0 means no logging limit. If a finite limit is used or the evidence is otherwise incomplete, mark rejection coverage as unresolved instead of extrapolating a total.

Reconcile Logical Records Without Counting Newlines

Physical lines are not a reliable denominator for CSV. A correctly quoted multiline field can span several lines while remaining one logical record. The fixture manifest supplies the logical population; the accepted table and reject evidence explain what happened to that population. If malformed quoting destroys reliable boundaries, quarantine the file rather than forcing an accepted-plus-rejected equality.

Use a keyed comparison, because equal row counts can hide one missing ID and one unexpected ID:

SELECT
    COALESCE(m.id, i.id) AS id,
    CASE
        WHEN m.id IS NULL THEN 'unexpected accepted ID'
        WHEN i.id IS NULL THEN 'missing accepted ID'
    END AS mismatch
FROM manifest AS m
FULL OUTER JOIN imported AS i USING (id)
WHERE m.id IS NULL OR i.id IS NULL;

The query should return zero rows for a complete match. Test the gate with a negative control in which one identifier is replaced while the total row count stays unchanged. The gate should still fail.

SELECT SUM(amount) AS expected_valid_amount
FROM manifest
WHERE expected_status = 'valid';

SELECT SUM(amount_value) AS accepted_valid_amount
FROM converted
WHERE amount_conversion_failed = false;

Do not invent a total for invalid numeric text. Report the amount as unresolved until an approved corrected source supplies the intended value.

Validate Each File Before Combining Schemas

When multiple files are read together, schema combination is reader behavior, not proof of business compatibility. DuckDB documents both position-based combination and schema combination by column name. By default, later files are aligned to the first file by position. With union_by_name = true, columns are aligned by name and missing fields are filled with NULL. The option can use more memory, which is another reason to record it explicitly rather than enabling it invisibly.

-- Position-based combination.
SELECT
FROM read_csv(['clean.csv', 'reordered.csv'], header = true);

-- Name-based combination.
SELECT
FROM read_csv(
    ['clean.csv', 'reordered.csv'],
    header = true,
    union_by_name = true
);

The safest bulk-load sequence is: validate each file against the approved names and required fields, record its digest, classify any allowed variation, and only then combine the accepted files.

Choose Accept, Quarantine, or Hold and Reimport

A successful command exit is not the release criterion. The decision must describe the population that consumers will receive and the evidence that supports it.

Partial acceptance is a business decision, not a parser default. If consumers are allowed to use a documented subset, publish the exact accepted population and its omissions. Do not silently redefine "complete" to mean "whatever remained after rejected rows were skipped."

Automate the Gate Without Weakening It

A repeatable ingestion job should fail closed. It must preserve evidence before the database connection ends and return a nonzero status when the contract is not satisfied.

1.        Verify immutable inputs. Compare each file name, byte size, digest, and expected object version with the run manifest.

2.        Verify the execution boundary. Record DuckDB and client versions plus every CSV reader option.

3.        Validate each file schema. Check required headers and approved variations before combining files.

4.        Run the complete projection. Materialize all required columns; do not substitute a count or preview.

5.        Persist reject tables immediately. Store scan metadata and detailed errors with the run ID and source digest.

6.        Classify conversions. Retain source text and distinguish legal nulls from failed casts.

7.        Reconcile IDs and valid totals. Compare with the independently authored manifest and execute negative controls.

8.        Emit a machine-readable decision. Keep parser, conversion, schema-contract, and unresolved-denominator failures as separate categories.

Archive the source fixtures, manifest, persisted reject tables, query output, accepted table, and machine-readable decision under the run ID.

Do not use ignore_errors merely to make the job green. Skipping is acceptable only when the rejection policy, persisted evidence, reconciliation denominator, and downstream disclosure are explicit.

Repair the Source or Contract Under Version Control

A failed import should create a new controlled input or an approved contract revision, not an in-place rewrite of the evidence.

•         Invalid values: obtain a corrected source or apply a reviewed correction process. Keep the original bytes and digest.

•         Structural defects: quarantine the original file and ask the producer to correct quoting, delimiters, or record layout.

•         Schema change: update the approved schema under version control with an owner decision, then rerun the full acceptance process.

•         New run: assign a new run ID and preserve the prior accepted rows, reject evidence, reconciliation output, and decision.

The verified import table can later feed downstream dbt and Snowflake transformations, but this acceptance boundary ends before those transformations. Passing the ingest gate means the released table has a documented source population, not that every downstream model is correct.

Publish Only the Population the Evidence Supports

Attach the acceptance evidence to the table or dataset release. Consumers should be able to determine what was expected, what was accepted, what was rejected, and what remains unresolved without reconstructing the ingestion session.

For readers developing the underlying SQL, analysis, and data-quality skills, review the published curriculum for the Refonte Learning Data Analytics Program. Confirm the current programme details on the official page before making an enrolment decision.