How to process multiple delta files using dataflow and we have to apply filter for date column to pick the latest records without importing columns

Question

How to process multiple delta files using dataflow and we have to apply filter for date column to pick the latest records without importing columns

SaiSekhar, MahasivaRavi (Philadelphia) 140

Hi Team,

Could you please let us know, There is an issue we are facing i.e we have to pass date value as filter condition in dataflow, without importing columns in data flow, referencedate is column name and date value will be like yyyy-MM-dd, we have to pass this value as parameter in dataflow to filter records (without importing the columns ). Could you please help us on this.

Thanks in Advance
Thank you.

Venkat Reddy Navari 2,885 Reputation points Microsoft External Staff Moderator

2025-04-07T14:08:44.9266667+00:00
Hi @SaiSekhar, MahasivaRavi (Philadelphia)

To process Delta Lake files dynamically (without importing schema) and filter records based on a date column (e.g., referencedate), using a parameter for the date value.

Create a Parameter in the Data Flow: Go to the Parameters tab and add a new string parameter, e.g., p_date_filter.

Set Up the Source Transformation: Use your Delta file path as the source. Do not import the schema. Enable "Allow schema drift" and optionally "Infer drifted column types" if needed.

Apply a Filter Transformation: Add a Filter transformation after your source. Use the byName() function to access the referencedate column without importing it:
toString(byName('referencedate')) == p_date_filter
If referencedate is a date type, use:
toDate(byName('referencedate')) == toDate(p_date_filter)

Pass the Parameter from the Pipeline: When invoking the Data Flow from your pipeline, pass the parameter like this:

{ "p_date_filter": "2025-04-07" }

For more details, please refer to the Microsoft documentation:
Parameterizing mapping data flows - Azure Data Factory & Azure Synapse | Microsoft Learn

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issue.
Venkat Reddy Navari 2,885 Reputation points Microsoft External Staff Moderator

2025-04-08T10:13:35.7033333+00:00

Hi @SaiSekhar, MahasivaRavi (Philadelphia) We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-08T16:26:09.1433333+00:00

Hi @Venkat Reddy Navari,

Thanks for the update,

But we are facing issue in foreach activity, in foreach we have around 89 tables few tables were having million of records, how to handle the below error. Could you please help us with the solution.
we are using the below appache spark pool - Medium (8 vCores / 64 GB) - 3 to 20 nodes
Error:
There are not enough resources available in the workspace, details: 'Your job requested 8 vcores. However, the workspace only has 4 vcores available out of quota of 60 vcores for node size family [MemoryOptimized]. Try ending the running job(s), reducing the numbers of vcores requested or increasing your vcore quota.

Thanks In Advance

Thanks
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-09T11:50:07.4966667+00:00

Hi @Venkat Reddy Navari,
Could you please help us on the above ask.

Thanks
Ravi Mahasiva
Anonymous

2025-04-09T12:43:43.6833333+00:00
Hello @SaiSekhar, MahasivaRavi (Philadelphia),

I understand your concern regarding the above error. First Can you please confirm whether the above approach for the primary ask about the Date column filtering is working or not?

And for the above error, first cross-check the below steps to troubleshoot the issue:

You have mentioned that you are using a For-each activity to do the task for the 89 tables. Here, there is a chance that the For loop is running in a parallel and that might be reason for the above excessive cores error. To resolve this, give a certain number of batches for the parallel execution of the For-each. By giving the batch number, it will limit the number of executions of the dataflow simultaneously so that it will execute as per the given cores. If still facing the issue, try with a sequential run of the for-each activity. This will reduce the load on the Runtime completely, but the pipeline run will take more time to complete its run.

Also, try to opt for the Memory-optimized Compute type in the dataflow activity integration run time.

If you're frequently working with heavy loads, go to Azure Portal → Synapse Workspace → Usage + quotas. Find your MemoryOptimized quota. Request increase.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issue.
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-09T14:41:43.9533333+00:00

Hi @Anonymous

Yes The above solution is working thank you help for helping us
Anonymous

2025-04-09T14:59:17.85+00:00

Hi @SaiSekhar, MahasivaRavi (Philadelphia),

Can you please also confirm whether any of the above troubleshootings steps worked or not? if not please let us know the more details about your pipeline configurations so that we can try to help you.
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-09T15:02:42.5966667+00:00

Hi @Venkat Reddy Navari,

The date filter dynamically passing is working and working on the for loop to run parallely.

Thanks

Ravi Mahasiva

Accepted answer

0 additional answers

Your answer

Venkat Reddy Navari 2,885 Reputation points Microsoft External Staff Moderator

2025-04-07T14:08:44.9266667+00:00

Hi @SaiSekhar, MahasivaRavi (Philadelphia)

To process Delta Lake files dynamically (without importing schema) and filter records based on a date column (e.g., referencedate), using a parameter for the date value.

Create a Parameter in the Data Flow: Go to the Parameters tab and add a new string parameter, e.g., p_date_filter.

Set Up the Source Transformation: Use your Delta file path as the source. Do not import the schema. Enable "Allow schema drift" and optionally "Infer drifted column types" if needed.

Apply a Filter Transformation: Add a Filter transformation after your source. Use the byName() function to access the referencedate column without importing it:
toString(byName('referencedate')) == p_date_filter
If referencedate is a date type, use:
toDate(byName('referencedate')) == toDate(p_date_filter)

