Datagrunt 3.1.9: Fixing DuckDB CSV Quote Auto-Detection for Large Datasets

Large datasets can be unpredictable. When parsing files with hundreds of thousands of rows, structural quirks that only appear deep in the file can easily trip up automatic schema and delimiter detection engines.

In Datagrunt 3.1.9, we fixed a bug where the DuckDB query engine failed to parse large CSV files (such as the 280,000+ row electric vehicle population dataset) when quoted fields containing commas first appeared past the initial rows.

The Problem: DuckDB Auto-Detect Limits

When using the duckdb query engine, Datagrunt imports CSV files via DuckDB’s native read_csv function. To make parsing as fast as possible, DuckDB uses auto-detection to determine delimiters, headers, and quote characters.

However, DuckDB’s auto-detector only samples the initial chunk of rows to infer these settings. If your CSV starts with several rows of unquoted fields (e.g., standard integers or alphanumeric codes), and a quoted field containing a comma (like "POINT (-120.60199 46.59817)" or "Seattle, WA") only appears later in the dataset, DuckDB’s auto-detect will:

1. Fail to recognize " as a quote character.
2. Treat the comma inside the quotes as a column delimiter when it finally encounters it.
3. Throw a column count mismatch or parsing error (e.g., expecting 16 columns but finding 17).

Here is a simplified example of the failing structure:

col1,col2,col3
1,2,3
4,5,6
7,8,9
10,11,12
13,"hello, world",15   # <-- Commas inside quotes here would break parsing!

The Solution: Explicit Quote Injection via CSVDialect

Datagrunt already contains a robust Sniffer-based delimiter and dialect parser (CSVDialect). To solve this issue, we updated the DuckDBQueries component to dynamically retrieve the CSV’s quote character first using our Python-based sniffer and then pass it explicitly to the DuckDB query template.

Under the Hood

We added a cached quotechar property to retrieve the character from the file’s dialect:

@cached_property
def quotechar(self):
    """Get the quote character."""
    try:
        return CSVDialect(self.filepath).quotechar
    except Exception:
        return '"'

We then modified the read_csv SQL query templates to explicitly override DuckDB’s quote detection:

# Before
return f"""
    CREATE OR REPLACE TABLE {self.database_table_name} AS
    SELECT * FROM read_csv('{self.filepath}', auto_detect=true, ...);
"""

# After
return f"""
    CREATE OR REPLACE TABLE {self.database_table_name} AS
    SELECT * FROM read_csv('{self.filepath}', 
                            auto_detect=true, 
                            quote='{self.quotechar.replace("'", "''")}',
                            ...);
"""

This simple change guarantees that DuckDB always respects the correct quote boundary rules, preventing it from splitting columns on embedded commas regardless of how deep they are located in the file.

Performance and Verification

We tested this fix against the full 285,822-row Washington State Electric Vehicle Population dataset. The file imported cleanly and parsed successfully using the duckdb engine in under 0.20 seconds with perfect column alignment. All other CSV formats (standard, semicolon, and tab-separated) continue to parse successfully.

Upgrade Today

Datagrunt 3.1.9 is now building and releasing. You can pull the latest update from PyPI:

uv pip install --upgrade datagrunt