topic Re: Dataflow Gen2 fails when trying to create new table in Data Warehouse in Dataflow

Dataflow Gen2 fails when trying to create new table in Data Warehouse

frithjof_v — Sat, 23 Mar 2024 08:11:16 GMT

Hi, I did the following steps:

1. Create a new Data Warehouse.

2. Create a new Dataflow Gen2.

2.1 Use "Enter data" to enter some dummy data in the Dataflow Gen2 (text type, the content in each cell is just a single letter).

2.2 Set the Data Warehouse as the Destination with the options selected: Create new table, and Replace mode (not append mode).

2.3 Publish the Dataflow Gen2.

The Dataflow publishes successfully, but the refresh fails with the following error message:

_WriteToDataDestination: Mashup Exception Expression Error Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Expression.Error: Table is not a Lakehouse SQL table Details: Reason = Expression.Error;Microsoft.Data.Mashup.Error.Context = System

I tried the same multiple times in a row, creating new dataflows and new warehouses, and it's failing every time.
I don't understand why it's failing, and I don't understand the error message (I am working with Warehouse, not Lakehouse).
Am I missing something?

Thank you 😀

Re: Dataflow Gen2 fails when trying to create new table in Data Warehouse

miguel — Sun, 24 Mar 2024 18:20:58 GMT

Hi!

Could you right click your query in the Queries pane and check that it has "Enable staging" selected? if not, can you select it and then run your dataflow again?

Re: Dataflow Gen2 fails when trying to create new table in Data Warehouse

frithjof_v — Mon, 25 Mar 2024 05:48:41 GMT

Hi,

Yes, it has staging enabled:

This is what it looks like when I hover over the Data destination:

Re: Dataflow Gen2 fails when trying to create new table in Data Warehouse

miguel — Mon, 25 Mar 2024 06:07:19 GMT

Could you please raise a support ticket and share the ticket number with us? Below is the url to the support page so you can create the ticket:

https://support.fabric.microsoft.com/en-US/support/

im unable to repro this. Are you able to replicate this error / behavior in other workspaces with a different warehouse ?

Re: Dataflow Gen2 fails when trying to create new table in Data Warehouse

frithjof_v — Mon, 25 Mar 2024 07:50:29 GMT

Thank you miguel !

I tried creating two new warehouses, and a dataflows gen2 for each of them, in the same workspace, and I get the same error when refreshing these two dataflows gen2. They get the "Table is not a Lakehouse SQL table" error message.
I tried creating a lakehouse and a dataflow gen2 in the same workspace, and this dataflow refreshes successfully.

I tried creating a new warehouse and a new dataflow in another workspace, and this time I didn't get that error when refreshing the dataflow. It works fine in the other workspace.

At this time I am only testing these features in my trial capacity. I don't think I have time for the support process now.

Thank you! 😀

Re: Dataflow Gen2 fails when trying to create new table in Data Warehouse

pqian_MSFT — Mon, 25 Mar 2024 18:24:04 GMT

Loading to DW requires Staging.

From the traces it looks like your staging lakehouse is broken - it doesn't have a SQL endpoint. Going to a new workspace creates a new staging lakehouse, so the problem doesn't exist there.

You'll probably need to get rid of this broken workspace and start with a new one.

Re: Dataflow Gen2 fails when trying to create new table in Data Warehouse

OliverO — Tue, 26 Mar 2024 17:06:13 GMT

that is pretty poor

Re: Dataflow Gen2 fails when trying to create new table in Data Warehouse

frithjof_v — Tue, 26 Mar 2024 18:51:28 GMT

pqian_MSFT thank you for the information 😀

Do all the dataflows gen2 in a workspace, which have data warehouses as their destination, use the same staging lakehouse?

Do all the dataflows gen2 in a workspace, which are writing to lakehouses, use another staging lakehouse? I have some lakehouses also in this workspace, and those dataflows gen2 which are writing to the lakehouses are not getting this error. They also have staging enabled. So it seems not everything in this workspace is broken...

Obviously, I wish this error didn't happen, because now I need to move everything to another workspace...

But at least, now I know that this is what I need to do 😊

Re: Dataflow Gen2 fails when trying to create new table in Data Warehouse

pqian_MSFT — Tue, 26 Mar 2024 20:03:57 GMT

This error is pretty specific to the path that loads data into DW as output destination. In order to efficiently load to DW, the data must be staged in the LH and then bulk transferred to DW via the LH's SQL endpoint.

The "staging lakehouse" is a hidden artifact that is bound to a workspace. It gets created when the first dataflow gen2 gets created in that workspace. We just became aware that LH can get created but the SQL endpoint can fail to provision, leaving us with a semi-functional LH. This is a bug that has to be resolved by us. Unfortunately because the staging LH is hidden from the user, there isn't an easy way to recreate it (you can, for instance, access the staging LH directly if you know its endpoint\ID). The easiest mitigation right now is ditch the workspace.

We are still analyzing why LHs are getting created without functional SQL endpoints - but even if we fix that issue, it likely will not mitigate yours, since it's already broken.

Re: Dataflow Gen2 fails when trying to create new table in Data Warehouse

frithjof_v — Tue, 26 Mar 2024 20:42:51 GMT

Thank you pqian_MSFT

I appreciate the response 😀