Lakeouse questions

[smpa01]

1. Can you create views in SQL warehouse (the warehouse counterpart of a lakehouse; and not a standalone warehouse with no lakehouse) from the delta files directly? (I know that you can't create views, not materialized views in Lakehouse the same way you can in databricks catalog, from the delta files from volume/external storage).

select 
    base.*,    
from delta.`Files/test` as base

2. Can you read the parquet files (written in lakehouse files from daily fetch) directly with SQL endopint (warehouse counterpart of the lakehouse) somehow (I was thinking OPENROWSET, but I am getting an error).

create view dbo.p2 
as
select 
    base.*    
from openrowset( 
BULK 'https://onelake.dfs.fabric.microsoft.com/server/lakehouse_testing.Lakehouse/Files/test',
FORMAT = 'DELTA'
) as base

/* this does not work*/

So far, I have  concluded that there is no way to create a view in sql warehouse (counterpart) directly from the files of daily fetch. I am not interested in creating a SQL view of a table that already exists or creating a materialized view in Lakehouse. I simply intersted in determining whether FAbric can create Logical views the same way databricks can from delta?

@v-prasare 

@

[v-prasare · Accepted solution]

Hi @smpa01 

Thanks for actively participating in MS fabric community support.

There is currently no supported way to create a logical (non-materialized) view directly over Lakehouse files (e.g., daily Parquet/Delta fetches) in SQL Warehouse. This is a limitation of Microsoft Fabric compared to Databricks, which allows logical views directly over Delta locations.

please refer to below doc for more info:

https://learn.microsoft.com/en-us/fabric/data-warehouse/limitations

Currently, supported use cases include:

• Creating logical views over existing registered tables in the Lakehouse.

• Creating materialized views on those tables.

• Using OPENROWSET to read Parquet or CSV files.

To work with Delta files in SQL, the data must first be loaded into a table within the Lakehouse, after which views can be created as needed.

Thanks,

Prashanth Are

MS Fabric community support

[BhaveshPatel]

Yes you are right. You can not create views in Fabric Datawarehouse. The only way forward is Materialized Views in Silver tables in Lakehouse. Databricks has advantage here than Microsoft...But Microsoft in long run is beneficial....