A bridge table pre-joins multiple link and hub keys into a single wide table at a specific grain. Instead of joining 5+ tables at query time, downstream models read one table.
What the Bridge Contains
- As-of date — which date this pre-join is valid for
- All hub hash keys — order_line, order, customer, product, warehouse
- All link hash keys — the links connecting these hubs
- Effectivity filter — only active relationships included
Bridge Model
bridge_order_line
— grain: one row per order_line per as_of_date
— keys: order_line_hk, order_hk, customer_hk, product_hk, warehouse_hk
— links: link_order_line_order, link_order_customer,
link_order_line_product, link_order_warehouse Why a Bridge?
In a raw vault, getting from order_line to customer requires joining through: hub_order_line → link_order_line_order → hub_order → link_order_customer → hub_customer. That's 4 joins. The bridge does this once and stores the result — marts just join on a single key.
Effectivity Filtering
The bridge only includes active relationships. If an order was cancelled, its lines won't appear in the bridge for dates after cancellation. This uses the effectivity satellites loaded earlier in the pipeline.
Loading Pattern
Like PITs, the bridge uses TRUNCATE + INSERT for the new date range. Same idempotency guarantee — re-running produces identical results.