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
Edit this page