As a dbt project grows, a familiar pain point begins to surface: repetition. You find yourself writing the same CASE statements to handle nulls, the same casting logic to standardize data types, and the same complex calculations across multiple models. This redundant SQL not only slows down development but also introduces inconsistencies and creates a maintenance nightmare. What if a column name changes? You have to hunt down every instance and update it manually, hoping you don’t miss one.

This is where dbt macros come in. They are the solution to writing DRY (Don’t Repeat Yourself), maintainable, and scalable dbt code. Think of them as reusable functions that supercharge your SQL. At Stellans, we’ve found that a core set of macros is invaluable for accelerating our clients’ projects and ensuring long-term success. This article is your dbt macro starter kit. We’re sharing our curated toolkit of 10 essential, copy-paste-ready macros that will immediately add value to any dbt project, new or old.

In simple terms, dbt macros are reusable pieces of code that can be called throughout your dbt project. They function much like functions in a programming language like Python. You define a macro to perform a specific task, and then you can call it from any model, test, or even another macro, often passing in arguments to make it dynamic.

Under the hood, dbt uses a powerful templating engine called the Jinja templating language. When you run your dbt models, dbt first compiles your .sql files. This compilation step processes all the Jinja syntax, including your macro calls, and generates raw, executable SQL. This dynamic approach allows you to abstract away complexity and write cleaner, more powerful data models.

The Core Benefits: Standardization, Reusability, and Maintenance

Integrating macros into your workflow isn’t just a clever trick; it’s a fundamental practice for professional analytics engineering. The benefits are clear:

• Standardization: Macros ensure that the same logic is applied consistently everywhere. Whether it’s how you create a surrogate key or how you handle null values, a macro guarantees a single, standardized approach, eliminating guesswork for your team.
• Reusability: Instead of writing the same 10 lines of SQL in 20 different models, you write it once in a macro and call it with a single line. This drastically reduces the amount of code you have to write and manage.
• Maintenance: When business logic changes, you only need to update it in one place: the macro file. This single change then propagates to every model that uses the macro the next time you run dbt. This saves countless hours and reduces the risk of errors.

Here is our go-to list of battle-tested macros. To use them, simply create a new .sql file in your project’s macros directory (e.g., macros/utils.sql) and paste the code.

Macro 1: Convert Cents to Dollars Safely

Use Case: Financial data often arrives as integers (cents) to avoid floating-point precision issues. This macro safely converts a column of cents into a properly formatted decimal dollar amount, preventing common casting errors.

The Code:

How to Use It: In your model, you can call it directly within a SELECT statement.

Macro 2: Coalesce Null Values

Use Case: Null values can wreak havoc on calculations and aggregations. This macro provides a clean, shorthand way to replace NULL with a specified default value, making your downstream queries more robust.

The Code:

How to Use It: Replace nulls in a numeric column with 0 or in a string column with ‘N/A’.

Macro 3: Standardize Column Names

Use Case: Column names from different sources can be inconsistent (e.g., UserName, user_name, User Name). This macro standardizes them by trimming whitespace, converting to lowercase, and replacing spaces with underscores, which aligns with data modeling best practices.

The Code:

How to Use It: Useful in staging models to clean up column names as soon as data enters your warehouse.

Macro 4: Cross-Database Current Timestamp

Use Case: Different data warehouses have slightly different functions for getting the current timestamp (e.g., GETDATE() in SQL Server, CURRENT_TIMESTAMP() in PostgreSQL/Snowflake). This macro abstracts that difference, making your models more portable across different databases.

The Code:

Note: This pattern uses adapter.dispatch and is inspired by the dbt-utils package.

How to Use It: Add a loaded_at column to your models to track when the data was transformed.

Macro 5: Cross-Database Date Part Extraction

Use Case: Similar to timestamps, the syntax for extracting parts of a date (like year, month, or day) can vary between SQL dialects. This macro provides a consistent interface for these common operations.

The Code:

How to Use It: Easily extract features from a date column for analysis.

Macro 6: Generate Custom Schema Names

Use Case: When working with multiple environments (dev, prod), you often want dbt to build models in different schemas. This powerful macro allows you to dynamically generate schema names, for instance, by appending your target name (e.g., prod_marts, dev_marts).

The Code:

Note: This macro overrides dbt’s default behavior. It should be placed in its own file like macros/get_custom_schema.sql and configured in your dbt_project.yml.

How to Use It: In dbt_project.yml, you configure models to use this logic.

