CODEWITHSUNDEEP
cloudsnowflake-cloud-data-platformdbt
Forsythe
Forsythe

Reputation: 31

How to pass variable to post_hook in dbt?

I want to drop multiple tables in snowflake. Instead of adding multiple commands to my dbt job and just pass the tables names, I used the TABLES view under the INFORMATION_SCHEMA of my database and found a way to construct my drop statements from there.

SELECT
    LISTAGG(CONCAT('"DROP TABLE IF EXISTS ', CONCAT(CONCAT_WS('.', TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME), '"')), ', ')
FROM "{{ var('db_name') }}"."INFORMATION_SCHEMA"."TABLES"
WHERE table_owner = 'TRANSFORM_ROLE'
AND table_schema = '{{ var("schema_name") }}'
AND table_name LIKE '%TEST%TABLE%'

Then, I inserted the statement above to my call statement in my dbt model:

{%- call statement('generate_drop_table_statement', fetch_result=True) -%}
SELECT
    LISTAGG(CONCAT('"DROP TABLE IF EXISTS ', CONCAT(CONCAT_WS('.', TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME), '"')), ', ')
FROM "{{ var('db_name') }}"."INFORMATION_SCHEMA"."TABLES"
WHERE table_owner = 'TRANSFORM_ROLE'
AND table_schema = '{{ var("schema_name") }}'
AND table_name LIKE '%TEST%TABLE%'
{%- endcall -%}

Then load the result:

{%- set drop_statements = load_result('generate_drop_table_statement')['data'][0][0] -%}

After this, I want to pass drop_statments to my post_hook in my config.

{{ config(
alias='DROP_STATEMENTS_TABLE',
materialized='table',
post_hook=['{{ drop_statements }}', "DROP TABLE IF EXISTS {{ var('db_name') }}.{{ var('schema_name') }}.FIRST_MODEL"]
) }}

The problem is, the query that was generated doesn't work while the other drop statement alongside the drop_statements variable does. I'm not sure if I'm calling the variable containing my drop statement incorrectly or this is not possible at all.

Whole code:

{%- call statement('generate_drop_table_statement', fetch_result=True) -%}
SELECT
    LISTAGG(CONCAT('"DROP TABLE IF EXISTS ', CONCAT(CONCAT_WS('.', TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME), '"')), ', ')
FROM "{{ var('db_name') }}"."INFORMATION_SCHEMA"."TABLES"
WHERE table_owner = 'TRANSFORM_ROLE'
AND table_schema = '{{ var("schema_name") }}'
AND table_name LIKE '%TEST%TABLE%'
{%- endcall -%}

{%- set drop_statements = load_result('generate_drop_table_statement')['data'][0][0] -%}
{{ log(drop_statements, info=True) }}

{{ config(
alias='DROP_STATEMENTS_TABLE',
materialized='table',
post_hook=['{{ drop_statements }}', "DROP TABLE IF EXISTS {{ var('db_name') }}.{{ var('schema_name') }}.FIRST_MODEL"]
) }}

with statements as (
SELECT
    '{{ drop_statements }}'
)

SELECT *
FROM statements

Appreciate your help. Thanks, in advance.

Update:

I read a post/question published in November 2021 that this is not yet supported. Apparently, pre and post hooks only accept strings.

Upvotes: 0

Views: 4911

Answers (2)

Beso
Beso

Reputation: 1216

You can pass like this:

{% set SCHEMA = 'PUBLIC' %}
{% set TEST_ID = '123' %}

{{
    config(
        materialized='table',
        pre_hook=["DELETE FROM " ~ SCHEMA ~ ".TEST_TABLE WHERE SRC_ID = '" ~ TEST_ID ~ "';"]
    )
}}

Upvotes: 0

Josh D.
Josh D.

Reputation: 1326

I think the issue is that you’ve used curlies ({{}}) when you’re already inside a jinja block. Try removing those and see if it works!

Upvotes: 0

Related Questions