[CT-3224] [Regression] DBT 1.5.8 introduces breaking changes to incrementally materialized models

[epgui]

Is this a regression in a recent version of dbt-core?

• I believe this is a regression in dbt-core functionality
• I have searched the existing issues, and I could not find an existing issue for this regression

Current Behavior

Prior to 1.5.8 (<= 1.5.7), incremental models would accept implicit type casts such as VARCHAR(n) -> TEXT (or the other way around). In other words, prior to 1.5.8, if the model's DDL / contract specification was TEXT, then source data of type VARCHAR(n) would get incrementally added to the table without issue and without any need for explicitly casting (ie.: my_column::TEXT).

With dbt-cloud not allowing users to pin down a minor version of DBT, this caused models to suddenly stop working for us.

Expected/Previous Behavior

Prior to 1.5.8 (<= 1.5.7), incremental models would accept implicit type casts such as VARCHAR(n) -> TEXT.

Steps To Reproduce

See problem description in "Current behaviour". This should be trivially reproducible.

Relevant log output

20:42:22                     Source columns not in target: []
20:42:22                     Target columns not in source: []
20:42:22                     New column types: [{'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}, {'column_name': 'REDACTED', 'new_type': 'character varying(256)'}]

Environment

- OS: any (dbt-cloud)
- Python: any (dbt-cloud)
- dbt (working version): 1.5.7
- dbt (regression version): 1.5.8

Which database adapter are you using with dbt?

postgres

Additional Context

No response

[jtcohen6]

@epgui Thanks for opening! I am guessing that the root cause here must have been the fix to resolve this bug:

• [CT-2782] [Bug] Error - cannot reference permanent table from temporary table constraint when running incremental model with a foreign key #8022
• Fix #8022: Foreign key constraint on incremental model results in Database Error #8768

You could try "reverting" that fix, by adding an override of the create_table_as macro without this line changed, to see if it resolves the issue for you.

[jtcohen6]

After looking a bit more closely at that change, I believe it has a larger scope than the effect desired.

When we had previously talked about the scope of the fix for that bug (comment here), I'd said something like:

• Ideal: don't template any foreign-key constraints into create temporary table DDL statement, which aren't supported when creating temp tables
• Next-best: don't template any constraints into create temporary table DDL statement (the constraints will be applied on insert), but do template the column names + data types (so that type coercion still happens there)
• Acceptable [per , though it doesn't resolve this issue]: don't include column schema (names, data types, constraints) in create temporary table DDL statement — meaning the varchar <> text coercion won't happen

Whereas, I believe the change implemented in #8768 also disables the "pre-flight" contract check on an incremental builds of incremental models, including the "pre-flight" check in get_assert_columns_equivalent. If you have on_schema_change configured to evolve the schema accordingly, you'll end up with an output that hasn't been checked against the yaml contract. That's not right.

This change affects users of dbt-postgres, but I don't believe it affects any users of Redshift/Snowflake/BigQuery/Databricks.

[emmyoop]

@epgui I'm having trouble reproducing the exact behavior you're experiencing. Would you be able to provide a simple reproduction case similar to what Jeremy provided in this issue? That will help me track down exactly what's going on in your project. Thank you!