 Hello, welcome to SSUniTek Sushilis side and today we are going to see about the slowly changing dimension type 1. So first we need to understand what is slowly changing dimension and then we need to discuss about the SCD type 1. So what was the next slide to understand about the SCD? So what is slowly changing dimension? So a slowly changing dimension is a dimension that stores and manage both current and historical data over time in the data warehouse. It is considered and implemented as one of the most critical ETL task in tracking the history of the dimension record. So what does it mean? So it is saying in the data warehouse we will be having the dimension tables. Those are rarely going to update. So on that we will be going to implement the slowly changing dimension like if it will be going to replace the existing value. Second it will be going to keep the historical data there. So that we will see how we can manage all those. So in the type 1 the new data overwrites the existing data. Thus the existing data is lost as not stored anywhere else. So let us assume we are having a source and that source is having the data for the item. So if we are going to load that item information into the destination so simply it will be inserted. By the next time once we will get the data then it is required to check whether the data is available in the destination table or not. If data is already there then there is no need to do anything. If data is not there then we will be inserting data. If any record is updated at the source then we will be updating those records into the destination. So this is the STD type 1. So let me go into the browser and we will try to understand the requirement there. So here we are if we can go in the blob storage under this input folder then we are having the item details.csv file. So it is having the information for the item id, variant id then the item name, variant name, warehouse, zone bin. So all these are the data available in the source file. So as we can see the combination of the item and variant is the unique combination. So like this is the composite primary key for this table. So we want to load this data into the SQL Server table and here as of now we don't have any data in this table. So once we can refresh this by pressing control R so we can verify here we don't have any data. So once we will be going to execute our package data will be loaded here. So how we can implement the SCD. So we can go into the Azure data factory and here let me try to add a new data flow. So this data flow we are going to create for the SCD type 1. So let me call this as SCD type 1. Now let me on the data debug option here. So first what we need to do we need to select the source. So as we have already created the source for the file of the item details under this blob storage. So we can click on this add source. Then here we need to find out the data set that we have created. So we can see the data sets. So this is the data set we have already created. Let me try to open this. So here we can verify like this is pointing to the input folder. Then the item details dot CSV file. So the same file which we have opened here. Let me close this. Here we can go into the data preview and under the data preview once we refresh then we can see it will be having the data. So here as we can see the data but the item ID and the variant ID both are coming as an string but in the DB side it is having the integer columns. So we have to update the schema of this so we can go in the projection. Then here we can change the schema from a string to integer for the item ID and for the variant ID both. So it is updated from a string to integer go to the data preview and try to refresh it again. So this time we will be able to see the data but the data type of those column will be integer as we can see here. So once we have done this then we have to use the alter row transformation. So we have already discussed about the alter row transformation in detail. If you haven't watched that video so I will provide the link of that video in the description of this video. So you can watch that video before going forward. So here what we need to do we want to use the absurd property of the alter row. So what absurd property will do it will be going to update if any changes are happening in the existing data and if data is not there then it will be inserting those data. So here we can see the alter row condition. So here we can see the insertive, updateive, deleteive and the absurdive. So we want to do the absurd of this and here the condition. So I am going to do this for all the rows so 1 equals to 1. So it is indicating for all the rows absurd will be happening. Now once we can go in the data preview and try to refresh it so we should be able to see all the data which is available in the source site. So here as we can see so all the property which is coming the first column value which is the absurd indicating. Now let me try to click on this plus symbol and here we just want to dump this into the destination. So once we selected the destination here as a sync so we need to select the data set. We have already created the data set for the scd type 1 so I am going to select this one. Let me open so I can verify this. So this is pointing to the same sequence over and the table which is the item details scd type 1 and this is the item details scd type 1. So this is the same table. Let me close this. Let me go into the settings. So under the setting we can see the update methods what method we are going to apply. So instead of only insert we are using the absurd. So once we have done this absurd then what it is asking it is asking the key value. So what is the key value here so the key value is the item id and item variant. So we need to select the item id the first column. Let me add another column so the combination is the unique which is the variant id. So combination of this will be checking if that is not there then it will insert if that is there then it will be updated. So after doing that let me go into the data preview it is showing some error. So let me click on this error. So it is saying key column item id does not exist in the mapping. So let me go into the mapping side and disable this and here we can see everything looks okay. Now go to the data preview and try to represent. So this time we should be able to see all the data here. So what it is doing we are fetching the data from the source and then by using alter row we are applying the absurd condition and in the sync we are going to apply the absurd in the settings and specify the key columns as the item id and variant id. So this is what we have done. So after doing that let me try to save this and let me add a new pipeline here and this pipeline let me use this scd type 1 and let me try to execute it. So once it will be executed we should be able to see all the records into the destination table. So as of now we don't have any data as we have already seen so all the data should be loaded from the source table to the sync table in the first execution. So here as we can see it got executed with success. Let me go into the SSMS and try to refresh by pressing ctrl r. So it will be going to refresh and we should be able to see all the data as we can see. Now what we want to do let me try to update this warehouse from warehouse 001 to 080 something like this for the id 1 and variant 1 and let me at the same time let me delete this 4 and 1 like the item id 4 and the variant id as 1. So that we can see is deleted. So what it will do in the next execution it will be updated the warehouse from 080 to 001 for the item id 1 and variant 1 and will be inserting 1 record for the item id 4 and variant id 1. Now go back to the azio data factory and try to debug it again. So it will be going to execute and data will be updated and inserted into the destination table. So we have to wait little bit on this. So it got executed with success. Let me go back to the SSMS and try to refresh by pressing ctrl r. So it will be going to have the 9 record again. So as we can see 9 records here. So 41 is inserted this and here the first row like the warehouse is updated from warehouse 82 warehouse 1. So this is what is scd type 1 in the next video we will see about the scd type 2. So in the scd type 2 we will be keeping the information of the historical data. So thank you so much for watching this video see you in the next video.