How to build a Bridge Table

Introduction

Similar to PIT tables, Bridge Table purpose is to improve the performance of queries on the Raw Data Vault by reducing the number of required joins for the query. They are also part of the Business Vault, because the data in bridge tables contains business logic. Unlike PIT tables, which span across multiple satellites of a hub or link, a bridge table spans across multiple hubs and links. If PIT tables are mostly used to build information mart Dimensions, Bridge tables are commonly used as a starting point for Fact tables.

Bridge tables contains

A snapshot date
Hash keys from all hubs and links that they span (it is common practice to add their business keys as well).
Computed fields

To build a bridge table, you will have to start from a link and passing from hub to link to hub to link… draw a line in your model between the elements you want to regroup in your bridge table. All the hubs and links you go through must not end up in your bridge.

Example

We are using a model from a car dealer company. We have employees creating contracts for customers. Each contract is related to a car from a manufacturer.

From this model, we would like to create a bridge table with the total amount sell (contract value and discounted amount) by each employee by car manufacturer.

We will start from the contract_car link and first build the query to retrieve the manufacturer related to each contract. We have context satellites on almost all hubs and link, you can simply ignore these join if you do not have these context satellites.

Query the link (Step1) and join on snapshot dates

SQL

SELECT
    snap."snapshot_date",
    lcc.*
FROM "dv"."l_car_contract" lcc
CROSS JOIN "im"."ctrl_tim" snap

Bridge tables are always related to a given snapshot

Join the effectivity Satellite

SQL

SELECT
    snap."snapshot_date",
    lcc.*
FROM "dv"."l_car_contract" lcc
CROSS JOIN "im"."ctrl_im" snap
INNER JOIN "bv"."l_car_contract_crm_effsat" lcc_eff ON lcc_eff."hk" = lcc."hk" AND snap."snapshot_date" BETWEEN lcc_eff."load_dts" AND lcc_eff."end_dts"

The effectivity satellite will filter the result to get only the relationships that were in the source system at the date of the snapshot.

Join the Car hub (Step 2)

SQL

SELECT
    snap."snapshot_date",
    lcc.*
FROM "dv"."l_car_contract" lcc
CROSS JOIN "im"."ctrl_im" snap
INNER JOIN "bv"."l_car_contract_crm_effsat" lcc_eff ON lcc_eff."hk" = lcc."hk" AND snap."snapshot_date" BETWEEN lcc_eff."load_dts" AND lcc_eff."end_dts"
INNER JOIN "dv"."h_car" hc ON hc."hk" = lcc."car_hk"

Another option here is to join on a PIT Table if it exists using the hash key and the snapshot date (example below)

Join the Car_manufacturer link and its effectivity satellite (Step 3)

SQL

SELECT
    snap."snapshot_date",
    lcc.*
FROM "dv"."l_car_contract" lcc
CROSS JOIN "im"."ctrl_im" snap
INNER JOIN "bv"."l_car_contract_crm_effsat" lcc_eff ON lcc_eff."hk" = lcc."hk" AND snap."snapshot_date" BETWEEN lcc_eff."load_dts" AND lcc_eff."end_dts"
INNER JOIN "dv"."h_car" hc ON hc."hk" = lcc."car_hk"
INNER JOIN "dv"."l_car_manufacturer" lcm ON lcm."hk" = hc."manufacturer_hk"
INNER JOIN "bv"."l_car_manufacturer_crm_effsat" lcm_eff ON lcm_eff."hk" = lcm."hk" AND snap."snapshot_date" BETWEEN lcm_eff."load_dts" AND lcm_eff."end_dts"

Join the manufacturer hub (Step 4)

SQL

SELECT
    snap."snapshot_date",   
    hm."hk" AS manufacturer_hk,
    hm."bk" AS manufacturer_bk
FROM "dv"."l_car_contract" lcc
CROSS JOIN "im"."ctrl_im" snap
INNER JOIN "bv"."l_car_contract_crm_effsat" lcc_eff ON lcc_eff."hk" = lcc."hk" AND snap."snapshot_date" BETWEEN lcc_eff."load_dts" AND lcc_eff."end_dts"
INNER JOIN "dv"."h_car" hc ON hc."hk" = lcc."car_hk"
INNER JOIN "dv"."l_car_manufacturer" lcm ON lcm."hk" = hc."manufacturer_hk"
INNER JOIN "bv"."l_car_manufacturer_crm_effsat" lcm_eff ON lcm_eff."hk" = lcm."hk" AND snap."snapshot_date" BETWEEN lcm_eff."load_dts" AND lcm_eff."end_dts"
INNER JOIN "dv"."h_manufacturer" hm ON hm."hk" = lcm."manufacturer_hk"

