[CT-2440] dbt show throws Database Error for models with sql_header required for valid query

[MichelleArk]

If a model is configured with a sql_header that is necessary to successfully run the query, dbt show currently fails because the compiled_node.compiled_code does not include the sql_header SQL.

Reproduction case (run against BQ, but not a BQ-specific issue)

-- models/my_model.sql
{% call set_sql_header(config) %}
  CREATE TEMPORARY FUNCTION yes_no_to_boolean(answer STRING)
  RETURNS BOOLEAN AS (
    CASE
    WHEN LOWER(answer) = 'yes' THEN True
    WHEN LOWER(answer) = 'no' THEN False
    ELSE NULL
    END
  );
{%- endcall %}

select yes_no_to_boolean("yes") as column

dbt show --select my_model --project-dir
19:00:05  Found 1 model, 0 tests, 0 snapshots, 0 analyses, 551 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics, 0 groups
19:00:05  
19:00:06  Concurrency: 1 threads (target='dev')
19:00:06  
19:00:08  BigQuery adapter: https://console.cloud.google.com/bigquery?project=dbt-test-env&j=bq:US:9802c6ea-f771-4d46-9da3-bf6f521bd1da&page=queryresults
19:00:08  Encountered an error:
Runtime Error
  Database Error in model dummydep (models2/dummydep.sql)
    Function not found: yes_no_to_boolean at [8:8]

Acceptance criteria:
Instead of directly executing compiled_node.compiled_code, template it into a multi-statement query that includes the sql_header (similar approach to the one proposed for #7390)

[iknox-fa]

BLG - 4/24 assigned to @aranke for further research

[aranke]

Doing some research, the SQL header does indeed run before the query, but is in a different scope.

So, all tests pass in PostgreSQL if I change models_second_model to:

{% call set_sql_header(config) %}
CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
        BEGIN
                RETURN i + 1;
        END;
$$ LANGUAGE plpgsql;
{%- endcall %}
select
    sample_num as col_one,
    sample_bool as col_two,
    increment(41) as answer
from {{ ref('sample_model') }}

It seems like this might only be a problem in BigQuery, so I'm inclined to close this issue for now, please let me know if this is inaccurate.

cc @jtcohen6