Insert/delete/Update data using Fabric Warehouse to Lakehouse

Hi chetansolanki​,

The error you are receiving is expected for this scenario.

I can query a Lakehouse table from a Fabric Warehouse through a cross-database query, but I cannot use the Warehouse connection as a write-through interface to modify that Lakehouse table. The Lakehouse SQL analytics endpoint operates in read-only mode for the underlying Delta tables, so INSERT, UPDATE, DELETE and MERGE statements are not supported against those tables through T-SQL.

Microsoft documents this here: What is the SQL Analytics Endpoint for a Lakehouse?

The important distinction I would make is between these two directions:

-- Supported: read from the Lakehouse and write into a Warehouse table INSERT INTO dbo.WarehouseTarget SELECT * FROM MyLakehouse.dbo.LakehouseSource;

-- Not supported: write from the Warehouse into a Lakehouse table INSERT INTO MyLakehouse.dbo.LakehouseTarget SELECT * FROM dbo.WarehouseSource;

The first operation writes to a native Warehouse table, where T-SQL DML is supported. The second attempts to modify a table exposed through the read-only Lakehouse SQL analytics endpoint, which produces the error you are seeing.

Microsoft provides an example of the supported Lakehouse-to-Warehouse direction here: Ingest data into a Fabric Warehouse

For writing directly to the Lakehouse, I would use the Lakehouse compute layer instead. Depending on the wider solution, my options would include:

• A Fabric notebook using PySpark or Spark SQL
• The Lakehouse query explorer using Spark SQL
• A Fabric data pipeline
• Dataflow Gen2
• A Spark job definition

For example, Spark SQL can perform DML against a Lakehouse Delta table:

INSERT INTO target_table SELECT * FROM source_table;

It also supports UPDATE, DELETE and MERGE operations against Delta tables. Microsoft documents the supported Spark SQL statements in the Lakehouse query explorer here: Query data using the Lakehouse query explorer

If the requirement is specifically for the external application to perform transactional DML through a SQL connection, I would consider making a native Fabric Warehouse table the writable target. Fabric Warehouse supports T-SQL transactions and DML, whereas the Lakehouse SQL analytics endpoint is intended for querying and serving Lakehouse data through T-SQL rather than modifying it.

I would therefore choose between these two patterns:

• Lakehouse remains the writable target

External application         ↓ Pipeline, API or notebook         ↓ Spark writes to the Lakehouse Delta table

• A SQL interface is required for writes

External application         ↓ Fabric Warehouse SQL connection         ↓ Native Warehouse table

The Warehouse can still read and join the Lakehouse tables through cross-database queries, but it cannot be used to bypass the read-only boundary of the Lakehouse SQL analytics endpoint.

I hope this helps clarify the distinction.

AI-assisted drafting: AI was used to help structure and phrase this response. I reviewed and validated the technical content before posting.