We can already add the hash key (hk) and the business key (bk) of the manufacturer in the bridge table.

Now that we have the manufacturer related to each contract, we will retrieve the employee using the other part of the model, using always the same procedure. When joining a link, always filter using the context satellite, joins over hub are simpler

Join the contract hub (Step 5)

SQL

SELECT
    snap."snapshot_date",   
    hm."hk" AS manufacturer_hk,
    hm."bk" AS manufacturer_bk
FROM "dv"."l_car_contract" lcc
CROSS JOIN "im"."ctrl_im" snap
INNER JOIN "bv"."l_car_contract_crm_effsat" lcc_eff ON lcc_eff."hk" = lcc."hk" AND snap."snapshot_date" BETWEEN lcc_eff."load_dts" AND lcc_eff."end_dts"
INNER JOIN "dv"."h_car" hc ON hc."hk" = lcc."car_hk"
INNER JOIN "dv"."l_car_manufacturer" lcm ON lcm."hk" = hc."manufacturer_hk"
INNER JOIN "bv"."l_car_manufacturer_crm_effsat" lcm_eff ON lcm_eff."hk" = lcm."hk" AND snap."snapshot_date" BETWEEN lcm_eff."load_dts" AND lcm_eff."end_dts"
INNER JOIN "dv"."h_manufacturer" hm ON hm."hk" = lcm."manufacturer_hk"
INNER JOIN "dv"."h_contract" hco ON hco."hk" = lcc."contract_hk"

Alternative using pit table on contract hub

SQL

SELECT
    snap."snapshot_date",   
    hm."hk" AS manufacturer_hk,
    hm."bk" AS manufacturer_bk
FROM "dv"."l_car_contract" lcc
CROSS JOIN "im"."ctrl_im" snap
INNER JOIN "bv"."l_car_contract_crm_effsat" lcc_eff ON lcc_eff."hk" = lcc."hk" AND snap."snapshot_date" BETWEEN lcc_eff."load_dts" AND lcc_eff."end_dts"
INNER JOIN "dv"."h_car" hc ON hc."hk" = lcc."car_hk"
INNER JOIN "dv"."l_car_manufacturer" lcm ON lcm."hk" = hc."manufacturer_hk"
INNER JOIN "bv"."l_car_manufacturer_crm_effsat" lcm_eff ON lcm_eff."hk" = lcm."hk" AND snap."snapshot_date" BETWEEN lcm_eff."load_dts" AND lcm_eff."end_dts"
INNER JOIN "dv"."h_manufacturer" hm ON hm."hk" = lcm."manufacturer_hk"
INNER JOIN "bv"."h_pit_contract" cpit ON cpit.hk = lcc.contract_hk AND it.snapshot_date = snap.snapshot_date

To be able to link a bridge over a pit table you must use the same snapshots.

Join the contract_employee link and effectivity satellite (Step 6)

SQL

SELECT
    snap."snapshot_date",   
    hm."hk" AS manufacturer_hk,
    hm."bk" AS manufacturer_bk
FROM "dv"."l_car_contract" lcc
CROSS JOIN "im"."ctrl_im" snap
INNER JOIN "bv"."l_car_contract_crm_effsat" lcc_eff ON lcc_eff."hk" = lcc."hk" AND snap."snapshot_date" BETWEEN lcc_eff."load_dts" AND lcc_eff."end_dts"
INNER JOIN "dv"."h_car" hc ON hc."hk" = lcc."car_hk"
INNER JOIN "dv"."l_car_manufacturer" lcm ON lcm."hk" = hc."manufacturer_hk"
INNER JOIN "bv"."l_car_manufacturer_crm_effsat" lcm_eff ON lcm_eff."hk" = lcm."hk" AND snap."snapshot_date" BETWEEN lcm_eff."load_dts" AND lcm_eff."end_dts"
INNER JOIN "dv"."h_manufacturer" hm ON hm."hk" = lcm."manufacturer_hk"
INNER JOIN "dv"."h_contract" hco ON hco."hk" = lcc."contract_hk"
INNER JOIN "dv"."l_contract_employee" lce ON lce."contract_hk" = hco."hk"
INNER JOIN "bv"."l_contract_employee_crm_effsat" lce_eff ON lce_eff."hk" = lce."hk" AND snap."snapshot_date" BETWEEN lce_eff."load_dts" AND lce_eff."end_dts"

Join the employee hub (Step 7)

