Forum Discussion
DataflowGen2, which does not copy all the data...
Thanks for the info!
I'd recommend removing Staging from your queries if its enabled. Would also recommend removing the add custom column step to see if you get the same issue and to isolate the variables as you've mentioned that the issue could be strictly with the Value.NativeQuery usage.
There's a situation where the native query might not give you the same data that you get through the navigator of a connector. However, the difference would be at the table schema shown in the query and not the number of rows. This is because the connector provides projections or reference columns that have either "record" or "table" values which are not supported to be loaded to a Lakehouse, but they'll show up as columns in your query. A native query wouldn't "project" or provide those column references.
I finished doing some tests and I can see a behavior that I'd like to explain.
When we create a connection to a source, we may need to read multiple rows or make multiple calls to get the data that we need. In the activity statistics it may show up the total of all the rows that we read to perform the activity, but it doesn't directly mean that it was the output of the query that could've ran inside of the activity.
Both of the attemps that I had show this behavior:
But they write the same number of rows which is the number of rows that your query, in theory, should have.
You can run 2 validations to make sure that your query is evaluating to the number of rows expected:
- You can head over to the Power Query editor and in the "Transform" tab of the ribbon add a "Count rows" transform to read the total rows. After doing that it should match the "rows written" (in theory) - in my case it does match for 18 rows:
- You can check in your PowerApps admin access how many rows that table has. In my case it also matches the 18 rows written.
If both the queries that you've created are exactly equivalent from each other and they yield the same number of rows within the Power Query Editor (you can validate this by the process stated in #1 using the "Count rows"), then this is something that we would like to understand more and should be raised as a support ticket so an engineer can take a closer look. With a support ticket we would get to the bottom of your scenario and determine why one query loads X number of rows whereas the other might load Y.
miguel ,
Thank you very much for your feedback and testing.
I can confirm that the Staging feature is not enabled.
When I count the lines, I have more than 70,000 lines.
I did another test. I took only the 111 columns I need (out of 350).
And in this case, it works :
This confirms that it is Value.NativeQuery that causes problems when there are too many columns/too much data.
Can you reproduce a test on your side with a volume (rows and columns) equivalent to what I have on my side?
Thank you in advance for your feedback.
Vivien
I don't have an environment that matches that criteria, but it would help tremendously if our engineering team can capture some traces for your Dataflow. They wouldn't need access to the environment, but just be able to capture the traces from your account / dataflow with explicit consent and do some deeper investigations. Definitely do feel free to raise the support ticket if you have a chance and our team can get to the bottom of it.
In theory a "SELECT * FROM dbo.Account" is not exactly the same query plan that shows up for the query without the Value.NativeQuery, so the team might start by analyzing that first.
