So i think i must be missing something or maybe even forgetten.
My dataflow:
- I have a Data source connection (dsc) ingesting csv-files from a S3 bucket with an aggregation pattern since the files have the date in the filename to make sure we dont overwrite any files. Each row in each file has a column with the date. So all rows in the file has the same date. The ingestion has a data type override to override all columns to nvarchar(max) since we cant trust the data source.
- The data is then transfered to a to a table in a data area as is.
- Next step is with a view for validation and a simple table insert move data to a table with data types we want to work with.
This last table is a History table. Not that we use any scd we simply want to update the data and use the scd-columns to be able to know first seen/last seen stuff.
Problem/question:
Since this last table is a history table, its vital that rows are inserted in the correct order (date order i wrote about in step 1) to not update rows in backwards.
One possible solution is that we only ever keep one file in the area where the dsc reads from
and move/delete files ingested.
I would rather not however since we will have a lot of these kinda dsc’s.
Any thoughts/solutions/funktions in timextender im overlooking?
