Azure Data Factory using data flow with delta format

Raoul Mahadeen 1

I'm currently trying to load data from Azure data lake v2 in delta format to a azure sql data warehouse. I have it loading but I need to figure out how to filter the source so I'm not grabbing all the data from the source (azure data lake v2) I want to be able to say something like " Give me all the data that has CreatedDate (column) between getdate()_1 and getdate()
I tried this
between($DateCreated, toDate("2021-11-16 00:00:00"), toDate("2021-11-16 01:00:00"))

or
between($DateCreated, addDays(currentTimestamp(),-1), currentTimestamp())

but get this error

DF-EXP-061 at Source 'source1'(Line 2/Col 27): Column operands are not allowed in literal expressions - RunId: e7794b09-634d-47d0-8a3a-777a9939d096

ShaikMaheer-MSFT 37,816 Reputation points Microsoft Employee

2021-11-26T09:11:44.56+00:00

Hi @Raoul Mahadeen ,

Just checking in to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.
ShaikMaheer-MSFT 37,816 Reputation points Microsoft Employee

2021-11-29T13:38:23.913+00:00

Hi @Raoul Mahadeen ,

Following to see if the below answer helped. If this answers your query, do click and upvote for the same. And, if you have any further query do let us know.

1 answer

ShaikMaheer-MSFT 37,816 Reputation points Microsoft Employee

2021-11-24T16:56:10.757+00:00

Hi @Raoul Mahadeen ,

Thank you for posting query in Microsoft Q&A Platform.

You can use Filter transformation and provide condition there. You can use condition with > && < as shown in below image.

In below image "doj" is my column name

Hope this will help. Please let us know if any further queries.

----------------------------------

Please consider hitting Accept Answer button if this helps. Accepted answers helps community as well.
Please sign in to rate this answer.
Raoul Mahadeen 1 Reputation point

2021-11-29T13:42:18.633+00:00

thanks for the tip but does this cause retrieval of all the rows before the filter is applied? Meaning if I have 3 years of data at the source and I only want to query yesterday's data, would it retrieve all the data from the source (say 3 years) then filter it out yesterday's data? My source is Azure Data Lake in Delta format so I'm using Delta Format source which sends the query to spark. I don't want to have Spark cluster retrieve 100 million rows to just load yesterday's data (1 million rows, etc).

ShaikMaheer-MSFT 37,816 Reputation points Microsoft Employee

2021-12-01T10:10:06.22+00:00

Hi @Raoul Mahadeen ,

Here we are using Source transformation and then Filter transformation. That means, Source transformation is going to get all rows and on top of that rows you are applying Filter condition to filter out specific rows.

----
Please consider it as accepted answer if this helps. Please let us know if any further queries.

Raoul Mahadeen 1 Reputation point

2021-12-01T15:30:26.79+00:00

Ok that is what I was thinking but that is not good. I don't want to retrieve millions of rows to only load a few 100K rows after the filter. That is lots of overhead and waste of resources. What I'm trying to do is load from ADLS (Delta format) the newly loaded rows for yesterday's data. There is years and years of data stored on the lake but I only want to load into the destination (Azure DB) yesterday's data. Do you know another method of doing this without sourcing all the data from the lake just to load yesterday's data?

ShaikMaheer-MSFT 37,816 Reputation points Microsoft Employee

2021-12-02T05:21:21.197+00:00

Hi @Raoul Mahadeen ,

I assume you are using inline dataset type as "Delta" and Linked service pointing to "Data lake storage". If yes, as shown in below image there is no Query option available for you to query only specific rows directly from Source transformation. We should consider using Filter transformation only.

If our source is "Azure Databricks Delta lake" then we can query to desired data from source using Copy activity. Click here to know more about it.

I encourage you to log this feedback in below feedback portal. ADF product team closely monitor feedbacks there and consider them for future releases. Feel free to share your feedback as comment. So that if any other community member looking for same they can upvote your feedback. Thank you.
https://feedback.azure.com/d365community/forum/1219ec2d-6c26-ec11-b6e6-000d3a4f032c

Hope this helps. Please let us know if any further queries.

---------------

Please consider mark it as accepted answer if this helps. Accepted answers helps other community members as well.
Sign in to comment