[CT-3260] [Regression] Missing contract enforcement checks on temporary tables

[emmyoop]

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

contract enforcement checks aren't happening for an incremental model during an incremental run on Postgres

Expected/Previous Behavior

Contracts should be enforced for temporary tables

Steps To Reproduce

select 'some string' as string_column

and:

models:
  - name: my_model
    config:
      materialized: incremental
      on_schema_change: append_new_columns
      contract: {enforced: true}
    columns:
      - name: string_column
        data_type: text

Run that. Then change the model SQL to select 123 as int_column, and run again. dbt should raise an error

18:31:56  Compilation Error in model my_model (models/my_model.sql)
18:31:56    This model has an enforced contract that failed.
18:31:56    Please ensure the name, data_type, and number of columns in your contract match the columns in your model's definition.
18:31:56
18:31:56    | column_name   | definition_type | contract_type | mismatch_reason       |
18:31:56    | ------------- | --------------- | ------------- | --------------------- |
18:31:56    | int_column    | INT             |               | missing in contract   |
18:31:56    | string_column |                 | TEXT          | missing in definition |

Relevant log output

Right now it runs without error. dbt will happily add a new column to the incremental model:

# select * from dbt_jcohen.my_model;
 string_column | int_column
---------------+------------
 some string   |
               |        123
(2 rows)

Environment

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

Which database adapter are you using with dbt?

No response

Additional Context

After looking a bit more closely at the change in #8768 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 (#8022 (comment)), 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 Fix #8022: Foreign key constraint on incremental model results in Database Error #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.