· Alvaro Barber · Tutorials · 2 min read
Dimensional modeling vs Data Vault
Data modeling
Dimensional Modeling
Dimensional modeling is pretty effective for query performances from BI tools like PowerBI or dashboards. The drawbacks are that if you set a certain granularity in your table(by granularity lets take as an example the different foreign keys that points to your dimensions), once in production it will be costly to add another granularity(foreign key pointing to a new dimension) because that means that you need to reload the data, and backfill the historical data before the date when you created the new granularity.
For this purpose Data Vault is more flexible and instead of relying in a single backbone(fact table), in case of adding or removing a granularity, you can simply add or remove a satellite.
Data Vault
Data Vault is a very costly data modeling technique due to its logic with hash keys and hash diffs and how to model the satellites,but for data that is constantly changing, for example you have different providers for different countries that you need to ingest and that constantly changing its schema, data vault makes it easier to build it with just simply adding satellites.
Hybrid approach
Finally, you can make a mixed approach where you can build on top of those hubs and satellites (by making a UNION of satellites in the FACT table), the DIM and FACT tables. So at first you serve flexibility on how to ingest the data withour changing schemas with the creation of satellites plus on the other side you serve query performance from the Visualization BI tools by creating DIM and FACT tables on top of the Data Vault.
Normally just with one or approac or another should be sufficient with its benefits and drawbacks.
Check the video for more info https://youtu.be/l5UcUEt1IzM?si=juvZiOP-yFyCg933