2

I am trying to put the result of a query in a variable but it doesn't work. I am not sure what to do so it returns 0 as expected. Any ideas? I am using dbt and jinja. With the below code the results_list variable is (Decimal('0'),))

MACRO

{% macro source_freshness(model, column_name) %}
{% set freshness_query %}
SELECT COUNT 0 AS count
{% endset %}

{% set results = run_query(freshness_query) %}

{% if execute %}
{% set results_list = results.columns[0].values() %}
{% else %}
{% set results_list = [] %}
{% endif %}

{{ return(results_list) }}

{% endmacro %}

call in a model:

{% set freshness_query_test = source_freshness(ref('model'),'date') %}

{% if count in freshness_query_test == 0 %}
do this
{% else %}
do that
{% endif %}

Thank you!

Maria
  • 363
  • 4
  • 13
  • Does this answer your question? [Hi, how do we define select statement as a variable in dbt?](https://stackoverflow.com/questions/64007239/hi-how-do-we-define-select-statement-as-a-variable-in-dbt) – sgdata Mar 01 '21 at 19:41
  • 1
    thank you so much. I have been trying both options in different ways but it doesn't seem to work. The call statement works but the thing is that I am using a macro which has a query with CTE. I've tried adding the whole query in the call statement but it doesn't work. I thought it was the same but I was wrong so I have updated the question. Thanks. – Maria Mar 02 '21 at 10:53
  • It seems like you are missing `{% set freshness_query %}` in your macro `{% macro source_freshness(model, column_name) %} {% set freshness_query %} SELECT COUNT 0 AS count {% endset %}` – Teddy Mar 03 '21 at 07:45
  • ouch! I think I missed it when changing the query to publish. I have found a way to do this though. See the answer. Thanks :) – Maria Mar 03 '21 at 08:50
  • Does this work for Superset too or only for dbt? – Pardeep Naik Apr 03 '23 at 19:51

1 Answers1

0

thanks for your help with this. I have not been able to find a direct answer but what I have done is to add the macro in a separate model, and then use the call statement logic in the shared answer Hi, how do we define select statement as a variable in dbt?

Maria
  • 363
  • 4
  • 13