Problem with date colum when staging

[ArtecX]

Have anyone else had problem with date colum when using staging mode on Dataflow Gen 2?

My dataflow have started getting error "the type of column 'date' doesn't match the type of the column in the table. "

In the Datawarehouse the table is date. But somehow in the staging datalake/warehouse the table is date/time. and i cant delete the staging data? Any tips?

[alfBI]

Hi, We were suffering exactly the same problem described by @ArtecX  

Problem appeared simply creating a dataflow gen2 using as source a table with a column with date type and a warehouse as destination. When executing the dataflow gen2 were were receiveing an error  such below one

104100 Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: We can't update the table. The type of column 'FromDate' doesn't match the type of the column in the table.

We noticed that as  @ArtecX  said for whatever reason the table created on the hidden artifacts has a datetime column instead of date as expected. This seems to be the root cause issue. Curiously we have created today the Dataflow from scratch and the issue does not longer appears.

[ArtecX]

I havent tried to create a new datawarehouse, since thats not an option. The staging artifact is hidden from Workspace view, but you are still able to browse it through azure data studio. If i remove staging the dataflow gen 2 works. but my problem is that the dataflow gen 2 does take 7 hours longer when staging is off versus when its on for specific queries.

[miguel]

Staging is orthogonal to where the destination should be. You can have the staging mechanism, which happens internally as part of the Dataflow Gen2 process to the staging artifact, but you will load your data to your own Lakehouse.

Using the staging artifacts (lakehouse and warehouse) as final destinations for your data can create issues on how the staging mechanism and other mechanisms work.

Again, we do not encourage the usage of the system artifacts for anything other than internal / system processes that Dataflow Gen2 uses.

The way that the staging mechanism works is more as part of the query that you create and if the query has "staging" enabled or not. There are temporal tables created for the staging mechanism at the system artifact level

Date and DateTime are supported datatypes for the staging artifact. However, you can see the full list of supported data types per destination on the link below:

Data Factory Dataflow Gen2 limitations - Microsoft Fabric | Microsoft Learn 

[ArtecX]

Maybe i wasnt precise enough.

Im not using the system staging table for anything i just checked it out in Azure Data studio to troubleshoot the issue.

I have a dataflow gen 2 . where i have 9 queries. I have a Fabric Datawarehouse wich is the destination for the queries. «This is a self created Fabric Datawarehouse»

I have 2 queries that has staging enabled

There is a bug with the system artifact so the dataflow gen 2 staging table(System artifact staging table, from the staginglakehouse hidden from workspace) have somehow staged my date colum as date/time instead of date , so when i try to run the querie and it tries to stage it says the collum in the destination(StagingLakehouse) is a diffrent type.

[miguel]

Do you think that you could share some repro steps so that we could test the scenario on our end? If you could share any sample code that you're using and we could replicate on our end that would help tremendously 

[v-cboorla-msft]

Hi @ArtecX 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.
In case if you have any resolution please do share that same with the community as it can be helpful to others.
Otherwise, will respond back with the more details and we will try to help.

Thanks

[v-cboorla-msft]

Hi @ArtecX 

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet.
In case if you have any resolution please do share that same with the community as it can be helpful to others.
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
In case if you have any other question on a different issue, we request you to open a new thread.

Thanks 

[ArtecX]

I`ve kinda given up on Dataflow Gen 2 and Fabric Datawarehouse. There is to many bugs at the moment, but it has alot of potensial. 

[miguel]

Hi! Could you provide a few more details on how and when exactly you're getting this error?

When you refer to staging datalike/warehouse, are you referring to an artifact that you created and that you're referring to as Staging?

[ArtecX]

Im refering the system staging lakehouse that is used when you enable staging on a Gen2 Dataflow. 

[miguel]

Are you able to replicate the issue with a completely new Lakehouse? if yes, could you share a query that I could use to replicate the issue on my end? if sharing the query is not possible due to privacy reasons, you can raise a support ticket so an engineer can take a closer look at it: https://support.fabric.microsoft.com/support

The staging artifacts are only meant to be used by the internal processes that a Dataflow Gen2 executes. These staging artifacts are now hidden from the workspace list, so they shouldn't be accessible through this way.

You can create your own Lakehouse and/or Warehouse and load the data to them as needed using a Dataflow Gen2.