Look-Through Equity Ownership Analysis System Design for Tech Companies

1. From a Shareholder List to a Computable Data Model

Typical business analysis requests ask who the top shareholders are, how much they own, and who controls the company after multiple financing rounds. Although this may look like a simple leaderboard, building it as a system turns it into a hard data engineering problem.

Public information is naturally incomplete, inconsistent, and time-varying:

• Ownership percentages change with financing rounds, option exercises, and share transfers. A leaderboard is only a snapshot at one point in time.
• Economic interest, meaning dividends and liquidation rights, and voting power, meaning control, are often not the same number.
• Employee option pools may be reserved allocations or exercised shares, which have very different effects on dilution calculations.
• For indirect holdings through multi-layer funds, disclosed figures may be look-through numbers or direct holdings.

Therefore, the first design principle is: do not use a leaderboard as the data model. Use equity change events as the data model, and materialize leaderboards for any point in time as needed. All sample data below is illustrative only and does not represent the actual structure of any real company.

2. Core Data Model: Nodes, Edges, Events, and Snapshots

2.1 Four Core Entity Types

Entity	Description	Key fields
Node	Natural person, company, fund, employee pool, etc.	id, type, jurisdiction of registration, whether it belongs to an acting-in-concert group
Equity edge	Holder A holds shares in target B	target id, holder id, number of shares, effective date, expiration date, interest type
Financing event	Round, valuation, newly issued shares, lead investors	round name, date, pre-money or post-money valuation, newly issued shares
Special right	Super-voting rights, preferred conversion ratio, option pool	applicable entity, votes per share, conversion ratio, effective conditions

The most common modeling mistake is storing ownership percentage as a field. Percentage is a derived value; share count is the fact. All percentages should be calculated at a given point in time as shares / total shares, while voting power should be calculated separately as shares × votes per share / total votes. The two must never be mixed.

2.2 Point-in-Time Queries and Historical Snapshots

Equity edges must carry an effective period. To query any historical point in time, use interval overlap:

-- Query effective direct holdings at as_of
SELECT holder_id, target_id, shares
FROM edge_direct
WHERE effective_from <= :as_of
  AND (effective_to IS NULL OR effective_to > :as_of);

Every equity change should insert a new edge and close the old edge, following a temporal slowly changing dimension pattern, rather than updating rows in place. This makes it possible to answer questions such as “Since the last round, whose ownership has been diluted the most?”

3. Look-Through Calculation: Direct and Indirect Ownership

The mathematical model for penetration is a sum of path products on a weighted directed graph: entity X’s indirect ownership percentage in the final target is the sum of percentage products along all paths.

WITH RECURSIVE path AS (
    SELECT holder_id,
           shares * 1.0 / total_shares AS ratio,
           ARRAY[holder_id] AS trail
    FROM edge_direct WHERE target_id = :target_id
  UNION ALL
    SELECT e.holder_id,
           p.ratio * (e.shares * 1.0 / e.total_shares),
           p.trail || e.holder_id
    FROM path p
    JOIN edge_direct e ON e.target_id = p.holder_id
    WHERE NOT e.holder_id = ANY(p.trail)      -- Cycle pruning
      AND p.ratio > 0.00001                   -- Significance pruning
)
SELECT holder_id, SUM(ratio) AS indirect_ratio
FROM path GROUP BY holder_id;

Three engineering details must be handled:

1. Cycle pruning. Cross-shareholding and mutual holdings between parent and subsidiary companies are common in real equity graphs. Without path deduplication, recursion can expand indefinitely. A path array that records visited nodes is sufficient.
2. Numerical precision. Percentage multiplication can introduce floating-point error. After aggregation, normalization checks should be performed: the deviation between the sum of all top-level entity percentages and 1 must be less than 1e-6; otherwise, the system should alert rather than pass silently.
3. Large-graph pruning. For deeply nested fund-type shareholders, truncation by significance threshold can control computation cost, while retaining the original path for audit traceability.

4. Control Analysis: Voting Rights and Acting-in-Concert Parties

Control and ownership percentage often diverge, which is one of the most easily overlooked points in modeling.

from dataclasses import dataclass
@dataclass
class Holding:
    holder: str
    shares: float
    votes_per_share: float = 1.0   # Can be >1 in dual-class structures
def control_table(items, pool_shares=0.0, pool_votes_per_share=1.0):
    """Output both economic interest percentage and voting percentage."""
    total_shares = sum(i.shares for i in items) + pool_shares
    total_votes = (sum(i.shares * i.votes_per_share for i in items)
                   + pool_shares * pool_votes_per_share)
    rows = [
        {
            "holder": i.holder,
            "equity": round(i.shares / total_shares, 6),
            "vote": round(i.shares * i.votes_per_share / total_votes, 6),
        }
        for i in items
    ]
    if pool_shares:
        rows.append({"holder": "__OPTION_POOL__",
                     "equity": round(pool_shares / total_shares, 6),
                     "vote": round(pool_shares * pool_votes_per_share / total_votes, 6)})
    rows.sort(key=lambda r: r["vote"], reverse=True)
    return rows
# Illustrative: founder team holds 21% economic interest, but each share has 10 votes
demo = [
    Holding("founders", 21.0, votes_per_share=10.0),
    Holding("option_pool", 19.0),
    Holding("sovereign_fund_a", 8.0),
    Holding("cloud_vendor_a", 7.0),
    Holding("crossing_fund_a", 6.0),
    Holding("cloud_vendor_b", 6.0),
    Holding("chip_vendor_a", 5.0),
]
for r in control_table(demo):
    print(r)

The output illustrates a key conclusion: a founder team with 21% economic interest may have more than 75% voting power, while institutional shareholders with combined ownership of over 40% may have significantly compressed voting power. Any analysis system that outputs only ownership percentage cannot answer the question: “Who really controls this company?”

