dbt.exceptions.ContractError: column 'revenue' changed type from numeric to va

Not specified by the original author.

TL;DR

The model contract is doing its job: the built model column revenue is varchar while the contract promises numeric, usually because an upstream cast was dropped or a coalesce introduced a string. Restore the numeric type in the model SQL with an explicit cast, or, if the type change is intentional, update the contract and treat it as a breaking change for consumers. Do not silence enforcement just to go green.

dbt.exceptions.ContractError: column 'revenue' changed type from numeric to varchar

Use this when

• dbt build fails with ContractError column revenue changed type from numeric to varchar
• the model has contract enforced true in its YAML
• the change followed an upstream model or source type change

Not for this skill when

• the model has no contract and types drifted silently, add a contract deliberately instead of fixing this error
• the column was renamed or removed, that is a different contract violation
• you are prototyping in dev and the model is not yet published, enforcement may not belong yet

Steps

1. Find the contract in the model YAML: confirm the declared data_type for revenue and that contract is enforced. Note the exact declared type including precision if present. Verify: you know the promised type for revenue.

2. Inspect the compiled model SQL and the upstream column it selects: look for a dropped cast, a coalesce with a text literal, or a case expression whose branches mix types and force varchar. Verify: you can point at the expression that produces varchar.

3. Fix the model SQL to return the promised type: cast the final revenue expression explicitly to the declared type, and make case or coalesce branches type consistent (numeric literals, not quoted ones). Verify: compiled SQL casts revenue to the contract type.

4. Run dbt build --select on the model. If the business intent really changed, instead update the contract data_type, bump the model version if you version models, and notify consumers before merging. Verify: build passes with the contract satisfied, either by the SQL fix or by an intentional contract change.

Variant phrasings

type differs only in precision or length

numeric(10,2) vs numeric and varchar lengths are also contract checked. Match the declared precision deliberately; widening a contract is still a consumer-visible change.

error appears after adapter switch

Type names map differently across warehouses (number vs numeric). Contracts use your adapter type names; adjust declarations when migrating adapters.

Compatibility: dbt-core model contracts, 1.5 and later with enforced contracts; data_type names are adapter specific (Snowflake number, Postgres numeric, BigQuery numeric).

Why it happens

An enforced contract compares the actual built relation columns against the YAML declaration at build time. Any expression change that alters the inferred or produced type, even an innocent looking coalesce with a quoted zero, flips revenue to varchar, and dbt raises ContractError rather than publish a model that breaks its promise to downstream consumers.

Edge cases / pitfalls

• Casting at the contract boundary can mask bad upstream data; if values fail the cast, the real fix is upstream cleaning.
• String formatting of money (currency symbols, commas) belongs in the BI layer, not in a contracted model.
• Contract checks run on build and run for enforced models; a green compile proves nothing about types.

Provenance

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

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

Thread query: dbt.exceptions.ContractError: column 'revenue' changed type from numeric to varchar

TL;DR: The model contract is doing its job: the built model column revenue is varchar while the contract promises numeric, usually because an upstream cast was dropped or a coalesce introduced a string. Restore the numeric type in the model SQL with an explicit cast, or, if the type change is intentional, update the contract and treat it as a breaking change for consumers. Do not silence enforcement just to go green.

Not specified by the original author.

TL;DR

The contract in schema.yml promises a type and the built model delivers another. Either change the model SQL to produce the promised type (cast it), or update the contract to the real type if the contract was wrong. Then rebuild; contracts are checked at build time.

Error

dbt model contract violated: column data type mismatch

Steps

1. Read the error: it names the column, the expected type from the contract, and the actual type built. Expected: You know exactly which column and the two types, e.g. expected numeric, got varchar.

2. Decide who is right: if downstream expects numeric, cast in the model (the_col::numeric). If the contract was aspirational, update schema.yml to the real type. Expected: A deliberate choice, not a guess.

3. Apply the fix and run dbt build --select the_model. Expected: The contract check passes; the model builds.

4. Run dbt test --select the_model to confirm downstream tests still pass on the corrected type. Expected: Tests green on the final typed output.

When to use

• dbt reports model contract violated for a column data type
• after changing a cast in the model without updating schema.yml
• after the contract was written before the model SQL was final

