DBT’s macro system is quite powerful and can help you encapsulate and reuse SQL code across your project. Here are a few practical examples of custom macros that could be used in real-world scenarios:
1. Date Range Filter Macro
In many scenarios, you might want to filter your data based on a date range. Instead of writing the same WHERE clause repeatedly, you can encapsulate it into a macro:
{% macro date_range_filter(column, start_date, end_date) %}
{{ column }} >= '{{ start_date }}' AND {{ column }} < '{{ end_date }}'
{% endmacro %}
Then you can use this macro in your SQL code like so:
SELECT *
FROM {{ ref('my_table') }}
WHERE {{ date_range_filter('order_date', '2022-01-01', '2023-01-01') }}
2. Case When Status Macro
Often, you might want to map certain values to human-readable statuses. Consider a situation where you have a ‘status_code’ column that you want to map to specific statuses:
{% macro map_status(column) %}
CASE
WHEN {{ column }} = 1 THEN 'New'
WHEN {{ column }} = 2 THEN 'In Progress'
WHEN {{ column }} = 3 THEN 'Completed'
ELSE 'Unknown'
END
{% endmacro %}
You can then use this macro in your SQL code as follows:
SELECT
id,
{{ map_status('status_code') }} as status
FROM {{ ref('my_table') }}
3. Null Value Replacement Macro
You might want to replace null values in a certain column with a default value. This can be encapsulated in a macro:
{% macro handle_null(column, default_value) %}
COALESCE({{ column }}, '{{ default_value }}')
{% endmacro %}
You can then use this macro as follows:
SELECT
id,
{{ handle_null('email', 'N/A') }} as email
FROM {{ ref('my_table') }}
4. Aggregation Macro
This macro performs a specified aggregation on a column. It reduces the redundancy of repeated aggregation functions.
{% macro aggregate(column, function) %}
{{ function }}({{ column }})
{% endmacro %}
5. Column Renaming Macro
This macro renames a column. It’s helpful when you’re consistently renaming columns across multiple models.
{% macro rename_column(old_name, new_name) %}
{{ old_name }} AS {{ new_name }}
{% endmacro %}
6. Currency Conversion Macro
This macro converts a currency value from one currency to another. This is helpful when dealing with multi-currency datasets.
{% macro convert_currency(amount_column, rate_column) %}
{{ amount_column }} * {{ rate_column }}
{% endmacro %}
7. Data Deduplication Macro
This macro removes duplicate rows based on certain key columns. This is useful when dealing with data that may contain duplicates.
{% macro deduplicate(key_columns) %}
QUALIFY ROW_NUMBER() OVER (PARTITION BY {{ key_columns }} ORDER BY loaded_at DESC) = 1
{% endmacro %}
8. Percentage Calculation Macro
This macro calculates the percentage of a part to a whole. It’s useful in numerous scenarios, especially in creating ratios or rates.
{% macro calculate_percentage(part_column, total_column) %}
{{ part_column }} / NULLIF({{ total_column }}, 0) * 100
<span style="color: #0000ff;">{% endmacro %}
</span>9. Round-Off Macro
This macro rounds off a specified column to a desired number of decimal places. It’s helpful when you want to maintain consistency in the number of decimal places across your models.
{% macro round_off(column, decimal_places) %}
ROUND({{ column }}, {{ decimal_places }})
{% endmacro %}
10. Age Calculation Macro
This macro calculates the age (in years) based on a specified date column. It’s useful in numerous scenarios where age is a relevant factor.
{% macro calculate_age(date_column) %}
DATE_PART('year', AGE({{ date_column }}))
{% endmacro %}
Get more useful articles on dbt
