How to read Last Partitioned File from ADLS using ADF mapping Data Flow

David Tuñon 1

Hi All,

we have a requirement to read last ingested partitioned parquet files which are available in ADLS folders.

ADLS folder structure: XXX/processed/year=yyyy/month=MM/day=dd

Can any give me idea how to read last date partitioned files dynamically in ADF mapping data flow. I mean how to recover partitioned folders and select last in time.

Thank you!
Regards,
David

KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2021-12-04T00:51:22.457+00:00

Hi @David Tuñon ,

Thanks for using Microsoft Q&A forum and posting your query.

Could you please tell us how those parition files are being written? Is there any ADF/Synapse pipeline that generate those files or some other process? And also what is the frequency of those files being written - hourly/Daily/Weekly?
Few additional details would help us assist better on this requirement.

Thank you
David Tuñon 1 Reputation point

2021-12-04T15:58:46.037+00:00

Hi,

The partition files are in an Azure Datalake Gen1.
The ingest of data is daily.
The structure of partitions is by year, by month, by day.

Example of folders in my Datalake:
processed/year=2021/month=11/day=30
processed/year=2021/month=11/day=29
processed/year=2021/month=11/day=26
processed/year=2021/month=11/day=25

My problem is, for example if my pipeline executes on 28th of November, I want to find the last partition files folder (It should be “processed/year=2021/month=11/day=26”), but I don’t know which is the better solution in ADF or Dataflow ADF. I think I have to pass parameter to ADF Dataflow with this directory, but How can I find the last partition folder? To fill Wildcard paths field dinamically.

Best Regards,
David
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-01-18T19:49:19.177+00:00
Hi there,

One way to achieve this requirement is to by having a two-level pipeline using Get Metadata activity to retrieve and find out the most recent year/month/date and in the child, pipeline pass that latest date folder and in that particular date partitioned folder find the most recent file using another get metadata activity as described in the below discussions.

Please note that below implementation is just for reference, but you need to modify the implementation as per your requirement as mentioned above.

Latest file extraction (Azure Data factory)

Get Last Modified Date on Partitioned Data

ADF: copy last modified blob

But if you feel this as more complex approach, the other alternate could be writing an Azure function to find the last modified file in each sub folder and pass that out to your Dataflow as a parameter.
Here is a demo from community member on how to find last modified filed based on the file name but you may have to alter the code as per your requirement to find the last modified partitioned folders - Azure Data Factory - Copy the latest file with DATE PART to Folder using AZURE FUNCTIONS

Do let us know if you have further query.
KranthiPakala-MSFT 46,422 Reputation points Microsoft Employee

2022-01-20T22:31:17.47+00:00

Hello there,

Just checking in to see if the above suggestion was helpful. If you have any further query do let us know.
Wilf 1 Reputation point

2022-04-19T23:37:32.203+00:00

Hi David,

Did you ever come to a simple resolution to this?

I'm after the same requirement and this is no where near as simple as I had hoped.

My thoughts at the moment are leaning towards the creation of a trigger/metadata file in ADLS when the source file(s) are landed in ADLS - which contains the YYYY/MM/DD datepart file path in it - so the file will always contain the most recent file path. From there, the subsequent dataflow pipeline can then read the trigger file, fetch the datepart file path in it and then read from it.

For us the recommended solution by MSFT didn't fit the brief.