Macros for reusable SQL logic in dbt - Step-by-Step Execution

Concept Flow - Macros for reusable SQL logic

Define Macro with name and parameters

↓

Write SQL logic inside macro

↓

Call macro in SQL model or another macro

↓

Macro expands to SQL code

↓

Run compiled SQL query

↓

Get results with reusable logic

Macros let you write SQL code once and reuse it many times by calling the macro with different inputs.

Execution Sample

dbt

{% macro filter_active(table) %}
  SELECT * FROM {{ table }} WHERE status = 'active'
{% endmacro %}

{{ filter_active('users') }}

Defines a macro to filter active rows from any table, then uses it on the 'users' table.

Execution Table

Step	Action	Macro/SQL	Result/Output
1	Define macro 'filter_active'	macro filter_active(table)	Stored macro with SQL logic to filter active rows
2	Call macro with argument 'users'	{{ filter_active('users') }}	Expands to: SELECT * FROM users WHERE status = 'active'
3	Run expanded SQL query	SELECT * FROM users WHERE status = 'active'	Returns all rows from users where status is 'active'
4	Query result	Filtered rows	Data rows with status = 'active' from users table

💡 Execution stops after running the expanded SQL query and returning filtered results.

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
table	undefined	'users'	'users'	'users'
macro SQL logic	undefined	SELECT * FROM users WHERE status = 'active'	SELECT * FROM users WHERE status = 'active'	SELECT * FROM users WHERE status = 'active'

Key Moments - 3 Insights

Why does the macro need a parameter like 'table'?

The parameter 'table' lets the macro work with any table name, making the SQL reusable. See execution_table step 2 where 'users' is passed in.

What happens when the macro is called in the SQL?

The macro code replaces the call with actual SQL using the passed parameter. This is shown in execution_table step 2 and 3.

Can macros return data directly?

No, macros return SQL code that runs later. The actual data comes from running the expanded SQL query, as in execution_table step 3 and 4.

Visual Quiz - 3 Questions

Look at the execution_table, what SQL does the macro call expand to at step 2?

A SELECT * FROM table WHERE status = 'active'

B SELECT * FROM users WHERE status = 'active'

C SELECT * FROM filter_active WHERE status = 'active'

D SELECT * FROM users

💡 Hint

Check the 'Result/Output' column in execution_table row 2.

At which step does the macro get replaced by actual SQL code?

A Step 2

B Step 1

C Step 3

D Step 4

💡 Hint

Look at the 'Action' and 'Result/Output' columns in execution_table rows 1 and 2.

If you change the macro parameter to 'orders', what changes in variable_tracker?

A The macro SQL logic becomes 'SELECT * FROM users WHERE status = 'active'

B The macro definition changes

C The 'table' variable changes to 'orders' after step 2

D No changes happen

💡 Hint

Check the 'table' row in variable_tracker after step 2.

Concept Snapshot

Macros in dbt let you write reusable SQL logic.
Define with {% macro name(params) %} ... {% endmacro %}.
Call with {{ macro_name(args) }} to insert SQL.
Macros return SQL code, not data.
Use parameters to customize logic.
Helps avoid repeating SQL code.

Full Transcript