SQL

SELECT
    snap."snapshot_date",   
    hm."hk" AS manufacturer_hk,
    hm."bk" AS manufacturer_bk,
    he."hk" AS employee_hk,
    he."bk" AS employee_bk
FROM "dv"."l_car_contract" lcc
CROSS JOIN "im"."ctrl_im" snap
INNER JOIN "bv"."l_car_contract_crm_effsat" lcc_eff ON lcc_eff."hk" = lcc."hk" AND snap."snapshot_date" BETWEEN lcc_eff."load_dts" AND lcc_eff."end_dts"
INNER JOIN "dv"."h_car" hc ON hc."hk" = lcc."car_hk"
INNER JOIN "dv"."l_car_manufacturer" lcm ON lcm."hk" = hc."manufacturer_hk"
INNER JOIN "bv"."l_car_manufacturer_crm_effsat" lcm_eff ON lcm_eff."hk" = lcm."hk" AND snap."snapshot_date" BETWEEN lcm_eff."load_dts" AND lcm_eff."end_dts"
INNER JOIN "dv"."h_manufacturer" hm ON hm."hk" = lcm."manufacturer_hk"
INNER JOIN "dv"."h_contract" hco ON hco."hk" = lcc."contract_hk"
INNER JOIN "dv"."l_contract_employee" lce ON lce."contract_hk" = hco."hk"
INNER JOIN "bv"."l_contract_employee_crm_effsat" lce_eff ON lce_eff."hk" = lce."hk" AND snap."snapshot_date" BETWEEN lce_eff."load_dts" AND lce_eff."end_dts"
INNER JOIN "dv"."h_employee" he ON he."hk" = lce."employee_hk"

We have now the complete structure of our query, we just need to add the satellite with the values we need to compute and return the correct column values. In our example the values are in the two satellites of the contract hub.

Join the contract satellites (Step 8 et 9)

SQL

SELECT
    snap."snapshot_date",
    he."hk" AS employee_hk,
    he."bk" AS employee_bk,
    hm."hk" AS manufacturer_hk,
    hm."bk" AS manufacturer_bk,
FROM "dv"."l_car_contract" lcc
CROSS JOIN "im"."ctrl_im" snap
INNER JOIN "bv"."l_car_contract_crm_effsat" lcc_eff ON lcc_eff."hk" = lcc."hk" AND snap."snapshot_date" BETWEEN lcc_eff."load_dts" AND lcc_eff."end_dts"
INNER JOIN "dv"."h_car" hc ON hc."hk" = lcc."car_hk"
INNER JOIN "dv"."l_car_manufacturer" lcm ON lcm."hk" = hc."manufacturer_hk"
INNER JOIN "bv"."l_car_manufacturer_crm_effsat" lcm_eff ON lcm_eff."hk" = lcm."hk" AND snap."snapshot_date" BETWEEN lcm_eff."load_dts" AND lcm_eff."end_dts"
INNER JOIN "dv"."h_manufacturer" hm ON hm."hk" = lcm."manufacturer_hk"
INNER JOIN "dv"."h_contract" hco ON hco."hk" = lcc."contract_hk"
INNER JOIN "dv"."l_contract_employee" lce ON lce."contract_hk" = hco."hk"
INNER JOIN "bv"."l_contract_employee_crm_effsat" lce_eff ON lce_eff."hk" = lce."hk" AND snap."snapshot_date" BETWEEN lce_eff."load_dts" AND lce_eff."end_dts"
INNER JOIN "dv"."h_employee" he ON he."hk" = lce."employee_hk"
LEFT JOIN "bv"."v_sh_contract_info" hco_info ON hco_info."hk" = hco."hk" AND snap."snapshot_date" BETWEEN hco_info."load_dts" AND hco_info."load_end_dts" 
LEFT JOIN "bv"."v_sh_contract_amount" hco_amount ON hco_amount."hk" = hco."hk" AND snap."snapshot_date" BETWEEN hco_amount."load_dts" AND hco_amount."load_end_dts"

As we are not using Pit Tables here, we have to do a LEFT JOIN to query the satellites and use the satellites views that compute the end dates (bv.v_sh_*)

Alternative using pit table on contract hub

SQL

SELECT
    snap."snapshot_date",
    he."hk" AS employee_hk,
    he."bk" AS employee_bk,
    hm."hk" AS manufacturer_hk,
    hm."bk" AS manufacturer_bk,
