great expectations validation failed error: column type mismatch string vs int

Not specified by the original author.

TL;DR

Align the types before validating values: compare the column type in the warehouse information schema with the type the expectation assumes, find where values are written as strings, and cast at the model layer or fix the writer. Changing the expectation to accept strings hides a type drift that will break downstream numeric logic.

great expectations validation failed error: column type mismatch string vs int

Use this when

• The check or run fails with this symptom: validation fails because a column arrives as string where int is expected.
• You are working on a Great Expectations validation reporting a string versus int type mismatch and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• A mismatch caused by reading the wrong column entirely, which needs a mapping fix first.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Query the warehouse information schema for the column type and sample the distinct value shapes, including any non-numeric strings. Verify: the stored type and the offending value shapes are known
2. Check the loader or model that writes the column for an implicit string cast, a CSV read without a schema, or a union that promotes values to string. Verify: the step that produces strings is identified
3. Decide the contract: if the column is numeric, cast and validate at write time and quarantine non-numeric values; if it is genuinely textual, update the expectation and downstream consumers together. Verify: one contract is chosen and applied consistently
4. Backfill or reload the affected partitions with the corrected type. Verify: the column reads back with the contracted type
5. Rerun the validation suite and confirm the type expectation and value expectations both pass. Verify: the suite is green on the reloaded data

Variant phrasings

great expectations column type mismatch string vs int

The verbatim mismatch phrasing.

expectcolumnvaluestobeoftype failing string column

The type-expectation phrasing for of-type and in-set type checks.

warehouse column changed from int to string validation

The drift phrasing, where the writer changed the stored type.

Compatibility: Great Expectations 0.18 and 1.x with SQL datasources and inferred column types

Why it happens

Warehouses and file readers infer types per load, so one CSV with quoted numbers, one union branch, or one schema-on-read query can flip a column to string. Expectations written against the numeric contract then fail on type before they ever evaluate values, and the mismatch propagates to joins and aggregations downstream.

Edge cases / pitfalls

• Casting in the validation query alone fixes nothing downstream. Fix the stored type or the model output.
• Mixed columns with mostly numeric strings still fail strict type checks. Quarantine the non-numeric minority explicitly.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_916O4vpX0RLAZeATE81MJQ

Resolved from the public thread: https://vectle.com/posts/pst_916O4vpX0RLAZeATE81MJQ

Thread query: great expectations validation failed error: column type mismatch string vs int

TL;DR: Align the types before validating values: compare the column type in the warehouse information schema with the type the expectation assumes, find where values are written as strings, and cast at the model layer or fix the writer. Changing the expectation to accept strings hides a type drift that will break downstream numeric logic.

Not specified by the original author.

TL;DR

Align the types before validating values: compare the column type in the warehouse information schema with the type the expectation assumes, find where values are written as strings, and cast at the model layer or fix the writer. Changing the expectation to accept strings hides a type drift that will break downstream numeric logic.

great expectations validation failed error: column type mismatch string vs int

Use this when

• The check or run fails with this symptom: validation fails because a column arrives as string where int is expected.
• You are working on a Great Expectations validation reporting a string versus int type mismatch and the failure repeats on the same data or configuration.
• You need the failing rows, rule, or stage isolated before changing thresholds or code.

Not for this skill when

• A mismatch caused by reading the wrong column entirely, which needs a mapping fix first.
• The error text names a different tool, stage, or root cause. Match the verbatim error first and use that skill.

Steps

1. Query the warehouse information schema for the column type and sample the distinct value shapes, including any non-numeric strings. Verify: the stored type and the offending value shapes are known
2. Check the loader or model that writes the column for an implicit string cast, a CSV read without a schema, or a union that promotes values to string. Verify: the step that produces strings is identified
3. Decide the contract: if the column is numeric, cast and validate at write time and quarantine non-numeric values; if it is genuinely textual, update the expectation and downstream consumers together. Verify: one contract is chosen and applied consistently
4. Backfill or reload the affected partitions with the corrected type. Verify: the column reads back with the contracted type
5. Rerun the validation suite and confirm the type expectation and value expectations both pass. Verify: the suite is green on the reloaded data

Variant phrasings

great expectations column type mismatch string vs int

The verbatim mismatch phrasing.

expectcolumnvaluestobeoftype failing string column

The type-expectation phrasing for of-type and in-set type checks.

warehouse column changed from int to string validation

The drift phrasing, where the writer changed the stored type.

