☰
Navigation :

RISK_FACTORS_CATALOGUE

The RISK_FACTORS_CATALOGUE table contains enrichment data for risk factors.

Column Name Type Not Null Cube Field Default Value1 Description
AS_OF_DATE DATE Y Timestamp (at close of business) for the data.
RISK_FACTOR_ID STRING Y [Risk].[Risk Factors] N/A Internal risk factor/bucket identifier: instrument, curve, vol surface/cube identifier.
RISK_CLASS STRING Y [Risk].[Risk Classes] N/A Risk factor’s asset class: “Interest rate”, “Credit spread”, “Foreign exchange”, “Equity”, “Commodity”, “Hybrid”.
QUALIFIER STRING Y [Risk].[Qualifiers] N/A Identifier of a risk factor’s set.
RISK_FACTOR_TYPE STRING Y [Risk].[RiskFactorTypes] N/A Type of underlying risk factor.
RISK_FACTOR_CCY STRING Y [Risk].[RiskFactorCurrencies] N/A Three-letter ISO currency code that represents the currency of the risk factor.
CURVE_TYPE STRING Y [Risk].[CurveTypes] N/A Only populated if the risk class is a rates curve, otherwise left blank. Specifies the type of the curve. For example, “Interest rate”, “Tenor basis” or “Inflation”.

Unique Key

Columns
AS_OF_DATE
RISK_FACTOR_ID

Incoming Joins

Source Table Source Columns Target Columns
TRADEPNLS AS_OF_DATE
RISK_FACTOR		 AS_OF_DATE
RISK_FACTOR_ID
PNL AS_OF_DATE
RISK_FACTOR_ID		 AS_OF_DATE
RISK_FACTOR_ID
TRADE_SENSITIVITIES_VECTOR AS_OF_DATE
RISK_FACTOR_ID		 AS_OF_DATE
RISK_FACTOR_ID
TRADE_SENSITIVITIES_VECTOR AS_OF_DATE
RISK_FACTOR_ID2		 AS_OF_DATE
RISK_FACTOR_ID

Table creation script

Snowflake

Table creation

create OR REPLACE table RISK_FACTORS_CATALOGUE(
	AS_OF_DATE DATE NOT NULL,
	RISK_FACTOR_ID STRING NOT NULL DEFAULT 'N/A',
	RISK_CLASS STRING NOT NULL DEFAULT 'N/A',
	QUALIFIER STRING NOT NULL DEFAULT 'N/A',
	RISK_FACTOR_TYPE STRING NOT NULL DEFAULT 'N/A',
	RISK_FACTOR_CCY STRING NOT NULL DEFAULT 'N/A',
	CURVE_TYPE STRING  NOT NULL DEFAULT 'N/A',
	primary key (AS_OF_DATE, RISK_FACTOR_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 RiskFactorsCatalog.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/RiskFactorsCatalog.csv @MR_INPUT_RAW/2023-09-26;
PUT file://<full path to you data folder>/2023-09-27/RiskFactorsCatalog.csv @MR_INPUT_RAW/2023-09-27;
PUT file://<full path to you data folder>/2023-09-28/RiskFactorsCatalog.csv @MR_INPUT_RAW/2023-09-28;


copy into RISK_FACTORS_CATALOGUE from (
select
$1,
$2,
ifnull($3, 'N/A'),
ifnull($4, 'N/A'),
ifnull($5, 'N/A'),
ifnull($6, 'N/A'),
ifnull($7, 'N/A')
from @MR_INPUT_RAW (pattern => './RiskFactorsCatalog..csv.gz' , FILE_FORMAT => 'CSV_GZIP'));

  1. 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.  ↩︎