Skip to main content

Detecting Circular Dependencies

One of the most important concepts in dbt is understanding the dependency graph.

dbt does not simply execute SQL files one after another. Instead, it studies the relationships created by:

{{ ref('model_name') }}

and:

{{ source('source_name', 'table_name') }}

and builds a DAG — a Directed Acyclic Graph.

For example:

RAW
↓
stg_orders
↓
int_order_details
↓
fct_orders
↓
customer_sales

This is valid because the dependency flows in one direction.

But if a model eventually depends on an earlier model again, the graph becomes a cycle. That is a circular dependency.

What is a DAG?​

A DAG means:

Directed Acyclic Graph

Directed​

Dependencies have a direction.

stg_orders -> int_order_details -> fct_orders

Acyclic​

There is no loop.

A -> B -> C -> D

This is valid.

But this is invalid:

A -> B -> C -> A

The dependency eventually comes back to the starting point.

Why this matters in dbt​

A dbt project is built from models and dependencies. dbt must know the build order before execution begins.

If the dependency graph contains a cycle, there is no valid way to determine which model should run first.

A real-world e-commerce example​

Imagine fct_orders depends on dim_customers, and dim_customers also depends on fct_orders.

That creates a circular dependency:

fct_orders -> dim_customers -> fct_orders

This is a serious problem because dbt cannot safely execute either model first.

How circular dependencies happen​

They usually happen when two layers are designed in the wrong order.

For example:

-- fct_orders.sql
SELECT
o.order_id,
o.customer_id,
c.first_name,
c.last_name
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('dim_customers') }} c
ON o.customer_id = c.customer_id

and:

-- dim_customers.sql
SELECT
c.customer_id,
c.first_name,
c.last_name,
SUM(f.order_amount) AS lifetime_sales
FROM {{ ref('stg_customers') }} c
LEFT JOIN {{ ref('fct_orders') }} f
ON c.customer_id = f.customer_id
GROUP BY
c.customer_id,
c.first_name,
c.last_name

Now the dependency is circular.

Why dbt fails​

dbt must determine the build order, but here the dependency loop never ends:

Build dim_customers
→ needs fct_orders
Build fct_orders
→ needs dim_customers
Build dim_customers again

There is no valid execution start point.

The correct design​

The fix is usually to break the loop by creating a shared intermediate model.

Instead of:

dim_customers -> fct_orders -> dim_customers

use:

stg_customers -> int_customer_metrics -> dim_customers
|
v
fct_orders

Now the flow is one-directional and valid.

Example of a valid architecture​

RAW
↓
stg_customers
↓
stg_orders
↓
int_order_details
↓
customer_metrics
├──> dim_customers
└──> fct_orders

This structure is clean because each model depends only on upstream models.

Best practice​

When you see a loop in the dbt graph, ask:

  • Is one model depending on a downstream model?
  • Are both models sharing a common calculation?
  • Should the logic move to an intermediate model?

In most cases, the right fix is to create a shared upstream model and let both downstream models depend on it.

Summary​

A circular dependency occurs when the DAG loops back on itself.

dbt requires a directed acyclic graph.

This is why the most common fix is:

  • keep sources → staging → intermediate → marts flowing in one direction
  • extract shared logic into intermediate models
  • avoid dimensions depending on facts or facts depending on dimensions in a loop

This is one of the fundamental design rules in a healthy dbt project. Let's use our BEEMS TECHNE E-Commerce project.

Suppose the Sales team asks:

"I want fct_orders to contain customer information."

The developer creates:

-- fct_orders.sql

SELECT
o.order_id,
o.customer_id,
o.order_date,
o.order_status,
c.first_name,
c.last_name,
c.country
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('dim_customers') }} c
ON o.customer_id = c.customer_id

This creates:

stg_orders
↓
fct_orders

dim_customers
↓
fct_orders

So far, everything is fine.


5. Then the Customer Team Has a Requirement

Later, the Customer Analytics team says:

"We want dim_customers to contain customer lifetime sales."

For example:

CUSTOMER_ID
CUSTOMER_NAME
COUNTRY
TOTAL_ORDERS
LIFETIME_SALES

The developer decides to calculate lifetime sales from fct_orders.

They write:

-- dim_customers.sql

SELECT
c.customer_id,
c.first_name,
c.last_name,
c.country,
SUM(f.order_amount) AS lifetime_sales
FROM {{ ref('stg_customers') }} c
LEFT JOIN {{ ref('fct_orders') }} f
ON c.customer_id = f.customer_id
GROUP BY
c.customer_id,
c.first_name,
c.last_name,
c.country

Now look at the dependency.

fct_orders depends on:

dim_customers

And dim_customers depends on:

fct_orders

We have:

dim_customers
↓
fct_orders
↓
dim_customers

That's a circular dependency.


6. Why Can't dbt Build This?

