Applying SCD (slow changing dimensions) in OLTP or 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.