Why Macros for reusable SQL logic in dbt? - Purpose & Use Cases

The Big Idea

What if fixing one piece of code could fix hundreds of queries at once?

The Scenario

Imagine you have to write the same complex SQL code again and again for different reports or analyses. You copy and paste it everywhere, changing small parts manually each time.

The Problem

This manual copying is slow and risky. If you find a mistake, you must fix it in every place. It's easy to forget one spot, causing inconsistent results and wasted time.

The Solution

Macros let you write that SQL logic once and reuse it everywhere by calling the macro. If you update the macro, all your queries using it update automatically, saving time and avoiding errors.

Before vs After

✗ Before

SELECT * FROM sales WHERE date >= '2023-01-01' AND date <= '2023-01-31';
-- repeated with slight changes in many places

✓ After

{{ my_date_filter('2023-01-01', '2023-01-31') }}
-- macro handles the date filter logic

What It Enables

You can build consistent, easy-to-maintain SQL projects that scale as your data grows.

Real Life Example

A data analyst creates a macro for filtering active customers. Instead of rewriting the filter in every report, they call the macro, ensuring all reports use the same logic.

Key Takeaways

Manual SQL repetition wastes time and causes errors.

Macros let you write reusable SQL logic once.

Updating a macro updates all queries using it automatically.