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

Reputation: 65

Fail dbt run based on Query Result

I am trying to run a procedure using Custom Macro. The Macro run the SQL(Call Statememnt) present in the Model sql file.

I am able to run the procedure but my objective is to capture the response of the procedure and fail the dbt run if response is 'Failure'. The procedure can return string 'Success' or 'Failure'. I am able to capture the return but not sure how to explicitly fail the run based on the return value.

Macro:

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

{% set query %}
use schema "{{ database }}"."{{ schema }}"
{% endset %}

{% do run_query(query) %}

{%- call statement('main',fetch_result=True) -%}
{{ sql }}
{%- endcall -%}  

{% if execute %}
{%- set QRY_RSLT = load_result('main')['data'][0][0] -%}
{{log (QRY_RSLT)}}
{% endif %}

{{ return({'relations': []}) }}

{%- endmaterialization %}

Model SQL:

{{ config(
    materialized='call_proc',
    database='TEST_DB',
    schema = 'TEST'
    )
}}

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

Checking the QRY_RSLT in logs I can see Success/Failure being printed. How to fail the model if 'Failure' is returned by Proc?

Upvotes: 1

Views: 1483

Answers (1)

tconbeer
tconbeer

Reputation: 5815

As I mentioned in my other answer, I don't think it's a good idea to use a custom materialization to call a stored procedure. You're better off using a macro for this.

But putting that aside, you can raise an exception using jinja:

{% if execute %}
{%- set QRY_RSLT = load_result('main')['data'][0][0] -%}
{% if QRY_RSLT == "failure" %}
    {{ exceptions.raise_compiler_error("Invalid `QRY_RESULT`. Got: " ~ QRY_RSLT) }}
{% endif %}
{% endif %}

Upvotes: 2

Related Questions