Building an Automated Prime Broker to Accounting Ledger Pipeline

This guide walks you through building a fully automated pipeline to pull daily fixed income marks from a prime broker over SFTP, stage them in your data warehouse, transform them into a standardized format compatible with an accounting ledger, and load them directly into your book of record.

1. The Scenario

Every morning, middle-office and operations teams face the same high-stakes race: ingest daily marks, validate them, and load them into the accounting engine before the portfolio managers log in or the morning reports run. When dealing with fixed income, this daily ritual gets twice as complex. You aren't just dealing with a single price—you are managing clean prices, dirty prices, amortized costs, and accrued interest across various asset classes and identifiers.

To keep things concrete and easy to map to your own setup, we will build this pipeline around a standard, representative fixed income fund flow:

• Fund: Fixed Income Credit Fund (representative portfolio)
• Prime Broker: Prime Broker A (via secure SFTP)
• Data Payload: Daily clean and dirty prices for corporate bonds and sovereign debt
• Downstream Destination: Accounting Ledger Loader (.csv price update format)
• Cadence: Weekdays at approximately 6:00 PM ET

2. Infrastructure Prerequisites

Before constructing the workflow, ensure you have the following assets configured in your integration platform:

PrerequisiteWhere to Configure / VerifyDescription

SFTP Credential Credential Hub → FTP/SFTPPrime Broker A SFTP host, port, username, and SSH private key.

Geneva Credential: Credential Hub → Geneva Connection details, environment names, and API credentials for the accounting engine.

Warehouse: Databases A dedicated warehouse schema with a raw staging table (staging.pb_a_fi_prices_raw). CoSet can either connect directly to your existing environment via an onsite worker or CoSet can host PostgreSQL instances for you.

Bridge Agent: A local runner/agent with access to both your warehouse and the ledger environment. Cloud hosted resources (CoSet Databases / CoSet File Stores) do not need custom bridges.

3. File Formats: Inbound vs. Outbound

Our automation pipeline bridges the gap between raw broker data and the strict schemas required by downstream accounting systems.

Inbound (Prime Broker A)

The prime broker deposits a CSV file daily to an ftp server. The columns are broker-specific and loaded as-is into our raw staging table to preserve audit trails:

Broker_Sec_ID,CUSIP,ISIN,Issuer_Name,Price_Date,Clean_Price,Dirty_Price,Accrued_Int,Currency
SEC100293,912828XX0,US912828XX01,GOVERNMENT BOND,2026-07-15,98.2500,98.7230,0.4730,USD
SEC482019,254687XX5,US254687XX52,CORPORATE BOND,2026-07-15,102.1250,102.9450,0.8200,USD

Outbound (Standardized Ledger Loader)

To load these prices, the ledger expects a normalized .csv format. This requires mapping the broker identifiers to standard investment keys and splitting clean and dirty prices into separate, structured records:

Investment,PriceDate,Price,PriceType,Currency,PriceSource,AccruedInterest
912828XX0,20260715,98.2500,CLEAN,USD,PBA,0.4730
912828XX0,20260715,98.7230,DIRTY,USD,PBA,0.4730
254687XX5,20260715,102.1250,CLEAN,USD,PBA,0.8200
254687XX5,20260715,102.9450,DIRTY,USD,PBA,0.8200

4. File Store Layout

Keep your file system organized so that operations can quickly replay old files or audit historic data. We mirror the SFTP directory structure but partition the raw files from the ledger-ready outputs:

files/
└── pb/
    └── prime-broker-a/
        └── fi-prices/
            ├── raw/
            │   ├── PB_A_FI_PRICES_20260714.csv
            │   └── PB_A_FI_PRICES_20260715.csv
            └── ledger-loader/
                ├── PB_A_FI_PRICES_LOADER_20260714.csv
                └── PB_A_FI_PRICES_LOADER_20260715.csv

5. Automated Workflow Tree

This end-to-end process is governed by a unified workflow group that runs every weekday morning.

