What problem do point-in-time and bridge tables solve in a Data Vault?

## Why raw vault queries hurt A Data Vault raw layer is optimized for loading and retention, not for reading, and two of its design choices work directly against the query planner. First, **history is insert-only**. A satellite holds every version ever seen, keyed by parent hash key plus load date, with no current flag and often no end date. Asking "what did this customer look like on 30 June" means finding, per key, the row with the greatest `load_date` at or before that instant. Second, **attributes are spread across many satellites** — one per source system, plus splits by volatility and sensitivity. A single business question routinely needs four or five of them, each requiring its own per-key latest-as-of resolution, and each versioned on its own independent schedule. The result is a query with several correlated subqueries or window functions, one per satellite, all of which the engine must evaluate before it can join anything. It is slow, and — more damagingly — it is easy to write subtly wrong, because every analyst re-derives the same as-of logic by hand. ## The point-in-time table A PIT table removes the per-satellite resolution from query time by doing it once, on a schedule. It holds one row per business key per snapshot date, and for each satellite of that key a column carrying the exact load date of the version in force on that date: ```sql CREATE TABLE pit_customer ( customer_hk CHAR(32) NOT NULL, snapshot_date DATE NOT NULL, sat_crm_load_date TIMESTAMP NOT NULL, sat_billing_load_date TIMESTAMP NOT NULL, PRIMARY KEY (customer_hk, snapshot_date) ); ``` Queries then join each satellite on both the hash key and the load date the PIT row names — a plain equality join on the satellite's own primary key. All the ranking is gone. The grain of the snapshot is a design decision. Daily is common; some vaults keep a dense daily grid, others store only the dates on which something changed, which is far smaller but requires an as-of lookup to find the right snapshot row. ## The ghost record A PIT table's real value is that its joins stay **inner equality joins**, and that breaks the moment a satellite has no row at or before the snapshot date — a customer known to the CRM but not yet to billing, for instance. The standard fix is a **ghost record**: one row per satellite with a sentinel hash key (conventionally all zeros), a minimum load date, and null attributes. The PIT row points at the ghost's load date when there is no real version, and the join succeeds with nulls instead of dropping the row. Without it, one absent satellite silently deletes the customer from the result. ## The bridge table A bridge table solves the structural analogue. Because a Data Vault expresses every relationship as a link, answering a question that spans customer to order to order line to product means traversing several link tables, each with its own hash keys. A bridge precomputes the key combinations across that chain — hub and link hash keys, often with a snapshot date so it also carries the as-of resolution — so the query joins one wide bridge to the satellites it needs instead of walking the graph. Bridges are commonly built for the exact navigation paths a set of marts uses, which is why they tend to be shaped by consumption rather than by the source model. ## Both are disposable The crucial property of both structures is that they contain **no information the vault does not already hold**. Every value in a PIT or bridge table was derived from hubs, links and satellites. Drop them all and nothing is lost except speed; rebuild them and you get identical content. That is why they belong to the business vault rather than the raw vault, why they are usually rebuilt rather than incrementally maintained, and why they are safe to reshape when consumption patterns change. It also means they carry the standard costs of any precomputation: storage that scales with keys times snapshots, a rebuild job that must run and can fall behind, and staleness between rebuilds. A dense daily PIT over a large hub is not free, and shops routinely limit the snapshot horizon or restrict PIT coverage to the entities marts actually query as-of. ## Where the boundary sits PIT and bridge tables are a query-assist layer, not a consumption layer. They make vault queries tractable for the jobs that build marts; they do not turn the vault into something analysts should query directly. The marts are still the thing BI tools read.