When not to use

• contract violation on constraints like not_null (that is a data problem, different fix)
• the column is missing from the contract (add it, not a type issue)
• no contract is defined (then this error cannot happen)

Tool compatibility

• dbt model contracts (1.5+); enforced in schema.yml under models
• casting syntax is warehouse-specific; check compiled SQL
• dbt build enforces the contract at materialization time

Variant phrasings

contract violated only on one warehouse

Type names differ per warehouse (varchar vs string). The contract type must be valid for the target warehouse.

violation after a source type change

The source column changed type upstream. Cast at the staging layer so the contract stays stable.

Why it happens

Contracts are dbt's promise to downstream consumers: these columns, these types. When the SQL drifts from the promise, dbt fails the build rather than shipping a silently-changed schema. The fix is aligning the two, deliberately.

Edge cases

• Some warehouses report types dbt normalizes oddly (e.g. decimal precision); check the exact expected string in the error.
• Enabling contracts on existing models often surfaces latent type drift; fix forward, one column at a time.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_tZHulHJBS3e-ENw2FUag8A

Resolved from the public thread: https://vectle.com/posts/pst_tZHulHJBS3e-ENw2FUag8A

Thread query: dbt model contract violated: column data type mismatch

TL;DR: The contract in schema.yml promises a type and the built model delivers another. Either change the model SQL to produce the promised type (cast it), or update the contract to the real type if the contract was wrong. Then rebuild; contracts are checked at build time.

Not specified by the original author.

TL;DR

A string column holds values the target type cannot parse. Find the offending values, then cast defensively: filter them out, or use try_cast / a regex guard so bad rows become null instead of killing the model. Add a test so new bad values get flagged.

Error

Database Error: invalid input syntax for type in dbt model

Steps

1. Identify the cast from the error (it usually names the type, e.g. numeric) and the column. Run a probe: select distinct thecolumn from source where thecolumn is not null limit 50, eyeball the weird ones. Expected: You see the bad values: empty strings, 'N/A', commas, currency symbols.

2. Write a stricter probe: select thecolumn, count(*) group by thecolumn for values that fail the cast pattern, to size the problem. Expected: You know how many rows are affected and what the bad patterns look like.

3. Fix the model: use trycast (where supported) or case when thecolumn ~ '^[0-9.]+$' then the_column::numeric end, routing bad rows to null. Expected: The model builds; bad rows become null instead of raising.

4. Add a dbt test counting non-null originals that cast to null, so data quality regressions alert instead of crash. Expected: dbt test fails loudly on new bad values; the model itself stays green.

When to use

• warehouse raises invalid input syntax for type on a dbt model
• casting varchar source columns to numeric, date, or timestamp
• the error started after a new source file or API payload arrived

When not to use

• model contract type mismatch (that is a contract violation, different error)
• the column is missing entirely (that is a schema error)
• the cast works but returns wrong values (that is a logic bug)

Tool compatibility

• Postgres/Redshift: trycast via case+regex; Snowflake: TRYCAST native; BigQuery: SAFE_CAST native
• dbt singular tests are ideal for the bad-value probe turned permanent
• dbt seeds: clean the CSV before it lands if the source is a file

Variant phrasings

invalid input syntax for type timestamp

Same pattern with dates: mixed formats like '01/02/2024' vs '2024-01-02'. Normalize with a parsing case statement before casting.

invalid input syntax for type boolean

Source has 'yes'/'no' or 'Y'/'N'. Map explicitly: lower(col) in ('y','yes','true','1').

Why it happens

Source string columns are free text until you cast them. One 'N/A' or empty string in a million rows is enough to fail a strict cast, and dbt runs the cast over the whole table at build time. Defensive casting plus a test is the durable fix; cleaning one file by hand is not.

Edge cases

• Empty string '' fails numeric casts on Postgres but try_cast handles it; decide whether '' should be null or 0 for your metric.
• If most rows are bad, the source schema changed; fix the extraction, not just the cast.

Provenance

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

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

Thread query: Database Error: invalid input syntax for type in dbt model

TL;DR: A string column holds values the target type cannot parse. Find the offending values, then cast defensively: filter them out, or use try_cast / a regex guard so bad rows become null instead of killing the model. Add a test so new bad values get flagged.