set_sql_header and config.sql_header don't do anything.

[hanstwins]

When attempting to add a SQL header, nothing is generated. (for reference: https://docs.getdbt.com/reference/resource-configs/sql_header )

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

{% call set_sql_header(config) %}
declare @temp float = 1.345;
{%- endcall %}

select * from MyTable where MyColumn = @temp

this also doesn't work:

{{ 
    config(
        materialized='table',
        sql_header="""
declare @temp float = 1.345;
""") 
}}

select * from MyTable where MyColumn = @temp

The compiled SQL looks like:

select * from MyTable where MyColumn = @temp

[cody-scott]

Related dbt create statements.

https://github.com/dbt-labs/dbt-adapters/blob/706b7beaaa231da415d3dfbcdc5fb5b4d60a63c2/dbt/include/global_project/macros/relations/view/create.sql#L16

https://github.com/dbt-labs/dbt-adapters/blob/706b7beaaa231da415d3dfbcdc5fb5b4d60a63c2/dbt/include/global_project/macros/relations/table/create.sql#L22

I don't have time today, but i'll take a look next week at it.

[cody-scott]

So i'm not sure exactly what the way forward will be here.

How this was implemented is a bit of a block here with respect to table creation.

Basically its:

1. create table is called with some sql logic
2. create a temporary view to hold your model logic
3. insert into a table from the temporary view
4. drop the temporary view

The issue is that the model might have a declare and a argument to that variable @myvar, but SQL Server does not like it when its passed along to the create view statement.

Essentially it expects that the view creation doesn't have any declares and it's the first statement, which is not the case.

If I properly understand the reasoning here it is because dbt is a collection of .sql files, it proxies them into views to allow those to be inserted into a table, primarily because nested CTEs are not supported which would be the case any time someone creates a .sql file with a WITH ... AS statement .

As an example the file xyz.sql passes through this structure.

-- xyz.sql
{{ 
    config(
        materialized='table',
        sql_header="declare @myvar INT = 2;"
    ) 
}}
SELECT @myvar FROM {{ source('my', 'source')

into

-- this is where the @var will break

DECLARE @myvar INT = 2;
CREATE VIEW dbo.xyz_temporary AS 
SELECT @myvar FROM my.source

into

DECLARE @myvar INT = 2;
SELECT * INTO dbo.xyz FROM dbo.xyz_temporary

then finally

DROP VIEW IF EXISTS dbo.xyz_temporary

You cannot ignore the @myvar either in this case, as the create view will break too.

Its a bit of a catch in that the way a table is created from a .sql file means you need to create a temporary view to prevent it failing on CTEs, but that in turn breaks the ability to have a sql_header since it breaks the create view logic.

There is an underlying problem here around the create logic being proxied through a view. So if that could be solved to allow the creation of tables, without needing a view, then this in turn would probably work for tables. I cannot see it working properly for views in any case though.

[cody-scott]

Adding one more thought; i'm not sure you could even change the create table logic to fix this.

The behaviour of writing sql files that are compiled into your table makes it agnostic about whether its a view or a table, which means, at least on sql server, something that is written as...

-- my_cte_model.sql
WITH my_cte AS (
   SELECT @myvar as var_col
)
SELECT * FROM my_cte

still needs to pass through some sort of a create table type statement, be it SELECT * INTO or a CREATE TABLE then INSERT INTO. Problem again hinging on the sql statement is written to be closed as SELECT * FROM my_cte which doesn't really pass cleanly into how dbt processes it.

DECLARE @myvar INT = 1;

CREATE TABLE my_cte_model (
   var_col INT -- this is also needing to be defined somehow when going down the create table road.
)

INSERT INTO my_cte_model (var_col)
SELECT var_col FROM (
    WITH my_cte AS (
       SELECT @myvar as var_col
    )
    SELECT * FROM my_cte
)

Otherwise its

DECLARE @myvar INT = 1;
SELECT * INTO my_cte_model FROM (
    WITH my_cte AS (
       SELECT @myvar as var_col
    )
    SELECT * FROM my_cte
) cte_proxy

which breaks due to nested CTEs.

[cody-scott]

One work around would be to store that as its own model and join to that using a ref potentially. Certainly not as clean, but one potential option