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

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:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

#sql #dataengineer

[macx]

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

[Nalaka-Wanniarachchi]

Great stuff.Must learn one by every data enthusiast.

[shirazhussain]

I really appreciate your content and have even recommended your videos to my friends — they’ve been incredibly helpful in my work.

If you have time, and if you think it’s an important topic, I would really love to see a video comparing MERGE statements vs SCD Type 2.

I thought both approaches solve the same problem(for update and insert point of view) but I’d be very interested to hear your thoughts

Please consider making a video on MERGE vs SCD Type 2 — I’d really like to understand your perspective.

[subhajitdey]

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

[ankitbhangdia]

@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.

[shanthiprabhu]

Great Ankit, thanks. I am completely new to this concept and its very useful

[review]

Nice presentation, Ankit
What if I have child dim table. Ex: dimProd dimSubProd and both are SCD type2. How do we reflect dimSubProduct when there is change in DimProduct ?
Facts we refresh as it is Append only.

[ashanair]

If there was a way to love your videos and not just like.. Learning a lot Ankit. Thanks

[deepakj]

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

[amiyarout]

I was wondering who is playing the violin in the background 😂😂

[apexemperor]

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

[veereshk]

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.

[chaitanyanagare]

In SCD1 when first insert is completed we emptied the stg table.. How can we do changed to update dim without empty the stg after first insert

[Sachin_Sambare]

you are too good..very very nice explanation

[pradeepyogesh]

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

[anikethdeshpande]

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

[typing]

@ankit Bhaiya, Instead of doing manual work by query, We can create insert update trigger also, that will be a good automation work.
What you say brother.☺

[aadil]

sir, can we implement scd-1 via merge statement. i mean to ask is merge statement is nothing but the scd-1 only?

[chinmayaMuduli-kfdd]

Hi Ankit sir will you start any data engineering course ?

[Ironsouljourney]

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