Fabric Lakehouse: T-SQL write support, Spark vs T-SQL performance, and views in semantic models

T-SQL notebooks do not provide a write path to a Lakehouse through the Lakehouse SQL analytics endpoint. The SQL analytics endpoint for a Lakehouse is read-only - it exposes the Delta tables through a SQL interface, but DML statements such as INSERT, UPDATE, DELETE, and MERGE are not supported there. A T-SQL notebook connected to a Fabric Warehouse can perform T-SQL writes because the Warehouse is a writable SQL engine. So this is primarily a Lakehouse SQL analytics endpoint architecture constraint - not a general limitation of T-SQL. If you need T-SQL-based transformations that write relational tables, the Warehouse is the appropriate target - if you want to write Delta tables in a Lakehouse, Spark is the native approach.

I would not characterize Spark SQL as universally faster than T-SQL against the same Delta tables. They are different execution engines optimized for different workloads. Spark is the Fabric engine intended for large-scale data engineering, distributed transformations, joins, aggregations, and writing Delta data. The Lakehouse SQL analytics endpoint is primarily intended to provide SQL-based analytical access to Lakehouse Delta tables and is optimized around analytical/BI query workloads. AFAIK, architecture guidance generally positions Spark for data engineering and the Lakehouse for large-scale data transformation, while the Warehouse is positioned around T-SQL and relational analytics.

However, I am not aware of Microsoft publishing a general benchmark establishing that "Spark SQL is faster than the SQL analytics endpoint" across workloads. A defensible architecture standard would be to use Spark for large-scale curated-data preparation and Delta-table transformation, and use the SQL endpoint primarily for SQL access/querying of the resulting curated data. That is an architectural division of responsibility rather than a claim that Spark wins every performance test.

Using a SQL analytics endpoint view does not force the entire semantic model into DirectQuery. The distinction is between the storage mode of each semantic-model table and the particular Direct Lake mode being used. With Import mode, a SQL endpoint view can be used as an Import source - Power BI executes the SQL query during refresh and stores the resulting data in the model. That is independent of Direct Lake. With Direct Lake on OneLake, the native Direct Lake tables can continue to use OneLake/Delta data. A SQL endpoint view cannot itself be a native OneLake Direct Lake table because the view is a SQL abstraction rather than a physical Delta table. If you need that view's result in the model, it can be brought in as an Import table, creating a composite model in which the physical Lakehouse tables remain Direct Lake while the view-derived table is Import. That does not convert the existing Direct Lake tables to DirectQuery.

If the above response helps answer your question, remember to "Accept Answer" so that others in the community facing similar issues can easily find the solution. Your contribution is highly appreciated.

hth

Marcin