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

Common Use Case: Granting privileges

Post-hooks enable you to manage permissions using grant statements as soon as the model is materialized. This ensures that access controls are in place immediately upon build completion.

Example:


_10
{{ config(
_10
post_hook=[
_10
"GRANT SELECT ON TABLE {{ this }} TO ROLE analytics_users"
_10
]
_10
) }}

Y42 utilizes a two-stage Virtual Data Builds approach:

  • An internal layer containing all asset materializations.
  • A customer-facing layer that always reflects the most current, validated materialization, ensuring data reliability.

When privileges are applied using {{ this }}, they are first set on the internal layer and then automatically carried over to the customer-facing layer.

In BigQuery, privileges are referred to as roles, taking the form roles/service.roleName. For example, rather than granting select access on a model, you would assign roles/bigquery.dataViewer.

💡 Best Practice: Always apply the principle of least privilege by granting the minimal permissions necessary for users or groups.

PackagesJinja templating