Foreign Key Constraint Skips Execution Due to Deferred Job Metadata with Different target.name

[saicharan-c]

Thank you for addressing the runtime error in v1.0.4, which now skips constraint execution when a dependent object is missing during foreign key constraint creation in deferred run scenarios.

However, we observed an issue where foreign key constraints are skipped even when the dependent table exists in the data warehouse. This happens in cases where the target.name differs between the deferred job and the current execution, especially when the generate_schema_name macro uses target.name to differentiate schema names for environments like QA, dev, or prod.

Steps to Reproduce:

1. Trigger a CI job with target.name set to default and point the deferred job to dev or prod. (Refer: generate_schema_name logic image attached below)
2. Execute the CI run with command dbt build --select state:modified+, causing new objects to be built in a PR-prefixed schema (e.g., in Snowflake data warehouse).
3. Observe that the dbt_constraints package does not utilize existing objects in the deferred job environment(non-prefixed pr schema) for foreign key constraint execution when schemas are built differently using generate_schema_name logic(Refer: generate_schema_name logic image attached below).

Suggested Fix:
Update the logic in the create_constraints.sql file as follows:

{%- if pk_model.unique_id not in selected_resources -%}
    {%- set pk_table_relation = adapter.get_relation(
        database=(pk_model.config.database or pk_model.database),
        schema=(pk_model.config.schema or pk_model.schema),
        identifier=(pk_model.config.alias or pk_model.alias)) -%}
{%- else -%}
    {%- set pk_table_relation = adapter.get_relation(
        database=pk_model.database,
        schema=pk_model.schema,
        identifier=pk_model.alias) -%}
{%- endif -%}

This logic ensures that:

• If the required object is not found in the newly created PR schema, it checks for availability in the deferred job environment using the target.name.
• If found, it uses the existing object for foreign key constraint creation.
• If not found, it skips the foreign key constraint execution, consistent with current behavior.

Additional Context:
In environments using Snowflake, CI execution creates new objects in a schema prefixed with PR details. When dependent objects are missing in the PR schema, dbt should fall back to the deferred job environment based on target.name. The current logic in the dbt_constraints package does not handle this scenario correctly, leading to skipped foreign key constraints.

Let me know if further details or context are needed to resolve this issue. Thank you!

[sfc-gh-dflippo]

@saicharan-c, thanks, especially for the sample code since I haven't used deferred runs. I'll implement the code in a branch and let you know when it is ready to be tested.

[kokorin]

Another issue related to this #85