Navigation :

TRADE_ATTRIBUTES

The TRADE_ATTRIBUTES table contains the fields describing attributes of the trades.

Column Name	Type	Not Null	Cube Field	Default Value¹	Description
AS_OF_DATE	DATE	Y			Timestamp (at close of business) for the data.
TRADE_KEY	STRING	Y		N/A	Unique Trade (or Position) ID.
BOOK	STRING	Y	[Booking].[Books]	N/A	The book to map the trade to (must match the node in the Book Hierarchy).
LEGAL_ENTITY	STRING	Y	[Organization].[Legal Entities]	N/A	Legal Entity to map the trade to (must match the node in the Legal Entity Hierarchy).
COUNTERPARTY_ID	STRING	Y	[Counterparties].[CounterpartyIds]	N/A	Counterparty to map the trade to (must match the node in the Counterparty Hierarchy).
NOTIONAL	DOUBLE				Notional of the trade/position.
NOTIONAL_CCY	STRING	Y	[TradeAttributes].[NotionalCurrencies]	N/A	Currency of the notional trade.
TRADER	STRING	Y	[TradeAttributes].[Traders]	N/A	Trader who performed the trade.
SALES	STRING	Y	[TradeAttributes].[Sales]	N/A	Salesperson who performed the sale of the trade (if applicable).
INSTRUMENT_CLASS	STRING	Y	[Instruments].[InstrumentClasses]	N/A	Highest level of instrument classification.
INSTRUMENT_TYPE	STRING	Y	[Instruments].[InstrumentTypes]	N/A	Main instrument classification.
INSTRUMENT_SUB_TYPE	STRING	Y	[Instruments].[InstrumentTypes].[InstrumentSubeType]	N/A	Sub-level of instrument classification.
TRADE_DATE	STRING	Y	[TradeAttributes].[TradeDates]	N/A	Date of the execution of the trade.
MATURITY_DATE	STRING	Y	[TradeAttributes].[MaturityDates]	N/A	Maturity date of the trade.
VAR_INCLUSION_TYPE	STRING	Y		R	Defines on what basis to include the VaR of this trade: ‘R’ for repricing ‘S’ for sensitivity,

Unique Key

Columns
AS_OF_DATE
TRADE_KEY

Incoming Joins

Source Table	Source Columns	Target Columns
TRADEPNLS	AS_OF_DATE TRADE_KEY	AS_OF_DATE TRADE_KEY
TRADE_ATTRIBUTES	AS_OF_DATE TRADE_KEY	AS_OF_DATE TRADE_KEY
TRADE_SENSITIVITIES	AS_OF_DATE TRADE_KEY	AS_OF_DATE TRADE_KEY
PNL	AS_OF_DATE TRADE_KEY	AS_OF_DATE TRADE_KEY

Outgoing Joins

Target Table	Source Columns	Target Columns
LEGAL_ENTITY_HIERARCHY	AS_OF_DATE LEGAL_ENTITY	AS_OF_DATE LEGAL_ENTITY
COUNTERPARTY_HIERARCHY	AS_OF_DATE COUNTERPARTY_ID	AS_OF_DATE COUNTERPARTY_ID
BOOK_HIERARCHY	AS_OF_DATE BOOK	AS_OF_DATE BOOK
COUNTERPARTIES	AS_OF_DATE COUNTERPARTY_ID	AS_OF_DATE COUNTERPARTY_ID

Table creation script

Snowflake

Table creation

create OR REPLACE table TRADE_ATTRIBUTES(
	AS_OF_DATE DATE NOT NULL,
	TRADE_KEY STRING NOT NULL DEFAULT 'N/A',
	BOOK STRING NOT NULL DEFAULT 'N/A',
	LEGAL_ENTITY STRING NOT NULL DEFAULT 'N/A',
	COUNTERPARTY_ID STRING NOT NULL DEFAULT 'N/A',
	NOTIONAL DOUBLE,
	NOTIONAL_CCY STRING NOT NULL DEFAULT 'N/A',
	TRADER STRING NOT NULL DEFAULT 'N/A',
	SALES STRING NOT NULL DEFAULT 'N/A',
	INSTRUMENT_CLASS STRING NOT NULL DEFAULT 'N/A',
	INSTRUMENT_TYPE STRING NOT NULL DEFAULT 'N/A',
	INSTRUMENT_SUB_TYPE STRING NOT NULL DEFAULT 'N/A',
	TRADE_DATE STRING NOT NULL DEFAULT 'N/A',
	MATURITY_DATE STRING NOT NULL DEFAULT 'N/A',
	VAR_INCLUSION_TYPE STRING NOT NULL DEFAULT 'R',
	primary key (AS_OF_DATE, TRADE_KEY),
	foreign key (AS_OF_DATE, BOOK) references BOOK_HIERARCHY(AS_OF_DATE, BOOK),
	foreign key (AS_OF_DATE, LEGAL_ENTITY) references LEGAL_ENTITY_HIERARCHY(AS_OF_DATE, LEGAL_ENTITY),
	foreign key (AS_OF_DATE, COUNTERPARTY_ID) references COUNTERPARTY_HIERARCHY(AS_OF_DATE, COUNTERPARTY_ID),
	foreign key (AS_OF_DATE, COUNTERPARTY_ID) references COUNTERPARTIES(AS_OF_DATE, COUNTERPARTY_ID)
);

Table population from files (optional)

If you want to populate the table from CSV files, you can run the following scripts (this example assumes that you want to load the files TradeAttributes.csv present in the folders 2023-09-26, 2023-09-27 and 2023-09-28):

create OR REPLACE stage MR_INPUT_RAW;
create OR REPLACE file format CSV_GZIP
TYPE = CSV
COMPRESSION = GZIP
SKIP_HEADER = 1
comment = 'File format to import MR data';
create OR REPLACE file format CSV_FORMAT
TYPE = CSV
COMPRESSION = AUTO
SKIP_HEADER = 1
comment = 'File format to import MR data';
PUT file://<full path to you data folder>/2023-09-26/TradeAttributes.csv @MR_INPUT_RAW/2023-09-26;
PUT file://<full path to you data folder>/2023-09-27/TradeAttributes.csv @MR_INPUT_RAW/2023-09-27;
PUT file://<full path to you data folder>/2023-09-28/TradeAttributes.csv @MR_INPUT_RAW/2023-09-28;
copy into TRADE_ATTRIBUTES from (
select
$1,
$2,
ifnull($3, 'N/A'),
ifnull($4, 'N/A'),
ifnull($5, 'N/A'),
$6,
ifnull($7, 'N/A'),
ifnull($8, 'N/A'),
ifnull($9, 'N/A'),
ifnull($10, 'N/A'),
ifnull($11, 'N/A'),
ifnull($12, 'N/A'),
ifnull($13, 'N/A'),
ifnull($14, 'N/A'),
ifnull($15, 'R')
from @MR_INPUT_RAW (pattern => './TradeAttributes..csv.gz' , FILE_FORMAT => 'CSV_GZIP'));

If the default value is marked as empty, it means that the default value is 'null' for nullable fields, and that a value needs to be explicitly set for non-nullable fields. ↩︎