Clearing Statement Models¶

This page documents the EWE clearing bank statement models for ABN AMRO and BNP Paribas integration.

Overview¶

EWE maintains clearing accounts with two banks for exchange-traded derivatives:

Bank	Full Name	Data Types
ABN	ABN AMRO Clearing Bank N.V.	Cash movements, summaries, P&S, transactions, positions
BNP	BNP Paribas S.A.	Journal entries, margin calls, P&S, trades, positions

Unified Position Model¶

fct_clearing_statement_open_position¶

Consolidated view of clearing bank open positions from both banks.

Purpose: Single source for exchange-traded position analysis across clearing banks.

Grain: metric_date + account + exchange + symbol + buy_sell

Column	Type	Description
`metric_date`	DATE	Statement date
`dim_party_external_bu_le_attribute_id`	INTEGER	FK to clearing bank party
`account`	VARCHAR	Clearing account number
`exchange`	VARCHAR	Exchange code (EEX, ICE, etc.)
`symbol`	VARCHAR	Contract symbol
`isin`	VARCHAR	ISIN identifier
`buy_sell`	VARCHAR	Direction: 'Buy' or 'Sell'
`contract_size`	NUMBER	Contract size
`quantity`	NUMBER	Number of contracts
`volume`	NUMBER	Total volume (contracts * size)
`delivery_start_date`	DATE	Contract delivery start
`delivery_end_date`	DATE	Contract delivery end
`ote`	NUMBER	Open trade equity
`trade_date`	DATE	Original trade date
`trade_price`	NUMBER	Original trade price
`settlement_price`	NUMBER	Current settlement price
`previous_settlement_price`	NUMBER	Prior day settlement
`variation_margin`	NUMBER	Daily margin movement

Party Dimension: - ABN positions link to bunit_name = 'ABN AMRO CLEARING BANK N.V. BU' - BNP positions link to bunit_name = 'BNP PARIBAS CLEARING BU'

Example Queries:

-- Open positions by clearing bank
SELECT
    pe.bunit_name AS clearing_bank,
    f.exchange,
    f.symbol,
    f.buy_sell,
    SUM(f.quantity) AS contracts,
    SUM(f.volume) AS total_volume,
    SUM(f.ote) AS total_ote
FROM fct_clearing_statement_open_position f
JOIN dim_party_external pe
    ON f.dim_party_external_bu_le_attribute_id = pe.dim_party_external_bu_le_attribute_id
WHERE f.metric_date = CURRENT_DATE - 1
GROUP BY pe.bunit_name, f.exchange, f.symbol, f.buy_sell
ORDER BY pe.bunit_name, f.exchange, f.symbol

-- Variation margin by bank
SELECT
    pe.bunit_name AS clearing_bank,
    SUM(f.variation_margin) AS total_var_margin,
    SUM(f.ote) AS total_ote
FROM fct_clearing_statement_open_position f
JOIN dim_party_external pe
    ON f.dim_party_external_bu_le_attribute_id = pe.dim_party_external_bu_le_attribute_id
WHERE f.metric_date = CURRENT_DATE - 1
GROUP BY pe.bunit_name

-- Position changes day-over-day
WITH today AS (
    SELECT * FROM fct_clearing_statement_open_position
    WHERE metric_date = CURRENT_DATE - 1
),
yesterday AS (
    SELECT * FROM fct_clearing_statement_open_position
    WHERE metric_date = CURRENT_DATE - 2
)
SELECT
    COALESCE(t.exchange, y.exchange) AS exchange,
    COALESCE(t.symbol, y.symbol) AS symbol,
    COALESCE(t.buy_sell, y.buy_sell) AS buy_sell,
    COALESCE(t.quantity, 0) AS today_qty,
    COALESCE(y.quantity, 0) AS yesterday_qty,
    COALESCE(t.quantity, 0) - COALESCE(y.quantity, 0) AS change
FROM today t
FULL OUTER JOIN yesterday y
    ON t.exchange = y.exchange
    AND t.symbol = y.symbol
    AND t.buy_sell = y.buy_sell
    AND t.account = y.account
