Why Jinja makes SQL dynamic in dbt - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

How does Jinja enable dynamic SQL in dbt?

Which of the following best explains why Jinja makes SQL dynamic in dbt?

A Jinja stores SQL queries in a database for reuse.

B Jinja converts SQL queries into machine code for faster execution.

C Jinja allows embedding Python-like code to generate SQL statements based on conditions and variables at compile time.

D Jinja automatically optimizes SQL queries by rewriting them.

💡 Hint

Think about how Jinja templates can change SQL before it runs.

Jinja lets you write code inside SQL files that runs before the SQL executes. This code can use variables and conditions to build different SQL queries dynamically.

❓ Predict Output

intermediate

Output of Jinja variable substitution in SQL

What is the output SQL after rendering this Jinja template?

SELECT * FROM users WHERE country = '{{ country }}';

Given the variable country = 'Canada'.

dbt

country = 'Canada'

from jinja2 import Template

sql_template = "SELECT * FROM users WHERE country = '{{ country }}';"
template = Template(sql_template)
output_sql = template.render(country=country)
print(output_sql)

A SELECT * FROM users WHERE country = 'Canada';

B SELECT * FROM users WHERE country = Canada;

C SELECT * FROM users WHERE country = {{ country }};

D SELECT * FROM users WHERE country = 'country';

💡 Hint

Remember Jinja replaces variables with their values as strings.

The variable country is replaced by its value 'Canada' inside quotes, producing valid SQL.

❓ data_output

advanced

Result of conditional logic in Jinja SQL template

Given this Jinja SQL template:

{% if is_active %}
SELECT * FROM users WHERE active = true;
{% else %}
SELECT * FROM users;
{% endif %}

What SQL is generated if is_active = False?

dbt

from jinja2 import Template

sql_template = """{% if is_active %}
SELECT * FROM users WHERE active = true;
{% else %}
SELECT * FROM users;
{% endif %}"""
template = Template(sql_template)
output_sql = template.render(is_active=False)
print(output_sql.strip())

A SELECT * FROM users;

B SELECT * FROM users WHERE active = true;

C SELECT * FROM users WHERE active = false;

D SyntaxError: invalid syntax

💡 Hint

Check which block runs when the condition is false.

When is_active is false, the else block runs, producing SELECT * FROM users;.

🔧 Debug

advanced

Identify the error in this Jinja SQL template

What error will this Jinja SQL template cause when rendered?

SELECT * FROM orders WHERE order_date >= '{{ start_date }}' AND order_date <= '{{ end_date }}'

Given start_date = '2023-01-01' but end_date is not defined.

dbt

from jinja2 import Template

sql_template = "SELECT * FROM orders WHERE order_date >= '{{ start_date }}' AND order_date <= '{{ end_date }}'"
template = Template(sql_template)
try:
    output_sql = template.render(start_date='2023-01-01')
except Exception as e:
    print(type(e).__name__)

A No error, renders with empty end_date

B KeyError

C TypeError

D UndefinedError

💡 Hint

What happens if a variable is missing in Jinja rendering?

Jinja raises an UndefinedError when a variable used in the template is not provided during rendering.

🚀 Application

expert

Using Jinja to dynamically generate SQL with loops

What is the output SQL after rendering this Jinja template with columns = ['id', 'name', 'email']?

SELECT
{% for col in columns %}  {{ col }}{% if not loop.last %},{% endif %}
{% endfor %}
FROM users;

dbt

from jinja2 import Template

columns = ['id', 'name', 'email']
sql_template = """SELECT
{% for col in columns %}  {{ col }}{% if not loop.last %},{% endif %}
{% endfor %}
FROM users;"""
template = Template(sql_template)
output_sql = template.render(columns=columns)
print(output_sql.strip())

A SyntaxError: unexpected indent

B

SELECT
  id,
  name,
  email
FROM users;

C

SELECT
id name email
FROM users;

D SELECT id, name, email FROM users;

💡 Hint

Look at how the loop adds commas except after the last item.

The loop prints each column with commas except after the last one, preserving line breaks and indentation.