“Maximum Recursion Depth Exceeded” Error in dbt Macros
If you’re working with dbt (data build tool) and building reusable macros or packages, you might have run into a confusing error:
maximum recursion depth exceeded in comparison
This error can be especially frustrating because it often appears when you’re trying to do something that seems simple, like using variables to make your code more flexible. In this article, we’ll break down what this error means, why it happens, and how you can fix it.
What is dbt and Why Use Macros?
Before we dive into the error, let’s quickly recap what dbt is and why macros are useful.
- dbt is a tool that helps you transform data in your data warehouse using SQL and a bit of Jinja (a templating language).
- Macros in dbt are like reusable functions. You can write a macro once and use it in many models, making your code DRY (Don’t Repeat Yourself) and easier to maintain.
- Packages let you share macros and models across different dbt projects.
The Scenario: Using Variables in dbt Macros
Let’s say you want to write a macro that generates a SQL query for the last N days of data. You want to make it flexible, so you use variables for things like the database catalog and schema names.
Here’s a simplified version of what you might do:
-- In your model.sql
{% set catalog = var('my_catalog') %}
{% set schema = var('my_schema') %}
select * from {{ catalog }}.{{ schema }}.my_tableAnd in your macro:
{% macro get_table_name(catalog, schema, table) %}
{{ catalog }}.{{ schema }}.{{ table }}
{% endmacro %}You call the macro like this:
select * from {{ get_table_name(catalog, schema, 'my_table') }}So far, so good!
The Problem: The Recursion Error
Now, imagine you want to make your code even more reusable. You put your macro in a dbt package so you can use it in other projects. But when you try to use variables (like var('my_catalog')) in the parent project, you get this error:
maximum recursion depth exceeded in comparison
What’s going on?
Let’s explain this in plain English.
- Recursion means something is calling itself over and over, like a loop that never ends.
- Maximum recursion depth exceeded means Python (which dbt is built on) has a limit to how many times something can call itself. If you go over that limit, you get this error.
But why would using a variable cause recursion?
The Core Issue: Variable Resolution and Circular References
Here’s what’s happening under the hood:
- dbt Macros and Variables:
When you use var('my_catalog'), dbt looks for that variable in your project’s configuration.
- Packages and Parent Projects:
If you’re using a macro from a package in another project, dbt tries to resolve the variable in the parent project’s settings.
- Missing Variables:
If the variable isn’t defined in the parent project, dbt keeps looking… and looking… and looking. It gets stuck in a loop, trying to find the variable, and eventually hits the recursion limit.
- Circular Calls:
Sometimes, the way macros and variables are set up can accidentally cause dbt to call the same logic over and over, especially if you use var() inside a macro that’s called by another macro that also uses var(). This creates a circular reference.
In short:
If dbt can’t find the variable you’re asking for, or if your macros are set up in a way that references themselves (directly or indirectly), you get the recursion error.
A Simple Analogy
Imagine you’re looking for your friend’s house, but you don’t know the address. You ask your neighbour, who says, “Ask the next neighbour.” You keep going from house to house, but nobody knows the address—they all tell you to ask someone else. Eventually, you get tired and give up. That’s what dbt is doing: it keeps looking for the variable, but never finds it, so it gets stuck in an endless loop.
How to Fix the Error
Let’s walk through the steps to fix this error, with simple code examples.
Always Define Your Variables in the Parent Project
If you’re using a macro from a package, make sure any variables it needs are defined in the parent project’s dbt_project.yml file.
# dbt_project.yml in your main project
vars:
my_catalog: analytics
my_schema: eventsNow, when you use var('my_catalog') in your macro, dbt knows where to find it.
Don’t Use var() Inside SQL Strings
It’s tempting to write something like this:
select * from {{ var('my_catalog') }}.{{ var('my_schema') }}.my_tableBut if you do this inside a macro that’s called by another macro, and the variable isn’t defined, you can trigger recursion.
Better:
Resolve the variable at the top of your model or macro, and then use the value.
{% set catalog = var('my_catalog') %}
{% set schema = var('my_schema') %}
select * from {{ catalog }}.{{ schema }}.my_tableAvoid Circular Macro Calls
Make sure your macros don’t call themselves, directly or indirectly. For example, don’t do this:
{% macro macro_a() %}
{{ macro_b() }}
{% endmacro %}
{% macro macro_b() %}
{{ macro_a() }}
{% endmacro %}This will cause infinite recursion.
Use Default Values for Variables
If you want your macro to work even if the variable isn’t defined, provide a default value:
{% set catalog = var('my_catalog', 'default_catalog') %}This way, dbt won’t keep looking if the variable is missing.
Troubleshooting Checklist
- Is every variable used in your macro defined in the parent project?
- Are you resolving variables at the top of your model or macro, not inside SQL strings?
- Are you avoiding circular macro calls?
- Are you providing default values for variables?
If you answer “yes” to all of these, you should be safe from the recursion error!
Final Thoughts
The “maximum recursion depth exceeded in comparison” error in dbt is almost always caused by missing variables or circular references in your macros. The fix is simple:
- Always define your variables in the parent project.
- Resolve variables before using them in SQL strings.
- Avoid circular macro calls.
- Use default values for variables.
By following these best practices, you’ll write more robust, reusable dbt macros and avoid frustrating errors. Happy modeling!
Further Reading
Discussion