[ADAP-939] [Bug] cannot reference permanent table from temporary table constraint when running incremental model with a foreign key

[aranke]

Most of the content below is copy-pasted from @amardatar within dbt-labs/dbt-core#8022

Is this a new bug in dbt-redshift?

• I believe this is a new bug in dbt-redshift
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

When running an incremental model with contract enforced and a foreign key, the model will fail on subsequent updates.

Expected Behavior

The update should succeed without an error.

Steps To Reproduce

First run a statement like:

create table if not exists numbers (n int not null primary key)

Then, given a schema.yml file:

version: 2

models:
  - name: test
    config:
      contract:
        enforced: true
      materialized: incremental
      on_schema_change: append_new_columns
      unique_key: n
    columns:
      - name: n
        data_type: integer
        constraints:
          - type: foreign_key
            expression: "numbers (n)"

And a SQL file:

select 1 as n

dbt will succeed on the first run, and subsequently fail with the error:

cannot reference permanent table from temporary table constraint

As per the error returned, the issue appears to be that while rendering a temporary table as part of the update, dbt will also render the foreign key constraint (which is not valid in the context of a temporary table). This leads to the above failure.

Relevant log output

No response

Environment

- OS: macOS Ventura 13.4.1
- Python: 3.11.4
- dbt: 1.5.2

[dbeatty10]

@aranke verified a fix for Postgres in dbt-labs/dbt-core#8768 and then created this backlog ticket for Redshift.

[jtcohen6]

I think the fix for dbt-postgres was too large in scope, leading to other issues:

• [CT-3224] [Regression] DBT 1.5.8 introduces breaking changes to incrementally materialized models dbt-core#8857 (comment)

[emmyoop]

The original fix (dbt-labs/dbt-core#8022) was modified slightly in dbt-labs/dbt-core#8889 to still enforce the contract.

[bdgeise]

Ran into this issue today and created a PR to address dbt-labs/dbt-redshift#927

[boxysean]

I have a customer who is interest in this being fixed. Anything we can do to move towards resolution? (e.g., @bdgeise's patch?)