FROM "dv"."l_car_contract" lcc
CROSS JOIN "im"."ctrl_im" snap
INNER JOIN "bv"."l_car_contract_crm_effsat" lcc_eff ON lcc_eff."hk" = lcc."hk" AND snap."snapshot_date" BETWEEN lcc_eff."load_dts" AND lcc_eff."end_dts"
INNER JOIN "dv"."h_car" hc ON hc."hk" = lcc."car_hk"
INNER JOIN "dv"."l_car_manufacturer" lcm ON lcm."hk" = hc."manufacturer_hk"
INNER JOIN "bv"."l_car_manufacturer_crm_effsat" lcm_eff ON lcm_eff."hk" = lcm."hk" AND snap."snapshot_date" BETWEEN lcm_eff."load_dts" AND lcm_eff."end_dts"
INNER JOIN "dv"."h_manufacturer" hm ON hm."hk" = lcm."manufacturer_hk"
INNER JOIN "bv"."h_pit_contract" cpit ON cpit.hk = lcc.contract_hk AND it.snapshot_date = snap.snapshot_date
INNER JOIN "dv"."l_contract_employee" lce ON lce."contract_hk" = hco."hk"
INNER JOIN "bv"."l_contract_employee_crm_effsat" lce_eff ON lce_eff."hk" = lce."hk" AND snap."snapshot_date" BETWEEN lce_eff."load_dts" AND lce_eff."end_dts"
INNER JOIN "dv"."h_employee" he ON he."hk" = lce."employee_hk"
INNER JOIN "dv"."sh_contract_info" hco_info ON hco_info."hk" = cpit."contract_info_hk" AND hco_info."load_dts" = cpit."contract_info_load_dts"
INNER JOIN "dv"."sh_contract_amount" hco_amount ON hco_amount."hk" = cpit."contract_amount_hk" AND hco_amount."load_dts" = cpit."contract_amount_load_dts"

We can use INNER JOIN because the PIT table ensure we have line for all snapshots and all business keys
We have to join using Hash keys AND load date from the pit table

Now that we have the all our date available we can return values with a correct look and feel for a Bridge table.

Returning

The snapshot date
the Hash key and business keys of the entities we are interested in (here only employee and manufacturer)
Some computed values aggregated

SQL

SELECT
    snap."snapshot_date",
    he."hk" AS employee_hk,
    he."bk" AS employee_bk,
    hm."hk" AS manufacturer_hk,
    hm."bk" AS manufacturer_bk,
    SUM(hco_amount.total_amount) AS contract_value,
    SUM(hco_amount.total_amount - (hco_amount.total_amount* hco_info.discount::decimal)) as payed_amount
FROM "dv"."l_car_contract" lcc
CROSS JOIN "im"."ctrl_im" snap
INNER JOIN "bv"."l_car_contract_crm_effsat" lcc_eff ON lcc_eff."hk" = lcc."hk" AND snap."snapshot_date" BETWEEN lcc_eff."load_dts" AND lcc_eff."end_dts"
INNER JOIN "dv"."h_car" hc ON hc."hk" = lcc."car_hk"
INNER JOIN "dv"."l_car_manufacturer" lcm ON lcm."hk" = hc."manufacturer_hk"
INNER JOIN "bv"."l_car_manufacturer_crm_effsat" lcm_eff ON lcm_eff."hk" = lcm."hk" AND snap."snapshot_date" BETWEEN lcm_eff."load_dts" AND lcm_eff."end_dts"
INNER JOIN "dv"."h_manufacturer" hm ON hm."hk" = lcm."manufacturer_hk"
INNER JOIN "dv"."h_contract" hco ON hco."hk" = lcc."contract_hk"
INNER JOIN "dv"."l_contract_employee" lce ON lce."contract_hk" = hco."hk"
INNER JOIN "bv"."l_contract_employee_crm_effsat" lce_eff ON lce_eff."hk" = lce."hk" AND snap."snapshot_date" BETWEEN lce_eff."load_dts" AND lce_eff."end_dts"
INNER JOIN "dv"."h_employee" he ON he."hk" = lce."employee_hk"
LEFT JOIN "bv"."v_sh_contract_info" hco_info ON hco_info."hk" = hco."hk" AND snap."snapshot_date" BETWEEN hco_info."load_dts" AND hco_info."load_end_dts" 
LEFT JOIN "bv"."v_sh_contract_amount" hco_amount ON hco_amount."hk" = hco."hk" AND snap."snapshot_date" BETWEEN hco_amount."load_dts" AND hco_amount."load_end_dts" 
GROUP BY snap."snapshot_date", he."hk", he."bk", hm."hk", hm."bk";

Don’t forget the GROUP BY clause if needed