Azure SQL Data Warehouse – How to See True Query Plan

Functions in Azure DW don't support select statements that access tables like in your use case, see CREATE FUNCTION (SQL Data Warehouse):

function_body
Specifies that a series of Transact-SQL statements, which do not reference database data (tables or views), define the value of the function.

Could you double check that function is created in DW?

Ref: https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-develop-best-practices-transactions

The transaction safety limits only apply to fully logged operations. CTAS is capable of being minimally logged. So answer depends, if CTAS is minimally logged safety limit does not apply and vice versa.

Unlike fully logged operations, which use the transaction log to keep track of every row change, minimally logged operations keep track of extent allocations and meta-data changes only. Therefore, minimal logging involves logging only the information that is required to rollback the transaction in the event of a failure or an explicit request (ROLLBACK TRAN). As much less information is tracked in the transaction log, a minimally logged operation performs better than a similarly sized fully logged operation. Furthermore, because fewer writes go the transaction log, a much smaller amount of log data is generated and so is more I/O efficient.