How to materialize a view from SQL endpoint as delta table consumable directly from DirectLake?

[jaryszek]

Hello Guys,

I created a view (Materialized Views are not supported for me yet) in sql endpoint on LakeHouse. 

View is working using this query:

Now I want to use notebook to create delta table from it and put it into LakeHouse layer, not sql endpoint view. In other words I want to materialize view to be used by power bi. 

I tried with:

but getting error:

AnalysisException: [TABLE_OR_VIEW_NOT_FOUND] The table or view `Mart2`.`Dim_Group_Descendant` cannot be found. Verify the spelling and correctness of the schema and catalog.
If you did not qualify the name with a schema, verify the current_schema() output, or qualify the name with the correct schema and catalog.
To tolerate the error on drop use DROP VIEW IF EXISTS or DROP TABLE IF EXISTS.; line 6 pos 5;
'Project ['GroupSK, 'GroupName, 'CategoryName]
+- 'UnresolvedRelation [Mart2, Dim_Group_Descendant], [], false

How to solve it?

Best,
Jacek

[v-pnaroju-msft · Accepted solution]

Hi jaryszek,

Thank you for your inquiry on the Microsoft Fabric Community Forum.

Based on my understanding, the error "TABLE_OR_VIEW_NOT_FOUND: Mart2.Dim_Group_Descendant" occurs because SQL Analytics Endpoint views are not directly visible to Spark. The view exists only in the SQL Endpoint catalog, while spark.sql() queries the Spark catalog. Therefore, Spark cannot resolve the view name.

To make the view available in the Lakehouse so that Power BI (Direct Lake) can use it, we must read the SQL view using the Fabric Spark SQL connector (synapsesql) and then write it as a Delta table.

Please follow the steps below, which might help to resolve the issue:

1. Read the SQL view in the notebook using:
   df = spark.read.synapsesql("dw3_1.Mart2.Dim_Group_Descendant")
   display(df.limit(20))

   Replace dw3_1, Mart2, and Dim_Group_Descendant with your actual warehouse (or lakehouse) and schema names.

2. Materialise the data into a Lakehouse Delta table using:
   df.write.format("delta").mode("overwrite").saveAsTable("Mart2.Dim_Group_Descendant_Materialized")

   This creates a physical Delta table in the Lakehouse.

For further information, please refer to the links provided:
Spark connector for Microsoft Fabric Data Warehouse - Microsoft Fabric | Microsoft Learn
Lakehouse and Delta Tables - Microsoft Fabric | Microsoft Learn
Direct Lake overview - Microsoft Fabric | Microsoft Learn

We hope the information helps to resolve the issue. If you have any further queries, please feel free to contact the Microsoft Fabric community.

Thank you.

[jaryszek]

Thank you, 

Jacek