CODEWITHSUNDEEP
snowflake-cloud-data-platformdbt
Ankit Srivastava
Ankit Srivastava

Reputation: 65

Use Schema as pre hook in dbt not working

Trying to call a stored procedure using a Model. Call is made using a custom macro as below,

{% materialization call_proc, adapter='snowflake' -%}

{%- call statement('main') -%}
{{ sql }}
{%- endcall -%}  

{%- endmaterialization %}

Model definition:

{{ config(
    materialized='call_proc',
    database='TEST_DB',
    schema = 'TEST',
    pre_hook = "use schema {{ database }}.{{ schema }};"
    )
}}

call  "{{ database }}"."{{ schema }}".TEST_PROC('SAMPLE');

Procedure is a Snowflake Procedure created with "Execute as Caller" property. In snowflake history i can see this is called with db/schema. Internally the procedure calls another procedure which doesn't use fully qualified name. Ideally since it a Execute as Caller, the internal procedure should run using the DB/SCHEMA context set. FOr that i have specifically put in USE DB.SCHEMA as pre hook but seems it is not working.

Any ideas? I dont want to use fully qualified names in the call statement inside procedure body or even pass as parameter.

Upvotes: 1

Views: 2384

Answers (1)

tconbeer
tconbeer

Reputation: 5815

dbt uses a different session to execute pre-hooks and model code; use ... only applies to the current session, which is why the pre-hook has no effect on the behavior of your stored procedure.

Generally, calling stored procedures like this from dbt is a hack: dbt runs should be idempotent, so if you are inserting or deleting data, etc., then you're breaking the dbt paradigm, which is why stored procedures aren't supported more directly.

That said, if you have to use one, I would wrap it in a macro and then call that macro in either an on-run-start hook, a pre-hook, or using dbt's run-operation command. Models are intended to map 1:1 with database relations, and hacking a materialization macro like you have is really not ideal.

You can also use the target context variable to retrieve database and schema from your profile:

-- in macros/call_test_proc.sql

{% macro call_test_proc(my_arg) %}
begin;
use database {{ target.database }};
use schema {{ target.schema }};
call  TEST_PROC('{{ my_arg }}');
commit;
{% endmacro %}

Then from the command line:

dbt run-operation call_test_proc --args '{"my_arg": "SAMPLE"}' --target dev

Or as a hook:

# in dbt_project.yml
on-run-start: "{{ call_test_proc('SAMPLE') }}"

Upvotes: 0

Related Questions