☰
Navigation :

COUNTERPARTIES

The COUNTERPARTIES table contains data for counterparties.

Column Name Type Not Null Default Value1 Cube Field Description
AS_OF_DATE DATE Y Timestamp (at close of business) for the data.
COUNTERPARTY_ID STRING Y N/A [Counterparties].[CounterpartyIds] Counterparty identifier. Used as a foreign key when counterparty is referenced.
COUNTERPARTY_NAME STRING Y N/A [Counterparties].[CounterpartyNames] Full counterparty name.
RATING STRING Y N/A [Counterparties].[CounterpartyRatings] Rating of the counterparty.
SECTOR STRING Y N/A [Counterparties].[CounterpartySectors] Sector of the counterparty.
COUNTRY_OF_ADDRESS STRING Y N/A [Counterparties].[CounterpartyCountriesOfAddress] Country where the counterparty is located, in the form of a unique three-letter country identifier code.
COUNTRY_OF_RISK STRING Y N/A [Counterparties].[CounterpartyCountriesOfRisk] Country the risk of counterparty can be attributed to, in the form of a unique three-letter country identi

Unique Key

Columns
AS_OF_DATE
COUNTERPARTY_ID

Incoming Joins

Source Table Source Columns Target Columns
TRADE_ATTRIBUTES AS_OF_DATE
COUNTERPARTY_ID		 AS_OF_DATE
COUNTERPARTY_ID

Outgoing Joins

Target Table Source Columns Target Columns
COUNTRIES AS_OF_DATE
COUNTRY_OF_ADDRESS		 AS_OF_DATE
COUNTRY_CODE
COUNTRIES AS_OF_DATE
COUNTRY_OF_RISK		 AS_OF_DATE
COUNTRY_CODE

Table creation script

Snowflake

Table creation

create OR REPLACE table COUNTERPARTIES(
	AS_OF_DATE DATE NOT NULL,
	COUNTERPARTY_ID STRING NOT NULL DEFAULT 'N/A',
	COUNTERPARTY_NAME STRING NOT NULL DEFAULT 'N/A',
	RATING STRING NOT NULL DEFAULT 'N/A',
	SECTOR STRING NOT NULL DEFAULT 'N/A',
	COUNTRY_OF_ADDRESS STRING NOT NULL DEFAULT 'N/A',
	COUNTRY_OF_RISK STRING  NOT NULL DEFAULT 'N/A',
	primary key (AS_OF_DATE, COUNTERPARTY_ID),
    foreign key (AS_OF_DATE, COUNTRY_OF_ADDRESS) references COUNTRIES(AS_OF_DATE, COUNTRY_CODE),
    foreign key (AS_OF_DATE, COUNTRY_OF_RISK) references COUNTRIES(AS_OF_DATE, COUNTRY_CODE)
);

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 Counterparties.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/Counterparties.csv @MR_INPUT_RAW/2023-09-26;
PUT file://<full path to you data folder>/2023-09-27/Counterparties.csv @MR_INPUT_RAW/2023-09-27;
PUT file://<full path to you data folder>/2023-09-28/Counterparties.csv @MR_INPUT_RAW/2023-09-28;


copy into COUNTERPARTIES from (
select
$1,
$2,
$3,
ifnull($4, 'N/A'),
ifnull($5, 'N/A'),
ifnull($6, 'N/A'),
ifnull($7, 'N/A')
from @MR_INPUT_RAW (pattern => './Counterparties..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.  ↩︎