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.

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.