jinja2dbt

Multiple Select Statements with Jinja and DBT

I am working on a project in which we need to generate a table of data that is based on a condition, I have experience with dbt but not too much with Jinja and I am trying to overcome some of the more difficult table scripts that I am trying to replicate. I have started to work on an example of working with the data and hit an issue when I try to generate the Jinja table.

In the example I am only looking at employee IDs (Which I know I can loop in an In condition) but for the actual project the data generated is based on termination date and values generated will be based on things such as Start and End date and every employee will have new data generated for every iteration run.

The basic Jinja script looks like this:

{% set ids = ['1104', '9891'] %}

{% for ds in ids %}
SELECT * FROM
{{ source('dbo', 'LeaversReport') }}
WHERE Employee_ID = '{{ ds }}'
{% endfor %}

And I am getting the following error when I use dbt run to create the view / table:

[PARSE_SYNTAX_ERROR] Syntax error at or near 'SELECT': extra input 'SELECT'. SQLSTATE: 42601 (line 21, pos 0)

Is there a better way to splitting this run up so it only generates one select and inserts it at a time.

Solution

  • The placement of the for loop is wrong. dbt compiles your code like this:

    
SELECT * FROM
<db>.<schema>.LeaversReport
WHERE Employee_ID = '1104'

SELECT * FROM
<db>.<schema>.LeaversReport
WHERE Employee_ID = '9891'

    Lines inside the for and endfor block will be repeated. In your case entire SELECT statement is inside the for block. That's why you are getting Syntax error at or near 'SELECT'...

    Since you want to repeat ds only, your code should look like below:

    
{% set ids = ['1104', '9891', '8795'] %}

SELECT * FROM
{{ source('dbo', 'LeaversReport') }}
WHERE
{% for ds in ids%}
 Employee_ID = '{{ ds }}' {% if not loop.last %} AND {% endif %}
{% endfor %}

    It will be compiled to:

    SELECT * FROM
 <db>.<schema>.'LeaversReport'
WHERE 
 Employee_ID = '1104' AND
 Employee_ID = '9891' AND
 Employee_ID = '8795'

    You can check how jinja will be compiled in this jinja playground.