SA ETL for SBM delta

This page provides an overview of ETL for SBM Delta, including the following breakdowns:

Table showing you the fields included in each “SBM” row of a Delta Sensitivity CSV Input Data file, indicating which stores these fields are copied to during the ETL process.
Key fields for vector creation and the fields that have the potential to be populated with vectors, as opposed to always containing scalar values.

Vectorization

The following table provides information about the vectors employed within the CSV Input files for each risk class:

Risk Class	Vector Information
- GIRR - CSR non-Sec - CSR Sec CTP - CSR Sec non-CTP - Equity - Commodity - FX	If Delta sensitivities are provided in the input file as a vector, the indices of the vectors are the sensitivity dates.

Un-Vectorization

Vectors within the input are split into several scalar lines by the RiskClassTuplePublisher. For each SensitivityDate in the vectorized input, the corresponding index of DeltaSensitivities is used to create scalar tuples under the scalar risk factor. The scalar risk factor is the concatenation of the input RiskFactor and the SensitivityDate, using a space as a delimiter. If SensitivityDates is not provided, a default of “0Y” is used.

Normalization

Delta-relevant stores

The stores that are relevant for Delta are:

Stores
SASensitivities
RiskFactorDescription
UnderlyingDescription

Mapping of SBM Delta CSV file fields onto the stores that they populate

Field from CSV file OR ColumnCalculator (if the CSV file field is null)	SASensitivities	RiskFactorDescription	UnderlyingDescription
AsOfDate	(AsOfDate)	(AsOfDate)	(AsOfDate)
TradeId	(TradeKey for trade level data)
Book	(part of TradeKey for summary data)
LegalEntity	(part of TradeKey for summary data)
Ccy
DeltaSensitivities (vector-valued field)
RiskClass	(Risk Class)	(Risk Class)	(Risk Class)
SensitivityDates (vector-valued field)		(SensitivityDates)
RiskFactor (if missing `RiskFactorColumnCalculator` is used)	(RiskFactor)	#(RiskFactor)
Type		(RiskFactorType)	(GIRR Curve Type)
GIRRCcy			(GIRR Ccy)
Underlying (if missing, `UnderlyingColumnCalculator` is used)		(Underlying)	(Underlying)
CSRQuality			(CSRQuality)
CSRSector			(CSRSector)
CSRTrance (Deprecated)
EquityEconomy			(EquityEconomy)
EquityMarketCap			(EquityMarketCap)
EquitySector			(EquitySector)
CommodityLocation		(CommodityLocation)
CommodityTime (Deprecated)
CommodityGrade (Deprecated)
CommodityRoute (Deprecated)
FXCounterCurrency		(FXCounterCurrency)
OriginalOptionality
CSRRating
FxComplexDelta
FxOtherCcy
FxDividerEligibility
Bucket
Pool
Attachment
Detachment
UnderlyingFXOriginalCcy (populated via UnderlyingFxCcyColumnCalculator)			(UnderlyingFXOriginalCcy)

Column Calculators and Tuple Publishers

RiskFactorColumnCalculator

If Risk Factor is not provided, RiskFactorColumnCalculator will create one based on risk class the table below explains how Risk Factors are derived.

Risk Class	Creation Method for RiskFactor
Commodity	Derived by concatenating Underlying and CommodityLocation, using ‘space’ as a delimiter
GIRR	Underlying is used
Equity	Derived by concatenating Underlying and RiskFactorType, using ‘space’ as a delimiter
CSR non-Sec	Derived by concatenating Underlying and RiskFactorType, using ‘space’ as a delimiter
CSR Sec CTP	Derived by concatenating Underlying and RiskFactorType, using ‘space’ as a delimiter
CSR Sec non-CTP	Derived by concatenating Underlying and RiskFactorType, using ‘space’ as a delimiter
FX	If FxCounterCcy is empty, Underlying is used, otherwise derived by concatenating Underlying and FxCounterCcy, using “\|” as a delimiter

If sbm.risk-factor.always-append-tenor is set to true, then tenor will also be added to Risk Factor.

The Tuple Publisher and Publisher classes

The function of the TuplePublisher and its associated Publisher classes is to separate data in the incoming file row according to its relevance to particular stores and apply ETL logic to the incoming rows:

Tuple Publisher Class	Publisher Class	Function
RiskClassTuplePublisher		Splits fields according to the following criteria: - fields relevant to DeltaPublisher OR VegaPublisher OR CurvaturePublisher - fields relevant to StaticDataPublisher classes for RiskFactorDescription and UnderlyingDescription stores. Note: If multiple sensitivities are held on the same line, the line is de-multiplexed here.
	DeltaPublisher	DeltaPublisher publishes to the SASensitivities store.
	RiskFactorPublisher (extends StaticDataPublisher)	RiskFactorPublisher publishes to the RiskFactorDescription store.
	UnderlyingPublisher (extends StaticDataPublisher)	UnderlyingPublisher publishes to the UnderlyingDescription store.

​Vectorization

​Un-Vectorization

​Normalization

​Delta-relevant stores

​Mapping of SBM Delta CSV file fields onto the stores that they populate

​Column Calculators and Tuple Publishers

​RiskFactorColumnCalculator

​The Tuple Publisher and Publisher classes

Vectorization

Un-Vectorization

Normalization

Delta-relevant stores

Mapping of SBM Delta CSV file fields onto the stores that they populate

Column Calculators and Tuple Publishers

RiskFactorColumnCalculator

The Tuple Publisher and Publisher classes