Salesforce Account Hierarchy with Recursive CTEs

Enterprise Salesforce orgs use the self-referential ParentAccountId field to model corporate hierarchies. A subsidiary rolls up to a regional entity, which rolls up to a global parent. Finance teams need this hierarchy resolved to answer questions like “what’s our total revenue across the entire Acme Corp family?” — a question that requires summing opportunities across the parent account and all its descendants.

The ParentAccountId creates a tree structure in a single table. Each account can have one parent, and that parent can have its own parent, up to an arbitrary depth. Resolving this in SQL requires a recursive CTE.

The Recursive CTE Pattern

BigQuery supports recursive CTEs (as of early 2023), making this straightforward. The pattern has two parts: an anchor query that finds top-level accounts (no parent), and a recursive query that walks down the tree joining children to parents.

-- int__account_hierarchy.sql
WITH RECURSIVE

account_tree AS (
    -- Anchor: top-level accounts (no parent)
    SELECT
        account__id,
        account__name,
        account__parent_id,
        account__id AS account__ultimate_parent_id,
        account__name AS account__ultimate_parent_name,
        0 AS account__hierarchy_level
    FROM {{ ref('base__salesforce__account') }}
    WHERE account__parent_id IS NULL

    UNION ALL

    -- Recursive: child accounts
    SELECT
        child.account__id,
        child.account__name,
        child.account__parent_id,
        parent.account__ultimate_parent_id,
        parent.account__ultimate_parent_name,
        parent.account__hierarchy_level + 1
    FROM {{ ref('base__salesforce__account') }} AS child
    INNER JOIN account_tree AS parent
        ON child.account__parent_id = parent.account__id
)

SELECT
    account__id,
    account__name,
    account__parent_id,
    account__ultimate_parent_id,
    account__ultimate_parent_name,
    account__hierarchy_level
FROM account_tree

The output gives you every account with its account__ultimate_parent_id — the top of its hierarchy chain — and its account__hierarchy_level (0 for top-level accounts, 1 for their direct children, 2 for grandchildren, and so on).

How the Recursion Works

The anchor query selects all accounts where account__parent_id IS NULL. These are the roots of the tree — accounts with no parent. For each root, its ultimate parent is itself (hence account__id AS account__ultimate_parent_id), and its hierarchy level is 0.

The recursive query joins base__salesforce__account (as child) to the already-resolved account_tree (as parent) on child.account__parent_id = parent.account__id. For each child, it inherits the parent’s account__ultimate_parent_id and increments the hierarchy level by 1.

BigQuery executes this iteratively: first pass finds all roots, second pass finds their direct children, third pass finds grandchildren, and so on until no new rows are produced. For most Salesforce orgs, hierarchies are 3-5 levels deep, so the recursion terminates quickly.

Revenue Rollup

The primary use case for the resolved hierarchy is rolling up revenue to the ultimate parent. Finance teams need to know that “Acme Corp” has $5M in total pipeline across Acme Corp, Acme Europe, Acme Japan, and Acme Australia — even though each subsidiary has its own Account record and its own Opportunities.

-- mrt__sales__account_family_revenue.sql
WITH

hierarchy AS (
    SELECT
        account__id,
        account__ultimate_parent_id,
        account__ultimate_parent_name
    FROM {{ ref('int__account_hierarchy') }}
),

opportunities AS (
    SELECT
        opportunity__account_id,
        opportunity__amount,
        opportunity__is_won,
        opportunity__is_closed
    FROM {{ ref('base__salesforce__opportunity') }}
)

SELECT
    hierarchy.account__ultimate_parent_id,
    hierarchy.account__ultimate_parent_name,
    SUM(opportunities.opportunity__amount) AS family__total_pipeline,
    SUM(CASE WHEN opportunities.opportunity__is_won
        THEN opportunities.opportunity__amount ELSE 0
    END) AS family__won_revenue,
    COUNT(DISTINCT hierarchy.account__id) AS family__account_count
FROM hierarchy
INNER JOIN opportunities
    ON hierarchy.account__id = opportunities.opportunity__account_id
GROUP BY 1, 2

This produces one row per corporate family with total pipeline and won revenue summed across all subsidiaries.

Edge Cases

Circular References

Salesforce doesn’t enforce acyclicity in ParentAccountId. A bad admin or data import can create circular references: Account A’s parent is Account B, and Account B’s parent is Account A. Recursive CTEs in BigQuery will hit the iteration limit (default 500) and fail.

Protect against this with an iteration limit in your model:

-- Add a safety check in the recursive query
WHERE parent.account__hierarchy_level < 20  -- no real hierarchy is 20 levels deep

If this filter ever removes rows, investigate the source data. A hierarchy deeper than 10 levels is unusual; deeper than 20 almost certainly indicates bad data.

Orphaned Accounts

Accounts where account__parent_id references a parent that doesn’t exist in the Account table (because it was deleted, for example) won’t appear in the recursive CTE output. The INNER JOIN to account_tree requires the parent to exist. These orphaned accounts silently disappear from your hierarchy model.

Handle this by running a data quality test:

models:
  - name: int__account_hierarchy
    tests:
      - dbt_utils.equal_rowcount:
          compare_model: ref('base__salesforce__account')

If the hierarchy model has fewer rows than the base model, you have orphaned accounts that need investigation.

Accounts Without Parents That Should Have Them

Not every account without a parent is a true top-level entity. Some accounts simply haven’t been linked to their parent by the sales team. The hierarchy model treats them as independent top-level accounts, which inflates your “corporate family” count and understates revenue for families that have unlinked subsidiaries.

This is a data quality issue, not a modeling issue. Flag accounts with no parent that have characteristics suggesting they should be linked (e.g., similar names, same domain, same billing address) and route them to your Salesforce admin for cleanup.

Materialization

This model should be materialized as a table, not a view. Recursive CTEs are compute-intensive, and the hierarchy changes infrequently (accounts get reparented rarely). Rebuilding the table once per dbt run is much cheaper than executing the recursive query every time a downstream model reads from it.

If your Account table is very large (100K+ accounts), incremental materialization is possible, but detecting which hierarchy paths changed is complex. Full rebuild once per run is the simpler approach for most orgs.

HubSpot Comparison

HubSpot has no native account hierarchy concept. The association model supports parent-child company relationships through associations, but there is no ParentCompanyId field and no built-in hierarchy structure. Building hierarchy rollups from HubSpot association data is more complex than the Salesforce self-referential pattern.