Azure Data Factory: I'm trying to understand how to get MetaData information from CSV Files used in a Data Flow to get only the latest file created?

Question

I am using the Filter by last modified criteria in the Copy Data task with Azure Data Factory to determine the latest data file (one file created per day). The challenge I'm facing is that the file in Azure are calculated in UTC time but the files created at in EST (Eastern Time). The process works as long as the latest file is uploaded to Azure at a consistent time each day.

What I would prefer to be able to read the Azure folder and order the files by date descending order and just import the latest file that way. I haven't been able to figure out how to accomplish this preferred method. Just curious if anyone has determined a way to do that? Any advice would be greatly appreciated.

Answer 1

Hello Marty Scherr,

Thank you for posting your question in the Microsoft Q&A forum.

To address your challenge of importing the latest file from an Azure folder based on the file's date in Eastern Time (EST), you can use Azure Data Factory (ADF) to list files in the folder, sort them by their last modified timestamp, and then process the latest file. While ADF does not natively support sorting files by last modified timestamp in a descending order, you can achieve this by combining ADF with Azure Functions or a custom logic in a pipeline.

By combining the Get Metadata activity with Azure Functions or custom pipeline logic, you can sort files by their last modified timestamp and process the latest file. This approach addresses the time zone difference issue and ensures that the correct file is processed regardless of the upload time. If you prefer a serverless solution, Azure Functions is the recommended approach. Otherwise, you can implement custom logic within the pipeline using ADF activities.

Another way, if you prefer not to use Azure Functions, implement custom logic within the pipeline using ADF's ForEach and Set Variable activities. Initialize a variable to store the latest file name, iterate through the file list, and update the variable if a newer file is found.

You may refer below related documentation:

https://learn.microsoft.com/en-us/azure/data-factory/control-flow-get-metadata-activity

https://learn.microsoft.com/en-us/azure/data-factory/control-flow-for-each-activity

• https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-overview

If the above answer helped, please do not forget to "Accept Answer" as this may help other community members to refer the info if facing a similar issue. Your contribution to the Microsoft Q&A community is highly appreciated.

Answer 2

I figured out a solution using the Filter by Date criteria of the Source Dataset. What I hadn't accounted for was the correct calculation of from Eastern Time to UTC Time. I adjusted the trigger executing my pipeline to a UTC time that would accommodate the time when the files get uploaded to Azure.

Having said I did research the other methods suggested in the Answer provided, but I came to the conclusion that I get needed to get the date criteria correct knowing that the files are uploaded daily at the same time.