Skip to main content

sql_header

models/<modelname>.sql
{{ config(
sql_header="<sql-statement>"
) }}

select ...


dbt_project.yml
config-version: 2

models:
<resource-path>:
+sql_header: <sql-statement>

Definition

An optional configuration to inject SQL above the create table as and create view as statements that dbt executes when building models and snapshots.

sql_headers can be set using the config, or by call-ing the set_sql_header macro (example below).

Comparison to pre-hooks

Pre-hooks also provide an opportunity to execute SQL before model creation, as a preceding query. In comparison, SQL in a sql_header is run in the same query as the create table|view as statement.

As a result, this makes it more useful for Snowflake session parameters and BigQuery Temporary UDFs.

Examples

Set Snowflake session parameters for a particular model

This uses the config block syntax:

models/my_model.sql
{{ config(
sql_header="alter session set timezone = 'Australia/Sydney';"
) }}

select * from {{ ref('other_model') }}

Set Snowflake session parameters for all models

dbt_project.yml
config-version: 2

models:
+sql_header: "alter session set timezone = 'Australia/Sydney';"

Create a BigQuery Temporary UDF

This example calls the set_sql_header macro. This macro is a convenience wrapper which you may choose to use if you have a multi-line SQL statement to inject. You do not need to use the sql_header configuration key in this case.

models/my_model.sql
-- Supply a SQL header:
{% 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 %}

-- Supply your model code:


select yes_no_to_boolean(yes_no) from {{ ref('other_model') }}
0