Macros in dbt are reusable pieces of SQL code. You define a macro with a name and parameters. Inside, you write SQL that can use those parameters. When you call the macro in your SQL model, dbt replaces the call with the SQL code from the macro, filling in the parameters you gave. Then dbt runs the full SQL query. This way, you write common SQL logic once and reuse it many times with different inputs. For example, a macro can filter active rows from any table by passing the table name as a parameter. The macro call expands to a full SQL SELECT statement with the filter. The actual data comes from running this expanded SQL. This saves time and keeps your SQL DRY (Don't Repeat Yourself).

Practice

1. What is the main purpose of using macros in dbt?

easy

A. To create visualizations from data

B. To store data permanently in the database

C. To reuse SQL code and make it easier to maintain

D. To write Python scripts inside dbt

Solution

1. Step 1: Understand what macros do

   Macros are reusable pieces of SQL code that help avoid repetition.

2. Step 2: Identify the main benefit

   Using macros makes SQL cleaner and easier to maintain by reusing code.

3. Final Answer:

   To reuse SQL code and make it easier to maintain -> Option C

4. Quick Check:

   Macros = reusable SQL code [OK]

Hint: Macros = reusable SQL snippets for cleaner code [OK]

Common Mistakes:

• Thinking macros store data
• Confusing macros with visualization tools
• Assuming macros run Python code

2. Which of the following is the correct way to define a macro in dbt?

easy

A. {% macro my_macro() %} SELECT * FROM table {% endmacro %}

B. CREATE MACRO my_macro AS SELECT * FROM table;

C. def my_macro(): SELECT * FROM table

D. macro my_macro() { SELECT * FROM table }

Solution

1. Step 1: Recall dbt macro syntax

   dbt macros use Jinja templating with {% macro %} and {% endmacro %} tags.

2. Step 2: Match syntax to options

   {% macro my_macro() %} SELECT * FROM table {% endmacro %} correctly uses Jinja syntax for defining a macro.

3. Final Answer:

   {% macro my_macro() %} SELECT * FROM table {% endmacro %} -> Option A

4. Quick Check:

   dbt macros use {% macro %} ... {% endmacro %} [OK]

Hint: Look for {% macro %} and {% endmacro %} tags [OK]

Common Mistakes:

• Using SQL CREATE MACRO syntax (not dbt)
• Writing Python function syntax
• Using curly braces without Jinja tags

3. Given this macro definition:

{% macro select_active_users() %}
SELECT * FROM users WHERE active = true
{% endmacro %}

What will be the output SQL of this dbt model?

SELECT id, name FROM ({{ select_active_users() }})

medium

A. SELECT id, name FROM {{ select_active_users() }}

B. SELECT id, name FROM users WHERE active = true

C. Error: Macro call syntax incorrect

D. SELECT id, name FROM (SELECT * FROM users WHERE active = true)

Solution

1. Step 1: Understand macro expansion

   The macro returns the SQL: SELECT * FROM users WHERE active = true.

2. Step 2: Substitute macro call in model SQL

   The model SQL becomes: SELECT id, name FROM (SELECT * FROM users WHERE active = true)

3. Final Answer:

   SELECT id, name FROM (SELECT * FROM users WHERE active = true) -> Option D

4. Quick Check:

   Macro expands SQL inside parentheses [OK]

Hint: Macros insert SQL code where called [OK]

Common Mistakes:

• Forgetting parentheses around macro SQL
• Thinking macro returns table name only
• Using macro call without {{ }}

4. Identify the error in this macro definition:

{% macro get_orders() %}
SELECT * FROM orders

medium

A. Macro name cannot have underscores

B. Missing {% endmacro %} tag to close the macro

C. SELECT statement must be inside double quotes

D. Macro cannot return SELECT statements

Solution

1. Step 1: Check macro syntax

   Macros must start with {% macro %} and end with {% endmacro %} tags.

2. Step 2: Identify incorrect closing tag

   The macro incorrectly uses instead of {% endmacro %}.

3. Final Answer:

   Missing {% endmacro %} tag to close the macro -> Option B

4. Quick Check:

   Macro must end with {% endmacro %} [OK]

Hint: Always close macros with {% endmacro %} [OK]

Common Mistakes:

• Using HTML-like closing tags
• Thinking underscores are invalid
• Believing SELECT needs quotes in macros

5. You want to create a macro that accepts a table name and a column name, then returns SQL to count distinct values in that column. Which macro definition correctly achieves this?

hard

A. {% macro count_distinct(table, column) %} SELECT COUNT(DISTINCT {{ column }}) FROM {{ table }} {% endmacro %}

B. {% macro count_distinct(table, column) %} SELECT COUNT(DISTINCT column) FROM table {% endmacro %}

C. {% macro count_distinct(table, column) %} SELECT COUNT(DISTINCT '{{ column }}') FROM '{{ table }}' {% endmacro %}

D. {% macro count_distinct(table, column) %} SELECT COUNT(DISTINCT $column) FROM $table {% endmacro %}

Solution

1. Step 1: Understand macro parameters usage

   Parameters must be inserted with {{ }} to inject SQL identifiers.

2. Step 2: Check each option for correct syntax

   {% macro count_distinct(table, column) %} SELECT COUNT(DISTINCT {{ column }}) FROM {{ table }} {% endmacro %} correctly uses {{ column }} and {{ table }} without quotes, so SQL identifiers are injected properly.

3. Final Answer:

   {% macro count_distinct(table, column) %} SELECT COUNT(DISTINCT {{ column }}) FROM {{ table }} {% endmacro %} -> Option A

4. Quick Check:

   Use {{ }} to insert parameters as SQL code [OK]

Hint: Use {{ }} to insert macro parameters in SQL [OK]

Common Mistakes:

• Putting parameters inside quotes (makes them strings)
• Using $variable syntax (not Jinja)
• Not wrapping parameters with {{ }}