Group: PB A Fixed Income Price Update
 └── FTP Monitor                    [Schedule: Weekdays @ 06:30 AM ET]
      └── FTP Download              [On Success]
           └── SQL Bulk Load        [On Success] ──> staging.pb_a_fi_prices_raw
                └── Table to CSV    [On Success] ──> view: ledger_pb_a_fi_price_loader
                     └── Ledger Loader [On Success] ──> Target: UAT / Prod Env
      └── Email Notification        [On Failure] ──> Alert Ops Team

Job Configuration Notes:

• FTP Monitor: Watches the remote directory /outbound/prices for a file pattern matching PB_A_FI_PRICES_*.csv
  .
• FTP Download: Connects via the SFTP credential and downloads the file to files/pb/prime-broker-a/fi-prices/raw/
  
• SQL Bulk Load: Clears/appends or inserts directly into staging.pb_a_fi_prices_raw
  
• Table to CSV: Instead of pointing directly to the raw table, point this job to the SQL database view (ledger_pb_a_fi_price_loader). This extracts our cleaned and reshaped data directly into a .csv file in the ledger-loader/
  directory.
• Ledger Loader: References the generated .csv file path and pushes it to your accounting system. Run this in UAT first for verification, then promote to Prod.

6. The Transformation View (Staging → Ledger Shape)

Because the downstream system requires separate lines for Clean and Dirty prices, we write a SQL view to handle the normalization.

SQL

CREATE OR REPLACE VIEW staging.v_ledger_pb_a_fi_price_loader AS
-- Segment 1: Clean Prices
SELECT 
    COALESCE(CUSIP, ISIN) AS Investment,
    TO_CHAR(TO_DATE(Price_Date, 'YYYY-MM-DD'), 'YYYYMMDD') AS PriceDate,
    CAST(Clean_Price AS NUMERIC(18,6)) AS Price,
    'CLEAN' AS PriceType,
    Currency AS Currency,
    'PBA' AS PriceSource,
    CAST(Accrued_Int AS NUMERIC(18,6)) AS AccruedInterest
FROM staging.pb_a_fi_prices_raw
WHERE Price_Date = (SELECT MAX(Price_Date) FROM staging.pb_a_fi_prices_raw)
  AND Clean_Price IS NOT NULL

UNION ALL

-- Segment 2: Dirty Prices
SELECT 
    COALESCE(CUSIP, ISIN) AS Investment,
    TO_CHAR(TO_DATE(Price_Date, 'YYYY-MM-DD'), 'YYYYMMDD') AS PriceDate,
    CAST(Dirty_Price AS NUMERIC(18,6)) AS Price,
    'DIRTY' AS PriceType,
    Currency AS Currency,
    'PBA' AS PriceSource,
    CAST(Accrued_Int AS NUMERIC(18,6)) AS AccruedInterest
FROM staging.pb_a_fi_prices_raw
WHERE Price_Date = (SELECT MAX(Price_Date) FROM staging.pb_a_fi_prices_raw)
  AND Dirty_Price IS NOT NULL;

7. Failure Handling & Recovery Rules

When automation meets real-world data, things will occasionally break. Here is how our workflow handles issues:

1. The Broker File is Late (FTP Monitor Fails)

If the file isn't on the SFTP by 7:15 AM ET, the monitor job times out. The system halts the pipeline and sends an immediate Email Alert to the Ops team so they can investigate.

2. The Data is Malformed (SQL Bulk Load Fails)

If the prime broker shifts their column layout, the SQL Bulk Load job fails. Because we use an On Success chain, the downstream database views and the Ledger Loader are safely blocked from executing, preventing corrupt or empty price runs.

3. Ledger API is Down (Ledger Loader Fails)

If the database and conversion succeed but the accounting engine is undergoing maintenance, the .csv remains safely staged in the file directory. Once the system is back online, Ops can trigger a manual "Replay" of the Loader step without having to re-fetch or re-process the raw file.

8. Key Takeaways

By implementing this structure, you have established a highly resilient, modern data ops pattern:

• Decoupled Ingestion: You ingest raw data as-is, saving historical context for easier debugging.
• Database-Driven Transformations: You offloaded complex row manipulation (clean vs. dirty rows) to SQL views, keeping your workflow orchestration configuration simple and maintainable.
• Safe Promotion: By utilizing discrete file directories and sandbox environments, you protect production accounting records from upstream file errors.