I have this flow, where I constantly needs to update the state of rows in a table. I know a notebook can I easily update a table in a lakehouse, which I have already implemented, but I have a step in my pipeline where I want to remove a row from the table. Running a script activity on warehouse table is much quicker at this than a notebook is on a lake house table.
I already recreating the table in a warehouse to run my script activity, it works well. Then I tried created a shortcut to the warehouse table, so my notebooks can interact with the table. However when I try to do an UPDATE statement on the table I get a 403 forbidden response stating User is not authorized to perform current operation for... I don't get it since it is the same OneLake, I am the owner all the resources talking to each other here.
I did read https://learn.microsoft.com/en-us/fabric/onelake/onelake-shortcuts and it says "When accessing shortcuts through Power BI semantic models or T-SQL, the calling user’s identity is not passed through to the shortcut target. The calling item owner’s identity is passed instead, delegating access to the calling user.", but then I don't see why I cannot do this.
Is there a way to set the permissions, so I can do my update or is it simply impossible and I will have to live with minute long duration for a deletion statement, that takes a couple of seconds at most, using a notebook?
Only TSQL can modify warehouse tables. You can read the Delta files directly or with Spark, but you can't modify them.
TSQL can read lakehouse tables, and read and write warehouse tables.
Spark can read warehouse tables, and read and write lakehouse tables.
If you need to your notebook can use JDBC or ODBC to connect to the Warehouse SQL endpoint and run TSQL commands or stored procedures to modify warehouse tables. So for instance your notebook could stage some data in a lakehouse table and then ask Warehouse to read that data and modify some warehouse tables.
Although if the operation is long-running you're probably better off letting the notebook exit and using a subsequent Script or Stored Procedure activity in a pipeline.