SENSI_LADDERS_VECTOR

The SENSI_LADDERS_VECTOR table contains the values of the sensitivity ladder shift.

Unique Key

Outcoming Joins

Table creation script

Snowflake

Table creation

create OR REPLACE table SENSI_LADDERS_VECTOR(
  VECTOR_INDEX INT NOT NULL,
  AS_OF_DATE DATE NOT NULL,
  RISK_CLASS STRING NOT NULL DEFAULT 'N/A',
  SCALE DOUBLE NOT NULL DEFAULT 0.0,
  PRIMARY KEY(VECTOR_INDEX, AS_OF_DATE, RISK_CLASS),
  foreign key (AS_OF_DATE,RISK_CLASS) references SENSI_LADDERS(AS_OF_DATE,RISK_CLASS)
);

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/LadderDefinition.csv' @MR_INPUT_RAW/2023-09-26;
PUT 'file://<full path to you data folder>/2023-09-27/LadderDefinition.csv' @MR_INPUT_RAW/2023-09-27;
PUT 'file://<full path to you data folder>/2023-09-28/LadderDefinition.csv' @MR_INPUT_RAW/2023-09-28;
insert into SENSI_LADDERS_VECTOR
select
v.index,
d.$1 as AS_OF_DATE,
ifnull(d.$2, 'N/A') as TENOR_LABELS,
to_double(v.value) as SCALE
from
(select $1, $2, $4 from @MR_INPUT_RAW (pattern => '^.LadderDefinition..csv$' , FILE_FORMAT => 'CSV_FORMAT')) d,
lateral flatten(input => STRTOK_TO_ARRAY($4,';')) v;