Capital Markets Trading & Investment data model
Kimball warehouse for trading and asset management: order and execution facts, an accumulating settlement snapshot, daily position and P&L snapshots, and corporate actions over a conformed security master, portfolio, account, counterparty, and trader dimensions.
Capital markets and investment analytics covering the full trade lifecycle — orders, executions, and settlement — plus daily position and P&L snapshots and corporate actions, built as a Kimball star schema over a conformed security master and portfolio, account, counterparty, and trader dimensions for best-execution, exposure, and MiFID II / Dodd-Frank transaction reporting. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 14
- tables
- 132
- columns
- 26
- relationships
- 3
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Separate order and execution facts
An order can produce zero, one, or many fills; modeling orders and executions as distinct facts supports fill-rate, slippage, and best-execution analysis.
Daily position snapshot distinct from executions
Holdings and market value are semi-additive and needed daily for risk and NAV independent of trade volume — a periodic snapshot fits.
Accumulating snapshot for settlement
Settlement runs on a T+1/T+2 cycle with pending → settled/failed milestones; an accumulating snapshot captures fails and settlement lag.
Security master as conformed SCD2 dimension
Sector, ratings, and identifiers change over time; SCD2 preserves the attributes as-of trade date for accurate historical attribution.
Tables in the Capital Markets Trading & Investment model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Fact tables (6)
fct_order
Grain: one row per order
Order-grain fact capturing intent before execution.
| Column | Type | Keys |
|---|---|---|
| order_key | bigint | PK |
| date_key | bigint | |
| security_key | bigint | |
| account_key | bigint | |
| trader_key | bigint | |
| order_type_key | bigint | |
| exchange_key | bigint | |
| order_id | varchar | |
| side | varchar | |
| order_quantity | decimal | |
| limit_price | decimal | |
| order_status | varchar | |
| filled_quantity | decimal |
fct_execution
Grain: one row per execution / fill
Execution-grain fact — the core trade event.
| Column | Type | Keys |
|---|---|---|
| execution_key | bigint | PK |
| date_key | bigint | |
| security_key | bigint | |
| account_key | bigint | |
| counterparty_key | bigint | |
| trader_key | bigint | |
| exchange_key | bigint | |
| settle_date_key | bigint | |
| trade_id | varchar | |
| side | varchar | |
| executed_quantity | decimal | |
| execution_price | decimal | |
| gross_amount | decimal | |
| commission | decimal | |
| exchange_fees | decimal | |
| tax_amount | decimal | |
| currency | varchar |
fct_position_snapshot
Grain: one row per account per security per day (periodic snapshot)
Daily holdings snapshot — semi-additive market value.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| account_key | bigint | |
| security_key | bigint | |
| portfolio_key | bigint | |
| quantity | decimal | |
| average_cost | decimal | |
| market_price | decimal | |
| market_value | decimal | |
| cost_basis | decimal | |
| unrealized_pnl | decimal | |
| currency | varchar |
fct_settlement
Grain: one row per trade settlement (accumulating snapshot)
Accumulating snapshot tracking the settlement cycle.
| Column | Type | Keys |
|---|---|---|
| settlement_key | bigint | PK |
| date_key | bigint | |
| security_key | bigint | |
| account_key | bigint | |
| counterparty_key | bigint | |
| settle_date_key | bigint | |
| trade_id | varchar | |
| settlement_amount | decimal | |
| currency | varchar | |
| settlement_status | varchar | |
| fail_reason | varchar | |
| days_to_settle | smallint |
fct_daily_pnl
Grain: one row per portfolio per day (periodic snapshot)
Daily P&L attribution per portfolio.
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | |
| portfolio_key | bigint | |
| account_key | bigint | |
| realized_pnl | decimal | |
| unrealized_pnl | decimal | |
| trading_fees | decimal | |
| net_pnl | decimal | |
| gross_market_value | decimal | |
| daily_return_pct | decimal |
fct_corporate_action
Grain: one row per corporate action applied to a holding
Corporate actions affecting positions (dividends, splits, etc.).
| Column | Type | Keys |
|---|---|---|
| corporate_action_key | bigint | PK |
| date_key | bigint | |
| security_key | bigint | |
| account_key | bigint | |
| action_id | varchar | |
| action_type | varchar | |
| action_ratio | decimal | |
| cash_amount_per_share | decimal | |
| total_cash_amount | decimal | |
| currency | varchar |
Dimension tables (6)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| is_trading_day | boolean |
dim_security
Grain: one row per security per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| security_key | bigint | PK |
| security_id | varchar | BK |
| isin | varchar | |
| cusip | varchar | |
| ticker | varchar | |
| security_name | varchar | |
| asset_class | varchar | |
| instrument_type | varchar | |
| issuer_name | varchar | |
| sector | varchar | |
| currency | varchar | |
| issue_date | date | |
| maturity_date | date | |
| coupon_rate | decimal | |
| contract_size | decimal |
dim_account
Grain: one row per trading account per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| account_key | bigint | PK |
| account_id | varchar | BK |
| account_number | varchar | PII |
| account_type | varchar | |
| legal_entity | varchar | |
| base_currency | varchar | |
| open_date | date | |
| account_status | varchar |
dim_portfolio
Grain: one row per portfolio / fund
| Column | Type | Keys |
|---|---|---|
| portfolio_key | bigint | PK |
| portfolio_id | varchar | BK |
| portfolio_name | varchar | |
| strategy | varchar | |
| mandate | varchar | |
| benchmark | varchar | |
| base_currency | varchar | |
| portfolio_manager | varchar | PII |
dim_counterparty
Grain: one row per trading counterparty
| Column | Type | Keys |
|---|---|---|
| counterparty_key | bigint | PK |
| counterparty_id | varchar | BK |
| counterparty_name | varchar | |
| lei | varchar | |
| counterparty_type | varchar | |
| country | varchar | |
| credit_rating | varchar |
dim_trader
Grain: one row per trader
| Column | Type | Keys |
|---|---|---|
| trader_key | bigint | PK |
| trader_id | varchar | BK |
| trader_name | varchar | PII |
| desk | varchar | |
| book | varchar | |
| license_id | varchar |
Lookup tables (2)
dim_order_type
Grain: one row per order type
| Column | Type | Keys |
|---|---|---|
| order_type_key | bigint | PK |
| order_type_code | varchar | BK |
| order_type_name | varchar | |
| time_in_force | varchar |
dim_exchange
Grain: one row per exchange / venue
| Column | Type | Keys |
|---|---|---|
| exchange_key | bigint | PK |
| exchange_code | varchar | BK |
| exchange_name | varchar | |
| mic | varchar | |
| country | varchar | |
| timezone | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_order.date_key → dim_date (many-to-one)
- fct_order.security_key → dim_security (many-to-one)
- fct_order.account_key → dim_account (many-to-one)
- fct_order.trader_key → dim_trader (many-to-one)
- fct_order.order_type_key → dim_order_type (many-to-one)
- fct_order.exchange_key → dim_exchange (many-to-one)
- fct_execution.date_key → dim_date (many-to-one)
- fct_execution.security_key → dim_security (many-to-one)
- fct_execution.account_key → dim_account (many-to-one)
- fct_execution.counterparty_key → dim_counterparty (many-to-one)
- fct_execution.trader_key → dim_trader (many-to-one)
- fct_execution.exchange_key → dim_exchange (many-to-one)
- fct_position_snapshot.date_key → dim_date (many-to-one)
- fct_position_snapshot.account_key → dim_account (many-to-one)
- fct_position_snapshot.security_key → dim_security (many-to-one)
- fct_position_snapshot.portfolio_key → dim_portfolio (many-to-one)
- fct_settlement.date_key → dim_date (many-to-one)
- fct_settlement.security_key → dim_security (many-to-one)
- fct_settlement.account_key → dim_account (many-to-one)
- fct_settlement.counterparty_key → dim_counterparty (many-to-one)
- fct_daily_pnl.date_key → dim_date (many-to-one)
- fct_daily_pnl.portfolio_key → dim_portfolio (many-to-one)
- fct_daily_pnl.account_key → dim_account (many-to-one)
- fct_corporate_action.date_key → dim_date (many-to-one)
- fct_corporate_action.security_key → dim_security (many-to-one)
- fct_corporate_action.account_key → dim_account (many-to-one)
Related templates
All templates →- Kimball
Core Banking Analytics
Banking & Financial Services
- Kimball
AML & Financial-Crime Surveillance
Banking & Financial Services
- 3NF
Investment Management Platform — IBOR/ABOR (3NF)
Banking & Financial Services
- Data Vault 2.0
Investment Management EDW — Aladdin-style (Data Vault 2.0)
Banking & Financial Services
From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.