Value of PIT and Bridge Tables in Data Vault 2.0

this is a short entry about the value received when properly implementing pit and bridge tables as defined in data vault 2.0 (building a scalable data warehouse with data vault 2.0) – the link is to the canadian amazon site because i am in canada as i write this entry.

what is a pit or a bridge?

let me start off by offering a brief definition (the full definition is offered in the book, and in my cdvp2 – certified data vault 2.0 class).  you can find classes in north america, europe, new zealand, australia, here:

a pit is: a point in time, a system driven satellite loaded with a hub or link hash key, hubs’ business key(s), and the surrounding satellites’ primary key values.  these pk’s are a full copy of the entire pk housed in the satellite.  it is absolutely vital that you follow the standard for the full pk, and not just copy the “load date only” to the pit table.  this, again is explained in detail in the certification class and in the book.

what is a bridge table?

a bridge table is quite different in structure – it houses hub and link keys only (for starters), and is a cross-combination of keys that are governed by a where clause (number of rows are controlled by a business use case / requirement) for just what the business wants to see.  no, this is not the same as a fact table.  again, the standards are defined in the class and in the book.

what are the similarities?

the following is a list of similarities:

  • snapshot based, driven by schedule
  • sit in the business data vault side – which means you can add computed fields, and copies of satellite fields needed in where clauses issued by ad-hoc user queries (through views)
  • raw data, key structures

what is the purpose of each of these tables? why build them?

well, quite frankly, the loading process to build each houses left outer join components.  before you climb my tree on performance, please note: they should be run frequently, which means they should only go after the “latest data sets”, which means, they should be “thin” in the amount of data they are left-outer-joining.

the purposes of each of these tables are:

  • to eliminate all outer-joins from the ad-hoc layers above, allowing equal-joins to take place
  • to provide full scalability in views or virtual dimensions and virtual facts on top of the data vault
  • to buffer data release in accordance with sla’s regardless of data arrival rates (ie: think microsecond real-time inserts)
  • to co-locate data and indexes (in an mpp solution) where necessary
  • to enable table-elimination from the ad-hoc query engine going through the views on top of these tables.
  • to enhance partitioning
  • to enhance performance
  • to allow any “star-join” optimizations to be utilized (when offered by the query resolution engine)

keep in mind, that this technique is one of the single most powerful techniques inside data vault 2.0, and that utilizing these techniques can enable you to offer virtual (view based) dimensions and facts for 95% of your user based requests.  eliminating the need to physicalize yet-another-copy of the data in downstream information marts.

when properly applied, they work in all environments appropriately and can provide tremendous value to the business as well as it.

can pits and bridges be used with dimensional warehouses?

yes, you can use pits and bridges in dimensional data warehouses, as well as operational source systems.  these two structures are extremely helpful in gathering answer sets, preparing data for bi consumption and making things faster by point in time queries.   as a side note, you can also add a) multiple time-lines, b) computed fields, c) replicated fields (for indexing purposes and data mining capacities) to enhance the level of bi queries you execute.

we teach all of these concepts in cdvp2 (certified data vault 2.0 practitioner) classes.

ps: kent graziano has some super blogs about virtualization and views, and building of pit and bridge structures.  you should check it out!

as always, i welcome comments below.

hope this helps,

(c) dan linstedt, 2016 all rights reserved.

Tags: , , , , , , , ,

No comments yet.

Leave a Reply