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"
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 SQL Database
Azure Data Factory
-
Golebiowska, Paula 1 Reputation point
2021-07-20T08:01:28.703+00:00 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.
Sign in to comment
3 answers
Sort by: Most helpful
-
Ryan Abbey 1,151 Reputation points
2021-07-19T20:08:31.517+00:00 -
MarkKromer-MSFT 5,186 Reputation points • Microsoft Employee
2021-07-20T00:36:33.39+00:00 ... 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.
-
Golebiowska, Paula 1 Reputation point
2021-07-20T07:45:58.403+00:00 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?
-
Golebiowska, Paula 1 Reputation point
2021-07-20T07:47:42.403+00:00 So what kind of rows shows adf in preview? Some random rows? For sure those are not first rows
-
Mark Kromer MSFT 1,146 Reputation points
2021-07-20T07:50:22.453+00:00 Did you specify the sort order in your source?
-
Golebiowska, Paula 1 Reputation point
2021-07-20T07:52:42.257+00:00 Yes, they are sorted. I set to see 100 rows and yet some rows are missing that should be in top 10.
-
Ryan Abbey 1,151 Reputation points
2021-07-20T20:27:35.427+00:00 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
-
ShaikMaheer-MSFT 37,231 Reputation points • Microsoft Employee
2021-07-22T09:20:40.213+00:00 Hi @Golebiowska, Paula ,
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.
-
Golebiowska, Paula 1 Reputation point
2021-07-22T09:23:53.703+00:00 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 :/
-
Golebiowska, Paula 1 Reputation point
2021-07-22T09:26:52.767+00:00 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
-
Mark Kromer MSFT 1,146 Reputation points
2021-07-22T14:30:37.38+00:00 How are you sorting? Are you using a SQL query in your source with an ORDER BY clause? If you are not doing that, you cannot guarantee ordering. Is your filter part of the source SQL query? If it is not, then cannot guarantee that the ID you are filtering on inside your data flow is present in the sample data unless you specify the filter in the source SQL query WHERE clause. Alternatively, set a very high row limit in the debug settings to ensure more rows are sampled.
-
Mark Kromer MSFT 1,146 Reputation points
2021-07-22T14:31:20.923+00:00 How many rows are you in your database table? How large is your row limit?
-
Golebiowska, Paula 1 Reputation point
2021-07-22T14:38:12.673+00:00 Order by clause is not supported there, You can't add it to the query in the source. You can try and see for yourself.
-
Mark Kromer MSFT 1,146 Reputation points
2021-07-22T14:45:32.91+00:00 Yes, you are right, my bad. Instead, you can use the debug settings > Sample Table feature and choose a view that has the sort order set in the ORDER BY in your view definition. Otherwise, your best bet is setting a higher debug row limit or put the filter in your source SQL query using WHERE clause.
-
Golebiowska, Paula 1 Reputation point
2021-07-22T14:49:23.343+00:00 @MarkKromer-MSFT I adjusted row limit to cover all records from the db but still the very same problem continues. What is more unexpecting, the very same problem occurs with csv with 50 rows. It's still hiding some values from me.
-
Mark Kromer MSFT 1,146 Reputation points
2021-07-22T14:51:48.507+00:00 IAre you using a Filter transformation? If so, is your expression exactly CardID = "ABC" ? Can you try it using single quotes instead of double quotes?
-
Golebiowska, Paula 1 Reputation point
2021-07-22T14:53:53.987+00:00 yes, I tried with single quotes. It works for : CardID = "ABD" or CardID = "ABA" but for some unknown reason it skips CardID = "ABC"
-
Mark Kromer MSFT 1,146 Reputation points
2021-07-22T14:56:29.433+00:00 How about something like this? toUpper(trim(CardID))=='ABC'
Taking out possibilities of unseen whitespace or casing issues
-
Ryan Abbey 1,151 Reputation points
2021-07-22T20:15:38.223+00:00 If you mean the filter from your screenshot, that will also be after the first rows are presented.
I see the further discussion below and if it's not showing despite all rows fitting within the preview, a little odder. How many of the total rows do you see (of the 50 row subset may be easier to work with)
Sign in to comment -
-
Mark Kromer MSFT 1,146 Reputation points
2021-07-22T14:56:04.43+00:00 How about something like this? toUpper(trim(CardID))=='ABC'
Taking out possibilities of unseen whitespace or casing issues
-
Golebiowska, Paula 1 Reputation point
2021-07-22T14:59:05.213+00:00 "no output data"
-
Golebiowska, Paula 1 Reputation point
2021-07-22T15:01:41.693+00:00 @MarkKromer-MSFT do you know if there's a way to contact someone from Microsoft directly to explain how exactly preview works?
-
Mark Kromer MSFT 1,146 Reputation points
2021-07-22T15:04:37.637+00:00 What happens when you use WHERE CardID = 'ABC' in your SQL Source query? Do you see the row in preview?
-
Golebiowska, Paula 1 Reputation point
2021-07-22T15:06:06.443+00:00 Yes, that's why I mentioned that I am certain that specific CardID exists in the db
-
Mark Kromer MSFT 1,146 Reputation points
2021-07-22T15:09:14.97+00:00 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?
Sign in to comment -
-
Mark Kromer MSFT 1,146 Reputation points
2021-07-22T15:09:00.057+00:00 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?
-
Golebiowska, Paula 1 Reputation point
2021-07-22T15:11:19.08+00:00 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)
-
Mark Kromer MSFT 1,146 Reputation points
2021-07-22T15:13:18.17+00:00 Seems very weird, not expected. Could be a data preview bug. Can you submit a support ticket from the Azure portal?
-
ShaikMaheer-MSFT 37,231 Reputation points • Microsoft Employee
2021-07-26T05:41:38.06+00:00 HI @Golebiowska, Paula ,
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/
Sign in to comment -