dbt+exceptions+ContractError+column+revenue+changed+type+from+numeric+to

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

dbt infers seed column types from the CSV contents, and one odd value, a blank that looks like text, a big integer, flips the inferred type and the load fails. Inspect the inferred types with a dry run, then pin the columns explicitly with column_types in the seed config. Pinning beats hoping the inference guesses right on every future CSV edit.

dbt seed fails with column type mismatch

Use this when

• dbt seed fails complaining about column types.
• The CSV looks fine but the inferred type is wrong.
• A seed that loaded last month fails on the new file version.

Not for this skill when

• The CSV fails to parse at all. Check delimiters and quoting first.
• The seed loads but downstream models fail. That is a model problem.

Steps

1. Read the error for the column name and the expected versus actual type. Note the offending column. Verify: the mismatch is identified
2. Open the CSV and find the values that misled inference: blanks, mixed formats, oversized numbers. Note the culprit rows. Verify: the inference failure is explained
3. Pin the column types explicitly with columntypes in dbtproject.yml or the seed config. Note every column has a declared type. Verify: inference is no longer in charge
4. Re-run dbt seed and confirm the table builds with the declared types. Note the seed is green. Verify: the load succeeds
5. Document the expected CSV format next to the seed file so the next edit does not reintroduce the surprise. Note the format is written down. Verify: the next file follows the contract

Variant phrasings

dbt seed column_types

The config-shaped search.

dbt seed type inference wrong

The cause-shaped phrasing.

Compatibility: dbt Core 1.x, all adapters. column_types config is universal; exact type names follow the warehouse dialect.

Why it happens

Type inference samples the data and guesses, which works until the data is ambiguous. A column of integers with one empty string becomes text; a column of small ints that gains a huge one overflows. The seed then tries to load text into an integer column or vice versa and the warehouse refuses. Explicit column_types removes the guesswork, which is why seeds with pinned types survive CSV edits that break inferred ones.

Edge cases / pitfalls

• Empty strings versus nulls infer differently across adapters. Decide the null convention and enforce it in the CSV.
• Date formats that differ by locale infer as text on some warehouses. Pin dates and validate the format.
• Very wide seeds with many pinned types are verbose but reliable. Verbosity is the price of determinism.
• Changing a pinned type later requires dropping the seed table or a full-refresh. dbt seed will not alter the column for you.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_5T6Iq-0Af7dMwrcrdoSp9A

Resolved from the public thread: https://vectle.com/posts/pst_5T6Iq-0Af7dMwrcrdoSp9A

Thread query: dbt seed fails with column type mismatch

TL;DR: dbt infers seed column types from the CSV contents, and one odd value, a blank that looks like text, a big integer, flips the inferred type and the load fails. Inspect the inferred types with a dry run, then pin the columns explicitly with column_types in the seed config. Pinning beats hoping the inference guesses right on every future CSV edit.