DBT models: how to create variable from query and use it in the If statement

user_8543148 0
Viewed 1557

I have been trying to create variables in my SQL models (defined by Select statement, not static variables) with no success (trying to mimick Declare/Set statements from SQL stored procedures). I have been using call statement function to run my statements and then using Set to assign the result of my statements to a variable but whatever I do, I get errors that either variable is missing from config or some compilation errors.

Trying to run the following IF statement:

{%- call statement(name='get_last_snapshot_date', fetch_result=True) -%}
    Select ifnull(max(snapshot_date),'9999-09-09') from my_data_source
{%- endcall -%}

{%- set data_last_snapshot_date = load_result('get_last_snapshot_date') -%}
{%- set last_snapshot_date = data_last_snapshot_date['data'][0][0] -%}

{%- call statement(name='get_current_date', fetch_result=True) -%}
    Select current_date('GB')
{%- endcall -%}


{%- set data_get_current_date = load_result('get_current_date') -%}
{%- set current_snapshot_date = data_get_current_date['data'][0][0] -%}

{% if current_snapshot_date == last_snapshot_date: %}
    Delete From my_data_source
    Where snapshot_date = current_snapshot_date
{% endif %}

Gives me the following error:

[2021-11-08 16:48:53,433] {pod_launcher.py:149} INFO - Compilation Error in model inventory_hist_test (models/inventory_hist_test.sql)
[2021-11-08 16:48:53,433] {pod_launcher.py:149} INFO -  expected token ':', got '}'
[2021-11-08 16:48:53,433] {pod_launcher.py:149} INFO -    line 29
[2021-11-08 16:48:53,434] {pod_launcher.py:149} INFO -      {% if {{ current_snapshot_date }} == {{ last_snapshot_date }} %} (edited)
dbt
1 Answers

The error you're getting is because you're missing a : at the end of your if statement line.

Additionally, once you're in a Jinja block you shouldn't need {{ }}.

Try:

{% if current_snapshot_date == last_snapshot_date: %}

Additionally, I believe that run_query() is the better way to run these queries — the statement docs have the following warning:

While the statement and load_result setup works for now, we intend to improve this interface in the future. If you have questions or suggestions, please let us know in GitHub or on Slack.

user_46919200
Related