[bug] sql_header and contract enforcement causes adapter syntax erorrs

[rchui]

Using contract enforcement and setting a sql_header can cause the DuckDB adapter to encounter errors when it attempts to describe the structure of a query because the sql_header is included when it shouldn't be.

For example it attempts to run:

# dbt_project.yml
    +sql_header: |
        set pg_connection_limit = 3 ;
        set threads = 2 ;

DESCRIBE (set pg_connection_limit = 3 ;
set threads = 2 ;select * from (
        

select *, current_timestamp::timestamptz as updated_at

Which results in the error:

  Parser Error: syntax error at or near "set"

Instead the adapter should not be inserting sql_header into the top of the query it is attempting to describe. You can work around this with:

    +sql_header: |
      {% if execute %}
        set pg_connection_limit = 3 ;
        set threads = 2 ;
      {% endif %}

[rchui]

Correction: the workaround does not work. It just removes the sql_header.

[jwills]

if you want to execute SQL on a model before it's run, shouldn't that be done via a hook, not sql header? I thought the idea of sql header was for like comments that you wanted injected into the logs

[jwills]

also generically sorry for replying to all of these issues in bulk on a Monday, been busy at work for a bit

[rchui]

sql_header should allow you to inject arbitrary SQL above the create table/view statement. For example, it is useful for applying session settings for specific models. In PG we use this to sometimes give specific models more memory (work_mem) so that they run faster.

[guenp]

Thanks @jwills , using pre hooks seems like a pretty good workaround for now.

@rchui , I'll take a look at this, looks like according to the dbt docs this is indeed a bug -- are you OK with using pre hooks for now?

[rchui]

@guenp pre-hooks are fine for now