question

GolebiowskaPaula-9120 avatar image
0 Votes"
GolebiowskaPaula-9120 asked ShaikMaheer-MSFT commented

Troubles with preview and filter in data factory , data flow

I create new data flow, then add source which is a table from the sql database from linked service. And in the preview I can't see some records that should be there (and I know for sure they exist there). When I add filter to the query in the source I can see that one record in the preview. But without that filter it is not presented in the preview. So when I used azure built in function "filter" this record is not presented

azure-data-factoryazure-sql-database
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

116222-image.png

This is how it starts: linked sql db, sorted, filter on CardID =="ABC" and preview says "No output data". But I know for sure that ABC is there.



@MarkKromer-MSFT
@RyanAbbey-0701

0 Votes 0 ·
image.png (20.4 KiB)
RyanAbbey-0701 avatar image
0 Votes"
RyanAbbey-0701 answered RyanAbbey-0701 commented

Preview is exactly that... a preview... it will only show some of the rows and therefore if you have more rows in the table than the preview is configured to show, you will have "missing rows"

· 18
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

... and, if you want to see more rows, click on Debug Settings > Row limit inside the data flow designer. You can include more rows than the 1000 row default. Or, if you'd like to test your logic against a specific value or set of values, choose sample table or sample file in the debug settings. Ensure that the values you wish to test against are present in those samples.

0 Votes 0 ·

I know this is preview but if I use a filter to see specifically CardID= "ABC" and preview says "No Output Data" it suggest that this CardID is not in a dataset. But I know for sure it is there. So why this is not visible in preview?

0 Votes 0 ·

How many rows are you in your database table? How large is your row limit?

0 Votes 0 ·

So what kind of rows shows adf in preview? Some random rows? For sure those are not first rows

0 Votes 0 ·

You're going to get whatever the database presents as the first rows ultimately... if you have the "sort" and expect this row to be one of the first 100, then that would mean the sort isn't getting pushed down so the database is giving you 100 rows (not necessarily the same 100 each time) and then the sort is being applied

1 Vote 1 ·

Okay, it may explain why I can't see all first 100 rows. But why if I use filter CardID== "ABC". That specific ID is not presented in preview and it says "no output data"? I encountered the same problem with using csv files from my blob storage, it's not an issue related to linked sql db only

0 Votes 0 ·
Show more comments

Did you specify the sort order in your source?

0 Votes 0 ·

Yes, they are sorted. I set to see 100 rows and yet some rows are missing that should be in top 10.

0 Votes 0 ·
Show more comments

Hi @GolebiowskaPaula-9120 ,

Thank you for posting your query here, Could you please select single partition inside your sort transformation and other transformations and see if you are getting your desired output?

Also, make sure to try restarting debug session or renaming source transformation name before preview.

0 Votes 0 ·

I have single partition set. And I tried restarting my debug session. Also this problem applies not only to linked db but also csv files I'm using as a source :/

0 Votes 0 ·
MarkKromer-2402 avatar image
0 Votes"
MarkKromer-2402 answered MarkKromer-2402 commented

How about something like this? toUpper(trim(CardID))=='ABC'

Taking out possibilities of unseen whitespace or casing issues

· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@MarkKromer-MSFT do you know if there's a way to contact someone from Microsoft directly to explain how exactly preview works?

0 Votes 0 ·

What happens when you use WHERE CardID = 'ABC' in your SQL Source query? Do you see the row in preview?

0 Votes 0 ·

Yes, that's why I mentioned that I am certain that specific CardID exists in the db

0 Votes 0 ·
Show more comments
MarkKromer-2402 avatar image
0 Votes"
MarkKromer-2402 answered ShaikMaheer-MSFT commented

So you see the row in the data preview pane in the Source query? But you do not see it in the subsequent Filter transformation data preview?

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

exactly and I see that record in sink (after exacuting that data flow in pipeline, I sink to a csv file that is being saved in my blob storage and when I open that blob, download that sink, open it in excel, then I see that record in the file)

0 Votes 0 ·

Seems very weird, not expected. Could be a data preview bug. Can you submit a support ticket from the Azure portal?

0 Votes 0 ·

HI @GolebiowskaPaula-9120 ,

Below is the link for creating support ticket for Azure. Feel free to let us know if any further queries. Thank you.
https://azure.microsoft.com/en-in/support/create-ticket/

0 Votes 0 ·