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') }}