For loops for dynamic SQL in dbt - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a for loop over the list 'tables'.

dbt

{% for [1] in tables %}
  SELECT * FROM [1]
{% endfor %}

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

Atable

Btables

Ci

Ditem

Attempts:

3 left

💡 Hint

Common Mistakes

Using the list name 'tables' instead of a single item variable.

Using an index variable like 'i' which is not typical in dbt loops.

In dbt Jinja, the variable name in the for loop should be a single item, like 'table', not the list itself.

2fill in blank

medium

Complete the code to output the table name inside the loop.

dbt

{% for table in tables %}
  SELECT * FROM {{ [1] }}
{% endfor %}

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

Atables

Btable

Cloop.index

Ditem

Attempts:

3 left

💡 Hint

Common Mistakes

Using the list name 'tables' inside the loop instead of the loop variable.

Using 'loop.index' which is a number, not a table name.

Inside the loop, use the loop variable 'table' to refer to the current table name.

3fill in blank

hard

Fix the error in the loop syntax to correctly iterate over 'columns'.

dbt

{% for col [1] columns %}
  {{ col }}
{% endfor %}

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

Ain

Bon

Cof

Dat

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'of' or 'on' instead of 'in' causes syntax errors.

Leaving out the keyword entirely.

The correct syntax for a for loop in Jinja is 'for variable in list'.

4fill in blank

hard

Fill all three blanks to create a dictionary comprehension that maps columns to their types if type is 'string'.

dbt

{% set col_types = { [1]: [2] for [3] in columns if columns[[3]] == 'string' } %}

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

Acol

Bcolumns[col]

Attempts:

3 left

💡 Hint

Common Mistakes

Using the list name 'columns' as key or value directly.

Mixing variable names inconsistently.

The key is the column name 'col', and the value is its type 'columns[col]'.

5fill in blank

hard

Fill all three blanks to create a loop that builds a SQL SELECT statement with dynamic columns and tables.

dbt

{% set select_cols = ', '.join([[1] for [2] in columns]) %}
SELECT {{select_cols}} FROM [3]

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

Acol

Ctable

Dcolumns

Attempts:

3 left

💡 Hint

Common Mistakes

Using different variable names in the list comprehension and loop.

Using the list name 'columns' instead of the variable.

Using 'columns' as the table name.

We use 'col' as the variable in the list comprehension and loop, and 'table' as the table name.