Returns Data Lineage
This diagram shows how data flows from source tables to returns calculations, and the comparison with BTIG.
Overview
Key Differences Between Methods
Method 1: Simple Flat Capital ("AIC Way")
- Uses flat capital from prior month-end
- Capital stays constant throughout the month
- Cash flows deferred to next month
- Source:
gold.v_capital_daily→sable.capital_daily
Method 2: Modified Dietz ("BTIG Way")
- Uses begin_nav (prior day's ending NAV) as denominator
- NAV updates daily based on market values
- Time-weighted approach matches BTIG methodology
- Source:
gold.v_nav_daily(derived fromsable.pnl_daily_v2)
Data Sources Summary
All sources are non-dbt tables in the sable schema:
| Component | Table | Schema | Notes |
|---|---|---|---|
| Transactions | trans_daily_v2 | sable | DEP/WDR for cash flows |
| P&L | pnl_daily_v2 | sable | Daily position P&L (from CLI upload) |
| BTIG Returns | perf_daily_v2 | sable | Daily returns (from CLI upload) |
| Capital | capital_daily | sable | Monthly capital allocations |
| Accounts | account | sable | Account → Entity mapping |
| Entities | entity | sable | Fund/entity definitions |
Upload Flow
The sable CLI uploads BTIG files via the sable_btig_upload_v2 edge function, which inserts directly into sable.pnl_daily_v2 and sable.perf_daily_v2. No dbt transformation is involved.
Cash Flow Aggregation
Entity-level cash flows aggregate across all accounts:
Previous bug: DISTINCT ON ... ORDER BY market_value DESC picked only the largest account (av7k), dropping xpb006152's cash flows.
Fix: GROUP BY entity_id, transaction_date with SUM() aggregation.
Loss Function Tracking
Results are stored for version control:
Related
- Returns Overview - Initiative goals and roadmap
- Modified Dietz Method - Calculation methodology details
- Troubleshooting - Debugging discrepancies