How to write/update in Data warehouse table using Notebook

[digitalbrain]

Hi there, I've a  secanrio where I need to read a value from lakehouse and update that value in data warehouse using Notebook. Is there a way to accompolish it? 

[FabianSchut]

Hi @digitalbrain,

As @frithjof_v mentioned, there is no direct way to connect your Warehouse to a notebook. Is it possilble that you create the table you need in the Warehouse in a Lakehouse first and then create a shortcut in your Warehouse to your Lakehouse? Then you have full control with a notebook over that table and still be able to access the data from the Warehouse.

[digitalbrain · Accepted solution]

Hi @FabianSchut, I think @frithjof_v  is correct here. I just tried to create a shortcut in data warehouse and there was no option to create it. Then I created a table in data warehouse and also created its shortcut in the lakehouse. I can read the data, but when I tried to overwrite the tabe via notebook, it threw an error. It confirms I can't write into the datawarehouse using notebook (same way as lakehouse table).

"Operation failed: "Forbidden", 403, PUT"

[frithjof_v]

It's not possible to create a shortcut in a Warehouse

There is an Idea for that:

Lakehouse shortcuts available in Warehouse also

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=e5e4905c-7c7a-ee11-a81c-000d3adc66b1

You can use cross-database queries, though.

However it's not exactly the same (you can't use it in a Direct Lake semantic model, I think. Admittedly, I haven't tried.).

And it's exposed to the potential for SQL Analytics Endpoint sync delay.

[FabianSchut]

My apologies, you are both correct @frithjof_v  @digitalbrain. There are no shortcuts in Warehouse (yet). 

[frithjof_v]

Usually when this question comes up, people mention pyodbc or jdbc as ways to write to Warehouse from Notebook.

I haven't tried either of them myself, but I have read about others who have used these methods.

However my impression is that this is a "workaround". The Notebook is primarily meant to be used for the Lakehouse, and not really for the Warehouse. However Pyodbc or Jdbc may work and enable you to write to the Warehouse from a Notebook, but it has limited performance, is my impression based on what I've read other places. https://www.reddit.com/r/MicrosoftFabric/s/Z8LPqsbxeU

You can also vote for this Idea:

Write Data into Data Warehouse from Fabric Notebook

https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=ac5bdbd2-5afe-ee11-a73c-000d3a7c9aa4