an appropriately unhinged deep dive into kimball scds

I asked stable diffusion for an eldritch monster that represented the row proliferation that comes with SCDs and it truly delivered. I think this is great.

Entropy for all!

Nothing stays the same in life, or in your data. It’s a vomit-inducing cliche to say that change is the only constant, but it’s true 1.

One day, you will die. Your loved ones will remember you, but then they will die, and all traces of your existence will be wiped from the earth. Probably the central task of human existence is figuring out how to live with that knowledge.

Your data will also shift and change over time. Certain dimensions may die, or be renamed, or members of those dimensions may be reassigned. Most enterprise data is a living thing with descriptors that change over time. An important task as a data practitioner is figuring out how to handle that fact.

Consider the humble Croc

Maybe you, like this enterprising Etsy seller, are a purveyor of Croc charms2. Maybe, at the start of your business, you have red mushroom charms and clover charms together in the same sales category. You call that sales category “natural items”.

But Crocs are on a resurgence! For some bizarre, godforsaken reason, everyone wants Crocs again. Your charm business is taking off. Combine that with mushrooms also having a moment3, and you find yourself with the need to have a separate “mushroom” category for all the variations on mushroom charms you now offer.

The red mushroom charms now fall under this “mushroom” category instead of “natural items”. You’ve sold some red mushroom charms under the “natural items” category, and are now continuing to sell them in this new sales category.

One of your sales dimensions has changed. It’s just done so quite slowly.

Ch-ch-ch-ch-changes

You now have sales data on these red mushroom charms in two separate categories. How are you going to represent that change?

Well, first of all, you should ask yourself if you give a shit. I’m serious. Do you actually care whether your red mushroom charms did better in the “natural items” or “mushrooms” category? Depending on the size of your enterprise and the goals you have with your data, your answer may vary4.

If you don’t care about the potential difference in sales under one category vs. another, then you can go ahead with your bad self and just overwrite the category that those charms fall under warehouse-wide. That is a Type 1 dimension. All you have to do is overwrite the old category with the new one. It’s slick and easy, but you lose any history associated with that category.

So maybe you don’t care and you choose to treat your charms category as a Type 1 slowly-changing dimension. But Crocs, as a company? They probably do care about this type of change, and they need to give some thought as to how to capture it.

Oh this? It’s type 2, boo boo.

If you’re an analyst at Crocs, you probably care about the performance of charms in one sales category as opposed to another. It’s entirely possible that customers have an easier time finding (and therefore buying!) the mushroom charm they want when it’s housed under “mushrooms” as opposed to “natural items”. Moving your sales categories around could really improve product discoverability and ultimately improve sales numbers.

So you care about this category shift. As an analyst proficient in the ways of Ralph Kimball, you might choose to represent this sales category as a Type 2 SCD. Type 2 SCDs simply add effectivity dates to rows. Take a look at this.

This is a really simplified version of a product dimension with effectivity dates. These effectivity dates might vary in their nomenclature, but the addition of effectivity dates is a hallmark feature of Kimball Type 2 SCDs.

Adding these effectivity dates means that you can retain the old category that the Red Mushroom charm belonged to, and analyze sales of Red Mushrooms using the dates that demarcate when it belonged to which category. To analyze its current performance, all you’d need to do is filter sales based on the first date upon which Red Mushrooms belonged to the “Mushrooms” category.

She’s cute. She’s simple. She’s clean. I love a Type 2 SCD.

How else can I skin this cat?

When we read this chapter of Kimball’s Data Warehouse Toolkit in my Kimball book club, we spent a good amount of time clowning on the sheer number of SCD types. I believe there are 7 according to Mr. Kimball, and the numbering really doesn’t give you much clue into what the hell the dimensions are supposed to do.

Other topics around capturing changes in your data include change data capture and data vault. I have a fairly decent understanding of Kimball SCDs, but I couldn’t speak much to CDC or data vault. I’m just your local internet clown with strong opinions on modeling your BI-facing models well enough for your business users to interact with them easily. I don’t know everything!

If you’re someone who’s in charge of deciding how data changes get captured at your organization, you should probably know these things. Or at least know enough to figure out what the right tool for the job is.

Type 2 SCDs don’t seem to be exclusive of also using change data capture & data vault, if that’s what you want. Which is great for me, because I love a type 2 SCD. Those sweet sweet effectivity dates just make so much intuitive sense.

And, philosophically, there’s just something viscerally right about row effectivity dates in a data ecosystem. I, too, have parts of me whose effectivity dates are expired, such as the elasticity in my knees. I also have parts with an “effective from” date of just a few days ago, such as my new “enjoys caving” dimension that just went into effect.

Father Kimball has given us a mandate. It is issued thusly:

Think about your data and how it might change over time. Make a plan for how you’re gonna capture that. If you plan to capture it at all.

See you next week. Like & subscribe if you would like to throw me an internet cookie.

Subscribe now

1

Can someone please cure me of my sensitivity to cliche. I would prefer to not be this way.

2

Does anyone still call them Jibbitz? Or maybe that’s just the branded name.

3

Just search Hilary McBride on this topic.

4

Maybe it’s because I just moved house, but I’m not convinced every piece of data is worth keeping.