Allow T-SQL DML write in Lakehouse/Spark write to warehouse

[nancy_chiu]

Please support the ability to do either one or both of the following:

1. Allow T-SQL DML write to Lakehouse, even single-table transaction is great!
2. Allow Spark to write to warehouse, even update/insert is great!

If we can only have one, the former is more important!

The ability to share some writing across Lakehouse/Warehouse, even if low-volume, allows a much greater degree of flexibility

[nancy_chiu]

One of the key challenges in running an analytics platform is building a robust pipeline without turning into a nightmare. The best way to do this is to use a metadata-driven pipeline. In our SQL warehouse we can run a lot of stored-procs that are driven by metadata/control tables.

However, Fabric has a Warehouse/Lakehouse distinction. The challenge of maintaining a single pipeline over both set of artefacts become difficult. At the moment they do not "talk" to each other. This makes it quite challenging to build a unified metadata-driven pipeline across a Fabric workspace that has warehouses and lakehouses. By allowing Warehouse to do T-SQL DML updates to a Lakehouse table, as well as vice versa, we can exchange metadata about where the pipeline is at, and thus have a single pipeline across both.

As an example, I would like the Warehouse to do a batch of ETL to process the latest day's worth of data, then use spark jobs to consume the Warehouse data and return ML predictions in the Lakehouse, and then use the Warehouse ETL to consume the Lakehouse predictions, and then use the Lakehouse to write out the "gold tables" again for DirectLake. How can I coordinate this back-and-forth? If Warehouse can write to Lakehouse, or Lakehouse can write to a Warehouse table, then at least I can communicate all the job statuses by writing to a single metadata table.

[Fab329]

Enabling DML from SQL server endpoint would be fantastic!

[william13]

Agreed - this would be very useful for companies looking to implement a LH-LH-DWH medallion architecture, writing from Silver LH to Gold DWH. Also this limitation damages the overarching narrative around OneLake: to have flexibility behind reading and writing data between the different engines.

[fbcideas_migusr]

Hi,

I hope that a such feature could be released as soon as possible. Thanks

[#6]

Thank you for the idea. Can you tell us more about why this is important to you and what analytics scenarios this will enable for you?