Epic 4.7: Cash Flow Data Reconciliation

Status: ✅ Root Cause Resolved (January 2026)

Overview

The 34.7% returns match rate was caused by missing transaction types in the cash flow calculation. The original view only included DEP/WDR/FWT/EXW, but BTIG's perf.cash_flow field also includes settlement activity (REC/RES) and delivery transactions (DEL/DES).

Root Cause: Missing Transaction Types

The Problem: Our cash flow calculation was incomplete.

Transaction Type	Description	Included Before	Included After
DEP	Deposits	✅	✅
WDR	Withdrawals	✅	✅
FWT	Fee Withholding	✅	✅
EXW	Exchange Withdrawal	✅	✅
REC	Receipts/Settlements Received	❌	✅
RES	Settlements Paid	❌	✅
DEL	Delivery Out	❌	✅
DES	Delivery Settlement	❌	✅

Impact of Missing Types:

REC: $192M total (major contributor)
RES: -$53M total
DEL: -$58M total
DES: $2.4M total

Key Discovery

BTIG's perf.cash_flow field includes ALL cash-related transactions, not just deposits and withdrawals. This includes securities settlements and delivery transactions that were missing from our calculation.

Results: Before and After Fix

Improvement Summary

Metric	Before	After	Change
Match rate	49.3%	53.4%	+4.1%
Investigate cases	9	2	-78%
Review cases	36	38	+2
Missing trans data	30	29	-1

Feb 2023: The 96.86% Problem - SOLVED ✅

Before fix, Feb 2023 accounted for 96.86% of total loss:

Account	Month	Perf CF	Trans CF (OLD)	Trans CF (NEW)	Status
AV7K	Feb 2023	$121.1M	$57.6M	$121.1M	✅ Match
XPB006152	Feb 2023	-$43.7M	$3.1M	-$43.7M	✅ Match

Root Cause Breakdown:

AV7K Feb 2023:

OLD calc (DEP+WDR): $60M - $2.4M = $57.6M
Missing: REC $80.5M, RES -$17M
NEW calc: $57.6M + $80.5M - $17M = $121.1M ✓

XPB006152 Feb 2023 (account closure):

OLD calc (DEP only): $3.1M
Missing: DEL -$49.2M, DES $2.4M
NEW calc: $3.1M - $49.2M + $2.4M = -$43.7M ✓

Remaining Issues (2 Investigate Cases)

Both remaining issues are in January 2023 and appear to be data quality issues:

Account	Month	Perf CF	Trans CF	Diff	Issue
AV7K	Jan 2023	$30.3M	$45.3M	-$15M	Duplicate DEP in trans file
ARP	Jan 2023	$10.1M	$18.0M	-$8M	Similar pattern

Root Cause: The trans file has duplicate deposits on consecutive days (Jan 16 and Jan 17) for the same $15M amount. BTIG's perf file correctly shows only one deposit. This is a data quality issue in the trans file, not a calculation issue.

Key Insight: When BTIG reports zero cash flows in both sources, returns match perfectly. The calculation methodology is correct.

Evidence

Pattern Analysis

-- ARP months with zero BTIG cashflow match perfectly
SELECT account_code, month_end_date, btig_cashflow, diff_dietz_bps, status
FROM gold.v_returns_comparison
WHERE account_code = 'ARP' AND btig_cashflow = 0;

-- Result: All show 0.00 bps difference, status = 'match'

Specific Discrepancies

AV7K July 2023

Source	Cash Flow	Notes
Sable	-$19.2M	Includes $26M WDR on Jul 24
BTIG	+$13.5M	Missing the $26M withdrawal
Difference	$32.7M

Sable transactions for AV7K Jul 2023:

2023-07-24  WDR  -$26,000,000  U.S. DOLLAR
2023-07-19  DEP  +$4,485,604   U.S. DOLLAR
2023-07-20  WDR  -$3,730,000   U.S. DOLLAR
2023-07-17  WDR  -$3,000,000   U.S. DOLLAR
... (multiple smaller transactions)

ARP May 2024

