Skip to content

Org Hierarchy Bridge

Table Description

This bridge table establishes hierarchical relationships between organizations by capturing parent-child organization mappings across all levels of the organizational structure. It includes depth tracking, hierarchy position flags, and organization type information to support hierarchical analytics, rollup reporting, and organizational tree traversal queries.

Grain: One row per parent-child organization relationship across the entire organizational hierarchy.

Table Type: Bridge/Dimension

Scope: Edge, EdgeEx, MyPath, and all education platforms with organizational hierarchies

Key Business Uses: - Enable hierarchical rollup queries for revenue, enrollment, and performance analytics - Support organizational tree navigation for user interface displays - Track multi-level organizational relationships (districts → schools → classrooms) - Analyze organization-specific metrics across hierarchy levels - Support account management and territory analysis across org hierarchies - Enable compliance reporting across organizational structures

Primary Relationships: - Links to ORG_DIM via PARENT_ORG_KEY for parent organization details - Links to ORG_DIM via CHILD_ORG_KEY for child organization details - Joins with fact tables through organization keys for hierarchical aggregation

Table Overview

This table contains 7 columns.

Quick Navigation

Column Definitions

parent_org_key

Business Definition

The PARENT_ORG_KEY column represents the unique key of the parent organization in a hierarchical parent-child relationship. This is a foreign key that links to the ORG_DIM table and represents the ancestor organization at any level in the hierarchy (could be immediate parent or higher-level ancestor). It is used in hierarchical queries to trace organizational ancestry and enable rollup reporting across multiple organizational levels.

Data Type

NUMBER

Nullable

No

Last Updated

2025-01-23T00:00:00 by MARKDOWN_IMPORT

child_org_key

Business Definition

The CHILD_ORG_KEY column represents the unique key of the child organization in a hierarchical parent-child relationship. This is a foreign key that links to the ORG_DIM table and represents the descendant organization at any level in the hierarchy (could be immediate child or deeper descendant). It is used in hierarchical queries to trace organizational descendants and enable drilling down from higher organizational levels to lower levels in reporting and analysis.

Data Type

NUMBER

Nullable

No

Last Updated

2025-01-23T00:00:00 by MARKDOWN_IMPORT

depth_from_parent

Business Definition

The DEPTH_FROM_PARENT column represents the number of hierarchical levels between the parent and child organization, calculated as the distance in the organizational tree. A value of 0 indicates the same organization, 1 indicates an immediate parent-child relationship, 2 indicates a grandparent-child relationship, and so on. This column is critical for hierarchical analytics, allowing analysts to aggregate metrics at specific levels of the organizational structure and perform multi-level rollup calculations.

Data Type

NUMBER

Nullable

No

Last Updated

2025-01-23T00:00:00 by MARKDOWN_IMPORT

is_highest_parent

Business Definition

The IS_HIGHEST_PARENT column is a boolean flag indicating whether the parent organization in a given row is a root organization (has no parent itself) in the organizational hierarchy. A value of TRUE indicates that the parent_org_key organization is at the top level of the hierarchy (typically a District or Enterprise). This flag is used to identify top-level organizations for hierarchy traversal and to distinguish between different levels of hierarchical relationships.

Data Type

BOOLEAN

Nullable

No

Last Updated

2025-01-23T00:00:00 by MARKDOWN_IMPORT

is_lowest_child

Business Definition

The IS_LOWEST_CHILD column is a boolean flag indicating whether the child organization in a given row is a leaf organization (has no children itself) in the organizational hierarchy. A value of TRUE indicates that the child_org_key organization is at the bottom level of the hierarchy with no subordinate organizations (typically a Classroom or final operational unit). This flag is used to identify terminal organizations for rollup calculations and to distinguish between intermediate and leaf nodes in the organizational tree.

Data Type

BOOLEAN

Nullable

No

Last Updated

2025-01-23T00:00:00 by MARKDOWN_IMPORT

dw_created_on

Business Definition

The DW_CREATED_ON column represents the date and time in UTC when the org hierarchy bridge record was generated and loaded into the data warehouse. Since this table is rebuilt on every dbt run, this timestamp reflects the most recent pipeline execution. This column is used for data freshness monitoring, change tracking, and auditing to ensure analytical data reflects current organizational structures.

Data Type

TIMESTAMP_NTZ

Nullable

No

Last Updated

2025-01-23T00:00:00 by MARKDOWN_IMPORT

dw_created_by

Business Definition

The DW_CREATED_BY column represents the identifier of the user or process that created the org hierarchy bridge record in the data warehouse. For this automatically-rebuilt bridge table, this value will be the system account or process name executing the dbt pipeline (e.g., "DBT_SERVICE_ACCOUNT"). This column is used for data lineage, auditing, and troubleshooting to track which system or process is responsible for generating the hierarchy data.

Data Type

TEXT

Nullable

No

Last Updated

2025-01-23T00:00:00 by MARKDOWN_IMPORT