Skip to main content

Object Group Hooks

info

This feature is only available when SOLE's LIFECYCLE_ACTION is set to AGGREGATE, Plan-ALL, or APPLY-ALL.

info

Hooks support key-pair authentication and use the same authentication mode, variables, and credentials as SOLE. For more information, see the SOLE key-pair authentication guide on how to implement key-pair authentication for your organization.

SOLE (Snowflake Object Lifecycle Engine) supports the usage of pre- and post-hooks. Hooks perform actions before and after lifecycle management of the respective #resource-group-hooks.

The pre-hooks and post-hooks are executed for each run of SOLE. You can specify the name of the Snowflake warehouse SOLE uses to run hooks by defining the variable SNOWFLAKE_WAREHOUSE in your environment configuration. If it is not defined, an internal default warehouse is used. To use the same warehouse for hooks and grant management, assign SNOWFLAKE_WAREHOUSE the same value as DATAOPS_SOLE_WAREHOUSE used by grant management.

SOLE has four different object group hierarchies:

  • Account-Level
  • Databases
  • Database-Level
  • Grants

Account-Level and Databases belong to the same group as per Snowflake. In SOLE, they are executed separately so that databases can be executed by themselves. For hooks, though, they are treated as a single group.

Each group has pre- and post-hooks. This allows you to define setup and teardown actions for each group for more control over the lifecycle of objects.

Below is an illustration of the execution per object group:

Execution flow of pre- and post-hooks for each object group !!shadow!!

warning

Hooks do not support object name resolution and must always be fully qualified (database.schema.table/view). You can use template rendering to inject the current database's name with {{ env.DATAOPS_DATABASE }} though.

Example​

You can define hooks in any existing or new YAML file parsed by SOLE or SOLE for Data Products, typically any file in the directory specified in the parameter CONFIGURATION_DIR.

SOLE and SOLE for Data Product Configuration
account_level_hooks:
  pre_hooks:
    - command: "ALTER ACCOUNT SET RESOURCE_MONITOR = USAGE_MONITOR;"
      environment: snowflake
  post_hooks:
    - command: "$CI_PROJECT_DIR/dataops/sole_hooks/account_level_post_hook.sql"
      environment: snowflake
    - command: whoami
      environment: bash

database_level_hooks:
  post_hooks:
    - command: "CALL add_table_to_share('SHAREFOO','DATAOPS_DEMO5_DEV','AW_PERSON','ADDRESS')"
      environment: snowflake
    - command: terraform validate
      environment: shell

grants_hooks:
  pre_hooks:
    - command: "$CI_PROJECT_DIR/dataops/sole_hooks/grant_pre_hook.sql"
      environment: snowflake
  post_hooks:
    - command: "$CI_PROJECT_DIR/dataops/sole_hooks/grant_post_hook.sql"
      environment: snowflake
    - command: "$CI_PROJECT_DIR/dataops/sole_hooks/grant_post_hook.py"
      environment: python
    - command: [ls, -l, /usr/bin, "|", grep, pip]
      environment: shell
    - command: ls -l /usr/bin | grep pip
      environment: shell
info

See project configuration for more information about hooks configuration in SOLE for Data Products.

Multiple hooks in a single Snowflake session​

By default, SOLE executes each hook in a separate Snowflake session. However, you can configure SOLE to execute all the hooks from the group in a shared Snowflake session by setting the auto_commit property to False:

SOLE and SOLE for Data Product Configuration
grants_hooks:
  pre_hooks:
    - command: "INSERT INTO {{ env.DATABASE_NAME_PREFIX }}_TRANSACTION_DB_{{ env.DATAOPS_ENV_NAME }}.SCHEMA_1.TABLE_1 (COLUMN_1, COLUMN_2) VALUES ('value1', 1);"
      environment: snowflake

  post_hooks:
    - environment: snowflake
      commands:
        - "TRUNCATE TABLE {{ env.DATABASE_NAME_PREFIX }}_TRANSACTION_DB_{{ env.DATAOPS_ENV_NAME }}.SCHEMA_1.TABLE_1;"
        - "INSERT INTO {{ env.DATABASE_NAME_PREFIX }}_TRANSACTION_DB_{{ env.DATAOPS_ENV_NAME }}.SCHEMA_1.TABLE_1 (COLUMN_1, COLUMN_2) VALUES ('value2', 2);"
        - "INSERT INTO {{ env.DATABASE_NAME_PREFIX }}_TRANSACTION_DB_{{ env.DATAOPS_ENV_NAME }}.SCHEMA_1.TABLE_1 (COLUMN_1, COLUMN_2) VALUES ('value3', 'value4');"
      auto_commit: False
Last updated on