Corporate Hierarchy Modeling and Ultimate Parent Reporting
Atlas Commercial Bank uses Customer 360 for 320,000 organization records. Risk reporting needs corporate hierarchies for about 8,000 groups, up to seven levels deep, including ultimate parent. The current model has a single parent_org_id field maintained manually by 15 stewards. Acquisitions create circular ownership, such as A owns B, B owns C, and C owns A, and joint ventures have 50/50 ownership. Some holding companies have no direct customer accounts. Nightly loads bring 2 million relationship rows from three source systems with conflicting parent information. Stewards spend most of their time correcting parent_org_id, and risk reports still show inconsistent ultimate parents. The bank wants a governed hierarchy without slowing nightly loads.
1. How would you model the hierarchy in Customer 360, and why not just keep parent_org_id?
Read the worked answer
Model the hierarchy as a relationship entity between Organization records, with relationship type such as parent-of, effective dates, ownership percentage, source system, and status. Do not keep a single parent_org_id: it cannot represent joint ventures, effective-dated acquisitions, multiple relationship types, or conflicting sources. The relationship entity allows many-to-many ownership and preserves history. Use hierarchy management to visualize and edit the graph, but treat the relationships as the source of truth and derive ultimate parent as a calculated attribute for reporting. For holding companies with no customer accounts, keep them as Organization records with a type flag so the hierarchy is complete without polluting customer counts. Trade-off: more complex modeling and stewardship, but a single parent field cannot satisfy risk reporting or audit requirements.
2. How would you handle circular ownership, joint ventures, and conflicting sources operationally?
Read the worked answer
At intake, run a cycle-detection validation on the proposed parent relationship before commit; if adding the edge creates a cycle, quarantine the relationship and create a steward task instead of silently dropping it. For joint ventures, store ownership percentage and allow multiple parents; do not force a single parent. For conflicting sources, define source precedence per relationship type, but require steward review when sources disagree on parent or ownership above a threshold. Use effective dates so acquisitions do not rewrite history. Stewards should work from an exception queue, not manually key parent_org_id. Track cycle attempts and source conflicts as quality metrics. Trade-off: quarantining relationships can leave temporary gaps in the hierarchy, so risk reporting must show the last approved hierarchy and flag pending changes.
3. What approach would you take for ultimate-parent reporting performance?
Read the worked answer
Do not compute ultimate parent recursively at report runtime over 2 million relationships; that will be slow and unpredictable. Precompute a closure or ancestor table after each approved load, or materialize the resolved hierarchy into a reporting table refreshed on a schedule. Validate the computed hierarchy against a sample of known groups before publishing. Keep the operational hierarchy in Customer 360 and export the resolved edges to the reporting layer; this separates stewardship from heavy analytics. If near-real-time ultimate parent is required, refresh incrementally for changed groups rather than rebuilding all 8,000 groups. Monitor refresh duration and row counts. Trade-off: precomputation adds latency between a hierarchy change and reporting, so define an acceptable refresh window and show the as-of timestamp on reports.