BTIG File Integration

BTIG is the prime broker providing P&L and performance data. This guide covers file formats and processing.

File Types

P&L Files (Profit & Loss by Security)

Naming Pattern:

{account}_{start_date}_{end_date}_Profit&LossBySecurity.csv

Example:

XPB006160_20200101_20261009_Profit&LossBySecurity.csv

Key Columns:

• AccountCode - BTIG account identifier
• Symbol - Security symbol
• PnlDate - Date of P&L record
• MarketValue - End of day market value
• UnrealizedPnL - Unrealized gain/loss
• RealizedPnL - Realized gain/loss

Performance Files

Naming Pattern:

{account}_Performance_{date}.csv

Example:

XPB006160_Performance_20261009.csv

Key Columns:

• AccountCode - BTIG account identifier
• ReportMonth - Month of performance record
• MonthlyReturn - Monthly return percentage
• YTDReturn - Year-to-date return

File Detection

When a file is uploaded, the system automatically detects:

1. Source Type - btig_pnl or btig_performance
2. Account Code - Extracted from filename
3. Date Range - Extracted from filename

Detection happens in pipelines/detector.py every 10 seconds.

Account Code Normalization

Account codes are normalized to lowercase throughout the pipeline:

XPB006160 → xpb006160

This ensures consistent matching between BTIG files and Sable entities.

Processing Flow

Upload CSV → Detection (10s) → Bronze Load → dbt Transform → Gold Tables

Status Transitions

Status	Meaning
uploaded	File received, awaiting detection
detected	Source type identified
bronze_loaded	Raw data in bronze table
processing	dbt transformation running
completed	Data in gold tables
failed	Error during processing

Data Quality Checks

After upload, observability tests verify:

• Bronze/Silver row count parity
• No duplicate positions
• P&L formula integrity
• Data freshness

Reconciliation

BTIG vs Sable comparison available at:

GET /api/returns/comparison

Status	Difference	Action
match	≤10 bps	Auto-approved
review	10-50 bps	Manual review
investigate	>50 bps	Debug required