The Bridge Key Is a Hash of a Path
Hub And Link Keys Hash Into One Bridge Key Reloads Can Only Add To
August 21, 2026
Every Data Vault eventually grows a query problem. The raw vault is shaped for loading: hubs hold business keys, links hold relationships, satellites hold history. That shape is exactly wrong for the analyst who asks "which products did this customer actually order?" and gets a five-join traversal for an answer. Bridge tables exist to walk that path once, at load time, so every downstream query joins once.
The awkward part of a bridge table is not the joins. It's the key. A hub row has an obvious identity: its business key. A link row has one too: the combination of the hubs it connects. But a bridge row represents a traversal, this hub, through this link, through that link. What is the primary key of a path?
Most hand-built bridges dodge the question. They key on an identity column, which changes every time the table is rebuilt, or they skip the key entirely and truncate-and-reload on every run. Both choices leak. Identity keys break any downstream table that stored them. Truncate-and-reload turns the bridge into the most expensive table in the warehouse as the vault grows. And both are a quiet confession that the loader can't tell whether it has seen a path before.
BimlFlex answers the question directly: the key of a path is a hash of the path. Here is what the generated bridge loader actually does, and why it makes reloads boring in the best possible way.
The Key Is the Path
In BimlFlex you model a bridge as its own object type, point it at a starting hub, and declare the link joins it traverses. The rest is derived. With the Bridge Add Surrogate Key setting (DvBridgeAddSurrogateKey) enabled, the generated loader composes the bridge's surrogate key from the hub's key plus the key of every link leg, in traversal order, and runs the result through the same hash pattern every hub and link key in your model already uses.
We covered the hash function half of this story in One Model, One Hash: the same algorithm, null handling, and casing rules produce identical keys on every target platform. This post is about the other half: what goes into the hash. For a bridge, the input is the traversal itself. On SQL Server, the generated key expression looks like this (SHA-1 shown, condensed, with example model names):
UPPER(CONVERT(CHAR(40), HASHBYTES('SHA1', CONVERT(NVARCHAR(MAX),
COALESCE(CAST(hub.[CustomerSK] AS VARCHAR(100)), 'NVL')
+'~'+ COALESCE(CAST(l1.[CustomerOrderSK] AS VARCHAR(100)), 'NVL')
+'~'+ COALESCE(CAST(l2.[OrderProductSK] AS VARCHAR(100)), 'NVL')
)), 2)) AS [CustomerOrderProductSK]
Notice what the inputs are: hash keys. The bridge key is a hash of hashes, seeded with the hub's surrogate key and extended with one segment per link leg. The separator is your StringConcatenator setting (~ by default), and the null replacement is your HashNullValue setting (NVL by default), the same two knobs that govern every other hash in the model. Nothing about the bridge key is special-cased.
That uniformity is the point. The key is a pure function of which rows the path visits. Load the same hub and link rows tomorrow, next month, or on a rebuilt server, and the same path produces the same key. There is no sequence to reseed and no identity gap to explain. If you have modeled your business keys and relationships well, the bridge key is as deterministic as the hub keys underneath it.
Zero Keys, Not NULLs
A path is not always complete. The bridge joins are outer joins for a reason: a customer with no orders is still a customer, and the bridge should still carry the row. That leaves the question of what to store in the link-key columns for the legs that didn't match.
The generated loader never stores NULL there. Two substitutions happen, and they are deliberately different. Inside the hash input, a missing leg becomes the HashNullValue replacement string, so the absence itself is hashed deterministically and a partial path gets a stable key distinct from every complete path. In the stored columns, a missing leg becomes the Data Vault zero key: a fixed default key value you can override per model with the DvZeroKeyExpression setting.
The zero key is what keeps the bridge pleasant to consume. Downstream queries can join every leg without outer-join gymnastics, because the key columns always contain a joinable value. It is the same courtesy a well-built dimensional model extends with its unknown member, applied at the vault delivery layer.
An Insert-Only MERGE
The load itself is where the key design pays off. The generated procedure on SQL Server is a MERGE with a shape worth staring at for a second:
MERGE INTO [dv].[brg_CustomerOrderProduct] TGT
USING
(
SELECT /* hashed bridge key, hub key, link keys with zero-key
fallbacks, effective date from the traversed legs */
FROM [dv].[hub_Customer] hub
LEFT JOIN [dv].[lnk_Customer_Order] l1
ON l1.[CustomerSK] = hub.[CustomerSK]
AND l1.[FlexRowEffectiveFromDate] > @cur_from_date
/* ...one join per declared link leg... */
WHERE COALESCE(l1.[FlexRowEffectiveFromDate],
hub.[FlexRowEffectiveFromDate]) > @cur_from_date
) INS
ON TGT.[CustomerOrderProductSK] = INS.[CustomerOrderProductSK]
WHEN NOT MATCHED THEN
INSERT (...)
VALUES (...);
There is no WHEN MATCHED THEN UPDATE branch. None. If the incoming path hashes to a key already in the table, the row is skipped. If it hashes to a new key, it is inserted. Those are the only two outcomes the statement can produce.
That is not an omission, it is the contract. A bridge row asserts "this path existed as of this effective date." Facts like that don't get edited; new facts get appended. By construction, running the loader can grow the table and can do nothing else. The grain you declared in metadata is the grain you will find in the table after any number of executions, in any order, on any day.
Why Reruns Are Boring
Put the two halves together and idempotency stops being a property you test for and becomes a property you can read off the code. The key is deterministic, so reprocessing a window of data regenerates exactly the keys it generated last time. The MERGE is insert-only, so every one of those regenerated keys matches an existing row and lands in the do-nothing branch.
A failed job gets rerun without ceremony. An overlapping load window reprocesses rows it has already seen and adds zero duplicates. An operator who runs the procedure twice out of caution costs you compute, not correctness. Compare that with the truncate-and-reload bridge, where a failure mid-load leaves an empty or partial table and every rerun is a full rebuild, or with the identity-keyed bridge, where a rebuild silently reassigns every key.
The load is also incremental by construction. The generated joins and the WHERE clause are bounded by the current load date, so each execution walks only the paths whose legs have moved since the last one. The expensive traversal happens once per new path, not once per query and not once per full table per day.
Where This Sits in the Model
Bridges are delivery-layer furniture, part of the query-assistance layer that sits between the raw vault and whatever consumes it, alongside point-in-time tables. If you are deciding when a bridge earns its place, the guide to delivering data from a Data Vault covers the delivery patterns, and the broader case for generating this layer instead of hand-coding it is laid out in our complete guide to Data Vault automation.
The bridge loader is one instance of a pattern that runs through the whole product: you declare structure in metadata, and the generated code carries that declaration all the way into the artifact. You declare which hub anchors the bridge and which links it walks; the generator derives the key composition, the zero-key handling, and the insert-only load, using the same accelerator-driven modeling metadata that built the hubs and links in the first place. Nobody on your team writes the hash expression, so nobody on your team writes it differently in two places.
A bridge table's grain is a promise about what one row means. BimlFlex turns that promise into a key, and then generates a loader that is physically incapable of breaking it.
