Append two tables based on latest date column

I think you can achieve this by using any of the tools which you mentioned.

So I think you can use a notebook, data pipeline or dataflow gen 2.

You need to schedule it to run perhaps once each day.

You need to query both table A and table B. 

PS! You should not query the SQL Analytics Endpoint, because the SQL Analytics Endpoint can have some delay. You should query the Lakehouse tables / OneLake directly to get the current data.

Then you compare the max value in the Date column in each table.

If the max date in A is later than the max date in B, then you append A into B.

Otherwise do nothing.

What is the easiest way to implement this, depends on your current skillset. This can easily be done in Dataflow Gen2. It may not be the most performant and it may use more CUs on your capacity. But I think it should be easy to do this in Dataflow Gen2.