Epic 4: Final Convergence - Reduce Remaining Discrepancies

Status: In Progress (January 2026)

Overview

Goal: Achieve 100% of months within 10 bps threshold. Currently at 84.2% (96/114 months).

Current State

Metric	Value
Total months analyzed	114
Within threshold (≤10 bps)	96 (84.2%)
Exceeds threshold (>10 bps)	18 (15.8%)

Pending Discrepancies

Critical Data Quality Issues (2)

These are data problems, not methodology issues:

Entity	Month	Diff (bps)	Sable	BTIG	Issue
Anthracite Realty Partners	Dec 2022	100,081	1000.91%	0.11%	Near-zero NAV
Anthracite Realty Partners	Jan 2023	3,294	34.57%	1.63%	Continuation

Root Cause: Division by near-zero NAV causing impossible return calculations. These early months likely have incomplete position data in Sable.

Large Methodology Discrepancies (2)

Entity	Month	Diff (bps)	Sable	BTIG
Anthracite Holdings LP	Jan 2023	-70	7.35%	8.04%
Anthracite Realty Partners	Dec 2025	-54	2.65%	3.18%

Medium Discrepancies (14)

Entity	Month	Diff (bps)
Anthracite Realty Partners	May 2024	-33
Anthracite Holdings LP	Dec 2022	-17
Anthracite Holdings LP	Nov 2025	-17
Bird Family Foundation	Apr 2025	+16
Anthracite Holdings LP	Oct 2025	-15
Anthracite Holdings LP	Jan 2026	+15
Anthracite Holdings LP	May 2025	-15
Anthracite Holdings LP	Mar 2024	-14
Anthracite Holdings LP	Feb 2023	-13
Anthracite Holdings LP	Apr 2025	-13
Bird Family Foundation	Dec 2022	-11
Bird Family Foundation	Oct 2024	+11
Anthracite Holdings LP	Jun 2024	-10
Anthracite Realty Partners	Nov 2023	-10

Root Causes Under Investigation

1. Cash Flow Timing

BTIG and Sable may record cash flows on different dates (trade date vs settle date).

Data Available: sable.trans_daily_v2 contains both trade_date and settle_date for capital flow transactions (DEP, WDR, FWT, EXW).

-- Check cash flow timing for a discrepant month
SELECT txn_type, trade_date, settle_date,
       settle_date - trade_date as settle_lag,
       base_amount
FROM sable.trans_daily_v2
WHERE txn_type IN ('DEP', 'WDR', 'FWT', 'EXW')
  AND DATE_TRUNC('month', trade_date) = '2025-11-01'
ORDER BY trade_date;

2. NAV Computation

• Sable: Computes NAV from position-level market values in pnl_daily_v2
• BTIG: Has their own NAV source in performance reports

-- Compare NAV for a specific date
SELECT pnl_date, SUM(end_market_value) as sable_nav
FROM sable.pnl_daily_v2
WHERE entity_id = 2 AND pnl_date = '2025-11-30'
GROUP BY pnl_date;

3. Missing Data

Some entities/months may have incomplete position data in Sable, especially for early periods (2022-2023).

Tickets Created

18 individual investigation tickets created under this epic:

Priority	Count	Description
Critical	2	Data quality bugs (ARP Dec 2022, Jan 2023)
High	2	Large methodology gaps (>50 bps)
Normal	10	Medium discrepancies (13-33 bps)
Low	4	Near-threshold (10-11 bps)

Resolution Strategy

Phase 1: Fix Critical Data Issues

1. Investigate NAV for ARP Dec 2022 - likely near-zero causing division error
2. If data is unfixable, mark as "acknowledged" with explanation
3. This alone removes 95%+ of total loss

Phase 2: Investigate Methodology

1. Test if using settle_date instead of trade_date improves alignment
2. Compare NAV values between systems for discrepant months
3. Identify systematic patterns (e.g., all AHL months off by similar amount)

Phase 3: Frontend Integration

1. Fix bug: Return method setting not flowing to charts
2. Create Sable returns dashboard that uses gold.v_dietz_daily
3. Add email notifications for new discrepancies

Related Work

Bug: Frontend Not Using Configured Method

The admin can change return calculation method in Settings, but frontend charts still show BTIG raw data. Root cause: No page queries gold.v_dietz_daily.

Fix: Create /protected/returns page that queries the correct view.

Enhancement: Email Notifications

Alert admins when new discrepancies exceeding 10 bps are detected.

Approach: Database trigger on sable.returns_discrepancy_reviews insert.

Success Criteria

Criterion	Target	Current
Months within 10 bps	100%	84.2%
No month > 50 bps	True	False (2 data issues)
Frontend shows configured method	Yes	No
Auto-notify on discrepancies	Yes	No

Data Error Loss Function

In addition to the return difference loss function (L = Σ(diff)²), we need a data error loss function that counts structural data problems.

Error Types