dbt needs to determine an execution order.

It would need to do:

Build dim_customers
↓
But dim_customers needs fct_orders
↓
Build fct_orders
↓
But fct_orders needs dim_customers
↓
Build dim_customers
↓
But...

The process never has a valid starting point.

This is why dbt requires the dependency graph to remain acyclic.


7. The Problem Is Usually the Design

Notice something important.

The SQL statements themselves might look perfectly reasonable.

The problem isn't necessarily:

SELECT ...

The problem is:

Who should depend on whom?

This is an architecture/design problem.

A good data engineer doesn't try to solve this by changing SQL syntax.

Instead, they redesign the dependency.


8. The Wrong Design

Our original design is:

stg_customers
↓
dim_customers
↓
fct_orders
↓
dim_customers

This is a loop.

Or visually:

       ┌────────────────┐
│ ↓
stg_customers → dim_customers
│
↓
fct_orders
│
└──────────→ dim_customers

This should be avoided.


9. The Correct Design

Instead of calculating customer lifetime sales from fct_orders inside dim_customers, create an intermediate model.

For example:

stg_customers
│
▼
int_customer_orders
│
▼
customer_metrics
│
├──────────────┐
▼ ▼
dim_customers fct_orders

Now both models can use a common upstream model.

There is no loop.


10. Better E-Commerce Architecture

A cleaner design could be:

                     RAW
│
┌─────────────┼─────────────┐
▼ ▼ ▼
stg_customers stg_orders stg_order_items
│ │ │
│ └──────┬──────┘
│ ▼
│ int_order_details
│ │
└────────────┬───────┘
▼
customer_metrics
│
┌──────┴──────┐
▼ ▼
dim_customers fct_orders
│ │
└──────┬──────┘
▼
customer_sales

Notice the important change:

dim_customers
↑
│
customer_metrics
│
↓
fct_orders

Both models depend on the shared upstream logic.

They do not depend on each other.


11. The Common Intermediate Model Pattern

This is one of the most useful ways to solve circular dependency problems.

Suppose:

Model A needs logic X

Model B also needs logic X

Don't make:

A → B

or:

B → A

Instead create:

       Shared Logic
/ \
↓ ↓
A B

In dbt:

              int_customer_metrics
/ \
/ \
▼ ▼
dim_customers fct_orders

This is a much healthier DAG.


12. Another Real-World Example

Suppose the product team creates:

dim_products

They want:

product_id
product_name
category
total_sales

So they use:

{{ ref('fct_orders') }}

Later, the Sales team creates:

fct_orders

and wants product information:

{{ ref('dim_products') }}

Now we have:

dim_products
↓
fct_orders
↓
dim_products

Again:

Circular dependency.


13. How Should We Fix It?

Move the shared product/order calculation into an intermediate model.

For example:

stg_products
│
├───────────────┐
│ │
▼ ▼
dim_products int_order_details
│
▼
fct_orders

If product metrics are needed:

stg_products
│
▼
int_product_sales
│
├───────────────┐
▼ ▼
dim_products product_sales_mart

Again, we have one-directional dependencies.


14. How dbt Detects the Problem

When dbt parses the project, it examines the dependencies created by:

{{ ref(...) }}

and:

{{ source(...) }}

If those relationships create a cycle, dbt cannot construct a valid DAG.

For example:

fct_orders
↓
dim_customers
↓
fct_orders

dbt reports a dependency cycle rather than attempting to execute the models in an invalid order.


15. Important: This Is Not a Snowflake Problem

The circular dependency exists at the dbt dependency graph level.

It is not primarily a Snowflake table problem.

For example, this isn't about:

Snowflake
↓
Table A
↓
Table B

The issue is that dbt's model dependencies say:

Model A
↓
Model B
↓
Model A

dbt needs to know the build order before executing the transformations.


16. How to Prevent Circular Dependencies

Follow these design principles.

Rule 1 — Keep dependencies flowing downward​

Prefer:

Source
↓
Staging
↓
Intermediate
↓
Fact / Dimension
↓
Mart

Avoid:

Mart
↓
Dimension
↓
Fact
↓
Staging

Rule 2 — Dimensions should not normally depend on facts​

For example, avoid:

dim_customers
↓
fct_orders
↓
dim_customers

Instead:

stg_customers
↓
dim_customers

stg_orders
↓
fct_orders

Rule 3 — Move shared logic upstream​

If both models need the same calculation:

Model A
Model B

create:

int_shared_logic

and then:

          int_shared_logic
/ \
↓ ↓
Model A Model B

Rule 4 — Don't build business metrics into the wrong layer​

For example:

dim_customers

should primarily represent customer attributes.

Metrics such as:

lifetime_sales
total_orders
average_order_value

may be better calculated in:

customer_metrics

or:

customer_360

depending on the project design.


17. Hands-On Exercise

Create the following intentionally broken models.

