Epic 4.6: Account-Level Comparison Consistency

Status: ✅ Complete (January 2026)

Overview

Fixed v_returns_comparison to compare at account-level instead of entity-level. The view was comparing entity-level Sable returns against account-level BTIG returns (apples to oranges).

Problem

Source	Match Rate	Data Level
View (before)	52.8%	Entity-level
Function	34.7%	Account-level

Root Cause

For entities with multiple accounts (Anthracite Holdings LP = AV7K + XPB006152), the same entity-level Sable return was compared against different account-level BTIG returns.

BTIG reports per account:
  AV7K:       5.0% return
  XPB006152:  3.0% return

Old view compared:
  AV7K (BTIG 5.0%)       vs Entity-level (4.5%)  → -50 bps
  XPB006152 (BTIG 3.0%)  vs Entity-level (4.5%)  → +150 bps

This masked real sub-account discrepancies.

Solution

Refactored v_returns_comparison to use account-level pre-computed tables:

Table	Purpose
sable.nav_daily_account	Daily NAV by account
sable.capital_daily_account	Prior month-end NAV as capital
sable.capital_daily_dietz_account	Daily begin_nav for Dietz calculation

Now joins by account_code instead of entity_id.

Result

Source	Total	Matches	Rate
View (after)	144	50	34.7%
Function	144	50	34.7%

View and function now produce identical results.

Architecture

Position-level (source)
    └── sable.pnl_daily_v2
            │
            ├── Account-level aggregation
            │       └── Pre-computed tables
            │               ├── nav_daily_account
            │               ├── capital_daily_account
            │               └── capital_daily_dietz_account
            │                       │
            │                       ├── v_returns_comparison (reconciliation)
            │                       └── f_get_returns_comparison() (reconciliation)
            │
            └── Entity-level aggregation
                    └── v_dietz_daily (user reports)

Use Cases

Layer	Level	Purpose	View/Function
Reconciliation	Account	Debug Sable vs BTIG discrepancies	v_returns_comparison, f_get_returns_comparison()
User Reports	Entity	Show fund performance to investors	v_dietz_daily

Key Changes

Before (entity-level joins)

-- OLD: Joined by entity_id (wrong level)
LEFT JOIN sable_simple_returns s
    ON aem.entity_id = s.entity_id
    AND b.month_end_date = s.month_end_date
LEFT JOIN sable_dietz_monthly d
    ON aem.entity_id = d.entity_id
    AND b.month_end_date = d.month_end_date

After (account-level joins)

-- NEW: Joined by account_code (correct level)
LEFT JOIN sable_simple_returns ssr
    ON b.account_code = ssr.account_code
    AND b.month_end_date = ssr.month_end_date
LEFT JOIN sable_dietz_monthly sdm
    ON b.account_code = sdm.account_code
    AND b.month_end_date = sdm.month_end_date

Verification

-- Verify view and function produce identical results
SELECT 'view' as source,
    COUNT(*) as total_months,
    SUM(CASE WHEN status = 'match' THEN 1 ELSE 0 END) as within_10bps
FROM gold.v_returns_comparison
WHERE month_end_date <= '2025-12-31'
UNION ALL
SELECT 'function' as source,
    COUNT(*) as total_months,
    SUM(CASE WHEN status = 'match' THEN 1 ELSE 0 END) as within_10bps
FROM sable.f_get_returns_comparison('00000000-0000-0000-0000-000000000001')
WHERE month_end_date <= '2025-12-31';

-- Result:
-- view     | 144 | 50
-- function | 144 | 50

Account Breakdown

Account	Entity	Matches	Review	Investigate
ARP	Anthracite Realty Partners	30	2	4
AV7K	Anthracite Holdings LP	0	4	32
BFF	Bird Family Foundation	2	4	30
XPB006152	Anthracite Holdings LP	18	0	18

Entity-Level Aggregation Math

For user-facing reports, entity returns are calculated from rolled-up raw numbers:

Entity-level return = Σ(account P&L) / Σ(account NAV)

Not averaged percentages:

Account AV7K:      +5% on $190M = +$9.5M
Account XPB006152: +3% on $7M   = +$0.21M
                                ─────────
Entity total:                    +$9.71M on $197M = +4.93%

Simple average (wrong): (5% + 3%) / 2 = 4.0%

Related

• Epic 4: Final Convergence - Parent epic
• Epic 4.5: Entity Start Date Filtering - Previous epic
• Data Lineage - Full data flow diagram