Logging Rejected Rows From a CSV Import for Review

An import that drops bad rows silently is a liability, and an import that stops on the first bad row is a nuisance. The useful behavior is in between: load what is good, keep what is not, and make the leftovers easy to read. The word that matters is "keep". A reject that exists only as a line in a log file saying "3 rows skipped" does not help anyone fix anything. A reject that carries the file, the line, the column, the reason and the original text can be fixed by a person in a minute and replayed. This article shows what a good reject record holds, how DuckDB produces one with two options, and the design choices around where it lives. It follows from the load stage in automating CSV imports into a database.

What a reject record must hold

A person fixing a rejected row needs to answer four questions: which file, where in it, what was wrong and what did it say. That makes five fields at minimum:

The raw text is the one teams leave out. They store parsed fields instead, and the parsed form has already lost the evidence: a stray quote that merged two fields into one, an extra delimiter that shifted every value, a trailing control character. The raw line shows it. Keep it verbatim, as text.

Add two more when you can: the time of the load, and a status for review (new, fixed, ignored). The status is what turns a pile of rejects into a queue.

A file with three bad rows

The sample is five orders, three of them broken in different ways: a total that is not a number, a date that is not a date, and a row with a field missing.

order_id,customer,placed_on,total
1001,Acme Ltd,2026-10-01,120.50
1002,Beta Corp,2026-10-01,eighty
1003,Cy Foods,not-a-date,15.00
1004,Delta Inc,2026-10-02,200.00
1005,Echo,2026-10-02

DuckDB reads CSV with a declared type per column, and two options tell it what to do with rows that do not fit: ignore_errors=true skips them, and store_rejects=true records them in tables you can query.

CREATE TABLE orders AS
SELECT * FROM read_csv('rej.csv',
  columns={'order_id':'INTEGER','customer':'VARCHAR','placed_on':'DATE','total':'DECIMAL(10,2)'},
  header=true, ignore_errors=true, store_rejects=true,
  rejects_table='rej_errors', rejects_scan='rej_scans');

SELECT * FROM orders ORDER BY 1;
SELECT line, column_idx, column_name, error_type, csv_line FROM rej_errors ORDER BY line;

order_id  customer    placed_on   total
1001      Acme Ltd    2026-10-01  120.50
1004      Delta Inc   2026-10-02  200.00

line  column_idx  column_name  error_type       csv_line
3     4           total        CAST             1002,Beta Corp,2026-10-01,eighty
4     3           placed_on    CAST             1003,Cy Foods,not-a-date,15.00
6     3           total        MISSING COLUMNS  1005,Echo,2026-10-02

Two rows loaded and three were rejected, each with its physical line number (the header is line 1, so order 1002 is on line 3), the column that failed, an error type and the original line. The rej_scans table, written alongside, records which file and which options produced the rejects, so the two join on a scan ID. That is nearly the record described above, with the file name coming from the scan table. Copy it to a permanent table with the load date and a status column:

CREATE TABLE IF NOT EXISTS import_rejects (
  source_file VARCHAR, line UBIGINT, column_name VARCHAR,
  error_type VARCHAR, csv_line VARCHAR, loaded_at TIMESTAMP, status VARCHAR DEFAULT 'new'
);
INSERT INTO import_rejects
SELECT 'rej.csv', line, column_name, error_type::VARCHAR, csv_line, now(), 'new' FROM rej_errors;

Copy the rejects into the permanent table straight after the load, in the same session and before another scan runs.

Without DuckDB

Any loader can produce the same record. In Python, catch the exception per row and write the row's text and line to a file, as in the importer in automating CSV imports into a database, where the reject file had a line, a reason and the fields. For the raw text, read the file as lines and parse each line separately, or use the reader's line_num and re-read. PostgreSQL's COPY has an ON_ERROR ignore option in recent versions that skips rows with conversion errors and reports a count, but no reject table (see the COPY reference), so load into a text-typed staging table and validate with SQL, inserting failures into your reject table with a WHERE that mirrors the checks (skipping bad rows during PostgreSQL imports).

MySQL's LOAD DATA records warnings you can read with SHOW WARNINGS right after the statement, capped by the max_error_count setting, so a large file can lose most of its warnings. Stage into text columns there too.

Where rejects live

A file next to the data file is the simplest: orders-2026-10-02.csv.rejects.csv, readable in a spreadsheet by whoever owns the feed. A table is better when you want a queue, a status and counts over time. Often both: the table for tracking, the file for sending back to the provider.

Sending rejects to the provider closes the loop. A file of rows with the reason column, in the same format as the original plus the reason, lets them fix the data at the source, and a feed whose rejects are returned each day tends to improve. A weekly summary of reject counts by reason makes the conversation specific.

The reject rate is a metric

Count rejects per file and plot them. A steady two per day is a source with a known quirk, and you may decide to fix them in the pipeline. A sudden jump to 40 percent means the format changed, a column shifted or the encoding differs, and the right response is to stop and look, not to keep loading the 60 percent that parsed. A threshold does this: if rejects exceed 1 percent of rows (the number is yours), fail the file and quarantine it. Handling a CSV provider who changes columns without warning covers the header-level version of the same alarm.

Personal data in rejects

A reject table copies rows that failed, and those rows contain whatever the source contained: names, emails, addresses. It is a second copy of personal data, usually with looser access than the main tables because "it is just errors". Apply the same controls: the same roles, a retention period (30 days is common) and deletion when the row is fixed or ignored. Do not log rejected rows into a general application log, where retention is long and access is broad. The GDPR angle is in handling CSV files full of customer data under GDPR.

When rejects are the wrong answer

Rejecting rows is right for independent records. It is wrong when the file is a whole: a snapshot where a missing row means a deletion, a ledger where every line must balance, a batch whose totals are checked against a footer. A partial load of those corrupts meaning, and the right behavior is to reject the file and load nothing. Decide per feed, and write it in the runbook.

It is also wrong to let rejects accumulate unread. A table of rejects that nobody reviews is a bin, and the first time anyone looks it is a year old and impossible to act on. Assign an owner, set a weekly review and delete what is stale. The DuckDB behavior used above is documented in reading faulty CSV files.