SCD Type 1 and Type 2 using SQL | Implementation of Slowly Changing Dimensions

Рет қаралды 10,825

3 ай бұрын

In this video we will learn how to implement scd type 1 and type 2 using SQL. This will help you build logics using update and insert statement.
script:
CREATE TABLE product_stg(
Product_id INT,
Product_Name VARCHAR(50),
Price DECIMAL(9,2)
);
CREATE TABLE product_dim(
Product_id INT primary key,
Product_Name VARCHAR(50),
Price DECIMAL(9,2),
last_update date
);
create TABLE product_dim(
product_key int identity(1,1) primary key,
Product_id INT,
Product_Name VARCHAR(50),
Price DECIMAL(9,2),
start_date date,
end_date date
);
Zero to hero(Advance) SQL Aggregation:
• All About SQL Aggregat...
Most Asked Join Based Interview Question:
• Most Asked SQL JOIN ba...
Solving 4 Trick SQL problems:
• Solving 4 Tricky SQL P...
Data Analyst Spotify Case Study:
• Data Analyst Spotify C...
Top 10 SQL interview Questions:
• Top 10 SQL interview Q...
Interview Question based on FULL OUTER JOIN:
• SQL Interview Question...
Playlist to master SQL :
• Complex SQL Questions ...
Rank, Dense_Rank and Row_Number:
• RANK, DENSE_RANK, ROW_...
#sql #dataengineer

Пікірлер: 38

@macx8360 3 ай бұрын

you wont believe ,i was just learning the same concept from your python course today itself in the morning

@Nalaka-Wanniarachchi 3 ай бұрын

Great stuff.Must learn one by every data enthusiast.

@RoamingSoming 3 ай бұрын

Great way of explaining SCD types

@meropahad7537 2 ай бұрын

Thanks for the video Ankit

@subhajitdey6806 3 ай бұрын

great video ! need more data modelling and data engineering videos man !

@uditkapadia7104 Ай бұрын

Great Explanation !

@sravankumar1767 3 ай бұрын

Superb explanation 👌 👏 👍

@ankitbhangdia3931 Ай бұрын

@ankit bansal: Great job on explaining the concept. qq: Instead of making the end date as forever, will it make sense to keep it as NULL & include another column such as is_current_value which would be a boolean field. When someone wants to track the history in the report, an analyst can simply put the condition for start_date, end_date IS NULL and is_current_value = 'n' to take a look at the previous record or they could query on the start_date, end_date IS NOT NULL and is_current_value ='y'. You could even use an OR operator in the query with the structure I'm proposing. Using forever as the end_date is frowned upon in the data warehousing world IMHO.

@gazart8557 3 ай бұрын

Yaay just yesterday only I learned this thanks

@srinubathina7191 3 ай бұрын

Thank you Ankit Bro

@nehashahpatel1741 2 ай бұрын

Million Thanks

@deepakj6717 3 ай бұрын

Best video.. Thanks !! If possible pls make videos on SQL performance tuning or launch course.

@vamsivamsi2029 3 ай бұрын

Thanks @ankit

@apexemperor 3 ай бұрын

Needed this video but 6months ago... Bt we did it together in office with a friend that time 😀😺 using sql

@pradeepyogesh4481 3 ай бұрын

Very good information and thanks for the content. How to create staging tables in the first place?

@milindzuge906 3 ай бұрын

Thank you so much Ankit ❤😊

@ankitbansal6 3 ай бұрын

My pleasure 😊

@vijiinfo 15 күн бұрын

❤❤❤

@Aditya61515 2 ай бұрын

my question is if we connect the data in power bi desktop so we need to manually do this scd 2 or it will automatically updated

@VaanisToonWorld-rp5xy 22 күн бұрын

you have written ELT as extract tranform and load. It's extract load and tranform

@chinmayaMuduli-kf3dd 3 ай бұрын

Hi Ankit sir will you start any data engineering course ?

@anikethdeshpande8336 Ай бұрын

Hi Ankit, great explanation, how to handle scenario in scd2 type two, when there are insert, update and delete all together in staging for the same record. Assuming we are using cdc to keep track of changes and using cdc info to update the dim tables

@sandeepguptha6440 25 күн бұрын

then you need to create one more temp_table while running script by keeping where timestamp in stg_table > max(timestamp) in dim_table to get the only changed records to temp_stg table now data is in temp_table (which has only latest records) dim_table has old records as of now (we did not performed any transformations yet) now follow anikt procedure to keep history track

@HumansofChn 3 ай бұрын

Can't we implement it using Merge statemnet

@veereshk6065 3 ай бұрын

Thank you for creating such quality content. I have a question, is it possible to implement such SCD2 using merge ? (where update and insert are involved to maintain history, same example as described in video). Thanks in advance.

@ankitbansal6 3 ай бұрын

It can be done but merge operation can have performance issues.

@007SAMRATROY 3 ай бұрын

Can't we use merge to perform the SCD2 implementation?

@ankitbansal6 3 ай бұрын

Performance is not good with merge.

@nupoornawathey100 3 ай бұрын

for mysql query is slightly changed: set @updated_date='2024-01-20'; UPDATE product_type1_dim a, product_stg b SET a.price = b.price, a.last_update = @updated_date WHERE a.product_id = b.product_id ;