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_ordersto 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_customersto 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.