FXRATES

The FXRATES table contains all the FX Rates. It is an isolated table and not part of any cube facts.

FX Rates are looked up via the default implementation of IFXRates API.

Unique Key

Table creation script

Snowflake

Table creation

create OR REPLACE table FXRATES(
	AS_OF_DATE DATE NOT NULL,
	MARKET_DATA_SET STRING NOT NULL DEFAULT 'N/A',
	BASE_CCY STRING NOT NULL DEFAULT 'N/A',
	COUNTER_CCY STRING NOT NULL DEFAULT 'N/A',
	TERM STRING NOT NULL DEFAULT 'N/A',
	RATE DOUBLE NOT NULL DEFAULT 1.0,
	RISK_FACTOR_ID STRING,
	primary key (AS_OF_DATE, MARKET_DATA_SET, BASE_CCY, COUNTER_CCY, TERM)
);

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 FXRates.csv present in the folders 2023-09-25, 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-25/FXRates.csv @MR_INPUT_RAW/2023-09-25;
PUT file://<full path to you data folder>/2023-09-26/FXRates.csv @MR_INPUT_RAW/2023-09-26;
PUT file://<full path to you data folder>/2023-09-27/FXRates.csv @MR_INPUT_RAW/2023-09-27;
PUT file://<full path to you data folder>/2023-09-28/FXRates.csv @MR_INPUT_RAW/2023-09-28;
copy into FXRATES from (
select
$1,
ifnull($2, 'N/A'),
$3,
$4,
ifnull($5, 'N/A'),
ifnull($6, 1.0),
$7
from @MR_INPUT_RAW (pattern => './FXRates..csv.gz' , FILE_FORMAT => 'CSV_GZIP'));