☰
Navigation :

COUNTERPARTY_PARENT_CHILD

The COUNTERPARTY_PARENT_CHILD table contains the parent/child relationships used to build the counterparty hierarchy.

note

The COUNTERPARTY_HIERARCHY table is populated from the COUNTERPARTY_PARENT_CHILD table.

Column Name Type Not Null Cube Field Default Value1 Description
AS_OF_DATE DATE Y Timestamp (at close of business) for the data.
CHILD STRING Y Identifier of the node in the Counterparty hierarchy.
PARENT STRING Identifier of the parent node (or null if there is no parent).

Unique Key

Columns
AS_OF_DATE
CHILD

Joins

There is a self-join on the COUNTERPARTY_PARENT_CHILD table - see database creation script for details.

Table creation script

Snowflake

Table creation

create OR REPLACE table COUNTERPARTY_PARENT_CHILD(
	AS_OF_DATE DATE NOT NULL,
	CHILD STRING,
	PARENT STRING,
	primary key (AS_OF_DATE, CHILD)
);

The content of this table is used to populate the COUNTERPARTY_HIERARCHY table; see the script here.

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


copy into COUNTERPARTY_PARENT_CHILD from @MR_INPUT_RAW pattern='./CounterpartyParentChild..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.  ↩︎