Macro 7: Generate a Surrogate Key

Use Case: A cornerstone of dimensional modeling is the surrogate key, a unique identifier for a row in a dimension table. This macro generates a consistent, hashed surrogate key from one or more business keys, ensuring uniqueness and decoupling from source system IDs. It’s one of the most useful macros from the dbt-utils package.

The Code:

Note: This requires you to have the dbt-utils package installed in your packages.yml file.

How to Use It: Create a primary key for your dimension or fact tables.

Macro 8: Audit Helper to Compare Relations

Use Case: How do you verify that a model refactoring didn’t change the underlying data? This audit helper macro compares two relations (tables/views) and returns the rows that don’t match, making it perfect for regression testing.

The Code:

Note: This also comes from the dbt-utils package and is incredibly powerful for testing.

How to Use It: Run it as an operation to compare your new model (dev_dim_users) against the production version.

Macro 9: Grant Select Permissions on a Schema

Use Case: Data governance is critical. After your models are built, you need to grant usage rights to your BI tools or downstream consumers. This macro, when used as a post-hook, automates the process of granting SELECT privileges on all tables within a schema to a specific role.

The Code:

How to Use It: In your dbt_project.yml, add this as a post-hook to run after your models are built.

Macro 10: Check if a Date is a Weekend

Use Case: For business analytics, it’s often useful to distinguish between weekdays and weekends. This simple macro returns a boolean value, making it easy to flag weekend activity for reporting or analysis.

The Code:

How to Use It: Create a boolean column in a dimension table.

Now that you have your starter kit, keeping your macros organized and effective is the next step. Following a few best practices will ensure they remain a valuable asset as your project scales.

Keep Them Simple and Focused

A macro should do one thing and do it well. This means creating small, focused macros instead of monolithic ones that handle multiple, unrelated tasks. A small, focused macro like cents_to_dollars is easier to understand, test, and debug than one that tries to handle currency conversion, nulls, and standardization all at once.

Document Everything

Your future self (and your teammates) will thank you. Use comments within the macro’s code to explain what it does, what its parameters are, and any assumptions it makes. For more complex logic, consider adding a README.md file in your macros directory that provides a comprehensive overview of your custom macros.

Test Your Macros

You can test a macro’s compiled output without running a full model by using the dbt run-operation command. This is a great way to quickly iterate and ensure your macro generates the correct SQL. Simply call your macro from another macro and use the log function to print the result to the command line.

Leverage Community Packages

Before you write a macro from scratch, check if it already exists in a community package like dbt-utils. This package contains dozens of battle-tested macros that solve the most common problems in analytics engineering. Take advantage of robust, community-vetted solutions whenever they are available. For more details, refer to dbt’s official documentation.

Implementing these macros is a great first step toward building a more professional and efficient dbt workflow. However, as your team and data complexity grow, scaling dbt across a large organization requires a more strategic approach to governance, performance, and analytics engineering best practices.

This is where a dedicated partner can make all the difference. Stellans’ dbt Development Support is designed to help you move beyond the basics. We work with your team to establish robust data modeling frameworks, optimize your dbt project for performance and cost, and build a truly scalable data platform. We help you connect your data transformation strategies to real business outcomes, turning your data team into a driver of growth.

Ready to build a truly scalable and maintainable dbt practice? Contact Stellans today to learn how our experts can help.

dbt macros are a fundamental tool for any serious analytics engineer. They are the key to transforming your dbt project from a collection of individual SQL queries into a cohesive, maintainable, and scalable data asset. By abstracting away repetitive logic, you not only save time but also improve the reliability and consistency of your data transformations.

The 10 macros provided here are a powerful starting point. They address some of the most common, recurring challenges faced in data modeling. We encourage you to integrate them into your projects and start experiencing the benefits of writing DRYer, cleaner code today.

What are dbt macros used for?

dbt macros are used to write reusable, modular SQL code. They help analytics engineers standardize logic, reduce code duplication, and manage complex transformations more efficiently across their projects, acting like functions for your SQL.

How do you create a dbt macro?

You create a dbt macro by defining it within a .sql file in the macros directory of your dbt project. The syntax uses Jinja templating, starting with {% macro macro_name(arguments) %} and ending with {% endmacro %}.

How do you reuse dbt macros across projects?

dbt macros can be reused across projects by creating a dbt package. By packaging your common macros, you can install that package as a dependency in any other dbt project, making your macro toolkit portable and easy to maintain.