Snapshots, is it SCD2 or SCD6?

### Contributions

- [x] I have read the contribution docs, and understand what's expected of me.

### Link to the page on docs.getdbt.com requiring updates

https://docs.getdbt.com/docs/build/snapshots#what-are-snapshots

### What part(s) of the page would you like to see updated?

## Context

We recently had an internal discourse and I thought it would be interesting to raise it as a proposal here.

The current `dbt snapshots` documentation [states](https://docs.getdbt.com/docs/build/snapshots):

> Snapshots implement [type-2 Slowly Changing Dimensions](https://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2:_add_new_row) over mutable source tables.

The thing I want to pitch is that it looks like the **SCD2 is actually not really supported**. And probably more precise is to call it SCD6. Or enhance the tooling.

Something tells me that majority of those who may read the statement above might think that I am completely wrong. Well, then I'd really appreciate the feedback. The rationale behind my motivation to raise this discussion is provided at the end.

## Why it doesn't look like SCD2

... if we follow an "original" definition of it.

The concept of SCD was introduced by Ralph Kimball as a set of principal techniques that are used in the Dimensional Modelling approach within DWH. It is not only about just change tracking a table, it also differentiates and guides how the other aspects of the data architecture in DWH are addressed.

One of such aspects is data consistency. SCD is assumed to be applied within the dimensional tables (which stems from the name) and each type also has a certain prescription on how to handle the FK references consistency from the fact tables.

Now if we even look into the mentioned Wikipedia article we will [see](https://en.wikipedia.org/wiki/Slowly_changing_dimension#Type_2:_add_new_row) that provided examples generate the `SupplierKey` as a unique identifier for each record.

| Supplier_Key | Supplier_Code | Supplier_Name | Supplier_State | Start_Date | End_Date |
|--------------|---------------|-----------------|----------------|---------------------|---------------------|
| 123 | ABC | Acme Supply Co | CA | 2000-01-01T00:00:00 | 2004-12-22T00:00:00 |
| 124 | ABC | Acme Supply Co | IL | 2004-12-22T00:00:00 | NULL |

What is not stressed there, but outlined in [Kimball's definition]([Type2](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-2/)) is

> ...a new primary surrogate key is assigned and __used as a foreign key__ in all fact tables from the moment of the update until a subsequent change creates a new dimension key and updated dimension row...

## And what is a SCD6?

While the surfaced definition states that it is a "combination of 1+2+3". But I find this definition shallow and impractical.

R. Kimball's [definition](https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/type-6/) gives a better hint

> Type 6 builds on the type 2 technique by also embedding current type 1 versions of the same attributes in the dimension row so that **fact rows can be filtered** or grouped **by either the type 2 attribute value in effect when the measurement occurred** or the attribute’s current value.

Ok, that's probably too academic language, but the **bold** part outlines the difference with type 2 - consistency is now not controlled by a surrogate key and fact table can reference records by a value "in effect when fact is measured" or "latest".

> [!NOTE]
> By type1 attribute meant any static part of the set of records, that is changed for all "entity-instance" related records at once. For example, that could be `current_sk` to quickly filter on `current_sk = sk` for active record. Type6 allows such attributes, but nothing forces to have it at all, so the example from the dbt snapshots docs (below) perfectly fits into type6.

| id | status | updated_at | dbt_valid_from | dbt_valid_to |
|----|---------|------------|----------------|--------------|
| 1 | pending | 2024-01-01 | 2024-01-01 | 2024-01-02 |
| 1 | shipped | 2024-01-02 | 2024-01-02 | null |

Basically the same idea outlined directly in the [Wikipedia article ](https://en.wikipedia.org/wiki/Slowly_changing_dimension#Pure_type_6_implementation).

And anyone's interested in a more illustrative comparison, I really like this article about [Temporal Modelling](https://www.researchgate.net/publication/330798405_Temporal_Dimensional_Modeling) (Lars Rönnback, Olle Regardt 2019). Also citing the distinction of SCD2 and SCD6 from it

> Trying to remedy some of the disadvantages of type 2, type 6 depicted in Figure 6 shares almost the same structure, but with a different primary key. The advantage of this type is that it is no longer needed for any part of the dimension to remain static, since the same `dim_ID` will remain across changes, thanks to `dim_ValidFrom` now being part of the primary key.

Figure 6:

## Summary SCD2 vs SCD6

Since technique of generating a change tracking inside a single table does not really drastically differ for type2 and type6 - it is a frequent case when SCD6 design is called SCD2, while (as it seems) not really being it.

My current uderstanding, is that the biggest difference is in what consistency controls this types of tracking support. In SCD2 it assumed we possess a surrogate key which we MUST use as an FK. With SCD6 - we can continue to use original table PK + range filtering.

## But dbt generates a surrogate key, right?

In the current implementation of `dbt snapshots` we have `dbt_scd_id` as a surrogate key. So the mechanism that does snapshotting provides the grounds to use both SCD2 or 6 approaches.

But...


this note `This is used internally by dbt` makes it unclear if I can really implement an SCD2. Basically - can we use it as an FK at all?

## Rationale behind raising the topic and suggested changes

Ok, that all might look as a nitpicking, but actually there's a rationale which motivated me to raise this.

Dbt is now (and for some time already) an industry leader when it comes to data engineering. Since I am interviewing candidates to DE positions in my company, I can say that more than 40% (and it's growing) of candidates highlight their experience with `dbt`. Many of them also stress that they completely and unquestionably take the `dbt` philosophy as a guiding principle.

In that situation the statements in dbt documentation become nearly axioms. Having said that - in a few years by leveraging the growing community dbt can manage the semantical meaning of any term used in the data industry. More and more frequently "but dbt suggests" becomes an argument. That is quite impressive 😄

The ideal solution to my case is to make the docs at least highlight that there's a difference between type2 and type6, not necessarily explain in what sense. It just might help engineers, data architects and analysts (analytics engineers) to study modelling principles, and not just master dbt configuration.

Even better would be to additionaly look into the option of making `dbt_scd_id` as a surrogate key that is assumed to be used for SCD2 referential integrity feature. At the moment I don't know if it's safe to use it for it.

> [!NOTE]
> I commit to suggest a change for the documentation site if we conclude that it is beneficial. Also I'd be happy to move this to discourse, if the GH issues is not a right place for this.

Thanks for reading, and I'd appreciate any kind of feedback.

### Additional information

_No response_