Macros for reusable SQL logic in dbt

Introduction

Macros help you write SQL code once and use it many times. This saves time and keeps your work neat.

When you need to repeat the same SQL calculation in many places.

When you want to keep your SQL code clean and easy to read.

When you want to change a logic in one place and update it everywhere.

When you want to avoid mistakes by reusing tested SQL snippets.

When you want to make your SQL code easier to maintain.

Syntax

dbt

{% macro macro_name(arg1, arg2) %}
  -- SQL code using {{ arg1 }} and {{ arg2 }}
{% endmacro %}

-- Use macro in SQL:
{{ macro_name(value1, value2) }}

Macros are written inside {% %} tags.

You call macros using double curly braces {{ }}.

Examples

This macro adds two numbers and returns the sum.

dbt

{% macro add_numbers(a, b) %}
  {{ a }} + {{ b }}
{% endmacro %}

SELECT {{ add_numbers(5, 3) }} AS sum;

This macro selects only active rows from a given table.

dbt

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

{{ filter_active('users') }}

Sample Program

This example defines a macro to calculate discounted prices. It then uses this macro in a query to get discounted prices for sales data.

dbt

{% macro calculate_discount(price, discount_rate) %}
  ROUND({{ price }} * (1 - {{ discount_rate }}), 2)
{% endmacro %}

WITH sales AS (
  SELECT 100 AS price, 0.1 AS discount_rate
  UNION ALL
  SELECT 200, 0.15
)

SELECT
  price,
  discount_rate,
  {{ calculate_discount(price, discount_rate) }} AS discounted_price
FROM sales;

Important Notes

Macros can take multiple arguments to make them flexible.

Use macros to avoid repeating complex SQL code.

Macros help keep your project DRY (Don't Repeat Yourself).

Summary

Macros let you reuse SQL code easily.

They make your SQL cleaner and easier to maintain.

Use macros to save time and reduce errors.