For loops for dynamic SQL in dbt - Time & Space Complexity

Time Complexity: For loops for dynamic SQL

O(n)

Understanding Time Complexity

We want to understand how the time needed to run dynamic SQL with for loops changes as the input grows.

How does the number of loop runs affect the total work done?

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

{% set tables = ['sales', 'customers', 'products'] %}

{% for table in tables %}
  {{
    run_query(
      """
      SELECT COUNT(*) FROM {{ table }}
      """
    )
  }}
{% endfor %}

This code runs a SQL query for each table in a list, counting rows dynamically.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Running a SQL query inside a for loop.
• How many times: Once for each table in the list.

How Execution Grows With Input

As the number of tables increases, the number of queries grows the same way.

Input Size (n)	Approx. Operations
3	3 queries run
10	10 queries run
100	100 queries run

Pattern observation: The work grows directly with the number of tables.

Final Time Complexity

Time Complexity: O(n)

This means if you double the number of tables, the total queries run will also double.

Common Mistake

[X] Wrong: "Running queries inside a loop is always constant time because each query is separate."

[OK] Correct: Each query adds to total time, so more tables mean more queries and more total work.

Interview Connect

Understanding how loops affect query counts helps you write efficient data transformations and explain your code clearly.

Self-Check

"What if we nested another loop inside to run queries for each column in each table? How would the time complexity change?"