Custom Macros in Coalesce.io
When working in data transformation platforms like Coalesce.io, efficiency and maintainability are everything. As your data pipelines grow, so does the complexity of your transformation logic. This is where custom macros become a powerful ally — they let you write reusable, dynamic SQL code that can be applied consistently across your entire data model.
In this article, we’ll explore how to use macros in Coalesce.io with the help of the Jinja templating language. You’ll see how a small example — converting currency values from cents to dollars — can scale into a reusable pattern that saves hours of development and maintenance time.
In this article:
- What Are Macros in Coalesce.io?
- Why Use Macros?
- Setting Up the Example: Converting Cents to Dollars
- Creating a Custom Macro in Coalesce.io
- Applying the Macro in a Transformation
- Benefits of Using Macros in Coalesce.io
- Debugging and Testing Macros
- When to Use Macros
- Best Practices for Writing Coalesce.io Macros
- Real-World Impact
- Final Thoughts
- Next Steps
- Watch the Video
- Meet the Speaker
What Are Macros in Coalesce.io?
Macros are reusable pieces of logic that can be dynamically inserted into your SQL transformations. They’re written in Jinja, a templating language originally popularized in web development frameworks such as Django and Flask. Coalesce.io leverages Jinja to make SQL more dynamic and parameterized, letting you write logic once and reuse it anywhere.
In simple terms, a macro is like a small function that can generate SQL code on the fly. You define a macro once — for example, to perform a mathematical conversion, a date calculation, or a string manipulation — and then call it across multiple nodes in your Coalesce.io project.
Why Use Macros?
As data models evolve, repetitive transformations become a maintenance challenge. Imagine you have multiple columns across different tables that need to be converted from cents to dollars. You could copy and paste the same transformation logic everywhere, but what happens when the business decides to change the precision or switch the data type?
You’d have to update every single instance manually — a process that’s time-consuming and error-prone. With a macro, you simply change the logic once, and it updates everywhere it’s used. This drastically reduces maintenance efforts and improves consistency.
Setting Up the Example: Converting Cents to Dollars
Let’s walk through a simple example. Suppose you have a customer table with an account_balance column that stores values in cents. The business requires all monetary values to be in dollars for reporting and analytics. This means we need to divide the column by 100 and adjust the data type accordingly.
In Coalesce.io, we can perform this transformation directly within a stage node. You might start with something like:
{{ SC }} / 100::NUMERIC(18, 2)
The {{ SC }} syntax tells Coalesce.io to dynamically insert the source column name. After running the transformation, you’ll see that account_balance is now expressed in dollars instead of cents. So far, so good — but what if multiple columns or nodes need this conversion?
Copying and pasting this logic everywhere quickly becomes inefficient. This is the perfect case for turning the transformation into a macro.
Creating a Custom Macro in Coalesce.io
To define a new macro, navigate to your project’s Build Settings and locate the Macros section. There, you can create workspace-level macros that are available across your entire Coalesce.io environment.
Here’s a simple Jinja macro to convert cents to dollars:
{% macro cents_to_dollars(column, scale=2) %}
({{ column }} / 100)::NUMERIC(18, {{ scale }})
{% endmacro %}
This macro does a few important things:
- Accepts parameters — The
columnparameter specifies which column to transform, andscaledefines the number of decimal places (defaulting to 2). - Performs the conversion — It divides the value by 100 and casts it to a numeric type.
- Is reusable — You can now call this macro anywhere without rewriting the logic.
Applying the Macro in a Transformation
Once your macro is defined, you can use it directly within your stage or transform node. For example, if you want to create a new column that stores the converted dollar value, simply write:
{{ cents_to_dollars('customer.account_balance') }}
This will execute the macro, substitute the SQL expression, and generate the proper transformation logic dynamically. You can even adjust the precision if needed:
{{ cents_to_dollars('customer.account_balance', scale=4) }}
With just one line of code, you’ve achieved a flexible, reusable transformation that can be applied across multiple nodes and columns.
Benefits of Using Macros in Coalesce.io
Macros may seem like a small feature, but their impact on data engineering workflows is significant. Here are a few key advantages:
1. Reduce Maintenance Overhead
When transformation logic changes, you only need to update it in one place. This ensures consistency across your pipelines and minimizes human error.
2. Promote Reusability
Macros make your codebase more modular. Teams can share standardized transformation logic, reducing duplication and ensuring best practices are applied everywhere.
3. Improve Readability
Instead of cluttering transformations with complex SQL expressions, you can reference clean, descriptive macro calls. This improves readability and helps onboard new team members faster.
4. Enhance Collaboration
Macros can be shared across teams or workspaces, enabling collaborative development in larger analytics engineering environments.
5. Simplify Complex Logic
As transformations get more sophisticated, you can encapsulate multi-step logic inside a macro. For example, handling data type conversions, conditional mappings, or even custom business rules — all within one reusable function.
Debugging and Testing Macros
Because macros generate SQL dynamically, debugging can sometimes feel tricky. Coalesce.io helps by allowing you to preview the compiled SQL code. After running a transformation that uses a macro, open the results to view the underlying SQL — you’ll see exactly how your macro was expanded and executed.
This visibility is crucial for validating logic and ensuring your macros behave as expected.
When to Use Macros
A good rule of thumb is simple:
If you find yourself repeating the same transformation in more than one place, consider turning it into a macro.
Macros are particularly useful for:
- Currency or unit conversions
- Data cleansing logic (e.g., trimming whitespace, normalizing case)
- Timestamp or date formatting
- Standard calculations (e.g., margin, growth rate, ratios)
- Conditional logic applied across multiple datasets
By standardizing these repetitive transformations, your data environment becomes cleaner, easier to manage, and more scalable.
Best Practices for Writing Coalesce.io Macros
- Keep macros simple — Each macro should serve a single clear purpose. Break complex logic into smaller, composable macros when possible.
- Use default parameters wisely — Providing defaults (like
scale=2) makes macros flexible and user-friendly. - Document your macros — Add comments explaining what each macro does and the parameters it expects. Future maintainers will thank you.
- Test before scaling — Validate each macro on a small dataset before applying it widely.
- Version control your macros — Store them in a shared Git repository or Coalesce.io workspace for collaboration and traceability.
Real-World Impact
In production environments, macros can save hours of repetitive work each week. Imagine applying the same data type conversion or formatting rule across dozens of tables — a single macro call replaces all those redundant SQL snippets.
Macros also make large-scale refactoring much safer. If a business requirement changes (for example, moving from numeric to money data types), one macro edit automatically updates every transformation that depends on it.
Final Thoughts
Custom macros in Coalesce.io aren’t just a convenience — they’re a foundation for scalable, maintainable data engineering. By abstracting common logic into reusable templates, you streamline your transformations, reduce technical debt, and empower your team to focus on building insights rather than maintaining code.
As a best practice, always look for opportunities to generalize repetitive logic. When you spot patterns across transformations, that’s your cue to create a macro. Start small, experiment, and watch your data pipelines become cleaner, faster, and easier to manage.
Next Steps
Explore Coalesce’s documentation on macros and Jinja templating to deepen your understanding. You can also download our free Data World Handbook — a comprehensive guide to modern data architecture, data vault modeling, and transformation best practices.
And if you’ve built interesting macros of your own, share them in the comments below. We’d love to see how you’re simplifying your Coalesce.io workflows.
Watch the Video
Meet the Speaker
Tim Kirschke
Senior Consultant
Tim has a Bachelor’s degree in Applied Mathematics and has been working as a BI consultant for Scalefree since the beginning of 2021. He’s an expert in the design and implementation of BI solutions, with focus on the Data Vault 2.0 methodology. His main areas of expertise are dbt, Coalesce, and BigQuery.
