How to load SQL file into dbt macro using jinja include command?

Question

I have a dbt macro where I am trying to load a stand-alone sql file. I would then like to execute the SQL statement that I loaded by calling my macro. I am attempting to use jinja's include statement.

# test_sql_macro.sql

{% macro test_sql_macro() -%}
  {%- if execute -%}
    {%- call statement('do_stuff', fetch_result=True) -%}
      {% include 'select.sql' %}
    {%- endcall -%}

    {%- set call_results = load_result('do_stuff') -%}
    {{ log("Snowflake response: " ~ call_results, info=True) }}

    
  {% endif %}
{%- endmacro %}

# select.sql

SELECT * FROM MY_DB.MY_SCHEMA.MY_TABLE

I am running the macro with

dbt run-operation test_sql_macro --project-dir . --profiles-dir .

but I am getting an error

Encountered an error while running operation: Compilation Error in macro statement (macros/etc/statement.sql)
  no loader for this environment specified

After some search I came across setting template renderer in Python, however I am wondering if it is possible to set the renderer in the macro itself.

Is it possible to accomplish this in dbt?

Answer 1

dbt doesn't support jinja's include tag.

As a workaround, you can use a macro to "import" other sql statements into the jinja context.

For example:

Use a macro to hold arbitrary sql statements.

{% macro my_query() %}

    drop table if exists films cascade;

    create table films (
        film_id integer,
        title varchar
    );

    insert into films (film_id, title) values
        (1, 'The Godfather'),
        (2, 'The Wizard of Oz'),
        (3, 'Citizen Kane')
    ;

{% endmacro %}

Call the sql statement from another macro.

{% macro  my_macro() %}
    {# use a statement block #}
    {% call statement('films', fetch_result=True, auto_begin=True) %}
        {# sql query is wrapped in a macro  #}
        {{ my_query() }}
    {% endcall %}

    {# verify the results, print to stdout #}
    {% set results = run_query('SELECT * FROM films') %}
    {{ results.print_table() }}

{% endmacro %}

run the operation

$ dbt run-operation my_macro
 
Running with dbt=1.2.0
| film_id | title            |
| ------- | ---------------- |
|       1 | The Godfather    |
|       2 | The Wizard of Oz |
|       3 | Citizen Kane     |