dbt macros are Jinja2 templating functions that enable code reusability, dynamic SQL generation, and abstractions for complex transformations. Mastering macros separates junior from senior data engineers.
Understanding dbt Macros and Jinja2
dbt macros are Jinja2 template code that gets compiled into SQL before execution. A macro is a reusable code snippet with input parameters that generates SQL. Think of macros as functions: they take arguments, execute logic, return SQL text.
Jinja2 provides conditionals (if/else), loops (for/while), filters (upper, lower, join), and variable substitution. This enables dynamic SQL generation—same macro generates different SQL based on inputs.
Basic Macro Structure
SELECT
{%- for col in columns %}
{{ col }}{{ “,” if not loop.last else “” }}
{%- endfor %}
FROM table_name
{%- endmacro %}
Macro Parameters and Context
Macros accept multiple parameters. The special ‘execute’ context variable determines if Jinja2 code runs during parsing (execute=False) or execution (execute=True). This enables conditional logic that adapts to dbt runtime state.
Dynamic SQL Generation with Jinja2 Filters
Jinja2 filters transform data. Common filters: upper (uppercase), lower (lowercase), join (concatenate list with delimiter), default (provide fallback value), length (count items). These enable compact, readable SQL generation.
Control Flow: If/Else and For Loops
Macros use if/else for conditional SQL generation and for loops for iterating over lists. This allows one macro to generate vastly different SQL based on simple parameters.
Calling Database Queries in Macros with execute
During execute phase, macros can run SQL queries via run_query(). This enables dynamic behavior based on actual database state. Use execute context to guard expensive operations.
5 Complete Interview Questions with Solutions
Question 1: Build a Reusable Macro for Null-Safe Comparisons
Create a macro that safely compares two columns, returning true only if both are equal or both are null. Demonstrate usage in a model checking for plan changes.
({{ col1 }} = {{ col2 }} OR ({{ col1 }} IS NULL AND {{ col2 }} IS NULL))
{% endmacro %}
— Usage:
WHERE NOT {{ null_safe_equals(‘old_plan’, ‘new_plan’) }}
Explanation: Macro abstracts null-safe comparison logic. Reusable across models without duplicating conditional logic. Cleaner, more maintainable code.
Stripe – Senior Data Engineer
Question 2: Macro for Dynamic Column Aliasing
Create a macro that takes a list of column names and applies transformations (lowercase, remove spaces). Output aliased columns for clean staging model output.
{%- for col in columns %}
{{ col | lower | replace(‘ ‘, ‘_’) }} as {{ col | lower | replace(‘ ‘, ‘_’) }}
{%- if not loop.last %},{% endif %}
{%- endfor %}
{% endmacro %}
Explanation: Jinja2 filters transform column names. Loop iterates over list. Macro generates clean SQL without manual aliasing.
LinkedIn – Senior Data Engineer
Question 3: Macro Using run_query() for Database Introspection
Create a macro that dynamically lists all columns in a table during compilation. Return as comma-separated string for use in SELECT for automatic schema discovery.
{% if execute %}
{% set result = run_query(
“SELECT column_name FROM information_schema.columns WHERE table_name = ‘” ~ table ~ “‘”
) %}
{% set column_names = result.columns[0].values() %}
{{ column_names | join(‘, ‘) }}
{% else %}
*
{% endif %}
{% endmacro %}
Explanation: execute context enables run_query() during compilation. Queries database schema, builds column list dynamically. Supports schema changes without code edits.
Airbnb – Senior Data Engineer
Question 4: Conditional SQL Generation with Jinja2 If/Else
Create a macro that generates different WHERE clauses based on database type (Snowflake vs BigQuery syntax). Handle date arithmetic differences across platforms.
{% if target.type == ‘snowflake’ %}
{{ column }} >= DATEADD(day, -{{ days_back }}, CURRENT_DATE())
{% elif target.type == ‘bigquery’ %}
{{ column }} >= DATE_SUB(CURRENT_DATE(), INTERVAL {{ days_back }} DAY)
{% else %}
{{ column }} >= CURRENT_DATE – INTERVAL ‘{{ days_back }} days’
{% endif %}
{% endmacro %}
Explanation: target.type identifies database. If/else generates appropriate SQL syntax. Enables code portability across databases without duplication.
Google – Senior Data Engineer
Question 5: Complex Macro for Dynamic Pivot Operations
Build a macro that pivots a table based on input column list. Generate CASE statements dynamically for monthly revenue pivot from transaction table.
{%- for val in values %}
SUM(CASE WHEN {{ pivot_col }} = ‘{{ val }}’ THEN {{ value_col }} ELSE 0 END) as {{ val | lower }}
{%- if not loop.last %},{% endif %}
{%- endfor %}
{% endmacro %}
SELECT
customer_id,
{{ generate_pivot_case(‘amount’, ‘month’, [‘Jan’, ‘Feb’, ‘Mar’]) }}
FROM transactions
GROUP BY customer_id
Explanation: For loop generates CASE statements dynamically. Avoids manual repetition. Pivot logic becomes reusable across models.
Stripe – Senior Analytics Engineer
15 Practice Questions (Test Yourself)
Question 6: Create a macro that generates SELECT statements with data type casting based on a configuration dictionary mapping column names to SQL types. How would you handle type conversions across databases?
Google – Senior Analytics Engineer
Question 7: Explain when to use Jinja2 templating in dbt versus native SQL. Design decision framework for scenarios where each is more appropriate based on complexity and reusability.
Databricks – Senior Data Engineer
Question 8: Build a macro for data validation at runtime. Check column cardinality, null percentages, and value ranges using run_query(). How do you surface validation failures to users?
Stripe – Senior Analytics Engineer
Question 9: Design a macro that generates WHERE clauses from a list of filters with proper operator precedence. Handle nested AND/OR logic and parenthesis placement correctly.
Google – Senior Data Engineer
Question 10: Create a macro that generates surrogate key logic using hashing functions (MD5, SHA256). How would you ensure consistent hashing across database platforms with different functions?
Meta – Senior Data Engineer
Question 11: Build a macro that auto-generates INSERT statements from CSV seed file metadata. How would you handle schema inference and default values programmatically?
Airbnb – Senior Analytics Engineer
Question 12: Explain the difference between macros and packages. When would you repackage macro logic as a shareable package versus keeping locally? What are packaging implications?
Google – Senior Analytics Engineer
Question 13: Macro performance optimization. How does Jinja2 compilation impact execution speed? When does macro overhead become problematic and how do you measure it?
Databricks – Senior Data Engineer
Question 14: Create a macro that handles nested loops for complex aggregations over multiple dimensions. How would you prevent SQL explosion and maintain readability in generated code?
Stripe – Senior Data Engineer
Question 15: Debug a macro producing incorrect SQL. How would you inspect compiled SQL output and use dbt parse to validate macro expansion without running full dbt run?
Uber – Senior Analytics Engineer
Question 16: Use Jinja2 context variables to create environment-aware macros (dev versus prod). How would you generate different SQL paths based on execution context without code duplication?
Google – Senior Data Engineer
Question 17: Design a macro that auto-generates documentation for business logic encoded in SQL. How would you create metadata-driven documentation from macro definitions?
Stripe – Senior Data Engineer
Question 18: Create a macro with error handling for invalid inputs. Use assertions and error messages to catch problems at compile time rather than runtime failures.
Databricks – Senior Analytics Engineer
Question 19: Build a macro that generates JOIN statements dynamically from relationship configurations. How would you handle different join types and cardinality specifications programmatically?
Meta – Senior Data Engineer
Question 20: Compare Jinja2 macros versus database-native functions (UDFs). When choose macro for SQL generation versus pushing logic to stored procedures in the database?
Airbnb – Senior Data Engineer
Master dbt Macros and Jinja2
Get our comprehensive Data Analytics: Ace All The Interview Concepts course.