In this lesson, you will learn how to structure organizational or categorical data of variable depth using the Adjacency List pattern.
Understanding Recursive Hierarchies
Often in data engineering, you encounter entities that reference themselves. Consider an organizational chart: a CEO manages VPs, VPs manage Directors, and Directors manage Managers. You don't know in advance how many levels deep this tree goes. A standard flat table won't work, but an Adjacency List solves this by storing the relationship directly within the dimension table.
The Adjacency List Structure
The core idea is to add a parent_id column to your dimension table. This column contains the surrogate key of the record's immediate supervisor or parent category. If a record is at the top of the hierarchy (like the CEO), the parent_id is simply NULL.
For a product category dimension, your table might look like this:
| product_cat_key | category_name | parent_cat_key |
|---|---|---|
| 1 | Electronics | NULL |
| 2 | Computers | 1 |
| 3 | Laptops | 2 |
| 4 | Gaming Laptops | 3 |
In this structure, "Gaming Laptops" (key 4) points to "Laptops" (key 3), which points to "Computers" (key 2), which points to the top-level "Electronics" (key 1).
Why Use This Approach?
The Adjacency List is elegant because it is simple to maintain during ETL processes. If a manager moves to a different department, you only need to update the parent_id on that single row. There is no need to restructure the entire table or move deep sub-trees, as the relationship is defined by the local link, not by hardcoded levels like "Level1_Name" or "Level2_Name."
While simple to write, remember that querying this requires recursive Common Table Expressions (CTEs) or iterative joins to "walk" the tree from a leaf node to the root. It is the fundamental building block for representing any structure where the depth is unknown or prone to change.
Key Takeaway: The Adjacency List models variable-depth hierarchies by storing a self-referencing parent_id key in each dimension record, allowing for flexible, easy-to-update parent-child relationships.