Source	Cash Flow	Notes
Sable	-$5.0M	WDR on May 31
BTIG	$0	No cash flow reported

BTIG doesn't record this withdrawal in May OR June.

Root Cause: CONFIRMED

Missing Transaction Types (Primary Issue)

The view was only including 4 transaction types when BTIG's perf.cash_flow includes 8:

-- OLD (wrong)
WHERE txn_type IN ('DEP', 'WDR', 'FWT', 'EXW')

-- NEW (correct)
WHERE txn_type IN ('DEP', 'WDR', 'FWT', 'EXW', 'REC', 'RES', 'DEL', 'DES')

Data Quality Issue (Secondary)

January 2023 has duplicate deposit records in the trans file that don't appear in the perf file. This affects 2 months and requires manual data cleanup.

Data Sources

BTIG provides two separate data feeds that we compare:

1. BTIG Performance Files (`perf_*.csv`)

Stored in sable.perf_daily_v2:

cash_flow - Daily cash flow as reported by BTIG
Used by BTIG for their return calculations

2. BTIG Transaction Files (`trans_*.csv`)

Stored in sable.trans_daily_v2:

Individual transactions with txn_type: DEP, WDR, FWT, EXW
base_amount - Transaction amount

Implemented Tooling

View: `gold.v_cashflow_reconciliation`

Compares BTIG perf cash flows vs trans cash flows by account/month:

SELECT * FROM gold.v_cashflow_reconciliation
WHERE account_code = 'AV7K'
ORDER BY month_end_date DESC;

Returns:

btig_perf_cashflow - From performance files
btig_trans_cashflow - Sum of DEP/WDR/FWT/EXW from transaction files
cashflow_diff - Difference (perf - trans)
status - 'match' (<$1K), 'review' ($1K-$100K), 'investigate' (>$100K), 'missing_trans'

Function: `sable.f_cashflow_loss()`

Aggregate loss function for optimization:

-- Overall loss
SELECT * FROM sable.f_cashflow_loss();

-- Per account
SELECT * FROM sable.f_cashflow_loss('AV7K');

Returns: total_loss, match_rate, worst_account, worst_month, worst_diff

Function: `sable.f_cashflow_loss_breakdown()`

Pareto analysis showing top contributors:

-- Top 10 contributors
SELECT * FROM sable.f_cashflow_loss_breakdown(NULL, 10);

Returns rows ordered by impact with loss_contribution, loss_pct, cumulative_pct.

Next Steps

Phase 1: Deploy View Fix ⏳

Update gold.v_cashflow_reconciliation via dbt to include all 8 transaction types:

WHERE txn_type IN ('DEP', 'WDR', 'FWT', 'EXW', 'REC', 'RES', 'DEL', 'DES')

This will automatically fix the loss functions since they use the view.

Phase 2: Investigate Jan 2023 Data Quality ⏳

Two remaining investigate cases need manual review:

AV7K Jan 2023: Duplicate $15M DEP on Jan 16 and Jan 17
ARP Jan 2023: Similar pattern

May need to deduplicate trans records or request corrected data from BTIG.

Phase 3: Fill Missing Trans Data

29 months have performance data but no transaction data. Request historical transaction files from BTIG.

SQL Queries

Compare Cash Flows by Account/Month

WITH sable_cf AS (
    SELECT
        (DATE_TRUNC('month', trade_date) + INTERVAL '1 month' - INTERVAL '1 day')::DATE as month_end,
        account_code::text as account_code,
        SUM(CASE WHEN base_amount > 0 THEN base_amount ELSE 0 END) as deposits,
        SUM(CASE WHEN base_amount < 0 THEN base_amount ELSE 0 END) as withdrawals,
        SUM(base_amount) as net_cashflow
    FROM sable.trans_daily_v2
    WHERE txn_type IN ('DEP', 'WDR', 'FWT', 'EXW')
      AND deleted_at IS NULL
    GROUP BY DATE_TRUNC('month', trade_date), account_code
)
SELECT
    v.account_code,
    v.month_end_date,
    ROUND(s.deposits::numeric, 0) as sable_deposits,
    ROUND(s.withdrawals::numeric, 0) as sable_withdrawals,
    ROUND(s.net_cashflow::numeric, 0) as sable_net,
    ROUND(v.btig_cashflow::numeric, 0) as btig_cf,
    ROUND((COALESCE(s.net_cashflow, 0) - v.btig_cashflow)::numeric, 0) as diff
