Search code examples
pythonamazon-web-servicesjinja2dbt

how to get the number from a variable in jinja dbt

I have this code, it takes max value from the table

{%- call statement('my_statement', fetch_result=True) -%}
      select max(CAST(updated AS bigint)) as val from table_1
{%- endcall -%}
{%- set my_var = load_result('my_statement')['data'] -%}

{{ return(my_var) }}

the return variable I have

[(Decimal('1655275144000'),)]

I am trying to get only the number from this variable. When I write like this, I have an error enter image description here

{%- set my_var = load_result('my_statement')['data'][0][0] -%}

although in python with importing the library it works great enter image description here

How can I get this number in dbt with jinja

Solution

  • There could be a few different things going on here. Your code looks okay, and I couldn't reproduce your exact error. There are a few ways to go awry:

    1. If you are using load_result inside of a model, or in a macro that gets called by a model, you need to wrap that in an {% if execute %} block; otherwise it can create parsing/compilation errors. See the docs.
    2. I want to be clear that you're evaluating {%- set my_var = load_result('my_statement')['data'][0][0] -%} in the same macro or model where you define my_statement?

    Finally, I would recommend using the newer run_query macro, instead of the call statement... load_result pattern, since it's a little less confusing. See the docs for run_query