WHERE COALESCE(t.quantity, 0) != COALESCE(y.quantity, 0)
ORDER BY ABS(COALESCE(t.quantity, 0) - COALESCE(y.quantity, 0)) DESC

ABN AMRO Models¶

fct_raw_abn_open_positions¶

Raw ABN AMRO open position data.

Column	Description
`processing_date`	Statement date
`account`	Clearing account
`exchange`	Exchange code
`symbol`	Contract symbol
`buy_sell`	B or S
`quantity`	Contract quantity
`valuation_price`	Current price
`previous_valuation_price`	Prior price

fct_raw_abn_daily_cash_movement¶

Daily cash movements on clearing accounts.

fct_raw_abn_daily_cash_summary_account_level¶

Account-level cash summary.

fct_raw_abn_daily_cash_summary_client_level¶

Client-level cash summary.

fct_raw_abn_daily_purchase_and_sales¶

Daily purchases and sales.

fct_raw_abn_daily_trxs¶

Daily transactions detail.

BNP Paribas Models¶

fct_raw_bnp_open_positions¶

Raw BNP open position data.

Column	Description
`cob`	Close of business date
`account`	Clearing account
`exchange`	Exchange code
`symbol`	Contract symbol
`buy_sell`	Buy or Sell
`qty`	Contract quantity
`sett_price`	Settlement price
`prev_sett_price`	Previous settlement

fct_raw_bnp_journal_entries¶

Journal entry details.

fct_raw_bnp_mc_statement¶

Margin call statements.

fct_raw_bnp_pns¶

Purchases and sales.

fct_raw_bnp_pns_with_com¶

Purchases and sales with commissions.

fct_raw_bnp_trades¶

Trade execution details.

Data Pipeline¶

Source Files¶

Clearing banks send daily files that are loaded via Snowpipe:

Clearing Pipeline

Column Mapping¶

The unified model standardizes columns from both banks:

Unified Column	ABN Source	BNP Source
`metric_date`	`processing_date`	`cob`
`quantity`	`quantity`	`qty`
`settlement_price`	`valuation_price`	`sett_price`
`previous_settlement_price`	`previous_valuation_price`	`prev_sett_price`
`buy_sell`	`buy_sell` (standardized)	`buy_sell`

Buy/Sell Standardization¶

Both banks' data is standardized to 'Buy' or 'Sell':

CASE
    WHEN buy_sell IN ('B', 'BUY', 'Buy') THEN 'Buy'
    WHEN buy_sell IN ('S', 'SELL', 'Sell') THEN 'Sell'
    ELSE buy_sell
END AS buy_sell

Feature Flag¶

Clearing statement models are controlled by the feed.clearing_statements feature flag.

When disabled: - All clearing statement models are not built - No data appears in fct_clearing_statement_open_position

To enable, set in dbt_project.yml or var override:

vars:
  feed.clearing_statements: true

Data Monitoring¶

fct_data_arrival¶

Monitor clearing statement data arrival:

SELECT
    metric_date,
    abn_clearing_arrival,
    bnp_clearing_arrival
FROM fct_data_arrival
WHERE metric_date >= CURRENT_DATE - 7
ORDER BY metric_date DESC

Missing Data Investigation¶

-- Check if data arrived for specific date
SELECT COUNT(*) AS record_count
FROM fct_clearing_statement_open_position
WHERE metric_date = CURRENT_DATE - 1

-- Check by bank
SELECT
    pe.bunit_name,
    COUNT(*) AS records
FROM fct_clearing_statement_open_position f
JOIN dim_party_external pe USING (dim_party_external_bu_le_attribute_id)
WHERE f.metric_date = CURRENT_DATE - 1
GROUP BY pe.bunit_name

Best Practices¶

Use unified model: fct_clearing_statement_open_position provides consistent data across banks
Join to party dimension: Identify clearing bank via dim_party_external
Check data arrival: Use fct_data_arrival to verify daily data receipt
Compare to prior day: Track position changes for reconciliation
Monitor variation margin: Important for cash management

Query Patterns - Clearing Statements - SQL examples
Business Glossary - Clearing Terms - Terminology
Troubleshooting - Clearing Data - Common issues