FROM gold.v_returns_comparison v
LEFT JOIN sable_cf s
    ON v.account_code = UPPER(s.account_code)
    AND v.month_end_date = s.month_end
WHERE v.account_code = 'AV7K'
ORDER BY v.month_end_date;

Find Large Transaction Discrepancies

-- Transactions over $1M that might explain differences
SELECT
    trade_date,
    account_code::text,
    txn_type,
    base_amount,
    security_description
FROM sable.trans_daily_v2
WHERE txn_type IN ('DEP', 'WDR', 'FWT', 'EXW')
  AND ABS(base_amount) > 1000000
  AND deleted_at IS NULL
ORDER BY trade_date DESC;

Success Criteria

Criterion	Target	Current
Cash flow reconciliation view created	Yes	✅ `gold.v_cashflow_reconciliation`
Loss function created	Yes	✅ `sable.f_cashflow_loss()`
Pareto breakdown function created	Yes	✅ `sable.f_cashflow_loss_breakdown()`
Cash flow match rate	>80%	53.4% (was 49.3%)
Feb 2023 discrepancies resolved	Yes	✅ Resolved
Investigate cases	<5	✅ 2 (was 9)
Returns match rate	>80%	34.7% (separate issue)

Tickets

Ticket	Description	Priority	Status
-	Create v_cashflow_reconciliation view	High	✅ Complete
-	Create f_cashflow_loss function	High	✅ Complete
-	Create f_cashflow_loss_breakdown function	High	✅ Complete
-	Investigate AV7K Feb 2023 ($63.5M diff)	Critical	✅ Root cause: missing REC/RES
-	Investigate XPB006152 Feb 2023 ($46.8M diff)	Critical	✅ Root cause: missing DEL/DES
-	Update view to include REC/RES/DEL/DES	Critical	⏳ Needs dbt deployment
-	Investigate Jan 2023 duplicate deposits	Medium	⏳ Data quality issue
TBD	Request missing trans files from BTIG	High	Pending (29 months)

Artifacts

View: gold.v_cashflow_reconciliation - Cash flow comparison by account/month
Function: sable.f_cashflow_loss() - Aggregate loss metrics
Function: sable.f_cashflow_loss_breakdown() - Pareto breakdown
Analysis: Cash Flow Reconciliation Analysis - January 2026
Devlog: Cash flow reconciliation tooling deployed
Devlog: Root cause fix: Include REC/RES/DEL/DES transaction types

Epic 4: Final Convergence - Parent epic
Epic 4.6: Account-Level Consistency - Previous epic
Data Lineage - Data flow diagram
Returns Overview - Initiative overview

Overview​

Root Cause: Missing Transaction Types​

Key Discovery​

Results: Before and After Fix​

Improvement Summary​

Feb 2023: The 96.86% Problem - SOLVED ✅​

Remaining Issues (2 Investigate Cases)​

Evidence​

Pattern Analysis​

Specific Discrepancies​

AV7K July 2023​

ARP May 2024​

Root Cause: CONFIRMED​

Missing Transaction Types (Primary Issue)​

Data Quality Issue (Secondary)​

Data Sources​

1. BTIG Performance Files (perf_*.csv)​

2. BTIG Transaction Files (trans_*.csv)​

Implemented Tooling​

View: gold.v_cashflow_reconciliation​

Function: sable.f_cashflow_loss()​

Function: sable.f_cashflow_loss_breakdown()​

Next Steps​

Phase 1: Deploy View Fix ⏳​

Phase 2: Investigate Jan 2023 Data Quality ⏳​

Phase 3: Fill Missing Trans Data​

SQL Queries​

Compare Cash Flows by Account/Month​

Find Large Transaction Discrepancies​

Success Criteria​

Tickets​

Artifacts​

Related​