In this blogpost I'm investigating the reference between Warehouse and Lakehouse. You can reference in Warehouse, Lakehouse tables (and views) and viceversa. But it is not the same experience. It happens in different ways. In Warehouse you can reference a table in the Lakehouse with SELECT * FROM <Lakehouse>.<schema>.<tablename> (or<viewname>) and in the Lakehouse you can link a table from the Warehouse with a shortcut.
Using data of a Lakehouse object in Warehouse
The first experiment is using Lakehouse data in Warehouse. It's a bit confusing because the option is + warehouses, but (in this example) I'm referencing a SQL Endpoint of the Lakehouse in the warehouse. Microsoft sees SQL endpoints as the same as a warehouse in Fabric. Click on the + Warehouses.
The select the SQL endpoint of the Lakehouse.
What happens when I change the name of the table in the Lakehouse? I change the name with the Rename option in Lakehouse
Let's rename it into "product_test".
And now check the query in Warehouse. I had to refresh the Lakehouse in Warehouse. If I don't do that, the query executes succesfully surprisingly. It keeps working 'correctly' until I refresh the Lakehouse. It's a bit confusing. May be it's a timing issue? It's also a bit awkward that whenever I leave the Warehouse item, the Lakehouse disappears and I have add it everytime when I enter the Warehouse again. In the end it will give this error.
Yet another thing is the option to remove the item form the dataset. It seems that Fabric is saying that the product table is added to the standard dataset? Let's try to remove it.
It will give an error :
The error message : "Unable to update BI model with these changes. Please try again later or contact support."
I'm not sure whether the error is correct now because I was renaming the table now and then. May be Fabric is now confused, now;-). Is it supposed to be that when Lakehouse is 'connected' that you can add tables of the Lakehouse to the Warehouse standard dataset? Then, it seems to me that this is not completely correcty implemented yet.
Using data of Warehouse in Lakehouse
Next is how we can use data from the Warehouse in the Lakehouse.
And then I have to tell Microsoft Fabric which location where I can find the object.
Select the Warehouse.
Select the tables (or views). I select the complete schema as a shortcut.
And now the tables are available in the Lakehouse
Can I query the tables in the notebook? And use these in the notebook? No I can't seem to get this working properly. I'm not sure whether what I'm doing wrong or that this is not properly implemented yet by Microsoft.
I think that I should create a shortcut directly to a table and not at a higher level at the Warehouse. When I reference the table 'sales' directly it's working.
So the table is shortcutted from the Warehouse in the Lakehouse and I can use this in my notebook. Interesting!