Hooks

Hooks

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

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

models/model-name.sql

_10
{{ config(
_10
pre_hook="SQL-statement" | ["SQL-statement"],
_10
post_hook="SQL-statement" | ["SQL-statement"],
_10
) }}
_10
_10
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

Using {{ 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

_10
{{ config(
_10
post_hook=[
_10
"ALTER TABLE {{ this }} MODIFY COLUMN sensitive_column SET MASKING POLICY sensitive_data_mask"
_10
]
_10
) }}
_10
_10
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.