Developer's query performing very poorly in custom database
varchar(max) columns with delimited lists is an anti-pattern, particularly if you split those lists out.  Properly designed relational tables provide much better performance.  However, that is likely secondary to your performance problem.
I'd bet money on the problem being blocking.  From Microsoft:

Blocking is an unavoidable characteristic of any relational database management system (RDBMS) with lock-based concurrency. On SQL Server, blocking occurs when one SPID holds a lock on a specific resource and a second SPID attempts to acquire a conflicting lock type on the same resource.

Blocking is probably occurring when the Tableau queries take a long time to complete, blocking the updates from occurring, and creating the impression of slowness.
Brent Ozar has an excellent set of tools that can help understand performance problems.  Specifically around blocking, this page contains a load of great resources.
By the way, it looks to me like that update statement in your question looks perfectly well-formed to me.  I see nothing wrong with it as shown.  If you provide query plans for the queries and update statements that are problematic, I'll provide more definitive advice.
If you do find that blocking is a significant problem, you may want to look into SQL Server's multi-version-concurrency-control option that can be enabled at the database level, READ COMMITTED SNAPSHOT.  From that document:

When the READ_COMMITTED_SNAPSHOT database option is set ON, read committed isolation uses row versioning to provide statement-level read consistency. Read operations require only SCH-S table level locks and no page or row locks. That is, the SQL Server Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions. A user-defined function can return data that was committed after the time the statement containing the UDF began.

Be aware, read committed snapshot isolation (RCSI) is not the silver bullet it can appear to be at first glance.  Like most things, it has positive and negative effects.  By design, it changes the concurrency profile of the database, such that you can no longer rely on locking to enforce select-then-update semantics, since the select will no longer block other transactions from accessing the row that will soon be updated.  Kendra Little has a [great article(https://littlekendra.com/2016/02/18/how-to-choose-rcsi-snapshot-isolation-levels/) talking about RCSI.