To Surrogate Key or Not... 

Incredible! Keep these SQL theory vids coming. This is high-level stuff. Thanks Patrick for continuing to be there for us enterprise level SQL folks. YOU DA MAN

Surrogate keys are THE most critical piece for good model management. Management of keys pushes you to ensure the proper Schema. Do it! Do it!

short and sweet. Thank you 🙂

Brilliant explanation!!!! Thank you

On a related note, I've seen users replace string-based natural keys with integer-based surrogate keys in their Power BI data models, assuming the smaller integer data type would be more performant. However, most of the time, the VertiPaq engine will hash these integers, making them strings anyways, so all the user is really doing is adding complexity to their transformations and compute to their refreshes.

Important to understand when to use them, and also when not to. Sometimes its very easy to overcomplicate your models with an insistence on 'incremental' SKs, even when they are not needed or even costly (e.g. in columnar datasets). I prefer the Super Natural Key / Durable Key model, or even better, having natural dimensional fields actually in the facts for the columnar data compression to work with.

Love this, thanks 👍

Amazing video x

Nice simple explanation of when & why to use surrogate keys. Particularly the why - too often surrogate keys are presented as being good design always, even when they aren't needed and the natural key will work fine. Side note: One habit that doesn't seem to go away, but has no value vs using the natural DATE is creating a YYYYMMDD integer surrogate key for a date (aka Calendar) table. Bad: The INT has more bytes than DATE - it will cause more IO. So it will be slower, even if only marginally. Bad: It has no integrity enforcement as a valid date. Bad: It is awkward to manipulate as a date - e.g. using date functions. Dates as 4 byte INT has not had value since the introduction of SQL Server 2008, with the 3 byte DATE type.

Nice video. But i have question. What if customerId was a varchar do we still need to have surrogate key?can you please clarify?

Good vídeo, I'm going to share this with my team! Also the new source system could have overlapping IDs (even thou it is varchar it can contain ints)

How about you need to allow to user to add like a budget fact table to the model (Copositive model) then they will need to know the SK value of an Item, if the Item Dimension has NK then they don't, just add it and it works.

Not my first priority. But you hit on the best reason to use it, if your IDs like CustomerID may change or be augmented in future.

During regular load then dimensions needs to be loaded first then facts by look-up on dimensions for keys

If the data you are processing has or could have more than one source, and you want this representation, the information you are providing, to be used by any other process, then an independent (surrogate) key is needed to represent the composite information. If you were to represent a multi-source dataset with the key from one of its sources, you would be changing the meaning of the source keyfield and adding a processing dependency. I would argue that every dataset should have an independent keyset because change is inevitable in real-world processes. Certainly, for repositories that are intended to be long-lived data warehouses, independent (surrogate) keys should be the default for the reasons illustrated here, among others.

Maybe i'm missing some thing, once there is a new line in the customer dimension table how can the fact table get the new CustomerSK?

I don’t think customer id is a natural key. Natural keys are derived from the columns. If the id is chosen arbitrarily, then it’s synthetic