sql_header

sql_header does not support Jinja or macros like ref and source

Unlike pre-hooks and post-hooks, macros like ref, source, and references like {{ this }}, aren't supported.

The primary function of set_sql_header is fairly limited. It's intended to:

• Create UDFs
• Set script variables (BigQuery)
• Set temporary session parameters (Snowflake)

Models

models/<modelname>.sql

{{ config(
  sql_header="<sql-statement>"
) }}

select ...

dbt_project.yml

config-version: 2

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

Seeds

This config is not implemented for seeds

Snapshots

snapshots/<filename>.sql

{% snapshot snapshot_name %}

{{ config(
  sql_header="<sql-statement>"
) }}

select ...

{% endsnapshot %}

dbt_project.yml

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

Property file

Setting sql_header in the config of a generic data test is available starting in dbt Core v1.12. Enable the require_sql_header_in_test_configs flag to use sql_header in properties.yml for generic data tests.

Here's an example of a model-level configuration:

models/properties.yml

models:
  - name: orders
    data_tests:
      - unique:
          name: unique_orders_order_id
          arguments:
            column_name: order_id
          config:
            sql_header: "-- SQL_HEADER_TEST_MARKER"

You can also use sql_header for column-level data tests:

models/properties.yml

models:
  - name: orders
    columns:
      - name: order_id
        data_tests:
          - not_null:
              name: not_null_orders_order_id
              config:
                sql_header: "-- SQL_HEADER_TEST_MARKER"

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).

(Applies to dbt v1.12 and later)

You can also set sql_header in the config of a generic data test at the model or column level in your properties.yml file. Use sql_header to define SQL that should run before the test executes (for example, to create temporary functions, set session parameters, or declare variables required by the test query). dbt runs this SQL before executing the test.

Enable the require_sql_header_in_test_configs flag to use sql_header for data tests. For more information, refer to Data test configurations.

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

Was this page helpful?

Privacy policyCreate a GitHub issue

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.