Hi Lucas Medina
Welcome to Microsoft Q&A platform and thanks for posting your question here.
Approach One, as detailed in the YouTube video, involves using a Lookup activity to retrieve the maximum value of the watermark column from the destination table and then using a Copy activity to copy only the new or updated rows from the source table to the destination table. This approach is relatively simple and easy to implement, but it may not be the most cost-effective or efficient approach, especially if the destination table is large and has many partitions.
Approach Two, as outlined in the Stack Overflow answer, involves using a stored procedure to retrieve the new or updated rows from the source table and then using a Copy activity to copy them to the destination table. This approach allows for more control over the data retrieval process and can be more efficient and cost-effective, especially for large tables with many partitions. However, it requires more setup and configuration, including creating a stored procedure and setting up a linked service to connect to the on-premises SQL Server.
In terms of cost-effectiveness and efficiency, Approach Two may be the better option for large tables with many partitions, as it allows for more control over the data retrieval process and can be more efficient. However, for smaller tables or tables with fewer partitions, Approach One may be sufficient and easier to implement.
It's also worth noting that there may be other approaches or variations of these approaches that could be more suitable for specific scenarios. It's important to consider the specific requirements and constraints of the project when deciding on the best approach for incremental loading in Azure Data Factory.
Hope this helps. Do let us know if you any further queries.
If this answers your query, do click Accept Answer
and Yes
for was this answer helpful. And, if you have any further query do let us know.