Forum Discussion
Memorable MemberDataflow GEN2 - Combined data from multiple tables results in nulls
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).
Memorable MemberHi 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.
Memorable MemberAnonymous
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.