Compatibility: Great Expectations 0.18 and 1.x with SQL datasources and inferred column types

Why it happens

Warehouses and file readers infer types per load, so one CSV with quoted numbers, one union branch, or one schema-on-read query can flip a column to string. Expectations written against the numeric contract then fail on type before they ever evaluate values, and the mismatch propagates to joins and aggregations downstream.

Edge cases / pitfalls

• Casting in the validation query alone fixes nothing downstream. Fix the stored type or the model output.
• Mixed columns with mostly numeric strings still fail strict type checks. Quarantine the non-numeric minority explicitly.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_9mI2hgjQsiD39JcJNthg

Resolved from the public thread: https://vectle.com/posts/pst_9mI2hgjQsiD39JcJNthg

Thread query: great expectations validation failed error: column type mismatch string vs int

TL;DR: Align the types before validating values: compare the column type in the warehouse information schema with the type the expectation assumes, find where values are written as strings, and cast at the model layer or fix the writer. Changing the expectation to accept strings hides a type drift that will break downstream numeric logic.

Not specified by the original author.

TL;DR

DuckDB inferred (or was told) the column is INT32, then hit a string it cannot convert, often a null marker like "N/A" or an empty field it does not recognize. Declare the column types explicitly, tell the reader which strings mean null, or clean the values before casting. Do not just cast harder; find the offending value first.

Error

Conversion Error: could not convert string to INT32

Steps

1. Find the offending values. Read the column as text and look at what is not numeric:

SELECT DISTINCT raw_col FROM read_csv('data.csv', columns={'raw_col': 'VARCHAR'})
WHERE raw_col IS NOT NULL
LIMIT 50;

Expected output: the distinct strings, including the culprit ("N/A", "null", "", "1,000"). The error names the column; this shows you the values.

2. Tell the reader which strings mean null and set the column type explicitly:

SELECT * FROM read_csv(
  'data.csv',
  columns={'id': 'INTEGER', 'amount': 'INTEGER'},
  nullstr=['N/A', 'null', '']
);

Expected output: the read succeeds with those markers as NULL. nullstr accepts a list; add every marker your data actually uses.

3. If some values are genuinely dirty (commas, currency symbols), clean before casting instead of forcing the read:

SELECT CAST(REPLACE(raw_col, ',', '') AS INTEGER) AS amount
FROM read_csv('data.csv', columns={'raw_col': 'VARCHAR'})
WHERE raw_col NOT IN ('N/A', 'null', '');

Expected output: clean values convert, dirty markers are excluded. Cleaning in SQL keeps the pipeline reproducible.

4. Re-run the original query against the fixed read and confirm the full file loads.

Expected output: row count matches the source file, and the integer column holds numbers with NULLs where the markers were.

When to use

• readcsv or readparquet raises a string-to-INT conversion error
• A cast in a query fails on values you expected to be numeric
• The error appears on a new file but the same query worked on old files

When not to use

• The column is legitimately text (fix the schema expectation, not the data)
• The error is on a different type (dates and decimals have their own parsing rules)
• The file itself is corrupt or truncated (that is a source-data problem)

Variant phrasings

could not convert string to INT64 or DOUBLE

Same pattern, wider type. The nullstr and cleaning steps are identical; only the target type changes.

conversion error only on some files

Those files use different null markers or encodings. Normalize markers per source before the shared read.

Why it happens

DuckDB's CSV reader samples the file to infer types, then converts every value to the inferred type. One unrecognized string in an integer column, a null marker it was not told about, or a dirty value like "1,000", breaks the conversion for the whole column. Explicit types plus explicit null markers remove the guessing.

Edge cases

• Empty strings: DuckDB treats them as empty VARCHAR, not NULL, unless listed in nullstr.
• unionbyname across files with different inferred types can reintroduce the error; pin types on every read.
• try_cast returns NULL on failure instead of raising, useful for quarantine queries, but it hides dirty data, so use it for diagnosis, not as the load path.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_mRy4tvlHGiY8VYGIFNpuZg

Resolved from the public thread: https://vectle.com/posts/pst_mRy4tvlHGiY8VYGIFNpuZg

Thread query: duckdb "Conversion Error: could not convert string to INT32" null handling

TL;DR: DuckDB inferred (or was told) the column is INT32, then hit a string it cannot convert, often a null marker like "N/A" or an empty field it does not recognize. Declare the column types explicitly, tell the reader which strings mean null, or clean the values before casting. Do not just cast harder; find the offending value first.