DLT - deduplication pattern?
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
03-06-2023 08:56 AM
Say we have an incremental append happening using autoloader, where filename is being added to the dataframe and that's all. If we want to de-duplicate this data in a rolling window, we can do something like
merge into logs
using dedupedLogs
on logs.id = dedupedLogs.id and logs.date > current_date() - interval 7 days
when matched and dedupedLogs.date > current_date() - interval 7 days then update set *
when not matched and dedupedLogs.date > current_date() - interval 7 days then insert *where dedupedLogs is a subset of the incremental append (7 days) with an ordered hash key where rownum = 1.
Can this be done in delta live table? I've attempted using apply_changes() but keep getting an error that an upstream has changed. DLT's new algorithm doesn't seem to pick up that the second table, the de-duped one, should be an incremental table yet.
Labels:
- Labels:
-
Current Date
-
DLT
-
Pattern