Should CSV/JSONL scan fail loudly on rows that don't match the inferred schema?

[eugenegujing]

The problem

Texera's CSV/JSONL scan source operators infer each column's type from only a sample of the first ~100 rows (INFER_READ_LIMIT). At read time, if a later row holds a value that doesn't parse as the inferred type, the operator currently silently drops the entire row — no error, no warning, no skipped-row count, nothing in the console. Every downstream count, aggregate, join, or model then runs on a silently truncated dataset, and the user has no way to know data was lost.

Concrete example: a CSV whose age column is integer for the first 100 rows (inferred INTEGER) but holds 55.5 at row 150. Row 150 is dropped, so a 403-row file scans as 402 rows — silently. There's also an inconsistency: an empty cell is kept as null (the row survives), but a cell that fails to parse takes the whole row down. The more destructive path is the quiet one.

Issue: #6279 — Proposed fix (PR): #6323

Why I'm raising it here

This is a behavior change, so I'd like committer/community input before it lands. The proposed fix converts the silent drop into a loud, actionable failure:

Before:  row fails to parse  ->  caught -> dropped        ->  no signal (silent data loss)
After:   row fails to parse  ->  clear console error       ->  run stops
                                  (names row, value, column, expected type)

Example of the new message:

Row 150: value '55.5' in column 'age' cannot be read as INTEGER. Column types were inferred from the first 100 rows of the file, and this value does not match. Fix the value in the file, or clean the data before scanning.

The trade-off: workflows that currently run to completion on slightly-dirty data will start failing on the first bad row. That's the intended improvement (loud > silent), but it will be visible to existing users, so I'd rather get consensus than surprise anyone.

What I found looking at other platforms

Before settling on "fail loudly", I did a small investigation into how comparable data systems handle the same situation (a sampled/inferred column type vs. a later value that doesn't match). The short version: not one of them silently drops whole rows — Texera's current behavior is the outlier. So I modeled the proposed fix on the common default across these systems.

System	Default on a mismatch	Silent?
Spark (FAILFAST), Polars, DuckDB	error / abort	no — fails loudly
PostgreSQL 17 COPY ... ON_ERROR ignore	skip the row and report a skipped-row count	no — surfaced
DuckDB ignore_errors + rejects table	skip, but log each rejected row (line, column, reason)	no — surfaced
Texera (today)	drop the whole row	yes — no signal

So the options on the table, all with real precedent:

Option	Behavior	Precedent
Fail loudly (what the PR does)	abort on the first unparsable row with an actionable error	Spark FAILFAST, Polars, DuckDB
Skip + surface a count	keep dropping, but report the skipped-row count / a warning	PostgreSQL 17, DuckDB rejects table

The inference sampling itself (first 100 rows) is not the bug and isn't changed here; the bug is that a mismatch at read time is handled silently.

Questions / where I'd love feedback

1. If we fail by default, do we want an opt-in escape hatch — e.g. a "skip malformed rows and report how many" mode, and/or a "sample all rows for inference" option (à la Polars infer_schema_length=None / DuckDB sample_size=-1) — so users who knowingly have heterogeneous data can still proceed?
2. Apply uniformly across all scan variants (CSV, JSONL, ParallelCSV, csvOld) in one change? (The current PR does.)

Very welcome to discuss — all suggestions and alternative directions are appreciated, and I'm happy to revise the PR (#6323) based on whatever consensus we reach here. Thanks!

[kunwp1]

The changes you are trying to make is related to this issue: #1181. Maybe @Yicong-Huang might have a context?