[CT-2994] [Bug] sql_header with jinja not parsed correctly with dbt show

[jeremyyeo]

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 you have some jinja in your sql_header, dbt show injects it verbatim, without doing any parsing - this results in invalid sql characters.

Expected Behavior

dbt show should be well behaved and sql_header should be parsed/resolved prior to sending the query to the datawarehouse.

Steps To Reproduce

1. Project setup:

# dbt_project.yml
name: my_dbt_project
profile: snowflake
config-version: 2
version: 1.0

# prettier-ignore
models:
  my_dbt_project:
    +materialized: table
    +sql_header: "{{ 'CALL SYSTEM$WAIT(' ~ var('wait_seconds', 0) ~ ');' }}"

-- models/foo.sql
select 1 id

2. With both dbt-core 1.5.latest (1.5.4) and 1.6.latest (1.6.0), do a show on foo:

$ dbt --debug show -s foo
23:43:46  Concurrency: 1 threads (target='default')
23:43:46  
23:43:46  Began running node model.my_dbt_project.foo
23:43:46  Re-using an available connection from the pool (formerly list_development_dbt_jyeo, now model.my_dbt_project.foo)
23:43:46  Began compiling node model.my_dbt_project.foo
23:43:46  Writing injected SQL for node "model.my_dbt_project.foo"
23:43:46  Timing info for model.my_dbt_project.foo (compile): 11:43:46.804486 => 11:43:46.817100
23:43:46  Began executing node model.my_dbt_project.foo
23:43:46  Using snowflake connection "model.my_dbt_project.foo"
23:43:46  On model.my_dbt_project.foo: /* {"app": "dbt", "dbt_version": "1.6.0", "profile_name": "snowflake", "target_name": "default", "node_id": "model.my_dbt_project.foo"} */
{{ 'CALL SYSTEM$WAIT(' ~ var('wait_seconds', 0) ~ ');' }}select 1 id
23:43:46  Opening a new connection, currently in state closed
23:43:48  Snowflake adapter: Snowflake query id: 01ae558f-0403-767c-000d-37831bf6791e
23:43:48  Snowflake adapter: Snowflake error: 001003 (42000): SQL compilation error:
syntax error line 1 at position 1 unexpected '{'.
23:43:48  Timing info for model.my_dbt_project.foo (execute): 11:43:46.820584 => 11:43:48.530728
23:43:48  On model.my_dbt_project.foo: Close
23:43:49  Database Error in model foo (models/foo.sql)
  001003 (42000): SQL compilation error:
  syntax error line 1 at position 1 unexpected '{'.
23:43:49  Finished running node model.my_dbt_project.foo
23:43:49  Connection 'master' was properly closed.
23:43:49  Connection 'model.my_dbt_project.foo' was properly closed.
23:43:49  Encountered an error:
Runtime Error
  Database Error in model foo (models/foo.sql)
    001003 (42000): SQL compilation error:
    syntax error line 1 at position 1 unexpected '{'.
23:43:49  Command `dbt show` failed at 11:43:49.175370 after 8.42 seconds
23:43:49  Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10e13b820>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x110836130>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x10fe41850>]}
23:43:49  Flushing usage events

Relevant log output

No response

Environment

- OS: macOS
- Python: 3.9.13
- dbt: 1.5.4 / 1.6.0

Which database adapter are you using with dbt?

snowflake

Additional Context

If we downgrade to dbt-core==1.5.0, show works as expected:

$ dbt --debug show -s foo
23:45:51  Concurrency: 1 threads (target='default')
23:45:51  
23:45:52  Began running node model.my_dbt_project.foo
23:45:52  Re-using an available connection from the pool (formerly list_development_dbt_jyeo, now model.my_dbt_project.foo)
23:45:52  Began compiling node model.my_dbt_project.foo
23:45:52  Writing injected SQL for node "model.my_dbt_project.foo"
23:45:52  Timing info for model.my_dbt_project.foo (compile): 11:45:52.017324 => 11:45:52.140537
23:45:52  Began executing node model.my_dbt_project.foo
23:45:52  Using snowflake connection "model.my_dbt_project.foo"
23:45:52  On model.my_dbt_project.foo: /* {"app": "dbt", "dbt_version": "1.5.0", "profile_name": "snowflake", "target_name": "default", "node_id": "model.my_dbt_project.foo"} */
select 1 id
23:45:52  Opening a new connection, currently in state closed
23:45:53  SQL status: SUCCESS 1 in 2.0 seconds
23:45:53  Timing info for model.my_dbt_project.foo (execute): 11:45:52.146470 => 11:45:53.773370
23:45:53  On model.my_dbt_project.foo: Close
23:45:54  Finished running node model.my_dbt_project.foo
23:45:54  Connection 'master' was properly closed.
23:45:54  Connection 'model.my_dbt_project.foo' was properly closed.
23:45:54  Command end result
23:45:54  Previewing node 'foo':
| ID |
| -- |
|  1 |

23:45:54  Command `dbt show` succeeded at 11:45:54.583669 after 9.60 seconds
23:45:54  Sending event: {'category': 'dbt', 'action': 'invocation', 'label': 'end', 'context': [<snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x105ba2ce0>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x105e98190>, <snowplow_tracker.self_describing_json.SelfDescribingJson object at 0x119358d00>]}
23:45:54  Flushing usage events

But that's simply because we don't inject sql_header as we are now doing with #7568

---

Additionally, in the dbt Cloud IDE, 1.5.latest works because it does show --inline with the file contents: