A typical brokerage runs on a patchwork of systems: one or more trading platforms, a CRM, a payment gateway, a KYC provider, a back-office ledger, a support desk, marketing tools and spreadsheets that someone in finance maintains by hand. Each system has its own idea of who the customer is. The management question "what is the lifetime value of clients acquired through this campaign?" touches five of them, and every department gets a different answer. A unified data warehouse for brokers turns that patchwork into one trustworthy view.
A unified data warehouse fixes this, but only if it is designed around the customer and the money rather than around the source systems. This article describes an architecture that works for brokers and the identity-resolution layer that holds it together.
The layered (medallion) architecture
A proven structure has four layers, whatever technology you choose (a cloud warehouse, a lakehouse on object storage, or a well-run on-premises database):
- Raw / landing. Exact copies of source data, loaded by change data capture (CDC) or scheduled extracts, never modified. Keep load timestamps and source identifiers. This is your audit trail and your replay buffer.
- Staging / cleaned. Typed, deduplicated and standardised per source: consistent time zones (store UTC, convert for display), currency codes, country codes, phone numbers in E.164 format, normalised names and emails.
- Core / conformed model. Business entities shared across sources: customer, account, instrument, order, trade, transaction, deposit, withdrawal, campaign, ticket. This is where identity resolution happens.
- Marts / semantic layer. Subject-specific models for finance, risk, marketing, sales and support, plus a semantic layer where metrics such as net deposits, active trader and churn are defined once.
Use a transformation framework that keeps SQL models in version control with tests and documentation, so every metric has a reviewed definition and a lineage.
Modelling the brokerage domain
Dimensional modelling still works well. Typical fact tables:
- fct_trades: one row per execution, with instrument, account, side, volume, price, commission, spread and swap.
- fct_transactions: deposits, withdrawals, transfers, bonuses and fees, with status history.
- fct_account_daily: a daily snapshot of balance, equity, margin, open positions and P&L per account. Snapshots make "as of" reporting simple.
- fct_interactions: calls, chats, emails and tickets from support and sales.
Dimensions include customer, account, instrument, campaign, employee and date. Use slowly changing dimensions (type 2) for attributes that matter historically, such as account manager, customer tier and country of residence, so reports reflect the situation at the time rather than today's values.
Customer identity resolution: one customer, many identifiers
The same person can appear as a lead in the CRM, two accounts on different trading platforms, a payer with three cards, and a contact in the support desk under a slightly different name. Identity resolution links these records into one golden customer with a stable internal ID.
A practical approach combines deterministic and probabilistic matching.
Deterministic rules come first because they are explainable:
- Same verified national ID or passport number → same person.
- Same verified email, after normalisation (lower case, trimmed; provider-specific rules applied carefully).
- Same phone number in E.164 format, if verified.
- An explicit link already recorded by an operational system, such as a CRM ID stored on the trading account.
Probabilistic matching handles the rest. Compare candidate pairs on name similarity (Jaro–Winkler, with transliteration for Persian and Latin spellings), date of birth, address, device fingerprints and payment instruments. Score each pair (the Fellegi–Sunter model or a trained classifier both work) and set two thresholds: above the upper one, merge automatically; between them, send to a data steward for review.
To keep this tractable:
- Blocking. Only compare records that share a cheap key such as phone suffix, birth year plus surname initial, or email domain plus first name. Without blocking, the number of pairs grows quadratically.
- Graph-based clustering. Treat matches as edges and take connected components as customers. Watch for over-merging: a shared office phone or a family card can chain unrelated people together. Limit cluster size and flag large clusters for review.
- Keep the links, not just the result. Store every match with its rule or score, timestamp and evidence. When a merge is wrong, you must be able to split it and recompute downstream tables.
- Survivorship rules. Decide which source wins for each attribute: KYC for legal name and date of birth, CRM for marketing preferences, the most recently verified value for phone and email.
Data quality and governance
Brokers are regulated, and their numbers end up in board packs and regulatory reports. Build quality checks into the pipeline:
- Freshness checks on every source, with alerts when a feed stops.
- Reconciliation: deposits in the warehouse must equal the payment processor's settlement totals; trade counts must match the platform's daily statements.
- Uniqueness and referential integrity tests on core keys.
- Anomaly detection on volumes and amounts by day and channel.
Governance should be practical: a named owner for each domain, a data catalogue with definitions, column-level classification of personal data, and role-based access so a marketing analyst sees aggregates while compliance sees identities. Mask or tokenise national IDs and card numbers in every layer except where they are strictly needed.
What the business gets
Once the conformed customer exists, the questions that used to take a week become one query or one dashboard:
- Acquisition cost and lifetime value by campaign, country and introducing partner.
- Deposit-to-first-trade conversion and time to first trade.
- Cohort retention and early churn signals.
- Account-manager performance tied to real net deposits rather than call counts.
- A single customer view for support agents.
- Clean, consistent features for machine learning, from churn models to next-best-action.
Lessons from implementation
Start with one painful question and deliver it end to end through all four layers rather than ingesting everything first. Involve finance early, because reconciliation earns trust faster than dashboards. Make identity resolution reviewable, since business users accept automation once they can see and correct it. And treat metric definitions as code: when "active client" changes, it should change in one place, with a review and a date.
A unified warehouse is less about technology than about agreeing on one version of the customer and the money. Identity resolution is where that agreement becomes concrete.