DuckDB read_csv auto-detect turns yes/no columns into BOOLEAN: "Conversion Error: Could not convert string '' to BOOL" on models_index.csv splat column
Symptom: querying the HackPrinceton 2026 Matterport models_index.csv directly with DuckDB (SELECT count(*) FROM 'models_index.csv' WHERE splat != '') fails with ConversionException: Conversion Error: Could not convert string '' to BOOL, pointing at the '' literal. The same CSV in pandas shows splat as a plain string column, so the error looks impossible.
Two wrong assumptions stack up here. First, the column is splat, not has_splat. Second, it holds neither URLs nor empty strings: the values are the literal words yes (232 rows) and no (14,712 rows). The same holds for defurnished and ai_summary.
Cause: DuckDB's CSV sniffer (seen with duckdb 2.0.0) treats yes/no as boolean literals. DESCRIBE SELECT * FROM 'models_index.csv' reports splat BOOLEAN, defurnished BOOLEAN, ai_summary BOOLEAN (and costar_property_id/homes_listing_id/drone_photos as BIGINT, created as DATE). Comparing a BOOLEAN to '' makes DuckDB cast '' to BOOL, which fails. WHERE splat = 'yes' happens to work because 'yes' casts to true, which hides the problem until someone writes a different predicate.
Fix, pick one:
-- use it as the boolean DuckDB inferred
SELECT count(*) FROM 'models_index.csv' WHERE splat; -- 232
-- or keep it as text
SELECT splat, count(*) FROM read_csv('models_index.csv', types={'splat':'VARCHAR'}) GROUP BY 1;
SELECT splat, count(*) FROM read_csv('models_index.csv', all_varchar=true) GROUP BY 1;CAST(splat AS VARCHAR) also works, but it gives back 'true'/'false', not 'yes'/'no', so string comparisons against 'yes' then silently return 0 rows.
Verify with DESCRIBE before writing predicates, and keep pandas and DuckDB code consistent: in pandas the same filter is df[df.splat == 'yes'], in DuckDB with default sniffing it is WHERE splat.