Why Jinja makes SQL dynamic in dbt - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to insert a Jinja variable into the SQL query.

dbt

SELECT * FROM users WHERE country = '[1]';

Drag options to blanks, or click blank then click option'

Aage_limit

Buser_id

Ccountry_var

Dorder_date

Attempts:

3 left

💡 Hint

Common Mistakes

Using a variable unrelated to the filter condition.

Forgetting to use double curly braces for Jinja variables.

The Jinja variable 'country_var' is used to dynamically insert the country value into the SQL query.

2fill in blank

medium

Complete the code to use a Jinja if-statement to add a WHERE clause only if a variable is set.

dbt

SELECT * FROM orders {% if [1] %} WHERE status = '{{ status }}' {% endif %};

Drag options to blanks, or click blank then click option'

Afilter_enabled

Bstatus

Cuser_id

Dorder_date

Attempts:

3 left

💡 Hint

Common Mistakes

Using the filter value variable instead of the control variable.

Not using the correct variable inside the if-statement.

The variable 'filter_enabled' controls whether the WHERE clause is added dynamically.

3fill in blank

hard

Fix the error in the Jinja loop that dynamically creates multiple OR conditions.

dbt

SELECT * FROM products WHERE {% for category in categories %} category = '[1]' {% if not loop.last %} OR {% endif %} {% endfor %};

Drag options to blanks, or click blank then click option'

Acategory_id

Bcategories

Ccategory.name

Dcategory

Attempts:

3 left

💡 Hint

Common Mistakes

Using the list variable instead of the loop variable.

Using an attribute that does not exist.

Inside the loop, the variable 'category' represents each item, so it should be used to insert the value.

4fill in blank

hard

Fill both blanks to create a dynamic SQL filter using Jinja with a comparison operator and a variable.

dbt

SELECT * FROM sales WHERE amount [1] [2];

Drag options to blanks, or click blank then click option'

A>

Bmin_amount

C<

Dmax_amount

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up the operator and variable positions.

Using the wrong comparison operator.

The operator '>' and variable 'min_amount' create a filter for sales greater than the minimum amount.

5fill in blank

hard

Fill all three blanks to build a dynamic CASE statement in SQL using Jinja variables.

dbt

SELECT user_id, CASE WHEN age [1] [2] THEN '[3]' ELSE 'Other' END AS age_group FROM users;

Drag options to blanks, or click blank then click option'

A<

B18

CMinor

D>=

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong comparison operator.

Confusing the age threshold value.

Mislabeling the age group string.

The CASE statement checks if age is greater or equal (>=) to 18, then labels as 'Minor'.