Thank you for your video. Nice explanation. I found below gap in the solution. In SCD Type-2, at any point of time, Source record might stop coming due to various reasons. At that time, the corresponding record in the target should be kept the status as "deleted" or "not active". So, if we do "left outer join" we won't be able to identify which record in target need to be in "deleted" status. it should be full outer join and need to alter code accordingly to make the target reflect the correct data.
i checked so many documents, so many articles. None of them explained the concept, and somehow copied the same what databricks told. This is the only video which explains it well. and Step 5 is the most important and the merge_key there is the trick
Hi Raja, Nice explanation. Not only this topic, you covered each and every topic in very much in detail. could you please share notebook for the above one.
In step 2 (Databricks cell.no 7) While Executing filterDF , you are concatenating all columns of both source and target and checking for the equivalent string to filter . But in this, columns having different values in both source and target can still can give equivalent string and that data should be updated in final table but it will be filtered out by the given condition. Example : dim1=100,dim2=201,dim=300,dim=400 Target_dim1=100,Target_dim2=20,Target_dim=1300,Target_dim=400 Concat value will be 100201300400 . So in this case it will be filtered out without updating
Hi Raja , instead of joins ,Can we write 2 merge conditions.. in sql we write stored procedures, it just insert the all the rows and when matches then update
Hi, could you recommend me a book on SCD using pyspark? I would like to delve deeper. I've done projects based on the Azure documentation and with this presentation made by you, I was very excited. Thanks for sharing your knowledge. I speak of Brazil and I already admire you. Thanks.
Thanks Welder for your comments! There is no specific book or documentation for scd but databricks has created few examples for merge operation with Pyspark and that can be leveraged
do we need to create separate Database in delta lake for dimensions tables. Usually we create database name "RDS" and put all the reporting tables such as dimenation and fact tables as dim_product,dim_customer,dim_date and Sales_Fact. is there any standard we can follow?
@@rajasdataengineering7585 is there any video where you have demonstrated such concepts. Stagging area, model and reporting. I saw the earlier videos where you have told about silver, bronze and gold. Do we create similar folders in Data lake.
Hai when we are running multiple times by updating the same record the history data is growing when we run 4 times with update we are getting 3 inactive and 1 active record ..can you help out this as in other process history is not growing ...
Hi Sujith, Keeping all historical records is business requirement in most of the cases. But if you don't want to keep many historical records, SCD type 1 or type 3 should be helpful
Hi sir , I am using simple SQL merge query in databricks... MERGE INTO ...TARGET TABLE... USING ...SOURCE TABLE... ON ....MERGE CONDITION... WHEN MATCHED THEN UPDATE SET ....MATCH CONDITION.... WHEN NOT MATCHED THEN INSERT () VALUES() but this is working fine for first time, wheni ran same condition multiple times.. it is giving ambiguity error
In step4 we are filtering the record where targetpk is not null...and then we are making mergekey column with null value.. There is a mistake while explaining.
Hi sir thank you this amazing videos. I am doing same thing. For me I need to track deletion of record as well. Where I can change in current notebook so that I will able to track deletion of records
HI i am getting this error while doing Merge - com.databricks.sql.transaction.tahoe.DeltaUnsupportedOperationException: Cannot perform Merge as multiple source rows matched and attempted to modify the same, please suggest
Hi sir, I am doing merge Operation which is working for the first time... When ran that merge statement multiple times it is giving ambiguity error.... Can you please suggest solution for this
Hi Mr.Raja i found your tutorial videos very interesting. Would you be able to share the dbc files for the tutorial...it will be helpful to refresh my memory and also prep for any future interviews...i will be happy to subscribe to a paid online course as well if any for the dbc files...
When I tried the same , I'm getting the below error. What could be the reason?. I have a row with updated values. This should eventually make flag as N and insert the updated value as new row. But this is failing in merge logic. com.databricks.sql.transaction.tahoe.DeltaUnsupportedOperationException: Cannot perform Merge as multiple source rows matched and attempted to modify the same
Hi Sir, SInce you mentioned as SCD type 2, if another new record for combination(222, unit 2) comes from source, then how are you deleting the current inactive record and make the current active record as in active. As SCD type 2 should maintain only one history record?
Wonderful 🙌
I have got a similar use case at work
Will be using this approach
Thanks!
Glad it was helpful!
Thank you for your video. Nice explanation. I found below gap in the solution.
In SCD Type-2, at any point of time, Source record might stop coming due to various reasons. At that time, the corresponding record in the target should be kept the status as "deleted" or "not active". So, if we do "left outer join" we won't be able to identify which record in target need to be in "deleted" status. it should be full outer join and need to alter code accordingly to make the target reflect the correct data.
Thanks for your comment.
Yes this solution doesn't cover delete feature. It is mainly upsert in this example
Well Explained Raja ! Appreciate your hard work Bhai !!!!
It's my pleasure! Thank you Ranjan
i checked so many documents, so many articles. None of them explained the concept, and somehow copied the same what databricks told.
This is the only video which explains it well. and Step 5 is the most important and the merge_key there is the trick
Thank you for your comment!
Nice explanation 👌
Thanks!
Hi Raja,
Nice explanation. Not only this topic, you covered each and every topic in very much in detail.
could you please share notebook for the above one.
This is Epic, Sir... This is very Ultimate... Thank you, Sir...
Thank you
In step 2 (Databricks cell.no 7) While Executing filterDF , you are concatenating all columns of both source and target and checking for the equivalent string to filter . But in this, columns having different values in both source and target can still can give equivalent string and that data should be updated in final table but it will be filtered out by the given condition.
Example :
dim1=100,dim2=201,dim=300,dim=400
Target_dim1=100,Target_dim2=20,Target_dim=1300,Target_dim=400
Concat value will be 100201300400 . So in this case it will be filtered out without updating
Hi Raja , instead of joins ,Can we write 2 merge conditions.. in sql we write stored procedures, it just insert the all the rows and
when matches then update
Hii ,when using merge key .. so this works only for table having two primary keys or can we apply this to table with one primary key as merge key ?
Hi, we can do for single primary key also
@@rajasdataengineering7585 Thank you so much for your prompt reply .
Welcome
Hi, could you recommend me a book on SCD using pyspark? I would like to delve deeper. I've done projects based on the Azure documentation and with this presentation made by you, I was very excited.
Thanks for sharing your knowledge. I speak of Brazil and I already admire you.
Thanks.
Thanks Welder for your comments!
There is no specific book or documentation for scd but databricks has created few examples for merge operation with Pyspark and that can be leveraged
@@rajasdataengineering7585 how to insert multiple conditions in merge? Current format is inserting newlines even with no changes from current records.
@@oiwelder I would suggest you to sort that out first using pyspark df and then do the merge operations after it
Great explaination.. Thank you..
Could you also make video on SCD type 1
Thanks Peter.
Have already made a video on SCD type 1. It is added in delta lake play list
@@rajasdataengineering7585 ok thank you
Hi, Do we have a Type 3 SCD video ? "Introduce new columns for updated values."
Hi, no not yet created video on scd type 3
do we need to create separate Database in delta lake for dimensions tables. Usually we create database name "RDS" and put all the reporting tables such as dimenation and fact tables as dim_product,dim_customer,dim_date and Sales_Fact. is there any standard we can follow?
We can keep fact and dimension tables in same database
@@rajasdataengineering7585 is there any video where you have demonstrated such concepts. Stagging area, model and reporting. I saw the earlier videos where you have told about silver, bronze and gold. Do we create similar folders in Data lake.
Yes that is the solution architecture of delta databases
Hai
when we are running multiple times by updating the same record the history data is growing
when we run 4 times with update we are getting 3 inactive and 1 active record ..can you help out this as in other process history is not growing ...
Hi Sujith,
Keeping all historical records is business requirement in most of the cases. But if you don't want to keep many historical records, SCD type 1 or type 3 should be helpful
Thanks for that reply
Hi Raja, Great tutorial! Can you add a video on Change Data Capture (CDC) for deletes?
Hi Purnima, thank you. Sure, will post a video for CDC
Superb sir 👌 can I access to this notebook
Thank you
Hi sir , I am using simple SQL merge query in databricks... MERGE INTO ...TARGET TABLE... USING ...SOURCE TABLE...
ON ....MERGE CONDITION...
WHEN MATCHED THEN UPDATE SET
....MATCH CONDITION....
WHEN NOT MATCHED THEN
INSERT ()
VALUES()
but this is working fine for first time, wheni ran same condition multiple times.. it is giving ambiguity error
Please suggest solution for this issue
@@niharikakota6027 One reason could be multiple matches on the condition, try narrowing the condition to single match and check
In step4 we are filtering the record where targetpk is not null...and then we are making mergekey column with null value.. There is a mistake while explaining.
Apologies if I made any mistake while explaining. Hope you got the concept
@@rajasdataengineering7585 yes I got the concept thanks for explaining nicely.
Hi Raja,
is it possible to share this notebook please?
What about delete?
Hi sir thank you this amazing videos.
I am doing same thing.
For me I need to track deletion of record as well. Where I can change in current notebook so that I will able to track deletion of records
Hi Sir....
As you mentioned this type SCD 2 will. Create duplicate primary keys... What is the solution for this problem, Sir...? Thank you, Sir...
Use an "updated_at" field as a timestamp
can you give documentation link of slowly changing dimension
HI i am getting this error while doing Merge - com.databricks.sql.transaction.tahoe.DeltaUnsupportedOperationException: Cannot perform Merge as multiple source rows matched and attempted to modify the same, please suggest
Hi sir, Can you do a Video for Delta Lake SCD Type 4 also.
Sure Tamil, will make a video on this requirement
@@rajasdataengineering7585 thanks for your reply sir.
Hi sir, I am doing merge Operation which is working for the first time... When ran that merge statement multiple times it is giving ambiguity error.... Can you please suggest solution for this
Hi Niharika .. even am facing this issue for the first few runs it works fine then it shows duplicate records .. how did u resolve that
thanks !
Welcome!
🙏🏻🙏🏻🙏🏻🙏🏻
Keep watching
can we pls have the code file please or the Ipython Notebook if possible ??
Hi Mr.Raja i found your tutorial videos very interesting. Would you be able to share the dbc files for the tutorial...it will be helpful to refresh my memory and also prep for any future interviews...i will be happy to subscribe to a paid online course as well if any for the dbc files...
When I tried the same , I'm getting the below error. What could be the reason?. I have a row with updated values. This should eventually make flag as N and insert the updated value as new row. But this is failing in merge logic.
com.databricks.sql.transaction.tahoe.DeltaUnsupportedOperationException: Cannot perform Merge as multiple source rows matched and attempted to modify the same
Hi Sir,
SInce you mentioned as SCD type 2, if another new record for combination(222, unit 2) comes from source, then how are you deleting the current inactive record and make the current active record as in active. As SCD type 2 should maintain only one history record?
Is it really one version of history should be maintained in scd2 ?
seems to be very lengthy process sir.
Yes that's right. SCD type 2 is always quite lengthy
Can you please share the excel