Hi, how do we define select statement as a variable in dbt?

Question

Hi I am trying to define a select statement in a set variable in dbt, can any one suggest how to set sql query as a variable in dbt and how to access those variables in below CTEs?

Answer 1

You can use call statement and get the result in a variable with load_result

Here is an example of retrieving only one field from the select statement:

{%- call statement('my_statement', fetch_result=True) -%}
      SELECT my_field FROM my_table
{%- endcall -%}

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

Then you can use {{ my_var }}

You can play with ['data'][0][0] depending on the rows and columns that your select returns

Answer 2

You could consider embedding your SQL statement in a macro and call that macro in your models.

{% macro get_data() %}
    {% set query %}

        select
            column_a_boolean,
            column_b_varchar
        from my_table

    {% endset %}

    {% set results = run_query(query) %}
    {# execute is a Jinja variable that returns True when dbt is in "execute" mode i.e. True when running dbt run but False during dbt compile. #}
    {% if execute %}
    {% set results_list = results.rows %}
    {% else %}
    {% set results_list = [] %}
    {% endif %}

    {{ return(results_list) }}

{% endmacro %}

You can then use the above macro in a model. For example, in a model below, we UNION records returned by the macro if a value in a column column_a_boolean is equal true.

{% for record in get_data() %}
    {% if record.column_a_boolean == true %}
        {% if not loop.first %}
            UNION ALL
        {% endif %}
            SELECT
                record.column_b_varchar
    {% endif -%}
{% endfor %}