Forum Discussion
Applying SCD (slow changing dimensions) in OLTP or OLAP
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?
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.
3 Replies
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.
Thanks for the detailed explaination. Also providing links for further reading is much appreciated.👍
A change is a transaction.
What do you mean by "apply" - please elaborate.
