Macros for reusable SQL logic in dbt - Time & Space Complexity

Time Complexity: Macros for reusable SQL logic

O(n)

Understanding Time Complexity

When using macros in dbt, we want to know how the time to run SQL changes as data grows.

We ask: How does reusing SQL logic with macros affect execution time?

Scenario Under Consideration

Analyze the time complexity of this dbt macro usage.

{% macro filter_active_users() %}
  WHERE status = 'active'
{% endmacro %}

SELECT *
FROM users
{{ filter_active_users() }}

This macro adds a reusable filter condition to SQL queries.

Identify Repeating Operations

Look for repeated actions that affect runtime.

• Primary operation: The database scans the users table rows.
• How many times: Once per query execution, regardless of macro use.

How Execution Grows With Input

Execution time depends on how many rows the database must check.

Input Size (n)	Approx. Operations
10	10 row checks
100	100 row checks
1000	1000 row checks

Pattern observation: Time grows roughly in direct proportion to the number of rows.

Final Time Complexity

Time Complexity: O(n)

This means the query time grows linearly with the number of rows in the table.

Common Mistake

[X] Wrong: "Using macros makes the query run faster because it reuses code."

[OK] Correct: Macros only help write cleaner code; they don't change how many rows the database processes.

Interview Connect

Understanding how macros affect query time shows you know the difference between code reuse and actual data processing cost.

Self-Check

"What if the macro included a join to another large table? How would the time complexity change?"