Data Vault 2.0 applied in a big company

Introduction

Let me explain my experience leading the whole Data Architecture (split by layers, see on my previous post here) and also leading and creating from zero the whole Enterprise Data Warehouse solution for my current company.

 

About the company

Avoiding names, I would like to introduce few facts about this company. It is one of the largest ecommerce companies in the world with more than 13,000 employees. This company has more than 70 active companies or services attached to its core company, providing a huge variety of business such as: Insurance, Telecommunication, bank, credit card, travel, sports (bought soccer club, beisbol club, etc),…

 

About the data

So for that, we have more than 500 databases distributed around the world with thousands and thousands of tables, as mentioned before, talking about very different business domains. Size of it, still unknown, but so far we have in our hands around 2PB (and growing).

 

How to implement Agile DW with DV2.0

 

First: Data ingestion (Bring all your data into your Data Lake and keep history records)

So in order to get all this data into our History Stage (copy of the original + history), we did developed a Python script and a MetaData Management to automate all the ingestion, but that’s another topic that I will cover in another post, here I am going to focus on the EDW solution to get as much as possible from all this data.

  

 

Second: prepare automation for your Data Vault 2.0

This is very important step as it will provide a huge benefit for a cheap price. Talking about my own experience, I created a whole framework in Python code that auto populates Hub, Satellite, Link and SameAsLink objects in about 2 weeks of Development.

How does it work? Easy! You just need to create a view that follows the Data Vault naming convention. Let’s say that we create a Satellite view to feed our DV, and it will automatically populate, not only a Satellite (table and load), but also Hubs reusing the same view (possibilities to automate are limitless). With a bit of code in our framework, our view will be able to:

  • * Create hub table (if not exists).
  • * Load hub information (only new data).
  • * Create Satellite table (if not exists)
  • * Drop and create views such as vw_[name]_current and vw_[name]_history.
  • * Load Satellite information (new, updated and deleted data).
  • * Get metadata and governance to know which Entities, DV Objects and business we are loading.

 

This is a sample of my naming convention for the framework:

[DV Object]_[Entity]_[Business]_[common/[others]] ==> sat_customer_bank_common

 

Third: split your Satellites using the flexibility of DV2.0

So, the beauty of Data Vault is its scalability and flexibility, which is perfect for complex scenarios such as developing an EDW with an initial unknown scope for a very big company. Since companies are so different from each other and we don’t know how many entities we will need at the end, we started creating some basic entities, such as: customer, order, orderdetail, product, item and so forth.

So we are also putting all the common attributes in a common Sat for each entity, because, even though Phone, a Bank or an eCommerce companies have almost nothing in common, they will still have customers and these customers will share common attributes such as Name, Address, DOB, Gender, etc. This is just finding semantical understanding to our data and using it.

For those attributes that are specific for each business, then we can just create a special satellite for customer banks (salary, rate, etc), other satellites for travel business (frequent flyer number, preferred destinations, etc).

That means, we don’t need to design the whole EDW in one goal, neither we need to include all the business of each entity in order to start getting value.

With the common attributes, we can reuse the same customer segmentation or KPI reports across each business or all together (or by groups) only with one report and a filter for the company/ies that we want to analyse.

What about Item, Order, Order detail, etc? Easy find the semantical understanding of the data. For example, in eCommerce item could be an ipad2, in bank could be loan package 30 years, in travel could be travel package, or even a hotel room (i.e.: “Holiday Inn – Atlanta – Double standard“) and so forth. So later you’ll be able to reuse the same report to check which item was the most successful during one period (agnostic of which kind of product it is).

NOTE: Of course, other good practice is to split your satellites by change rate, so the columns that change the most could be in one satellite and ETL it very often and the ones with low change/priority could be loaded daily or weekly etc.

 

Fourth: Supernova layer or “Business Delivery Layer” (c)

Thanks to our MetaDataManagement, we can automate creation of views removing the DV complexity. So, Business users can start looking at the EDW with a 3NF language that they are more comfortable or create (an even automate) our virtual Star Schemas, based on only views, so much easier to create, recreate and fine tune.
* BTW, I prefer to call it “Business Delivery Layer” .


 

Some references:

 

Pure (and real) Agile Data Warehouse

So the first and second steps are king of one off, once is developed and running in an automation way.

The third step is the one that can be done, one entity/business at the time and that means, because we are not aiming to deliver all of them at the same time. So we can apply agile sprints at target for a number of entities and business for this sprint (let’s say 2 weeks).

Once these new entities/business are in production, you can easily go to the fourth step and create the supernova of it (aim for automation when possible) and data consumers can start enjoying this part of the EDW, or at least, checking if they need something else, if they are missing things etc. So if that happens in the supernova layer, as they are all views, it will be very fast to fix it, if it is a major change in the DV layer, then next sprint we can fix it just creating new satellites (always adding/appending, never rebuilding or throwing away anything).

 


Did you like this? Share it:

Leave a Reply

*