Hooks

Run SQL statements before or after a model is built.

Overview

Hooks allow users to run a SQL statements before (pre-hook) or after (post-hook) a model is executed. As part of hooks, you can also invoke macros.

Hooks can either be embedded in the YAML files or in the model's .sql file config block as following:

dbt_project.yml

models:
  <resource-path>:
    +pre-hook: SQL-statement | [SQL-statement]
    +post-hook: SQL-statement | [SQL-statement]

models/model-name.sql

{{ config(
    pre_hook="SQL-statement" | ["SQL-statement"],
    post_hook="SQL-statement" | ["SQL-statement"],
) }}
select ...

Adding brackets ([SQL-statements]) allows you to append and run multiple SQL statements as part of your hook.

Error handling

In Y42, if a pre-hook or a post-hook fails, the entire job fails. This differs from dbt, where a job can succeed even if a hook fails. This setup in Y42 ensures that all parts of your job, including hooks, are successfully completed before marking the job as successful. This strict approach provides an added level of security and reliability, especially in critical operations such as data masking.

Leveraging `{{ this }}` in hooks

The {{ this }} syntax refers to the current table being manipulated. It's used in pre-hooks and post-hooks for direct table operations.

For instance, to apply a masking policy to a column in a model:

models/orders.sql

{{ config(
  post_hook=[
    "ALTER TABLE {{ this }} MODIFY COLUMN sensitive_column SET MASKING POLICY sensitive_data_mask"
  ]
) }}
SELECT * FROM raw_table

In this case, {{ this }} substitutes the name of the newly created table in the ALTER TABLE command, applying the masking policy to the sensitive_column.

Exposures Macros

Hooks

Overview

Error handling

Leveraging {{ this }} in hooks

Leveraging `{{ this }}` in hooks