Forum Discussion
Daily Incremental Refresh with Delta Capture and Reporting
This is only an incremental refresh in terms of the file date. What you are doing in fact is storing a bunch of snapshots of (nearly) the same data.
You need to identify a column in your CSV files that can serve as unique transaction key (maybe your ID column), and then you can use that in combination with the file date to run your change tracking for inserts/updates/deletes.
Depending on how big the CSVs are and how many you keep for your comparison you may not even need the incremental refresh.
Good 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.
That 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.
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.
