Forum Discussion
Helper IVDaily Incremental Refresh with Delta Capture and Reporting
Helper IVGood morning! I do have unique IDs in each of the table snapshots for every record. When you say use unique key and file date, are you referring to the actual file name "mm-dd-yyyy.csv"? I also add a 'snapshot_date' field to the CSVs in the data model for snapshot selections by the user. I.e., the user can pick their 'version of history' they want to look at.
The CSVs range in file size from 500KiB to 200MiB. The daily files would be 3MiB to 8MiB.
Do you have a method for actually identifying "changed", "new" or "deleted" records over time? I cannot seem to find a good tutorial.
Super UserThat seems like a manageable amount of data. I would just ingest the CSVs as is. If you have the file datetime inside the data then that is ok too, but the filename datetime is usually enough.
For the delta processing you can use the standard period over period patterns but also have a look at EXCEPT and INTERSECT.
- gemcityzach
Does it matter if my data does not have an audit date time field? Unfortuantely, it doesn't look like any of the target tables have a "lastUpdated" or "LastModified" field exposed to the report layer export. So the only thing I have are exact snapshots each day of the same fields with a file name of the previous day's export and an appended 'snapshot date' field into each record of the target snapshot.
- lbendlin
the file date is sufficient.
- gemcityzach
Thank you. I am still kind of lost. I've read the EXCEPT, INTERSECT and a bunch of tutorials about combining two tables. But my problem is that I don't have tables or distinct queries, I have one query with appended data already in it. I.e., my single query has 03-01-2024 through 03-31-2024 data all essentially stacked on top of itself with a 'snapshot date' field.
It's escaping me how on a rolling daily basis I compare today's records to yesterday's records to identify any records that have changed since I don't have an audit field (lastModified).