File-based snapshot table best practice
I have a need for an Incorta table which will be populated weekly via file upload. The measures are not additive over time.
The furthest upstream we can get to the data is pulling a query result from a website into Excel/CSV
I think we have three alternatives to approach sourcing this data:
1) Upload a file w/ a standard filename ( <SourceDataFileName> ) and keep the old files archived somewhere w/ a date suffix ( <SourceDataFileName_<WeekBeginDate> )
If we do that I think we'd need to set the table load to incremental based on the week begin date even though there'd be no other data in the source file?
Upside: Pretty easy, if cumbersome.
Downside: No way to easily correct history.
2) Upload the file w/ date suffix, create the table, and add sources to the table weekly
Downside: Nope. This can't be it ;-) We'd end up w/ 52 sources at the end of a year. Consider this me muttering to myself!
3) Import the data files into a DB table and source Incorta from there.
Upside: Can easily correct past weeks by deleting records and reloading for specific week begin dates only
Downside: Another moving piece outside of Incorta
Am I missing a best practice approach to this? Ideas and/or feedback appreciated.
Incorta supports the data files placed in a folder on the disk. You can create a subfolder under the regular <Incorta Tenant folder>/data folder and when you specify the file location for creating a table, enable the feature of Union Files and enable Incremental.
What Incorta will do during the scheduled incremental refresh is whenever you have a new file in the folder (based on the timestamp of the file), only the new file(s) will be loaded.
The file name should be unique and it is recommended to include the timestamp as part of the file name.
Incorta also provide an option to add the file name as a column in the table. You will be able to know the as of date/time for this snapshot table.