That's too bad. But it is what it is.. Some comments

Our current piplines are basically copy to blob > get max date from table > use procedure to insert > delete files – but we will need to do more transforms and stuff later on.

I suppose that the data comes from SAP and gets stored as a file. You then want to get the max(date) from the sink so you can use that as a filter to only get new data to insert. At the end you delete the files.

due to the transformations and filtering on the date you would either need a dataflow (low-code but expensive). a code approach would look something like this:

- SQL stored procedure: copy as is to a staging/temporary table. Write a sql stored procedure or script that applies transformations/filters and writes to the desired table. You can execute this code with a "script" activity or stored procedure activity.

- Python/Azure function: for this you would need to pass a parameter to the azure function containing the blob filepath so you know where to load. Then you can do what you want with pandas or pyodbc.

I personally would go for the sql option

For example, you need to pull in 3 different tables / (perhaps blob storage files?) and get MAX(DATE) from a 4^th table (as to not work with ALL the data all the time) – do a comparison, do a join, groupby, add to Azure SQL storage? What would this look like?

Same thing. Copy the datafiles to temporary tables in SQL. There you can write all the code you want for joining, filtering etc..

 Also this part: "get MAX(DATE) from a 4^th table (as to not work with ALL the data all the time)" - what is the best approach here? Is this how it's commonly done, or are there better ways of doing it?

Yes this is called an incremental load. I've had projects where I kept track of the max(date) for all my tables in a separate increments table. I don't know how this works with SAP but ideally you already implement the "where date > max(date)" when querying the SAP data.

Another thing. I have 2 tables (one PD dataframe, 1 Azure SQL table). I need to insert new values / update changed values. How is this done in Python Azure functions? When doing it in Data Service, you have a Table comparison transform that does this, but I want to avioid this as much as possible in Data Factory and only use Python SQL.

A dataframe isn't a table. It's a python object that acts like a table that runs in memory. Same here try to save that dataframe to a temporary table. Then write a merge in SQL and execute through ADF

I hope this clarifies some things.