Error Type	Description	Indicates
Zero Sable return	sable_return = 0 for a month	Missing P&L data
Zero BTIG return	btig_return = 0 for a month	Missing performance data
Zero NAV	nav = 0 or NULL	No positions loaded
Zero capital	capital = 0	Missing capital base
Zero P&L	total_pnl = 0 for entire month	Data not uploaded

Entity Start Dates

Each entity has a configurable returns_start_date column in sable.entity. Months before this date are excluded from data error calculations.

Entity	Entity ID	Start Date	Notes
Anthracite Realty Partners, LLC	1	2023-01-01	Dec 2022 excluded (incomplete data)
Anthracite Holdings LP	2	2023-01-01	Dec 2022 excluded (incomplete data)
Bird Family Foundation	3	2023-01-01	Dec 2022 excluded (incomplete data)

Implementation: The sable.entity.returns_start_date column defaults to 2023-01-01. Admins can adjust per-entity start dates if needed.

SQL Function

The gold.f_get_data_errors(org_id) function detects data quality issues:

-- Get all data errors for an organization
SELECT * FROM gold.f_get_data_errors('your-org-id');

-- Get summary counts by error type
SELECT * FROM gold.f_get_data_error_summary('your-org-id');

The function joins to sable.entity to respect per-entity start dates:

-- From gold.f_get_data_errors
FROM gold.v_dietz_daily d
JOIN sable.entity e ON d.entity_id = e.entity_id
WHERE d.org_id = p_org_id
  AND d.pnl_date >= COALESCE(e.returns_start_date, '2023-01-01'::date)

CLI Command (Proposed)

# Check for data errors
sable returns errors

# Output:
# Data Error Report
# =================
# Zero Sable returns: 2 months
# Zero BTIG returns: 0 months
# Zero NAV: 1 month
# Zero P&L: 0 months
# -----------------
# Total Data Errors: 3

Target

• Data Errors = 0 (no structural data problems)
• All months after entity start date should have non-zero values

SQL Queries

Get All Pending Discrepancies

SELECT e.entity_name, r.review_month, r.monthly_diff_bps,
       r.sable_monthly_return, r.btig_monthly_return
FROM sable.returns_discrepancy_reviews r
JOIN sable.entity e ON r.entity_id = e.entity_id
WHERE r.status = 'pending_review'
ORDER BY ABS(r.monthly_diff_bps) DESC;

Check NAV History for Entity

SELECT pnl_date, SUM(end_market_value) as nav
FROM sable.pnl_daily_v2
WHERE entity_id = 1  -- ARP
  AND pnl_date BETWEEN '2022-11-01' AND '2023-02-28'
GROUP BY pnl_date
ORDER BY pnl_date;

Compute Current Loss

sable returns loss --breakdown

---

Epic 4.5: Entity Start Date Filtering

Status: In Progress (January 2026)

Overview

With sable.entity.returns_start_date now configurable, we need to propagate this filtering across all views, functions, and tests. Returns and P&L data before an entity's start date will not be considered in calculations or validations.

Why This Matters

• December 2022 data is incomplete/unreliable for all three entities
• Different entities may start operations at different times
• The calendar should span the full range (earliest start date to today), but expectations per entity should respect each entity's start date

Implementation Pattern

-- Calendar spans full range (from earliest entity start)
WITH date_range AS (
    SELECT date_val::DATE as expected_date
    FROM generate_series(
        (SELECT MIN(returns_start_date) FROM sable.entity WHERE deleted_at IS NULL),
        CURRENT_DATE - INTERVAL '1 day',
        '1 day'::INTERVAL
    ) date_val
),
-- But entity expectations are per-entity
expected_entity_dates AS (
    SELECT dr.expected_date, e.entity_id
    FROM date_range dr
    CROSS JOIN sable.entity e
    WHERE e.deleted_at IS NULL
      AND dr.expected_date >= e.returns_start_date  -- KEY: Per-entity filtering
)

Components to Update

Component	File	Status
Data error function	f_get_data_errors.sql	✅ Complete
Calendar coverage test	btig_02_calendar_coverage.sql	✅ Complete
Daily load test	btig_01_daily_load_completeness.sql	✅ Complete
Returns comparison view	v_returns_comparison.sql	✅ Complete
Returns comparison function	f_get_returns_comparison.sql	✅ Complete
Data lineage diagram	data-lineage.md	⏳ Pending

Tickets

Epic: 8650a680-655d-46ea-a537-d99fa3e494bc

Ticket	Description	Priority
d7fa86c0	Update btig_02_calendar_coverage test	High
f042fda1	Update btig_01_daily_load_completeness test	Normal
3dc75b6f	Update v_returns_comparison view	Normal
cbccc369	Update f_get_returns_comparison function	Normal
916440d3	Update data-lineage.md diagram	Low

Success Criteria

Criterion	Target
All tests use returns_start_date	Yes
All views filter by entity start date	Yes
All functions respect entity start date	Yes
Data lineage diagram updated	Yes

---

Related

• Epic 1: CLI Loss Function - Measuring differences
• Epic 2: Method Configuration - Per-entity toggle
• Epic 3: Automated Reconciliation - Flagging system
• Data Lineage - Full data flow diagram