This video shows how to implement SCD type 2 using Delta tables. This is similar to the method available in SQL. if you missed introduction video of deltabricks, please see that first • Delta lake features an...
Delta is the format used here though the file format is parquet. If you go inside the delta folder you will see the log files. Log files are similar to sql transaction logs
can we have a some logic where we do not have to use this mergekey as extra column, as I also want schema evolution along with scd2, but using this approach with schema evolution I am also getting the mergekey column in the output, which I don't want, so is there any way?
Hey, Try to run merge query again and again. It will insert the records into Dim Table.. Beacuse of joinkey considering as null always EmoloyeeId from target not matched with null and keep on inserting records
Aren't you updating ALL historical versions of given customer instead of just the current one? Let's say you get new address of customer A od day 1 and the next day another update of the same customer. In the latter case you should be updating the end date of only the version from day 1.
That is correct. We need to update only the latest one. We can include the date filter in where condition to pick only the active record. Thanks for pointing this out
@@KnowledgeSharingjkb Thank for the video, as pointed out by Piotr, we will be updating even the historical data each time address line changes. Can you please share with me where I can include a date filter in the where condition to pick only the active record. Here is my code: %sql MERGE INTO myspace a USING ( SELECT fullPath AS mergeKey,* FROM allstars UNION ALL SELECT NULL as mergeKey, a.* FROM allstars a JOIN myspace b ON a.fullPath = b. fullPath AND b.modifiedTime != a.modifiedTime )b ON a.fullPath = b.mergeKey WHEN MATCHED AND b.modifiedTime != a.modifiedTime THEN UPDATE SET a.endDate = current_date()-1 WHEN NOT MATCHED THEN INSERT ( fullPath ,filePath ,fileName ,simpleFileType ,complexFileType ,fileFizeKB ,modifiedTime ,startDate ,endDate ) VALUES ( fullPath ,filePath ,fileName ,simpleFileType ,complexFileType ,fileFizeKB ,modifiedTime ,current_date() ,'9999-12-31' ) Thanks
if there is no change in source data and we try to run the merge code again as part of daily run then the mergeKey null records will be inserted again into target column as active and we will be ending with duplicates , how to solve it ?
