Foreign Keys in Satellites?

I’ve written about this danger many times, in fact, you can find some great postings and information about this subject on the forums on linkedin.com “Data Vault Discussions” but that’s beside the point.  There are no exceptions to this rule (as I see it).  Foreign keys simply should NEVER exist within a Satellite.  To do so severely damages the potential flexibility of the Data Vault model.  Let’s chat about some of the mathematics of maintenance behind this idea.

When we look at Foreign Keys, it is easy to think: “gee, it describes what the parent key seems to be at the moment” while at the same time it represents a link, a join, a relationship to other key or pertinent data.  It’s easy to fall down the slippery slope of simply deciding to add foreign keys to the Satellite structures.  There are all kinds of arguments as to “why” this can or should be done – but no matter what the argument – the fact remains: foreign keys in 3NF tables represent a relationship to other data.

In a Data Warehouse these foreign keyed relationships change over time and maintaining that history is essential for auditability and tracability.  However, this reasoning alone doesn’t negate the “desire” to put FK’s into the Satellite.

So what exactly is this driving force that keeps me from saying “ok”?

Let’s discuss!   By the way, please remember there are 10 years of R&D behind these rules and standards, and I hope that I’ve tested the architecture in many different situations well enough to understand the most common outcomes of architecture changes.  There is always a chance (a pretty good chance) that I missed something along the way, so if you think of something, go ahead and comment as a reply – and I’ll let you know my experience.  Now, on to the question at hand….

When we think about Data Models we often conveniently forget about complexity ratings.  That is: complexity ratings of the data model itself in relation to: ETL /ELT loading processes, BI Query processes, and of course MAINTENANCE of the Data Model.

More-over a Data Warehouse ISN’T A SIMPLE COMPLEXITY RATING!

Why?  Because a data warehouse stores data over time.  So that means, any time there is a structural change, it impacts all future data (yet to arrive), and all past data already stored in the EDW.  It is because of this “past” data set that we need to consider the complexity rating in conjunction with a multiplier effect.   In other words, the complexity rating of a data model without historical data might be “3” on a scale of 1 to 10, where the complexity rating (for absorbing changes) to an EDW (because of history) would be a 3^2  or 3^3 – all of the sudden the complexity rating is off the charts.  ie: a 9 or a 27 on a scale of 1 to 10….

This is because the NUMBER OF ITEMS impacted by the change, double or triple – then there is the “data” – what to do about the data that is already stored?   Some people fall down this slope and say: “that’s easy, just add the new Foriegn key to the Satellite and make it optional”.  Well, optional foreign keys (even in OLTP tables without history) increase the complexity rating on a factorial basis.

To understand this, we look at the complexity ratings used to measure level-of-effort in maintaining code.  In a programmattic sense, what happens to the complexity rating of a procedure/function when a “decision” or condition is introducted?  It makes the code inside the condition, optional based on some data driven element.  The complexity rating increases.  There are tons and tons of formulas to describe these effects in costs per defect, costs per option/decision, performance per decision or “branch”, etc….

The Satellites are the same…  DON’T INTRODUCE OPTIONAL DECISION MAKING TO A SATLLITE!!!  Why?  Because the foreign key structure in the satellite represented by the data of YESTERDAY will NOT equal the foreign key structure in the satellite represented by the data of TODAY.  This FORCES BI code to become far more complex than it needs to be to account for time-line breaks.

But it get’s worse.  All of the sudden the metadata (meaning/definition) and the “grain” of the data in the Satellite is called in to question.  For instance, IF you had a Satellite off CUSTOMER today, and it had a foreign key to SALESPERSON – and that FK was Optional.  What does the Customer data mean?  Well – if it has a foreign key filled in, then it means a salesperson sold that customer?  Ok, but what happens if another foreign key is added to the Satellite:  SALESTERRITORY.  Now, what does the “old data” mean if it doesn’t have a SalesTerritory?  Does it mean we never got the data?  Does it mean we got the data on the feed but ignored it?  Does it mean that no-one entered the SalesTerritory in today’s data?

What exactly does the FK represent?  Does the FK mean the customer is in a particular sales territory or does it mean it’s the sales territory of the salesperson, but ONLY when the salesperson FK is filled in?

As you can see, the complexity of deciphering all of these questions begins to raise the maintenance cost of the Data Vault model…  Something we all want to avoid.  if we put the foreign keys in Links, then “yesterday’s LINK A” has Customer and SalesPerson,  a NEW LINK (if the data truly means: a salesperson SOLD this customer IN this territory”) would contain: Customer, SalesPerson, and SalesTerritory (from this date forward).

There is a distinct difference in segregating the relationships out, compared to “storing them with the Satellites”.  What you are effectively doing (when you put an FK in a Satellite) is “OVERLOADING” the definition of the satellite (to use a coding term).

When you overload – you multiply the technical and business definitions, along with applying a factor to the complexity of understanding the information….  This is a BAD BAD practice.

