Docs
sql_header

sql_header

Learn how to use sql_header and set_sql_header in Y42 to execute statements within the same session of your asset.

Overview

SQL headers allow you to manipulate the current session settings, such as roles and timezones, directly within your asset's run session.

You can define SQL headers directly in the SQL file's config block, within YAML configs, or in the dbt_project.yml

Example in a Snowflake SQL asset:

models/model_name.sql

_10
{{ config(
_10
sql_header="alter session set timezone = 'Europe/London';"
_10
) }}
_10
_10
select * from {{ ref('model_name') }}

Example in dbt_project.yml:

dbt_project.yml

_10
config-version: 2
_10
_10
models:
_10
+sql_header: "alter session set timezone = 'Europe/London';"

Using set_sql_header

The set_sql_header macro offers a convenient way to inject multi-line SQL statements without using the sql_header configuration key. It simplifies the inclusion of complex session manipulations directly within your asset definitions.

models/model_name.sql

_10
{% call set_sql_header(config) %}
_10
CREATE OR REPLACE FUNCTION ssn_mask(ssn STRING)
_10
RETURNS STRING
_10
LANGUAGE SQL
_10
AS '
_10
REGEXP_REPLACE(ssn, ''[0-9]'', ''X'') /* 123-45-6789 -> XXX-XX-XXXX */
_10
';
_10
{%- endcall %}
_10
_10
select ssn_mask(ssn) from {{ ref('model_name') }}