Before: Broken dependency structure​

fct_orders
↓
dim_customers
↓
fct_orders

This is the problem: the fact table depends on the dimension, and the dimension depends back on the fact table.

fct_orders.sql​

{{ config(materialized='table') }}

SELECT
o.order_id,
o.customer_id,
o.order_date,
c.first_name,
c.last_name
FROM {{ ref('stg_orders') }} o
JOIN {{ ref('dim_customers') }} c
ON o.customer_id = c.customer_id

dim_customers.sql​

{{ config(materialized='table') }}

SELECT
c.customer_id,
c.first_name,
c.last_name,
SUM(f.order_amount) AS lifetime_sales
FROM {{ ref('stg_customers') }} c
LEFT JOIN {{ ref('fct_orders') }} f
ON c.customer_id = f.customer_id
GROUP BY
c.customer_id,
c.first_name,
c.last_name

Run:

dbt build

You should encounter a dependency cycle because the project graph loops back on itself.

After: Correct dependency structure​

stg_customers
│
▼
int_customer_metrics
│
┌──┴─────┬──────┐
▼ ▼ ▼
dim_customers customer_360

stg_orders
│
▼
int_order_details
│
▼
fct_orders

This version breaks the cycle by moving shared logic into an intermediate model.


18. Fix the Exercise

Create the shared intermediate model first:

models/silver/int_customer_metrics.sql

int_customer_metrics.sql​

{{ config(materialized='view') }}

SELECT
c.customer_id,
c.first_name,
c.last_name,
COUNT(DISTINCT o.order_id) AS total_orders,
SUM(o.order_amount) AS lifetime_sales,
AVG(o.order_amount) AS avg_order_value
FROM {{ ref('stg_customers') }} c
LEFT JOIN {{ ref('stg_orders') }} o
ON c.customer_id = o.customer_id
GROUP BY
c.customer_id,
c.first_name,
c.last_name

Then redesign the downstream models so they depend on the intermediate model instead of on each other.

dim_customers.sql​

{{ config(materialized='table') }}

SELECT
customer_id,
first_name,
last_name,
total_orders,
lifetime_sales,
avg_order_value
FROM {{ ref('int_customer_metrics') }}

customer_360.sql​

{{ config(materialized='table') }}

SELECT
customer_id,
first_name,
last_name,
total_orders,
lifetime_sales,
avg_order_value
FROM {{ ref('int_customer_metrics') }}

int_order_details.sql​

{{ config(materialized='view') }}

SELECT
o.order_id,
o.customer_id,
o.order_date,
o.order_amount,
i.product_id,
i.quantity
FROM {{ ref('stg_orders') }} o
LEFT JOIN {{ ref('stg_order_items') }} i
ON o.order_id = i.order_id

fct_orders.sql​

{{ config(materialized='table') }}

SELECT
order_id,
customer_id,
order_date,
order_amount
FROM {{ ref('int_order_details') }}

Now the dependency structure is one-directional and the graph is valid.


19. A Good Production DAG

For our BEEMS TECHNE E-Commerce project, a clean architecture could look like:

                         RAW
│
┌───────────────┼────────────────┐
▼ ▼ ▼
stg_customers stg_orders stg_products
│ │ │
│ ▼ │
│ int_order_details │
│ │ │
└───────┬───────┴────────────────┘
▼
customer_metrics
│
┌───────┴────────┐
▼ ▼
dim_customers fct_orders
│ │
└────────┬───────┘
▼
customer_sales_mart

The important characteristic is:

                 ↓
↓
↓
One-way
↓
↓
↓

There is no path that eventually comes back to an upstream model.


20. Circular Dependency Checklist

Before adding a ref(), ask:

Question 1​

Is this model already upstream of the model I'm building?

If yes, be careful.

Question 2​

Could this new ref() create a path back to this model?

If yes, you may be creating a cycle.

Question 3​

Is this logic actually shared by multiple models?

If yes, consider creating an intermediate model.

Question 4​

Am I making a dimension depend on a fact?

If yes, review the design.


Summary

A dbt project is a dependency graph.

Source
↓
Staging
↓
Intermediate
↓
Fact / Dimension
↓
Mart

A valid dbt graph is:

Directed
+
Acyclic
=
DAG

A circular dependency looks like:

A → B → C → A

In our e-commerce example:

dim_customers
↓
fct_orders
↓
dim_customers

This happens when models are designed to depend on each other.

The solution is usually not a SQL trick.

Instead:

Identify shared logic
↓
Move it upstream
↓
Create intermediate model
↓
Let downstream models depend on it

The ideal pattern is:

                 int_shared_logic
/ \
↓ ↓
Model A Model B

instead of:

             Model A
↓
Model B
↓
Model A

Key takeaway:

In dbt, dependencies should flow in one direction. If two models need each other, the model design usually needs to be refactored.