[Bug] Compiled post_hook code does not match actual post_hook

[jeremy-thomas-roc]

Is this a new bug in dbt-core?

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

Current Behavior

I have a post_hook to delete rows from an incremental table using a separate delete table. The config of the model looks like this:

{{
    config(
        materialized="incremental",
        on_schema_change="fail",
        unique_key=["cc_list_id", "photo_url"],
        incremental_strategy="delete+insert",
        cluster_keys=["cc_list_id"],
        post_hook="""
            {% if is_incremental() %}
                delete from {{ this }} t
                using {{ ref('mls_deletes') }} d
                where t.cc_list_id = d.cc_list_id
                    or expiration_date < current_timestamp()
            {% endif %}
        """,
    )
}}
...

When I look at the file in `target/run/.../my_model.sql, this is what I see:

begin
;

delete from <db>.<schema>.mls_photos
using <db>.<schema>.mls_photos__dbt_tmp
where
    (
        <db>.<schema>.mls_photos__dbt_tmp.cc_list_id
        = <db>.<schema>.mls_photos.cc_list_id
        and
        <db>.<schema>.mls_photos__dbt_tmp.photo_url
        = <db>.<schema>.mls_photos.photo_url

    )
;
    insert into <db>.<schema>.mls_photos ("CC_LIST_ID", "PHOTO_URL", "EXPIRATION_DATE", "MLS_INGESTION_DATE")
    (
        select "CC_LIST_ID", "PHOTO_URL", "EXPIRATION_DATE", "MLS_INGESTION_DATE"
        from <db>.<schema>.mls_photos__dbt_tmp
    );
commit
;

1. Why is my delete condition completely different?
2. Why is the post_hook running before the insert?

I ran dbt clean and tried, multiple times to look for a caching issue, and I even upgraded dbt versions from 1.7.4 to 1.8.8, no change. For some reason, dbt is compiling my delete statement entirely different than it is written in my file.

Expected Behavior

Compiled code should functionally match written code.

Steps To Reproduce

See code samples above. I confirmed that the incorrect delete statement was passed to Snowflake

Relevant log output

No response

Environment

- OS: MacOSX 15
- Python: 3.10..12
- dbt: 1.8.8

Which database adapter are you using with dbt?

snowflake

Additional Context

No response

[jeremy-thomas-roc]

This seems directly related to the unique_keys in my config. If I remove those, the delete statement disappears from my compiled file (????) but is running correctly in Snowflake.

I see how to fix my problem, but how and why does it work this way?

[jeremy-thomas-roc]

I realize now that unique_keys must generate a delete statement, which I had confused with my post_hook. My fault, although not well documented (that I could find) or intuitive. That is problematic for very large tables.

[dbeatty10]

I realize now that unique_keys must generate a delete statement, which I had confused with my post_hook. My fault, although not well documented (that I could find) or intuitive. That is problematic for very large tables.

Can you share more what you mean by "problematic for very large tables"? Does dbt-labs/dbt-adapters#150 describe what you are seeing?