Utilising dbt macros for sql readability

In dbt (core or cloud), you can utilise macros - a block of Jinja code to produce a text output which can be placed in a sql query - to help with many sql query writing tasks including for documentation.

Macros are saved in any folder path listed in your macro-paths (e.g. in your dbt project yml)

It is best practice to include your references or sources at the top of your sql files in dbt to make it clear to a reader what the dependencies of the query are:

with ref as (

    select * from {{ ref('model_name') }}

),

select * from ref

In this example, it is clear that model_name is the only dependency. This can quickly get cumbersome, hard to read, and awkward to write if you are referencing many models - you require one cte per referenced model.

A macro can be utilised to write the ctes for you!

The cte macro

I saved the following macro, create_dependencies_as_ctes.sql into my macros path:

{% macro create_dependencies_as_ctes(ref_names, cte_names) -%}

WITH
{%- for ref_name in ref_names -%}
    {{ "," if not loop.first else "" }}

    {{ cte_names[loop.index0] }} AS (
        SELECT * 
        FROM {{ ref(ref_name) }}
    )
{%- endfor %}


{%- endmacro %}

This macro dynamically generates a series of cte statements in a sql query using dbt's Jinja templating engine. It maps a list of dbt model references     (`ref_names`) to corresponding CTE names (`cte_names`) and formats them with correct comma separation. It works by looping through the lists given in the arguments.

Here is a section of my documentation for the macro, including an example usage:

Parameters:
    - ref_names: A list of dbt model names (as strings) to be referenced using the `ref()` function.
    - cte_names: A list of CTE alias names (as strings) corresponding to each model in `ref_names`.

    Output:
    Generates a properly formatted `WITH` clause with one CTE per ref_name → cte_name mapping.

    Example usage:
    {% set ref_names = ['orders', 'customers'] %}
    {% set cte_names = ['orders_cte', 'customers_cte'] %}
    {{ create_dependencies_as_ctes(ref_names, cte_names) }}

    Output:
    WITH
    orders_cte AS (
        SELECT *
        FROM {{ ref('orders') }}
    ),
    customers_cte AS (
        SELECT *
        FROM {{ ref('customers') }}
    )

Example in a mart

Using the macro makes it clear what the dependencies are, and what they have been aliased as - even when there might be many - at the top of the query. Here is an example using three tables:

The macro at the top is then decompiled into the following: