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

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.