Configure SQL before model execution with sql headers

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

sql_header(s) can be applied to all types of Y42 assets, including models, seeds, and snapshots.

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

{{ config(
  sql_header="alter session set timezone = 'Europe/London';"
) }}

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

Example in dbt_project.yml:

dbt_project.yml

config-version: 2

models:
  +sql_header: "alter session set timezone = 'Europe/London';"

While pre-hooks allow for SQL execution before the asset creation, sql_header run as part of the same session as the query that creates a table or view.

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

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

select ssn_mask(ssn) from {{ ref('model_name') }}

BigQuery partitioning and clustering Preview changes locally