Updates from Multiple Delta Input Files
I get daily delta files (includes only records with changes) from a production system. I need to update a Redshift table with these deltas. I specifically need to update them in date order since a key might be in multiple files. If the updates aren't in order by date, then older information might overwrite newer information.
If everything runs fine (daily processing) then I should never have multiple files. For whatever reasons, I need to process multiple files. If I do a standard "Load to S3" and "Update/Insert" it appears that I have no way to ensure that the updates are applied in date order, whether by using a timestamp field in the data or the filename (the filenames are prefixed with "YYMMDD".)
What are my options? I'm going down the path of using a file iterator to run each file through individually. Is that my best option?