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 MemberAnother update.
I found out that data is internally sent to different locations. Things work fine when data is being stored in "DataflowsStagingLakehouse". Whereas one of our largest tables in the dataflow has the combined data from multiple LH tables; and for some reason the data in this table is stored in "DataflowsStagingWarehouse".
I would guess that there is a recent regression. Perhaps the PG recently decided to use the "warehouse" for some types of scenarios, and that is causing problems when large amounts of data need to be loaded. I don't have problems when there are a smaller number of rows. I'm not sure where I crossed a threshold that causes things to stop working properly.
Here is the SSMS presentation that shows some of my tables landing in the lakehouse and others in the warehouse:
It is quite frustrating that the data in my GEN2 dataflow is handled in such different ways, for reasons that aren't explained to us. There is nothing in the GEN2 online editor that indicates these tables will be sent into different types of internal storage.
I suppose that developers who actually care about the storage implementation are expected to configure/customize the default destination. I guess that will be my next step ... especially as long as this bug remains.
Microsoft EmployeeHi dbeavon3,
The product group is actively investigating your issue.
The use of the staging warehouse is not new - it has always existed for combining staged data (both Append and Merge) as well as downstream transformations in general.
We are investigating whether the SQL endpoints or other parts of the stack may have changed behavior and will be sure to keep you posted.
Thanks
- dbeavon3
Thanks SidJay
>> The use of the staging warehouse is not new
The bug is not new either. You can find another customer who reported it three months ago. In their case the problem seems to have been a one-time occurrence and they brushed it off a lot more quickly than I would have.I think the thing that is new is a change in the factors that cause the bug to surface.
As-of this past week, we were observing it in a repeated way. I hoped the nulls would go away, but each day the problem kept repeating!FYI, As you may know, there is little transparency when working thru your Mindtree organization. Those engineers are paralyzed to share anything useful about Microsoft's bugs (big or small). I almost end up getting less help than I would by remaining in this community (and reddit). Sometimes I simply try to find workarounds for Fabric bugs. This may be how your other customers feel as well, and may explain why so many fabric bugs don't make their way back to the PG. The support structure itself is counterproductive. Customers would rather feel the pain of the bugs, than to feel the pain of this CSS support structure.