--- title: "Build a bridge table" canonical: "https://docs.vaultspeed.com/space/VPD/3039232005/Build%20a%20bridge%20table" format: markdown --- > Macro (toc) To build a Bridge Table, the base type bridge should be used after you define a bridge in the Business Vault Bridges screen In VaultSpeed. Choosing bridge as the base type will automatically include all the hubs and links added in the Bridge definition. Example: The following logic will be written in the template: DDL ```sql create table if not exists bv_layer.brg_campaigns_per_city ( brg_campaigns_per_city_hkey varchar(64) ,load_timestamp timestamp ,campaigns_hkey varchar(64) ,lnd_camp_prod_hkey varchar(64) ,products_hkey varchar(64) ,lnd_camp_cust_cont_hkey varchar(64) ,customers_hkey varchar(64) ,lnk_cust_addr_hkey varchar(64) ,addresses_hkey varchar(64) ,campaign_code_bk varchar(60) ,campaign_start_date_bk varchar(60) ,product_cc_bk varchar(120) ,product_et_code_bk varchar(30) ,product_part_code_bk varchar(150) ,customers_bk varchar(150) ,street_name_bk varchar(150) ,street_number_bk varchar(120) ,postal_code_bk varchar(18) ,city_bk varchar(150) ); ``` ELT/ETL ```sql CREATE OR REPLACE FUNCTION "moto_scn02_proc"."bv_etl_bridge_campaigns_per_city_bridge"() RETURNS void LANGUAGE 'plpgsql' AS $function$ /* __ __ _ _ _ __ ___ __ __ \ \ / /_ _ _ _| | |_ ___ ____ ___ ___ __| | \ \/ _ \/ / /_/ \ \ / / _` | | | | | __/ __| _ \ / _ \/ _ \/ _` | \/ / \ \/ /\ \ V / (_| | |_| | | |_\__ \ |_) | __/ __/ (_| | / / \/\ \/ / \_/ \__,_|\__,_|_|\__|___/ .__/ \___|\___|\__,_| /_/ \/_/\__/ |_| Vaultspeed version: 5.2.1.13, generation date: 2023/01/23 09:57:47 DV_NAME: moto_scn02 - Release: R1(1) - Comment: VaultSpeed setup automation - Release date: 2022/12/27 10:33:50, BV release: r3(3) - Comment: 3 - Release date: 2023/01/23 08:08:20, SRC_NAME: moto_sales_scn02 - Release: moto_sales_scn02(1) - Comment: VaultSpeed automated setup - Release date: 2022/12/27 10:30:54 */ BEGIN BEGIN -- bridge_table INSERT INTO "moto_scn02_bv"."bridge_campaigns_per_city"( "bridge_campaigns_per_city_hkey" ,"load_date" ,"addresses_hkey" ,"customers_hkey" ,"products_hkey" ,"campaigns_hkey" ,"lna_cust_addr_hkey" ,"lnd_camp_prod_hkey" ,"lnd_camp_cust_cont_hkey" ,"street_name_bk" ,"street_number_bk" ,"postal_code_bk" ,"city_bk" ,"product_cc_bk" ,"product_et_code_bk" ,"product_part_code_bk" ,"campaign_code_bk" ,"campaign_start_date_bk" ,"customers_bk" ) WITH "sat_src1_2" AS ( SELECT "sat_ed_src1_2"."addresses_hkey" AS "addresses_hkey" , "sat_ed_src1_2"."load_date" AS "load_date" , COALESCE(LEAD("sat_ed_src1_2"."load_date")OVER(PARTITION BY "sat_ed_src1_2"."addresses_hkey" ORDER BY "sat_ed_src1_2"."load_date") , TO_TIMESTAMP('31/12/2399 23:59:59.000000' , 'DD/MM/YYYY HH24:MI:SS.US'::varchar)) AS "load_end_date" , "sat_ed_src1_2"."delete_flag" AS "delete_flag" FROM "moto_scn02_fl"."sat_mm_addresses" "sat_ed_src1_2" ) , "sat_src3_3" AS ( SELECT "sat_ed_src3_3"."customers_hkey" AS "customers_hkey" , "sat_ed_src3_3"."load_date" AS "load_date" , COALESCE(LEAD("sat_ed_src3_3"."load_date")OVER(PARTITION BY "sat_ed_src3_3"."customers_hkey" ORDER BY "sat_ed_src3_3"."load_date") , TO_TIMESTAMP('31/12/2399 23:59:59.000000' , 'DD/MM/YYYY HH24:MI:SS.US'::varchar)) AS "load_end_date" , "sat_ed_src3_3"."delete_flag" AS "delete_flag" FROM "moto_scn02_fl"."sat_mm_customers" "sat_ed_src3_3" ) , "sat_src5_1" AS ( SELECT "sat_ed_src5_1"."campaigns_hkey" AS "campaigns_hkey" , "sat_ed_src5_1"."load_date" AS "load_date" , COALESCE(LEAD("sat_ed_src5_1"."load_date")OVER(PARTITION BY "sat_ed_src5_1"."campaigns_hkey" ORDER BY "sat_ed_src5_1"."load_date") , TO_TIMESTAMP('31/12/2399 23:59:59.000000' , 'DD/MM/YYYY HH24:MI:SS.US'::varchar)) AS "load_end_date" , "sat_ed_src5_1"."delete_flag" AS "delete_flag" FROM "moto_scn02_fl"."sat_mm_campaigns" "sat_ed_src5_1" ) , "sat_src7_2" AS ( SELECT "sat_ed_src7_2"."products_hkey" AS "products_hkey" , "sat_ed_src7_2"."load_date" AS "load_date" , COALESCE(LEAD("sat_ed_src7_2"."load_date")OVER(PARTITION BY "sat_ed_src7_2"."products_hkey" ORDER BY "sat_ed_src7_2"."load_date") , TO_TIMESTAMP('31/12/2399 23:59:59.000000' , 'DD/MM/YYYY HH24:MI:SS.US'::varchar)) AS "load_end_date" , "sat_ed_src7_2"."delete_flag" AS "delete_flag" FROM "moto_scn02_fl"."sat_mm_products" "sat_ed_src7_2" ) , "sat_src4_1" AS ( SELECT "sat_ed_src4_1"."lnd_camp_cust_cont_hkey" AS "lnd_camp_cust_cont_hkey" , "sat_ed_src4_1"."load_date" AS "load_date" , COALESCE(LEAD("sat_ed_src4_1"."load_date")OVER(PARTITION BY "sat_ed_src4_1"."lnd_camp_cust_cont_hkey" ORDER BY "sat_ed_src4_1"."load_date") , TO_TIMESTAMP('31/12/2399 23:59:59.000000' , 'DD/MM/YYYY HH24:MI:SS.US'::varchar)) AS "load_end_date" , "sat_ed_src4_1"."delete_flag" AS "delete_flag" FROM "moto_scn02_fl"."lds_mm_camp_cust_cont" "sat_ed_src4_1" ) , "sat_src6_2" AS ( SELECT "sat_ed_src6_2"."lnd_camp_prod_hkey" AS "lnd_camp_prod_hkey" , "sat_ed_src6_2"."load_date" AS "load_date" , COALESCE(LEAD("sat_ed_src6_2"."load_date")OVER(PARTITION BY "sat_ed_src6_2"."lnd_camp_prod_hkey" ORDER BY "sat_ed_src6_2"."load_date") , TO_TIMESTAMP('31/12/2399 23:59:59.000000' , 'DD/MM/YYYY HH24:MI:SS.US'::varchar)) AS "load_end_date" , "sat_ed_src6_2"."delete_flag" AS "delete_flag" FROM "moto_scn02_fl"."lds_mm_camp_prod_class" "sat_ed_src6_2" ) , "sat_src6_1" AS ( SELECT "sat_ed_src6_1"."lnd_camp_prod_hkey" AS "lnd_camp_prod_hkey" , "sat_ed_src6_1"."load_date" AS "load_date" , COALESCE(LEAD("sat_ed_src6_1"."load_date")OVER(PARTITION BY "sat_ed_src6_1"."lnd_camp_prod_hkey" ORDER BY "sat_ed_src6_1"."load_date") , TO_TIMESTAMP('31/12/2399 23:59:59.000000' , 'DD/MM/YYYY HH24:MI:SS.US'::varchar)) AS "load_end_date" , "sat_ed_src6_1"."delete_flag" AS "delete_flag" FROM "moto_scn02_fl"."lds_mm_camp_prod_emo" "sat_ed_src6_1" ) , "miv" AS ( SELECT UPPER(ENCODE(DIGEST( "dvo_src1"."addresses_hkey"::text || '#' || "dvo_src2"."customers_hkey"::text || '#' || "dvo_src3"."customers_hkey"::text || '#' || "dvo_src5"."campaigns_hkey"::text || '#' || "dvo_src7"."products_hkey"::text || '#' ,'MD5'),'HEX')) AS "bridge_campaigns_per_city_hkey" , "bvlwt_src"."fmc_begin_lw_timestamp" AS "load_date" , "dvo_src1"."addresses_hkey" AS "addresses_hkey" , "dvo_src3"."customers_hkey" AS "customers_hkey" , "dvo_src5"."campaigns_hkey" AS "campaigns_hkey" , "dvo_src7"."products_hkey" AS "products_hkey" , "dvo_src2"."lna_cust_addr_hkey" AS "lna_cust_addr_hkey" , "dvo_src4"."lnd_camp_cust_cont_hkey" AS "lnd_camp_cust_cont_hkey" , "dvo_src6"."lnd_camp_prod_hkey" AS "lnd_camp_prod_hkey" , "dvo_src1"."street_name_bk" AS "street_name_bk" , "dvo_src1"."street_number_bk" AS "street_number_bk" , "dvo_src1"."postal_code_bk" AS "postal_code_bk" , "dvo_src1"."city_bk" AS "city_bk" , "dvo_src5"."campaign_code_bk" AS "campaign_code_bk" , "dvo_src5"."campaign_start_date_bk" AS "campaign_start_date_bk" , "dvo_src7"."product_cc_bk" AS "product_cc_bk" , "dvo_src7"."product_et_code_bk" AS "product_et_code_bk" , "dvo_src7"."product_part_code_bk" AS "product_part_code_bk" , "dvo_src3"."customers_bk" AS "customers_bk" FROM "moto_scn02_fmc"."fmc_bv_loading_window_table" "bvlwt_src" INNER JOIN "moto_scn02_fl"."hub_addresses" "dvo_src1" ON 1 = 1 INNER JOIN "sat_src1_2" "sat_src1_2" ON "dvo_src1"."addresses_hkey" = "sat_src1_2"."addresses_hkey" AND "bvlwt_src"."fmc_begin_lw_timestamp" >= "sat_src1_2"."load_date" AND "bvlwt_src"."fmc_begin_lw_timestamp" < "sat_src1_2"."load_end_date" AND "sat_src1_2"."delete_flag" != 'Y'::text INNER JOIN "moto_scn02_bv"."lna_cust_addr" "dvo_src2" ON "dvo_src2"."addresses_hkey" = "dvo_src1"."addresses_hkey" INNER JOIN "moto_scn02_fl"."hub_customers" "dvo_src3" ON "dvo_src3"."customers_hkey" = "dvo_src2"."customers_hkey" INNER JOIN "sat_src3_3" "sat_src3_3" ON "dvo_src3"."customers_hkey" = "sat_src3_3"."customers_hkey" AND "bvlwt_src"."fmc_begin_lw_timestamp" >= "sat_src3_3"."load_date" AND "bvlwt_src"."fmc_begin_lw_timestamp" < "sat_src3_3"."load_end_date" AND "sat_src3_3"."delete_flag" != 'Y'::text INNER JOIN "moto_scn02_fl"."lnd_camp_cust_cont" "dvo_src4" ON "dvo_src4"."customers_hkey" = "dvo_src3"."customers_hkey" INNER JOIN "sat_src4_1" "sat_src4_1" ON "dvo_src4"."lnd_camp_cust_cont_hkey" = "sat_src4_1"."lnd_camp_cust_cont_hkey" AND "bvlwt_src"."fmc_begin_lw_timestamp" >= "sat_src4_1"."load_date" AND "bvlwt_src"."fmc_begin_lw_timestamp" < "sat_src4_1"."load_end_date" AND "sat_src4_1"."delete_flag" != 'Y'::text INNER JOIN "moto_scn02_fl"."hub_campaigns" "dvo_src5" ON "dvo_src5"."campaigns_hkey" = "dvo_src4"."campaigns_hkey" INNER JOIN "sat_src5_1" "sat_src5_1" ON "dvo_src5"."campaigns_hkey" = "sat_src5_1"."campaigns_hkey" AND "bvlwt_src"."fmc_begin_lw_timestamp" >= "sat_src5_1"."load_date" AND "bvlwt_src"."fmc_begin_lw_timestamp" < "sat_src5_1"."load_end_date" AND "sat_src5_1"."delete_flag" != 'Y'::text INNER JOIN "moto_scn02_fl"."lnd_camp_prod" "dvo_src6" ON "dvo_src6"."campaigns_hkey" = "dvo_src5"."campaigns_hkey" INNER JOIN "sat_src6_1" "sat_src6_1" ON "dvo_src6"."lnd_camp_prod_hkey" = "sat_src6_1"."lnd_camp_prod_hkey" AND "bvlwt_src"."fmc_begin_lw_timestamp" >= "sat_src6_1"."load_date" AND "bvlwt_src"."fmc_begin_lw_timestamp" < "sat_src6_1"."load_end_date" AND "sat_src6_1"."delete_flag" != 'Y'::text INNER JOIN "sat_src6_2" "sat_src6_2" ON "dvo_src6"."lnd_camp_prod_hkey" = "sat_src6_2"."lnd_camp_prod_hkey" AND "bvlwt_src"."fmc_begin_lw_timestamp" >= "sat_src6_2"."load_date" AND "bvlwt_src"."fmc_begin_lw_timestamp" < "sat_src6_2"."load_end_date" AND "sat_src6_2"."delete_flag" != 'Y'::text INNER JOIN "moto_scn02_fl"."hub_products" "dvo_src7" ON "dvo_src7"."products_hkey" = "dvo_src6"."products_hkey" INNER JOIN "sat_src7_2" "sat_src7_2" ON "dvo_src7"."products_hkey" = "sat_src7_2"."products_hkey" AND "bvlwt_src"."fmc_begin_lw_timestamp" >= "sat_src7_2"."load_date" AND "bvlwt_src"."fmc_begin_lw_timestamp" < "sat_src7_2"."load_end_date" AND "sat_src7_2"."delete_flag" != 'Y'::text ) SELECT "miv"."bridge_campaigns_per_city_hkey" AS "bridge_campaigns_per_city_hkey" , "miv"."load_date" AS "load_date" , "miv"."addresses_hkey" AS "addresses_hkey" , "miv"."customers_hkey" AS "customers_hkey" , "miv"."products_hkey" AS "products_hkey" , "miv"."campaigns_hkey" AS "campaigns_hkey" , "miv"."lna_cust_addr_hkey" AS "lna_cust_addr_hkey" , "miv"."lnd_camp_prod_hkey" AS "lnd_camp_prod_hkey" , "miv"."lnd_camp_cust_cont_hkey" AS "lnd_camp_cust_cont_hkey" , "miv"."street_name_bk" AS "street_name_bk" , "miv"."street_number_bk" AS "street_number_bk" , "miv"."postal_code_bk" AS "postal_code_bk" , "miv"."city_bk" AS "city_bk" , "miv"."product_cc_bk" AS "product_cc_bk" , "miv"."product_et_code_bk" AS "product_et_code_bk" , "miv"."product_part_code_bk" AS "product_part_code_bk" , "miv"."campaign_code_bk" AS "campaign_code_bk" , "miv"."campaign_start_date_bk" AS "campaign_start_date_bk" , "miv"."customers_bk" AS "customers_bk" FROM "miv" "miv" WHERE NOT EXISTS ( SELECT FROM "moto_scn02_bv"."bridge_campaigns_per_city" "bridge_src" WHERE "bridge_src"."bridge_campaigns_per_city_hkey" = "miv"."bridge_campaigns_per_city_hkey" ) ; END; END; $function$; ``` # **Step 1: Define a bridge configuration:** To build a custom bridge, configure the dependencies using the bridges tab and use the configured bridge in the VaultSpeed studio. ![image](media://d373ad85-8d13-41c2-a90b-a2b86bfa5788) This bridge combines the following tables and can be selected by dragging and dropping the necessary tables on the canvas: ![image](media://611e4857-8a9c-41fa-a636-f50542dc8d82) Business keys of the hubs should be included, so Include business keys for all hubs is set by right-clicking. ![image](media://884ed40e-5a12-4757-927b-20f36785e9f6) Once configured, go to the bridges tab and save this bridge. ![image](media://8cffa9be-edbc-483a-94c9-0a48f4191c1c) Enable create a bridge hash key and fill in the bridge name. ![image](media://60906363-244c-4cd2-9f72-c527c3aa5003) Also, enable **Generate VaultSpeed Studio Code. In**. This way, the template in the studio is used for generating code to load the bridge. If you disable this, then the code will be automatically generated by VaultSpeed. Once the bridge is ready, it will use the default bridge template. This default template can be overwritten by defining a template in the VaultSpeed studio. # **Step 2: Define a new template:** Click the +Add button to add a new template: ![image](media://7c60fd00-dfdc-49d5-8983-00f805a89d8d) - **Name/Description:** bridge / Bridge table - **Prefix/suffix:** brg / This affects the name of the target table. If the bridge were configured with the name campaigns_per_city, it would become brg_campaigns_per_city - **Signature Object**: BRG_TGT - **Object type**: This Bridge will be persisted, so the Object Type table is chosen. - **Load type**: If a separate template is needed for an initial load, this can be specified here. In this case, only an incremental template will be added. So INCR is chosen from the droplist. - **Signature schema**: BUSINESS_VAULT_LAYER - **Base type**: Bridge table - **Upload ETL:** This is done in a subsequent step - **Upload DDL:** This is done in a subsequent step Click the **Create** button when ready to create a first template definition. ![image](media://497e2b34-3b77-4716-baaf-471a3497688d) The template configuration is added, but as the warning message states, the template is not yet applied to specific tables. # **Step 3: Configure template objects** Continuing with the template setup, we can define which objects the template should generate a mapping in the dependencies screen. Since the base type we have chosen in this example is Bridge, this screen will show all bridges in the Business Vault. A set of unlinked Bridge objects can be selected to include in the template, as shown in the screenshot below (Linked Objects table). ![image](media://2a6f7338-bace-4148-aada-44b2f9e1872b) Clicking the arrow next to an object will show the dependent objects and their signature object, which is how they can be referenced in the templates. ![image](media://72cc5842-d812-4684-8b11-9fe50495fdb6) These Signature Objects can be used directly in the template to loop through all the dependent objects easily. All hubs and links have the Signature Object DVO. Just select the object and click the arrow > pointing to the right. ![image](media://5d6082a4-fa27-426b-b211-2ff670a48507) The campaigns_per_city bridge will be the object that the template is built. Once linking/unlinking of the objects is done, attributes for the target table can be defined, but it is unnecessary as all bridge Signature attributes are already automatically added. ![image](media://aa4d3617-149e-4a7a-b33b-29cb6047d1e4) # **Step 4: Configure the target attribute definition.** There is a separate window for configuring target definitions, as shown below. ![image](media://3168e97d-3bf4-40d6-b658-b52c754a8727) As we want to use the Signature Attributes default in the bridge, we Click + All from the Signature Object. ![image](media://4e2c2996-9cc9-4a6f-802a-6c8d0b3a80c9) ![image](media://a6348f50-baa6-4907-a19b-848dcff99438) # Step 5: Build an ETL template The query will be split into different blocks to show how to write a template (.dvt file format) that compiles into that query. Each of these blocks will be defined in the template language: ## 5.1. CTE SAT interval ``` /* CTE for SAT valid interval */ SELECT "sat_ed_src1_2"."addresses_hkey" AS "addresses_hkey" , "sat_ed_src1_2"."load_date" AS "load_date" , COALESCE(LEAD("sat_ed_src1_2"."load_date")OVER(PARTITION BY "sat_ed_src1_2"."addresses_hkey" ORDER BY "sat_ed_src1_2"."load_date") , TO_TIMESTAMP('31/12/2399 23:59:59.000000' , 'DD/MM/YYYY HH24:MI:SS.US'::varchar)) AS "load_end_date" , "sat_ed_src1_2"."delete_flag" AS "delete_flag" FROM "moto_scn02_fl"."sat_mm_addresses" "sat_ed_src1_2" ``` To check the validity of the Links and hubs in the bridge, we could calculate an interval for the satellites upon those objects. In this case, we filter upon all the satellites from a Moto_mktg source (source_short_name = mm). Satellites are on the second loop level $$ (multi-level looping), as shown in the screenshot below. This means we could loop through them by using $ and finding them by using $$ (second level) ![image](media://c069a55d-de7f-44b3-beb8-3b1af5ad57b2) ``` comp_group_start $ SAT_SRC_GROUP$$ INL_V_GRP componentgroupConditionedBy [(TAB SAT$$ : SOURCE_SHORT_NAME = mm)] RepeatedByObject SAT consists of target inline_view SAT_SRC$$ RepeatedByObject SAT$$ connectsFrom (SAT_ED_SRC$$) Attribute OBJECT_H_KEY AttributeConditionedBy [(TAB DVO$ : DV_TYPE = HUB)] expressedBy SAT_ED_SRC$$.OBJECT_H_KEY Attribute OBJECT_L_H_KEY AttributeConditionedBy [(TAB DVO$ : DV_TYPE = LNK LND NHL)] expressedBy SAT_ED_SRC$$.OBJECT_L_H_KEY Attribute SUBSEQUENCE_ATTRIBUTE AttributeConditionedBy [(TAB SAT$$ : MULTI_ACTIVE_SAT = Y)(TAB SAT$$ : HAS_SUBSEQUENCE_ATTRIBUTES = Y)] expressedBy SAT_ED_SRC$$.SUBSEQUENCE_ATTRIBUTE expressionDefinedByAttribute SAT_ED_SRC$$.SUBSEQUENCE_ATTRIBUTE Attribute LOAD_TIMESTAMP expressedBy SAT_ED_SRC$$.LOAD_TIMESTAMP expressionDefinedByAttribute SAT_ED_SRC$$.LOAD_TIMESTAMP Attribute LOAD_END_TIMESTAMP GROUP_1 expressedBy COALESCE(LEAD(SAT_ED_SRC$$.LOAD_TIMESTAMP) OVER (PARTITION BY GROUP_2 expressedBy SAT_ED_SRC$$.OBJECT_H_KEY ExpressionConditionedBy [(TAB DVO$ : DV_TYPE = HUB)] GROUP_2 expressedBy SAT_ED_SRC$$.OBJECT_L_H_KEY ExpressionConditionedBy [(TAB DVO$ : DV_TYPE = LNK LND NHL)] GROUP_3 expressedBy , SAT_ED_SRC$$.SUBSEQUENCE_ATTRIBUTE expressionConditionedBy [(TAB SAT$$ : MULTI_ACTIVE_SAT = Y)(TAB SAT$$ : HAS_SUBSEQUENCE_ATTRIBUTES = Y)] expressionDefinedByAttribute SAT_ED_SRC$$.SUBSEQUENCE_ATTRIBUTE GROUP_4 expressedBy ORDER BY SAT_ED_SRC$$.LOAD_TIMESTAMP GROUP_5 expressedBy , SAT_ED_SRC$$.CDC_LOGPOSITION expressionConditionedBy [(TAB SAT$$ : CDC_LOGPOSITION_AVAILABLE = Y)(PARAM : USE_CDC_TS_AS_LOAD_DATES = Y)] GROUP_6 expressedBy ),GTIMECAST[@#CURRENT_RECORD_LOAD_END_DATE#]) Attribute CDC_LOGPOSITION AttributeConditionedBy [(TAB SAT$$ : CDC_LOGPOSITION_AVAILABLE = Y)] expressedBy SAT_ED_SRC$$.CDC_LOGPOSITION Attribute DELETE_FLAG expressedBy SAT_ED_SRC$$.DELETE_FLAG consists of source table SAT_ED_SRC$$ RepeatedByObject SAT$$ comp_group_end ``` ## 5.2. CTE prepare hash keys ``` /* MAIN BLOCK FOR INSERT */ insert into bv_layer.brg_campaigns_per_city() select ... from ... where not exists ...; ``` The main block is defined as the last block in the template. This main ins_grp block will generate the INSERT statement and the main select clause. The interpreter will take care of how to print the insert block based on the target technology. Overview of blocks to build: - INSERT_GROUP INS_GRP (Main insert) (Inserts the data in the Bridge table) A template always gets a name. This can match the name in the VaultSpeed studio but is not necessary : ``` Template BRIDGE_TABLE ``` The target is the BRG_TGT (Bridge). The keywords target table in the CTE will trigger the SELECT … keyword. The hubs and links all have the Signature Object DVO. This is to simplify the logic, as the AUTO keyword in the join will then be able to build the joins automatically. The bridge_hkey is calculated using the combination of the object_h_key of the hubs/links/many to many links. The bridge hash key attribute will have the Signature Attribute OBJECT_P_H_KEY. This can be seen in the automatically added target Signature attributes in the VaultSpeed studio. The snapshot_timestamp here has the Signature attribute LOAD_TIMESTAMP. The bridge hash key expression consists of a hash function and a loop through all the keys of the hubs. If some parts of an expression need to be repeated and some don’t, an attribute can be split in multiple AttributePart, ensuring the hash function is not repeated, but the object_h_keys are. For the hubs and links themselves, the hub keys are OBJECT_H_KEY, and those from the links are OBJECT_L_H_KEY, so that is the reason behind two separate parts for all the hash keys. If we look up the HUB/LNK which we want to take an attribute from, we see the signature object has a $. This means that if we chose DVO, it would take only one object, but we want all objects, so we need to introduce looping ($). This must be done for the attribute and the join with the object itself. ![image](media://814c2e6b-e01d-4470-949c-b9d21d31f578) An example of attribute looping can be seen here. The dollar is put after the Attribute and we Repeat by object DVO → This will create a loop DVO$1 - DVO$2 - …. In the expressedBy, a dollar is put at the end of the table alias, as it should be filtered to just one dependency. ``` Attribute $ OBJECT_H_KEY RepeatedByObject DVO expressedBy DVO_SRC$.OBJECT_H_KEY expressionDefinedByAttribute DVO_SRC$.OBJECT_H_KEY ``` Subgroups are used to loop through the dependent hubs and links. Here the $ is put after the comp_subgroup_start to start a loop for the subgroup. A subgroup is needed because the two components join, and the joined object must always be right after each other so the interpreter knows which belongs together. The join within the subgroup uses the keyword auto. This will automatically insert the right join conditions without having to specify them. ``` comp_subgroup_start $ BRIDGE_SUBGROUP RepeatedByObject DVO consists of inner join JOIN_DVO_SRC$ RepeatedByObject DVO$ connectsFrom (BVLWT_SRC) connectionConditionedBy [(LOOP: GENERAL_LOOP = 1)] (JOIN_DVO_SRC$ PREV) connectionConditionedBy [(LOOP: GENERAL_LOOP > 1)] auto connectsFrom (DVO_SRC$) consists of joined table DVO_SRC$ RepeatedByObject DVO$ comp_subgroup_end ``` If we want to go to the satellite, this has loop level 2 ($$). We must start a loop within the loop. ![image](media://c069a55d-de7f-44b3-beb8-3b1af5ad57b2) This is because the satellite is an object on top of the hub/lnk and there can be multiple on one hub/lnk. ``` comp_subgroup_start $ BRIDGE_SUBGROUP RepeatedByObject DVO consists of inner join JOIN_DVO_SRC$ RepeatedByObject DVO$ connectsFrom (BVLWT_SRC) connectionConditionedBy [(LOOP: $ = 1)] (JOIN_DVO_SRC$ PREV) connectionConditionedBy [(LOOP: $ > 1)] auto connectsFrom (DVO_SRC$) consists of joined table DVO_SRC$ RepeatedByObject DVO$ comp_subgroup_start $ SAT_SUBGROUP componentsubgroupConditionedBy [(TAB SAT$$ : SOURCE_SHORT_NAME = mm)] RepeatedByObject SAT$ consists of inner join JOIN_SAT_SRC$$ RepeatedByObject SAT$$ connectsFrom (JOIN_DVO_SRC$) connectionConditionedBy [(LOOP: $$ = 1)] (JOIN_SAT_SRC$ PREV) connectionConditionedBy [(LOOP: $$ > 1)] connectsFrom (SAT_SRC$$) Artifact GENERAL_EXPRESSION GROUP_1 expressedBy DVO_SRC$.OBJECT_H_KEY = SAT_SRC$$.OBJECT_H_KEY expressionConditionedBy [(TAB DVO$ : DV_TYPE = HUB)] expressionDefinedByAttribute DVO_SRC$.OBJECT_H_KEY GROUP_1 expressedBy DVO_SRC$.OBJECT_L_H_KEY = SAT_SRC$$.OBJECT_L_H_KEY expressionConditionedBy [(TAB DVO$ : DV_TYPE = LNK LND NHL)] expressionDefinedByAttribute DVO_SRC$.OBJECT_L_H_KEY GROUP_2 expressedBy AND BVLWT_SRC.FMC_BEGIN_LW_TIMESTAMP >= SAT_SRC$$.LOAD_TIMESTAMP AND BVLWT_SRC.FMC_BEGIN_LW_TIMESTAMP < SAT_SRC$$.LOAD_END_TIMESTAMP GROUP_3 expressedBy AND SAT_SRC$$.DELETE_FLAG != GCASTFRMT[@#DELETE_FLAG_POSITIVE_VALUE#] expressionDefinedByAttribute SAT_SRC$$.DELETE_FLAG consists of joined inline_view SAT_SRC$$ RepeatedByObject SAT$$ comp_subgroup_end comp_subgroup_end ``` The full CTE template can be found in the following template. ``` comp_group_start MIV_GROUP INL_V_GRP RepeatedByObject BRG_TGT consists of target inline_view MIV RepeatedByObject BRG_TGT connectsFrom (JOIN_DVO_SRC$ LAST) AttributePart OBJECT_P_H_KEY GROUP_1 expressedBy HASHFUNC[ AttributePart $ OBJECT_P_H_KEY RepeatedByObject DVO GROUP_2 expressedBy CHARCAST[DVO_SRC$.OBJECT_H_KEY] || @#HASHKEY_DELIMITER# expressionDefinedByAttribute DVO_SRC$.OBJECT_H_KEY VERTICAL AttributePart OBJECT_P_H_KEY GROUP_3 expressedBy HASHFUNC] Attribute LOAD_TIMESTAMP expressedBy BVLWT_SRC.FMC_BEGIN_LW_TIMESTAMP Attribute $ OBJECT_H_KEY RepeatedByObject DVO expressedBy DVO_SRC$.OBJECT_H_KEY expressionDefinedByAttribute DVO_SRC$.OBJECT_H_KEY Attribute $ OBJECT_L_H_KEY RepeatedByObject DVO expressedBy DVO_SRC$.OBJECT_L_H_KEY expressionDefinedByAttribute DVO_SRC$.OBJECT_L_H_KEY Attribute $ BUSINESS_KEY RepeatedByObject DVO expressedBy DVO_SRC$.BUSINESS_KEY expressionDefinedByAttribute DVO_SRC$.BUSINESS_KEY Attribute $ CONCAT_BUSINESS_KEY RepeatedByObject DVO expressedBy DVO_SRC$.CONCAT_BUSINESS_KEY expressionDefinedByAttribute DVO_SRC$.CONCAT_BUSINESS_KEY consists of source table BVLWT_SRC RepeatedByObject BVLWT comp_subgroup_start $ BRIDGE_SUBGROUP RepeatedByObject DVO consists of inner join JOIN_DVO_SRC$ RepeatedByObject DVO$ connectsFrom (BVLWT_SRC) connectionConditionedBy [(LOOP: $ = 1)] (JOIN_DVO_SRC$ PREV) connectionConditionedBy [(LOOP: $ > 1)] auto connectsFrom (DVO_SRC$) consists of joined table DVO_SRC$ RepeatedByObject DVO$ comp_subgroup_start $ SAT_SUBGROUP componentsubgroupConditionedBy [(TAB SAT$$ : SOURCE_SHORT_NAME = mm)] RepeatedByObject SAT$ consists of inner join JOIN_SAT_SRC$$ RepeatedByObject SAT$$ connectsFrom (JOIN_DVO_SRC$) connectionConditionedBy [(LOOP: $$ = 1)] (JOIN_SAT_SRC$ PREV) connectionConditionedBy [(LOOP: $$ > 1)] connectsFrom (SAT_SRC$$) Artifact GENERAL_EXPRESSION GROUP_1 expressedBy DVO_SRC$.OBJECT_H_KEY = SAT_SRC$$.OBJECT_H_KEY expressionConditionedBy [(TAB DVO$ : DV_TYPE = HUB)] expressionDefinedByAttribute DVO_SRC$.OBJECT_H_KEY GROUP_1 expressedBy DVO_SRC$.OBJECT_L_H_KEY = SAT_SRC$$.OBJECT_L_H_KEY expressionConditionedBy [(TAB DVO$ : DV_TYPE = LNK LND NHL)] expressionDefinedByAttribute DVO_SRC$.OBJECT_L_H_KEY GROUP_2 expressedBy AND BVLWT_SRC.FMC_BEGIN_LW_TIMESTAMP >= SAT_SRC$$.LOAD_TIMESTAMP AND BVLWT_SRC.FMC_BEGIN_LW_TIMESTAMP < SAT_SRC$$.LOAD_END_TIMESTAMP GROUP_3 expressedBy AND SAT_SRC$$.DELETE_FLAG != GCASTFRMT[@#DELETE_FLAG_POSITIVE_VALUE#] expressionDefinedByAttribute SAT_SRC$$.DELETE_FLAG consists of joined inline_view SAT_SRC$$ RepeatedByObject SAT$$ comp_subgroup_end comp_subgroup_end comp_group_end ``` ## 5.3. Main insert/select With the CTE preparing all the hash keys, the insert can be triggered in the group (INS_GRP). It will add the keywords insert into … with a selection of the calculated attributes and a not exists check. Exists clauses are continuously built, as can be seen below. Add a filter with (not) exists in the General_expression and add a component group EXISTS_GRP with the existing logic. ``` comp_group_start INSERT_END_GROUP INS_GRP RepeatedByObject BRG_TGT consists of target table BRIDGE_TGT RepeatedByObject BRG_TGT connectsFrom (FILTER_MIV_SRC) Attribute OBJECT_P_H_KEY expressedBy MIV.OBJECT_P_H_KEY expressionDefinedByAttribute MIV.OBJECT_P_H_KEY Attribute LOAD_TIMESTAMP expressedBy MIV.LOAD_TIMESTAMP expressionDefinedByAttribute MIV.LOAD_TIMESTAMP Attribute OBJECT_H_KEY expressedBy MIV.OBJECT_H_KEY expressionDefinedByAttribute MIV.OBJECT_H_KEY Attribute OBJECT_L_H_KEY expressedBy MIV.OBJECT_L_H_KEY expressionDefinedByAttribute MIV.OBJECT_L_H_KEY Attribute BUSINESS_KEY expressedBy MIV.BUSINESS_KEY expressionDefinedByAttribute MIV.BUSINESS_KEY Attribute CONCAT_BUSINESS_KEY expressedBy MIV.CONCAT_BUSINESS_KEY expressionDefinedByAttribute MIV.CONCAT_BUSINESS_KEY consists of source inline_view MIV RepeatedByObject BRG_TGT consists of filter FILTER_MIV_SRC RepeatedByObject BRG_TGT connectsFrom (MIV) Artifact GENERAL_EXPRESSION expressedBy NOT EXISTS comp_group_end comp_group_start EXISTS_GROUP EXISTS_GRP RepeatedByObject BRG_TGT consists of target table BRIDGE_TGT RepeatedByObject BRG_TGT connectsFrom (FILTER_BRIDGE_SRC) Attribute DUMMY expressedBy 1 consists of source table BRIDGE_SRC RepeatedByObject BRG_TGT consists of filter FILTER_BRIDGE_SRC RepeatedByObject BRG_TGT connectsFrom (BRIDGE_SRC) Artifact GENERAL_EXPRESSION expressedBy BRIDGE_SRC.OBJECT_P_H_KEY = MIV.OBJECT_P_H_KEY expressionDefinedByAttribute BRIDGE_SRC.OBJECT_P_H_KEY comp_group_end ``` The complete template: ``` Template BRIDGE_TABLE comp_group_start $ SAT_SRC_GROUP$$ INL_V_GRP componentgroupConditionedBy [(TAB SAT$$ : SOURCE_SHORT_NAME = mm)] RepeatedByObject SAT consists of target inline_view SAT_SRC$$ RepeatedByObject SAT$$ connectsFrom (SAT_ED_SRC$$) Attribute OBJECT_H_KEY AttributeConditionedBy [(TAB DVO$ : DV_TYPE = HUB)] expressedBy SAT_ED_SRC$$.OBJECT_H_KEY Attribute OBJECT_L_H_KEY AttributeConditionedBy [(TAB DVO$ : DV_TYPE = LNK LND NHL)] expressedBy SAT_ED_SRC$$.OBJECT_L_H_KEY Attribute SUBSEQUENCE_ATTRIBUTE AttributeConditionedBy [(TAB SAT$$ : MULTI_ACTIVE_SAT = Y)(TAB SAT$$ : HAS_SUBSEQUENCE_ATTRIBUTES = Y)] expressedBy SAT_ED_SRC$$.SUBSEQUENCE_ATTRIBUTE expressionDefinedByAttribute SAT_ED_SRC$$.SUBSEQUENCE_ATTRIBUTE Attribute LOAD_TIMESTAMP expressedBy SAT_ED_SRC$$.LOAD_TIMESTAMP expressionDefinedByAttribute SAT_ED_SRC$$.LOAD_TIMESTAMP Attribute LOAD_END_TIMESTAMP GROUP_1 expressedBy COALESCE(LEAD(SAT_ED_SRC$$.LOAD_TIMESTAMP) OVER (PARTITION BY GROUP_2 expressedBy SAT_ED_SRC$$.OBJECT_H_KEY ExpressionConditionedBy [(TAB DVO$ : DV_TYPE = HUB)] GROUP_2 expressedBy SAT_ED_SRC$$.OBJECT_L_H_KEY ExpressionConditionedBy [(TAB DVO$ : DV_TYPE = LNK LND NHL)] GROUP_3 expressedBy , SAT_ED_SRC$$.SUBSEQUENCE_ATTRIBUTE expressionConditionedBy [(TAB SAT$$ : MULTI_ACTIVE_SAT = Y)(TAB SAT$$ : HAS_SUBSEQUENCE_ATTRIBUTES = Y)] expressionDefinedByAttribute SAT_ED_SRC$$.SUBSEQUENCE_ATTRIBUTE GROUP_4 expressedBy ORDER BY SAT_ED_SRC$$.LOAD_TIMESTAMP GROUP_5 expressedBy , SAT_ED_SRC$$.CDC_LOGPOSITION expressionConditionedBy [(TAB SAT$$ : CDC_LOGPOSITION_AVAILABLE = Y)(PARAM : USE_CDC_TS_AS_LOAD_DATES = Y)] GROUP_6 expressedBy ),GTIMECAST[@#CURRENT_RECORD_LOAD_END_DATE#]) Attribute CDC_LOGPOSITION AttributeConditionedBy [(TAB SAT$$ : CDC_LOGPOSITION_AVAILABLE = Y)] expressedBy SAT_ED_SRC$$.CDC_LOGPOSITION Attribute DELETE_FLAG expressedBy SAT_ED_SRC$$.DELETE_FLAG consists of source table SAT_ED_SRC$$ RepeatedByObject SAT$$ comp_group_end comp_group_start MIV_GROUP INL_V_GRP RepeatedByObject BRG_TGT consists of target inline_view MIV RepeatedByObject BRG_TGT connectsFrom (JOIN_DVO_SRC$ LAST) AttributePart OBJECT_P_H_KEY GROUP_1 expressedBy HASHFUNC[ AttributePart $ OBJECT_P_H_KEY RepeatedByObject DVO GROUP_2 expressedBy CHARCAST[DVO_SRC$.OBJECT_H_KEY] || @#HASHKEY_DELIMITER# expressionDefinedByAttribute DVO_SRC$.OBJECT_H_KEY VERTICAL AttributePart OBJECT_P_H_KEY GROUP_3 expressedBy HASHFUNC] Attribute LOAD_TIMESTAMP expressedBy BVLWT_SRC.FMC_BEGIN_LW_TIMESTAMP Attribute $ OBJECT_H_KEY RepeatedByObject DVO expressedBy DVO_SRC$.OBJECT_H_KEY expressionDefinedByAttribute DVO_SRC$.OBJECT_H_KEY Attribute $ OBJECT_L_H_KEY RepeatedByObject DVO expressedBy DVO_SRC$.OBJECT_L_H_KEY expressionDefinedByAttribute DVO_SRC$.OBJECT_L_H_KEY Attribute $ BUSINESS_KEY RepeatedByObject DVO expressedBy DVO_SRC$.BUSINESS_KEY expressionDefinedByAttribute DVO_SRC$.BUSINESS_KEY Attribute $ CONCAT_BUSINESS_KEY RepeatedByObject DVO expressedBy DVO_SRC$.CONCAT_BUSINESS_KEY expressionDefinedByAttribute DVO_SRC$.CONCAT_BUSINESS_KEY consists of source table BVLWT_SRC RepeatedByObject BVLWT comp_subgroup_start $ BRIDGE_SUBGROUP RepeatedByObject DVO consists of inner join JOIN_DVO_SRC$ RepeatedByObject DVO$ connectsFrom (BVLWT_SRC) connectionConditionedBy [(LOOP: $ = 1)] (JOIN_DVO_SRC$ PREV) connectionConditionedBy [(LOOP: $ > 1)] auto connectsFrom (DVO_SRC$) consists of joined table DVO_SRC$ RepeatedByObject DVO$ comp_subgroup_start $ SAT_SUBGROUP componentsubgroupConditionedBy [(TAB SAT$$ : SOURCE_SHORT_NAME = mm)] RepeatedByObject SAT$ consists of inner join JOIN_SAT_SRC$$ RepeatedByObject SAT$$ connectsFrom (JOIN_DVO_SRC$) connectionConditionedBy [(LOOP: $$ = 1)] (JOIN_SAT_SRC$ PREV) connectionConditionedBy [(LOOP: $$ > 1)] connectsFrom (SAT_SRC$$) Artifact GENERAL_EXPRESSION GROUP_1 expressedBy DVO_SRC$.OBJECT_H_KEY = SAT_SRC$$.OBJECT_H_KEY expressionConditionedBy [(TAB DVO$ : DV_TYPE = HUB)] expressionDefinedByAttribute DVO_SRC$.OBJECT_H_KEY GROUP_1 expressedBy DVO_SRC$.OBJECT_L_H_KEY = SAT_SRC$$.OBJECT_L_H_KEY expressionConditionedBy [(TAB DVO$ : DV_TYPE = LNK LND NHL)] expressionDefinedByAttribute DVO_SRC$.OBJECT_L_H_KEY GROUP_2 expressedBy AND BVLWT_SRC.FMC_BEGIN_LW_TIMESTAMP >= SAT_SRC$$.LOAD_TIMESTAMP AND BVLWT_SRC.FMC_BEGIN_LW_TIMESTAMP < SAT_SRC$$.LOAD_END_TIMESTAMP GROUP_3 expressedBy AND SAT_SRC$$.DELETE_FLAG != GCASTFRMT[@#DELETE_FLAG_POSITIVE_VALUE#] expressionDefinedByAttribute SAT_SRC$$.DELETE_FLAG consists of joined inline_view SAT_SRC$$ RepeatedByObject SAT$$ comp_subgroup_end comp_subgroup_end comp_group_end comp_group_start INSERT_END_GROUP INS_GRP RepeatedByObject BRG_TGT consists of target table BRIDGE_TGT RepeatedByObject BRG_TGT connectsFrom (FILTER_MIV_SRC) Attribute OBJECT_P_H_KEY expressedBy MIV.OBJECT_P_H_KEY expressionDefinedByAttribute MIV.OBJECT_P_H_KEY Attribute LOAD_TIMESTAMP expressedBy MIV.LOAD_TIMESTAMP expressionDefinedByAttribute MIV.LOAD_TIMESTAMP Attribute OBJECT_H_KEY expressedBy MIV.OBJECT_H_KEY expressionDefinedByAttribute MIV.OBJECT_H_KEY Attribute OBJECT_L_H_KEY expressedBy MIV.OBJECT_L_H_KEY expressionDefinedByAttribute MIV.OBJECT_L_H_KEY Attribute BUSINESS_KEY expressedBy MIV.BUSINESS_KEY expressionDefinedByAttribute MIV.BUSINESS_KEY Attribute CONCAT_BUSINESS_KEY expressedBy MIV.CONCAT_BUSINESS_KEY expressionDefinedByAttribute MIV.CONCAT_BUSINESS_KEY consists of source inline_view MIV RepeatedByObject BRG_TGT consists of filter FILTER_MIV_SRC RepeatedByObject BRG_TGT connectsFrom (MIV) Artifact GENERAL_EXPRESSION expressedBy NOT EXISTS comp_group_end comp_group_start EXISTS_GROUP EXISTS_GRP RepeatedByObject BRG_TGT consists of target table BRIDGE_TGT RepeatedByObject BRG_TGT connectsFrom (FILTER_BRIDGE_SRC) Attribute DUMMY expressedBy 1 consists of source table BRIDGE_SRC RepeatedByObject BRG_TGT consists of filter FILTER_BRIDGE_SRC RepeatedByObject BRG_TGT connectsFrom (BRIDGE_SRC) Artifact GENERAL_EXPRESSION expressedBy BRIDGE_SRC.OBJECT_P_H_KEY = MIV.OBJECT_P_H_KEY expressionDefinedByAttribute BRIDGE_SRC.OBJECT_P_H_KEY comp_group_end ``` Once this template is written, it can be uploaded to the VaultSpeed studio. # Step 6: Build a DDL template As a persisted Bridge is the target, a DDL template must also be built. The structure of the DDL template completely matches the structure of the ‘target table’ in the ETL template, so to transform this into a DDL template: - Take the target table and attributes and write a DDL component group shell around it. - Change the target into create_table. - Remove the connectsfrom and all other components. - Remove the expressedBy, expressionDefinedByAttribute and expressionConditionedBy - Add a `DROP_TABLE` component when needed. So this will give you the following template: ``` Template BRIDGE_TABLE comp_group_start DDL_GROUP CREA_GRP RepeatedByObject BRG_TGT consists of DROP_TABLE table BRIDGE_TGT RepeatedByObject BRG_TGT comp_group_end comp_group_start DDL_GROUP CREA_GRP RepeatedByObject BRG_TGT consists of CREATE_TABLE table BRIDGE_TGT RepeatedByObject BRG_TGT Attribute OBJECT_P_H_KEY Attribute LOAD_TIMESTAMP Attribute OBJECT_H_KEY Attribute OBJECT_L_H_KEY Attribute BUSINESS_KEY Attribute CONCAT_BUSINESS_KEY comp_group_end ``` This one can also be uploaded in the DDL template.