How Broker-Dealers Aggregate Data From Multiple Custodians and Clearing Firms

Broker-dealers aggregate data from multiple custodians and clearing firms by landing each firm’s daily files or API feeds in one database, translating them into a common data model, and mapping every account to a rep, branch and OSJ through a rep ID crosswalk the home office controls. Most firms do this with a combination of clearing firm data feeds, direct-business feeds from product sponsors, and a data platform or warehouse that does the normalization. Milemarker is a data platform built for this case: it connects clearing firms such as Pershing, National Financial Services, Apex, RBC Correspondent Services and LPL, plus CRMs and portfolio systems, into one normalized data layer on Snowflake that the broker-dealer owns.

The hard part is rarely the connection itself. It is the mapping: knowing that rep code A7Q at one clearing firm, rep 10432 at another and a split code on an annuity feed are the same person, sitting under the same OSJ, on the same date.

Where does a broker-dealer’s data actually come from?

A typical independent or hybrid broker-dealer has four families of source data.

• Clearing firms. Brokerage accounts, positions, trades, cash activity and commissions from the firm’s clearing relationships. Common ones are BNY Pershing, National Financial Services (Fidelity’s clearing arm), RBC Correspondent Services, Apex Clearing, Axos Clearing, Hilltop Securities and First Clearing. LPL clears for its own affiliated advisors, so an LPL-affiliated or hybrid firm often receives LPL data alongside another custodian’s.
• RIA custodians. Hybrid firms with a corporate RIA also hold advisory assets at Schwab, Fidelity Institutional or similar custodians, which arrive in a different file format with different account and rep identifiers.
• Direct business. Mutual funds, variable annuities, 529 plans and alternatives held directly with the sponsor rather than at the clearing firm. These usually come through DTCC services (such as Fund/SERV and the Insurance and Retirement Services networking feeds) or SS&C’s DST FAN Mail, which delivers client and position files from many sponsors in one place.
• Internal systems. The CRM, the commission and payout system, compliance and surveillance tools, licensing data (CRD numbers, registrations, CE) and the general ledger.

Each source answers part of a question. None of them knows the full picture of a rep’s book.

How do broker-dealers aggregate clearing data?

There are three common approaches, and most firms end up with a mix.

1. Rely on the clearing firm’s own tools. Pershing’s NetX360, NFS’s Wealthscape and similar platforms give a strong view of the assets they hold. This works well for a single-clearing firm with little direct business. It breaks down as soon as a second clearing relationship, a corporate RIA or a large annuity book appears, because each portal only sees its own accounts.

2. Use a consolidated reporting or aggregation service. Products such as Albridge Wealth Reporting (from BNY Pershing) consolidate clearing, custodian and direct-business positions for reporting and client statements. They are good at “what does this client own” and less suited to compensation, supervision or firm analytics, and the data usually stays inside the vendor’s application.

3. Build a data layer the firm owns. The firm lands every feed in a warehouse such as Snowflake, maps it to one model of accounts, clients, reps, branches and products, and runs reporting, payout, surveillance and AI on top. Firms either build this with an internal data team and ETL tools, or use a wealth-specific data platform that ships the connectors and the model pre-built. This is the approach that scales to multiple clearing firms, and it is where Milemarker fits. See data platforms for broker-dealers for a longer comparison.

How should rep IDs and the OSJ hierarchy be handled?

Rep identity is the backbone of broker-dealer data, and it is where most aggregation projects go wrong.

• One person, many codes. A single advisor can hold a rep code at each clearing firm, joint or split codes for team business, separate codes for advisory and brokerage, and producer numbers at each insurance carrier. Treat the CRD number as the person and every code as an alias.
• Codes change over time. Reps move branches, join teams, and inherit books. Store each rep-code-to-person and person-to-branch relationship with effective dates, so a report for March reflects March’s hierarchy, not today’s.
• The hierarchy is the firm’s, not the clearing firm’s. Branch and OSJ assignments live in the broker-dealer’s own records. The aggregation layer should hold one hierarchy table (rep, branch, OSJ, region, supervising principal) and apply it to every feed, rather than trusting each clearing firm’s office codes.
• Splits need their own table. A split code that pays 60/40 between two reps has to resolve to both people with percentages, or production and payout totals will double count or drop revenue.