DO NOT PUT FOREIGN KEYS IN YOUR SATELLITES!!!  IF YOU DO, YOU DO NOT HAVE A DATA VAULT!  Furthermore, you will not inherit the benefits of the Data Vault going forward – and it will eventually cause you to RE-DESIGN the whole EDW from the ground up because maintenance costs will spiral out of control.

There are many other technical reasons, but this – this is the major business reason.

Hope this helps clear the air,
Dan L
DanL@danLinstedt.com

Tags: , ,

8 Responses to “Foreign Keys in Satellites?”

  1. Roelant Vos 2010/04/29 at 1:16 am #

    Hi Dan,

    Would you consider adding foreign keys to -DWH governed- masterdata tables to a satellite? Things like internal classifications or codes (but containing a start and end date!).

    I was thinking of using this kind of trick to classify the type of relationship in a link satellite table. This (foreign key) attribute would refer to a limited set of relationship types in some sort of reference table. The reference table could theoretically change over time but I don’t see the impact on the DWH here because you would always select on the validity time period.

    Because the data is no real source but part of the DWH I figured the exception could be made. The alternative would be to create a big number of link tables.

    Kind regards,
    Roelant Vos

  2. dlinstedt 2010/04/29 at 2:49 am #

    I consider classification codes and type codes and the like to be descriptive data. This is the *one* instance that I allow a “logical” (not physical) foreign key to be embedded in a Satellite. Also because the type code is a role-playing descriptive attribute. I believe I blogged about Type Codes recently, if not, I’ll post something more concrete to this effect.

    So in your case, yes – type codes are allowed. What I’m specifically referring to with no FK’s (or complaining about) is the act of burying business key to business key relationshipsin the Satellites, this is a major no-no. As we move along I’ll try to blog some specific examples of the cause/effect and impact analysis of why this is such a big hang-up for me, and how it can lead to re-engineering in the near future.

    Hope this helps, let me know if I didn’t answer your question.
    Thanks,
    Dan L

  3. M. Streutker 2010/08/02 at 8:32 am #

    Hi Dan,

    after reading this article I was a bit confused about the usage of foreign keys in a datavault.
    In part 2 of your Data Vault Series, you give an example of the Northwind Database as a physical Data Vault Model.
    This example includes a lot of foreign keys on satellite level, i.e. the SAT_ORDERS satellite includes keys to EMPLOYEEID, CUSTOMERID, etc.

    How is that sample different from the advice you give in this article (don’t put FK’s in your satellites)? Or is that sample a bit outdated?

    Kind regards,

    M. Streutker

  4. dlinstedt 2010/08/02 at 6:59 pm #

    Hi M.

    The advice I gave in part 2 of my DV series (sorry to say) was a little incorrect. Part 2 was written in 2001, and since then I’ve performed a lot of testing on the architecture, and I have discovered that introducing foreign keys to Satellites (other than the parent key) causes great re-engineering efforts to take place every time a change is desired by the business. This is opposite of what we truly hope to achieve.

    The sample / example is quite out-dated.

    Hope this helps,
    Dan Linstedt

  5. M. Streutker 2010/08/03 at 12:50 am #

    Hi Dan,

    thank you for your clarification and quick response.
    It’s good to see that you continue to improve the architecture, I’ll keep in mind that the original series might a bit outdated on certain areas.

    Kind regards,

    M. Streutker

  6. dlinstedt 2010/08/03 at 4:25 am #

    Hi M,

    You’re welcome. It’s this kind of knowledge (and more) that are embedded in my coaching section of this site. Let me know if you’re interested in signing up. You have a unique opportunity at this time to get custom material built to answer your specific questions.

    Cheers,
    Dan L

  7. Basil Peace 2015/10/09 at 10:48 am #

    What if my source is a relational database, and it has many-to-one relationship. This relationship is considered as an attribute.
    My entities are the entities from this source system, so I’m sure that nothing changes before changing the source system (which would be very expensive and almost improbable). Even if that would happen, entire data vault should be remodeled.
    1. Could I model this specific case as attributes (satellites) instead of relationship (link)?
    2. Can I place FK on satellites in this specific case?

  8. Dan Linstedt 2015/12/09 at 7:39 am #

    Hi Basil,

    These concepts have been discussed at length here on my blog, as well as in the LinkedIn discussion groups: “Data Vault Discussions”. I actually teach these concepts in my CDVP2 (Data Vault 2.0 Boot Camp and Private Certification) class. You can also dive in deep to these concepts by reading our new book: “Building a scalable Data warehouse with Data Vault 2.0” available on Amazon.com today.

    The short answers are as follows:
    1. No – you cannot break the rules, to do so would damage the flexibility of the architecture
    2. No – you can *never* put other FK’s on Satellites in these cases, they would not be satellites but Links for these reasons. Again, it breaks the flexibility of the model.

    Hope this helps,
    Dan

Leave a Reply

*