Pass the Parameter from the Pipeline: When invoking the Data Flow from your pipeline, pass the parameter like this:

{ "p_date_filter": "2025-04-07" }

For more details, please refer to the Microsoft documentation:
Parameterizing mapping data flows - Azure Data Factory & Azure Synapse | Microsoft Learn

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issue.
Venkat Reddy Navari 2,885 Reputation points Microsoft External Staff Moderator

2025-04-08T10:13:35.7033333+00:00

Hi @SaiSekhar, MahasivaRavi (Philadelphia) We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-08T16:26:09.1433333+00:00

Hi @Venkat Reddy Navari,

Thanks for the update,

But we are facing issue in foreach activity, in foreach we have around 89 tables few tables were having million of records, how to handle the below error. Could you please help us with the solution.
we are using the below appache spark pool - Medium (8 vCores / 64 GB) - 3 to 20 nodes
Error:
There are not enough resources available in the workspace, details: 'Your job requested 8 vcores. However, the workspace only has 4 vcores available out of quota of 60 vcores for node size family [MemoryOptimized]. Try ending the running job(s), reducing the numbers of vcores requested or increasing your vcore quota.

Thanks In Advance

Thanks
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-09T11:50:07.4966667+00:00

Hi @Venkat Reddy Navari,
Could you please help us on the above ask.

Thanks
Ravi Mahasiva
Anonymous

2025-04-09T12:43:43.6833333+00:00

Hello @SaiSekhar, MahasivaRavi (Philadelphia),

I understand your concern regarding the above error. First Can you please confirm whether the above approach for the primary ask about the Date column filtering is working or not?

And for the above error, first cross-check the below steps to troubleshoot the issue:

You have mentioned that you are using a For-each activity to do the task for the 89 tables. Here, there is a chance that the For loop is running in a parallel and that might be reason for the above excessive cores error. To resolve this, give a certain number of batches for the parallel execution of the For-each. By giving the batch number, it will limit the number of executions of the dataflow simultaneously so that it will execute as per the given cores. If still facing the issue, try with a sequential run of the for-each activity. This will reduce the load on the Runtime completely, but the pipeline run will take more time to complete its run.

Also, try to opt for the Memory-optimized Compute type in the dataflow activity integration run time.

If you're frequently working with heavy loads, go to Azure Portal → Synapse Workspace → Usage + quotas. Find your MemoryOptimized quota. Request increase.

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issue.
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-09T14:41:43.9533333+00:00

Hi @Anonymous

Yes The above solution is working thank you help for helping us
Anonymous

2025-04-09T14:59:17.85+00:00

Hi @SaiSekhar, MahasivaRavi (Philadelphia),

Can you please also confirm whether any of the above troubleshootings steps worked or not? if not please let us know the more details about your pipeline configurations so that we can try to help you.
SaiSekhar, MahasivaRavi (Philadelphia) 140 Reputation points

2025-04-09T15:02:42.5966667+00:00

Hi @Venkat Reddy Navari,

The date filter dynamically passing is working and working on the for loop to run parallely.

Thanks

Ravi Mahasiva

Answer 1

Hello @SaiSekhar, MahasivaRavi (Philadelphia), Glad the above steps worked for you. Posting it as answer for the benefit of the community who faces similar issues in future.

First make sure you have not imported any source columns and enabled the Schema drift in the source of the dataflow.
To filter the date type column, create a string type parameter in the dataflow without any default value.
Now, in the dataflow, take a filter transformation with below expression.
```
	toString(byName('mydate'))==$dateparam
```
Here, byName() will give the column values if exists and if not, it will give the null values. The above expression will check required condition using the parameter.
You can pass the parameter value to the dataflow activity inside For-each activity.

Refer this MS documentation to know about the parameterization in the dataflows.

By this way you can pass date value as filter condition in dataflow, without importing columns in data flow.

Coming to the error that you have asked in the comments, please cross-check the below steps to troubleshoot the issue:

You have mentioned that you are using a For-each activity to do the task for the 89 tables. Here, there is a chance that the For loop is running in a parallel and that might be reason for the above excessive cores error. To resolve this, give a certain number of batches for the parallel execution of the For-each. By giving the batch number, it will limit the number of executions of the dataflow simultaneously so that it will execute as per the given cores. If still facing the issue, try with a sequential run of the for-each activity. This will reduce the load on the Runtime completely, but the pipeline run will take more time to complete its run.
Also, try to opt for the Memory-optimized Compute type in the dataflow activity integration run time.
If you're frequently working with heavy loads, go to Azure Portal → Synapse Workspace → Usage + quotas. Find your MemoryOptimized quota. Request increase.

Hope this helps.

If the answer is helpful, please click Accept Answer and kindly upvote it. If you have any further questions about this answer, please click Comment.

Anonymous

2025-04-15T04:41:08.01+00:00

Hi @SaiSekhar, MahasivaRavi (Philadelphia), just checking in to see if any of the provided suggestions helped. And, if you have any further queries do let us know. If the above solution helps, please do not forget to click "Accept the answer” and Yes, this can be beneficial to other community members.

Share via

How to process multiple delta files using dataflow and we have to apply filter for date column to pick the latest records without importing columns

0 additional answers

Your answer