How do you map accounts to reps?

Account-to-rep mapping is where the crosswalk earns its keep. A workable sequence:

1. Take the account’s rep code from the source feed.
2. Resolve that code through the crosswalk to one or more people (with split percentages) as of the business date.
3. Attach the person’s branch and OSJ from the hierarchy table for the same date.
4. Link the account to a client and household using SSN or tax ID matching where permitted, with name and address as a fallback, so the same client at Pershing and at a variable annuity carrier rolls up together.
5. Flag every account whose rep code does not resolve, and route it to an operations queue instead of silently dropping it.

The unmapped-code queue matters more than it looks. Unresolved codes are the usual reason a branch’s AUM or production looks wrong on a dashboard.

What are the steps to build it?

1. Inventory every source. List each clearing firm, custodian, DTCC or DST feed, carrier and internal system, with its delivery method, frequency and owner.
2. Agree the canonical model. Define what an account, client, household, rep, branch, OSJ and product mean at your firm, with one ID for each.
3. Build the rep crosswalk and hierarchy first. Every downstream use depends on them.
4. Connect the feeds. Start with the largest clearing relationship, then the second, then direct business.
5. Reconcile daily. Compare positions and cash to each source’s totals and alert on breaks.
6. Expose it by role. Apply row-level access so a rep sees their book, an OSJ principal sees their branches, and the home office sees everything.

What are the common pitfalls?

• Normalizing late. Building reports directly on raw clearing files means every report reinvents the mapping. Normalize once, in the data layer. See multi-custodian data normalization.
• Ignoring direct business. Annuities and direct mutual fund holdings can be a large share of a rep’s book and revenue.
• No effective dating. Without history on rep and branch assignments, prior-period payout and supervision reports cannot be reproduced for an exam.
• Data trapped in a vendor tool. If the only copy of the normalized data lives inside a reporting application, the firm cannot use it for payout, AI or a future system change.

Where does Milemarker fit?

Milemarker connects clearing firms and custodians (including Pershing, National Financial Services, Apex, RBC Correspondent Services, LPL, Schwab and Fidelity Institutional) alongside CRMs, portfolio accounting and planning tools, and normalizes them into one data layer in the firm’s own dedicated Snowflake database. It has 140+ wealth management integrations, and the broker-dealer solution gives the home office one view of every rep, branch and OSJ for supervision, Reg BI, registrations, production and payout. Access is controlled at the row level by advisor, office and role, with PII masking and SSO. Typical implementations run 2 to 12 weeks. Learn more at Milemarker for broker-dealers and Snowflake for broker-dealers.

Frequently asked questions

Can a broker-dealer aggregate data without replacing its clearing firm?

Yes. Aggregation reads the data each clearing firm already produces. The clearing relationships, portals and back-office processes stay the same; the firm adds a data layer that combines them.

What is the best unique identifier for a rep across clearing firms?

The CRD number is the most stable identifier for a registered person. Rep codes at each clearing firm, split codes and carrier producer numbers should all map to the CRD through a dated crosswalk.

How is direct business data brought in?

Most broker-dealers receive direct-business positions and transactions through DTCC networking feeds or SS&C’s DST FAN Mail. Those files are mapped to the same client, account and rep model as clearing data.

How often should clearing data be refreshed?

Daily is the norm, because most clearing firms deliver overnight files. Some offer APIs for intraday data, which is useful for surveillance and cash management but not required for reporting and payout.

Do we need a data team to do this?

A firm can build it in-house with data engineers, ETL tools and a warehouse. A wealth-specific data platform reduces that work because the connectors, data model and rep mapping patterns already exist.