[Bug] state:modified detection incorrect when config() block generated by macro with dynamic sql_header

[pbotros]

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

IF:

• a model's content, including its config() block, is generated via a macro
• and that macro produces a model with set_sql_header generating a dynamic header based on config or the model
• and dbt is invoked with a different target with the --state flag,

then state:modified detection always returns that the model has changed. See "Steps to Reproduce" for a minimal reproducible example.

Expected Behavior

state:modified should not detect a change when no model sources or macros involved have changed.

Steps To Reproduce

$> cat models/foo.sql
{{ generate_foo() }}

$> cat macros/generate_foo.sql
{%- macro generate_foo() -%}

{{ config(
    materialized='table'
) }}

{% call set_sql_header(config) %}
declare some_var timestamp default (
    select MAX(some_col) from {{ this }}
);
{% endcall %}

select 1 as some_col

{%- endmacro -%}

$> rm -rf target{,_old} && dbt --target dev compile && mv target{,_old} && dbt --target prod ls --select state:modified --state target_old
17:53:01  Running with dbt=1.9.0
17:53:02  Registered adapter: bigquery=1.9.0
...
<project_name>.foo

This occurs even with state_modified_compare_more_unrendered_values set as true:

flags:
  # See https://docs.getdbt.com/reference/node-selection/state-comparison-caveats#false-positives
  state_modified_compare_more_unrendered_values: True 

The bug does not occur if set_sql_header in the macro has a constant body (e.g. select TIMESTAMP("1970-01-01")). The bug also does not occur if the model is not generated entirely via macro.

The bug can be avoided if a dummy config() statement is put in the model source:

$> cat models/foo_shim.sql
{{ config(materialized='whatever') }}
{{ generate_foo() }}

and foo_shim does not show as modified when running the same above statement.

Relevant log output

See above.

Environment

- OS: Mac 14.7.1
- Python: 3.12.1
- dbt: 1.9.0

Which database adapter are you using with dbt?

bigquery

Additional Context

No response

[dbeatty10]

Thanks such a nice write-up @pbotros 🏆

I see what you are saying about state:modified in this case.

I saw the same issue as you when I tried this with dbt-duckdb.

The workarounds also worked for me with one lone exception: the config shim trick didn't work for me. But maybe it does specifically for dbt-bigquery -- I didn't try it out since it didn't affect the key thing you are raising here.