The DE Toolbox: dbt

dbt is powerful, but writing DRY (Don’t Repeat Yourself) SQL requires a good macro library. Here are the macros I use on day one of a new project.

1. The Dynamic Pivot

Writing CASE WHEN statements to pivot rows into columns is tedious. This macro automates it by dynamically reading the unique values from the table.

{% macro pivot_column(table_name, column_to_pivot, value_column) %}
    -- Dynamically generates pivot SQL
    {% set results = run_query("select distinct " ~ column_to_pivot ~ " from " ~ ref(table_name)) %}
    {% if execute %}
        {% for row in results %}
            SUM(CASE WHEN {{ column_to_pivot }} = '{{ row[0] }}' THEN {{ value_column }} ELSE 0 END) as {{ row[0] | replace(' ', '_') }}_total
            {% if not loop.last %},{% endif %}
        {% endfor %}
    {% endif %}
{% endmacro %}

2. Generate Surrogate Key (The Right Way)

Always use dbt_utils.generate_surrogate_key, but I like to wrap it in a local macro so I can enforce specific null-handling logic across the entire warehouse without changing 100 model files if the business logic changes.