Forum Discussion
Memorable MemberDataflow GEN2 - Combined data from multiple tables results in nulls
I'm having an unusual problem with dataflow GEN2.
I have several tables that are configured with "enable staging".
After the individual tables are loaded, they are combined into a final table which is where our problem appears.
I have not configured a target destination for the dataflow. I'm assuming that the default storage format is being used internally (parquet or similar).
Here are the tables that are successfully loaded with "enable staging" (four of them). The behavior in the preview (nulls presented from table) is the same as the behavior elsewhere. The four tables are good, and work as expected....
... however the final step is to combine all these together into one final mega-table. That is the table that becomes corrupted. (red underlines show nulls. The nuls are NOT found in any of the source tables).
Here is the code to combined the tables together.
Please let me know if anyone has seen this behavior where nulls are generated when combining data together. The four individual tables have about 2 million rows each. The final combined version of the data should be 4x that size.
For folks who want to avoid a table full of nulls, I asked the dataflow PG how to prevent data from getting stored in a DW table.
The way you do this is by using the function "Table.StopFolding" as the last step in a GEN2 dataflow, when preparing an entity.
That trick will force the data to remain in the "DataflowsStagingLakehouse" (parquet), and will never live in a DW table. It avoids all bugs related to DW tables. It may also be less expensive to use this for long-term storage and infrequent access. If you are only using the data for performing full table scans (filtered on one or two columns), then performance may not be that degraded as compared to a DW table. I am convinced that DW tables are probably overkill for most use-cases related to dataflows. And they are certainly not worth the risk of being presented with a bunch of null data!
Below you can see that the same dataflow which used to send a table to the DW is now keeping everything inside the LH.
I hope this helps anyone who encounters a table full of nulls in Fabric.
You might also refer to the ICM attached to this SR: TrackingID#2504100040007682
In that ICM I was able to reach out to at least two of the Microsoft PG's and explained that customers are being impacted in a consistent and repeating way. I think they were already aware to some degree, but perhaps they have not introduced any telemetry or monitoring for the problem so they don't now how serious it is yet.I am pretty content with the workaround, given that I don't actually interact with Fabric DW directly for my own solutions. I only encountered this bug indirectly by way of the internal implementation of dataflow GEN2. If others aren't very happy with the workaround, then I encourage you to spend your own time with Mindtree & Microsoft. I already did my fair share of work on this, overy the past couple weeks... I think Microsoft will be more inspired to fix this if others would reach out as well. Please expect at least 20 hours of effort at a minimum, and let them know there is already an ICM (that will save you another 20 hours).
14 Replies
dbeavon3I am hopeful that the null issue will go away. It might be a rare glitch.
I really would prefer that the dataflow would fail rather than generating bad data. But it will be hard to create a repro if this issue doesn't arise very often.
I found one other post in the community that seems to represent the same problem. A user got a bunch of nulls generated from a GEN2 dataflow, and then the problem goes away as suddenly as it appears.
Gen2 DataFlow inserts null values into WarehouseHi dbeavon3,
Thanks for reaching out to the Microsoft fabric community forum.
One potential cause for null values appearing during the table merge process is data type mismatches or schema mismatch across the source tables. If there's a discrepancy in data types such as one table having a column as text while another has it as a number or if a column exists in one table but not in another, Power Query will still perform the Table.Combine, but it will fill in null for missing columns in each respective table. To prevent this, ensure that all corresponding columns across the source tables have consistent data types and consistent schema before performing the merge. It’s recommended to explicitly define the data types during the transformation steps to maintain schema uniformity and avoid unexpected results.
If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards- dbeavon3
Hi Anonymous
I appreciate the comment. We are working with Mindtree on an SR (Tracking ID #2504100040007682) Unfortunately I do NOT think Microsoft has an ICM for this issue yet, but hopefully the bug will be reported to Microsoft soon. I'm working on that.I don't think there are any type mismatches... although perhaps it is still possible that the SQL Analytics Engine might be confused about my data format. I suspect you are right that if there is some sort of internal datatype or synch issue, then it originates with the "SQL Analytics Engine". It is not intuitive to the user, and the source of our problem is buried very deeply in the inner workings of the dataflow.
Again here is the step to combine data tables:
This step is similar to what is described in the blog:
https://blog.fabric.microsoft.com/en-us/blog/data-factory-spotlight-dataflows-gen2?ft=All
You can see the red box below, which is where I suspect our data is being replaced by nulls.
Interestingly I think this may be a timing issue.
... If I create a different dataflow and perform the Table.Combine at a much later time, in a totally different dataflow, then I'm able to get the results appended together in the expected way. In the past I've heard of bugs in the SQL Analytics Engine, that are referred to as "synch bugs". While I know that users are often expected to implement workarounds in their python notebooks, I did NOT believe there were cases where dataflow developers were expected to perform additional steps to avoid the "synch bugs">> "It’s recommended to explicitly define the data types during the transformation steps to maintain schema uniformity and avoid unexpected results."
I have not ever seen any recommendation like this. Do you have a link? Remember that a large part of what PQ accomplishes is to determine the proper schema that would describe our data. In fact, whenever we publish a dataflow, we must wait a very long time for the "evaluation" phase to execute. It can be a very long wait, and its only goal is to reflect upon the schema that would allow the data to be persisted in storage. If it was the user's job to define the schema, then an "evaluation" phase would be unnecessary.
- dbeavon3
Anonymous
Based on your user name, I cannot tell if you are from Microsoft. Are you with Microsoft or Mindtree or another vendor/partner? I'm eager to hear your input about what might be going wrong. Especially if you are an authoritative representative of Microsoft and have first-hand experience with the platform. I believe there is some sort of unexpected regression going on, and I'm hoping you would be able to confirm one way or another.
Hi dbeavon3,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
- dbeavon3
A known issue was finally published for this bug with nulls being inserted into DW tables.
... according to the article it is a rare issue. However in the reddit community there are currently a half dozen customers who are discussing this problem in several different threads. I'm sure those individuals represent only about 3 to 5% of the people who are impacted.
