Seed models parse CSVs before applying declared column types, so TEXT values lose leading zeros and integer columns become floats #6135

Description

A SEED model's CSV is parsed with pandas type inference, and the types declared in columns (...) are only applied afterwards in SeedModel.render_seed. Anything inference throws away can't be recovered by that later cast:

• A TEXT column holding identifiers like 00123 (ZIP codes, account numbers) is inferred as an integer and loaded as '123'.
• If that column also contains an empty value, pandas infers float64 and the value is loaded as '123.0'.
• An integer column that contains an empty value is inferred as float64, so values above 2^53 lose precision: 9007199254740993 is loaded as 9007199254740992.

Quoting the values in the CSV doesn't help. csv_settings has no dtype option either; one was considered in #2418 but not added. So the only workaround is to avoid declared TEXT/INT columns for such data.

Reproduction

Tested on main (cc8d8b0, 0.236.3+3) with DuckDB.

seeds/ids.csv:

code,n
00123,1
45958,
,3

models/ids.sql:

MODEL (
  name demo.ids,
  kind SEED (path '../seeds/ids.csv'),
  columns (code TEXT, n INT)
);

Rendered query:

SELECT CAST(code AS TEXT) AS code, CAST(n AS INT) AS n
FROM (VALUES ('123.0', 1.0), ('45958.0', NULL), (NULL, 3.0)) AS t(code, n)

demo.ids after sqlmesh plan:

   code    n
  123.0    1
45958.0 <NA>
   None    3

Without the empty value in code, the first row is loaded as '123' and pandas also emits:

FutureWarning: Setting an item of incompatible dtype is deprecated and will raise in a future error of pandas.
Value '['123']' has dtype incompatible with int64, please explicitly cast to a compatible dtype first.
  df.loc[:, string_columns] = df[string_columns].mask(

With columns (id BIGINT) and a column containing 9007199254740993 plus an empty value, the loaded value is 9007199254740992.

dbt seeds are affected the same way. With column_types: {zip: varchar}, a zip value of 00501 is loaded as '501', whereas dbt itself keeps '00501'. Integer columns with empty values, which agate infers as int, are read as floats.

Expected behavior

Declared column types should be respected when the CSV is parsed:

• TEXT values are kept exactly as written: '00123', '45958', NULL.
• Integer columns with empty values stay integers: 1, NULL, 3.

Related

• Fix: improve csv seed file type inference #2418: chunked seed type inference. Adding dtype to csv_settings was discussed there.
• Databricks CSV seed load coercing NULL booleans to False #5589 / Fix seed boolean null handling #5870: a similar inference problem with NULL booleans in seeds.