Applying SCD (slow changing dimensions) in OLTP or OLAP

[AmitDevkatte]

Hi , I have a doubt regarding where to apply the SCD type 1 and 2 in data engineering pipeline. which is the best option to implement it in OLTP or in OLAP?

[v-saisrao-msft · Accepted solution]

Hi @AmitDevkatte,

SCD Type 1 and Type 2 are best managed in the OLAP or data warehouse layer, rather than in OLTP systems. OLTP systems are designed to capture current transactional data, whereas OLAP systems handle historical analysis and dimensional modeling. Microsoft documentation states that SCD is a data warehousing technique applied to dimension tables through ETL tools. Therefore, OLTP systems provide the data changes, and SCD processes should be carried out in the OLAP layer.

Slowly changing dimension type 1 - Microsoft Fabric | Microsoft Learn

Slowly changing dimension type 2 - Microsoft Fabric | Microsoft Learn

Slowly Changing Dimension Transformation - SQL Server Integration Services (SSIS) | Microsoft Learn

Online Analytical Processing - Azure Architecture Center | Microsoft Learn

Thank you.

[AmitDevkatte]

Thanks for the detailed explaination. Also providing links for further reading is much appreciated.👍

[lbendlin]

A change is a transaction. 

What do you mean by "apply" - please elaborate.