Insight: Recursive View Dependencies in Redshift? Here's the Workaround

Insight:  Recursive View Dependencies in Redshift? Here's the Workaround

Why This Matters

If you’ve ever tried to trace dependencies between views in Amazon Redshift, you’ve probably run into a wall. Redshift doesn’t support recursive common table expressions (CTEs) like Postgres, which makes it tricky to figure out how one view builds on another—especially in large, layered systems.

That’s exactly the situation our teammate Prasad ran into. He needed to understand which views depended on others, ideally tracing the full chain. And while there’s no native recursive function in Redshift, there is a practical way to get the job done—with SQL, Python, or even a SQL-only emulation for locked-down environments.

This post walks through the solution, with code, explanation, and a downloadable GitHub Gist to make it drop-dead easy.


Step 1: Find Direct Dependencies with SQL

Redshift tracks view relationships in its internal catalog. The following query shows which views directly depend on which other views:

SQL

SELECT 
    dependent_view.relname AS dependent_view,
    source_view.relname AS source_view
FROM 
    pg_depend d
JOIN 
    pg_rewrite r ON d.objid = r.oid
JOIN 
    pg_class dependent_view ON r.ev_class = dependent_view.oid
JOIN 
    pg_class source_view ON d.refobjid = source_view.oid
WHERE 
    dependent_view.relkind = 'v' AND source_view.relkind = 'v';   

Example output:

dependent_view	source_view
v_mid	v_base
v_top	v_mid

This tells us that v_mid depends on v_base, and v_top depends on v_mid. That’s one level deep—now we’ll go further.


Step 2: Build the Tree with Python

To follow the full dependency chain, we use a small Python script that pulls all view relationships and prints a tree. The updated version includes lines in the style of the classic Linux tree command, which makes the structure pop:

Bash

v_base
└── v_mid
    └── v_top   

For larger graphs, the output remains clean and readable:

Bash

v_base
├── v_mid1
│   └── v_top1
└── v_mid2
    └── v_top2  

The script also detects cycles and will mark them with cycle to prevent infinite loops. This makes it reliable even in complex or misconfigured environments.

Step 3: Bonus for Locked-Down Teams — SQL-Only Workaround

If your team can’t use Python but still wants to trace the chain, we’ve included an experimental workaround in SQL. It uses temp tables and repeated inserts to simulate recursion. It’s not elegant—but it works. Perfect for audit situations where external tooling is off the table.


Step 4: Generate a Graphviz Diagram (Bonus)

Want to visualize the full hierarchy as a proper diagram? We’ve also included a small generator script that outputs a .dot file for use with Graphviz. The result is a clear, professional graph with arrows representing view dependencies.

Example output in DOT format:

Dot

digraph ViewGraph {
    "v_base" -> "v_mid";
    "v_mid" -> "v_top";
}   

You can open the .dot file in any Graphviz-compatible tool, including online viewers or native renderers. It's a great way to create visual architecture docs or dependency maps.


Everything You Need, Ready to Download

We’ve bundled the full solution into a GitHub Gist that includes:

• view_dependency_query.sql — the core SQL
• resolve_view_tree.py — the full Python script (with tree-style output)
• generate_view_graph_dot.py — Graphviz DOT file generator
• sample_views.sql — for testing
• recursive_sql_emulation.sql — if you’re stuck in SQL-only land
• README.md — a clear usage guide

This is the kind of problem that catches teams off guard, especially when migrating old systems or auditing legacy code. Hopefully, this makes it painless—and maybe even a little fun.

Want us to add a visualization or Graphviz-focused walkthrough next? Let us know. We love this stuff.

Need AWS Expertise?

We're happy to help you with your AWS projects!  Feel free to contact us.

Email: info@pacificw.com

Image: Gemini