Likewise, acting-in-concert parties, meaning multiple entities that have signed a common-action agreement, need to be tagged at the entity layer. Voting power should be aggregated by agreement group, while economic interest still needs to be split back to individual entities.

5. Dilution Simulation: What the Next Round Changes

Dilution analysis is essentially a waterfall calculation: newly issued shares are allocated among investors, the option pool is expanded proportionally, and existing entities are diluted accordingly. However, the degree of voting-power dilution depends on votes per share.

Key modeling points:

• Option pool expansion basis. Whether the pre-money or post-money share capital is used as the base can lead to very different results. The calculation basis must be passed explicitly as a parameter rather than hard-coded.
• Preferred share conversion. Convertible notes and preferred shares convert into common shares at agreed ratios upon listing or trigger events. They should be modeled as conditional edges, not directly included in current share capital.
• Scenario comparison. The system should support parameter sweeps over combinations such as “new financing amount × price per share × option pool expansion ratio” and output tabulated results for business users to compare.

It is recommended to implement the simulation as a pure function: input the current snapshot and parameters, output a new snapshot, and avoid implicit state dependencies. This makes unit testing straightforward: given input share counts, assert that the sum of output percentages equals 1.

6. Data Integration and Quality Governance

When public information is mixed with internal data, quality problems tend to surface quickly. Practical governance rules include:

1. Entity disambiguation. The same shareholder may appear under multiple names, such as “Fund A,” “Fund A Phase I,” or “Fund A Management Company.” Build an alias dictionary and use a canonical identifier, such as a company registration number or fund filing number where available, as the unique key. Merge only after manual confirmation.
2. Normalization of calculation basis. Standardize on “issued total share capital” as the denominator, and reject mixed use of “ratio derived from pre-money valuation” and “actual share count.”
3. Freshness metrics. Record the last update time for each data source, and display a data-freshness label on conclusions that exceed a threshold.
4. Change audit trail. Every data correction should generate an audit record containing the operator, original value, new value, and supporting source.
5. Explainability. Any percentage must be drillable down to specific paths and sources. This is the prerequisite for business users to trust this type of system.

For scheduling, daily full snapshots plus incremental change merging are recommended. Tasks should be idempotent: repeated execution for the same date should produce the same result. Retry and alerting should connect to unified monitoring and alerting capabilities. Raw payloads and structured intermediate results should be stored in object storage and relational or graph databases respectively, with hot and cold tiering to reduce storage cost.

7. Storage and Query Selection

• Look-through queries: Use a graph database or recursive queries in a relational database. For small and medium-scale graphs with thousands of nodes, recursive CTEs are usually sufficient; graph databases should be considered for deep multi-hop scenarios.
• Point-in-time leaderboards: Use materialized views with scheduled refresh to avoid full-graph traversal on every request.
• Raw materials: Store source documents and snapshots in object storage with versioning and lifecycle management.
• Search: Build full-text indexes on key fields and support combined filtering by entity name, financing round, and time range.

8. Permissions, Compliance, and Observability

Equity data is highly sensitive, so the default design should enforce least-privilege visibility:

• Field-level masking: unauthorized roles see only range-based ownership percentages, not specific share counts or amounts.
• Row-level permissions: investment teams and legal teams may have different visibility scopes.
• Full audit logging: queries, exports, and modifications should all be recorded and sent to a centralized log service for search and retention.
• Export controls: bulk exports should go through an approval workflow, and exported files should be watermarked and tracked.

Recommended observability metrics include at least: data freshness, missing rate of required fields, number of percentage-sum validation failures, number of detected cycles, P95 latency of penetration tasks, and queue time of simulation tasks.

9. Common Pitfalls

Pitfall	Consequence	Handling
Treating reserved option pool as issued shares	Understated dilution ratio	Distinguish authorized pool from exercised shares
Mixing pre-money and post-money bases	Percentages do not sum to 1	Unify denominator and perform normalization checks
Ignoring dual-class structures	Incorrect control judgment	Output both economic interest and voting power
Failing to prune cross-holdings	Recursive overflow or divergent results	Path deduplication plus significance threshold
Answering historical questions with latest data	Conclusions are not reproducible	Temporal edges plus point-in-time queries
Storing only percentages instead of share counts	Cannot recompute or audit	Share counts are facts; percentages are derived

10. Implementation Path and Acceptance Checklist

It is recommended to proceed in three phases rather than building a large, comprehensive platform all at once.

Phase 1 (1–2 weeks): Establish the data model and calculation-basis dictionary, manually enter current point-in-time data, and implement only look-through calculation and point-in-time leaderboards. The first priority is to calculate the numbers correctly.

Phase 2 (2–4 weeks): Connect automated collection and quality validation, add dilution simulation and scenario comparison, and complete audit and permission controls.

Phase 3 (ongoing): Add time-series analysis and trend dashboards, supporting retrospective analysis at any historical point in time and stress testing.

Acceptance checklist before launch:

• The sum of economic interest percentages across all entities equals 1 (error < 1e-6)
• The sum of voting power percentages across all entities equals 1 (error < 1e-6)
• Cycle detection can identify and skip pre-built test cyclic data
• Repeated execution for the same date produces the same result (idempotent)
• Any percentage can be drilled down to its path and source
• Historical point-in-time query results match archived snapshots
• Unauthorized roles cannot see plain-text sensitive fields

Turning a “shareholder list” into an “equity calculation engine” is essentially an upgrade from static information listing to event-driven modeling. When the model is correct, point-in-time leaderboards, control analysis, and financing dilution simulations are simply different views of the same engine. When the model is wrong, even the most polished charts only amplify the error.