How to load data from On-prem to Snowflake using ADF in better way

Hi,

My use case is as follows:

Our data source is an On-prem SQL Server, and it serves as our production database. Currently, we are building reports in Power BI and utilizing Snowflake as our data warehouse. I aim to extract 10 to 15 tables for my Power BI reporting into Snowflake, specifically wanting to construct a SCD Type 1 Pipeline, without the need to retain historical data.

To facilitate the data transfer from On-Prem to Snowflake, we are leveraging Azure Data Factory, with blob storage set up in Azure. We already have a Self-hosted runtime in place that connects to Data Factory.

Currently, I've employed the For Each loop activity to copy 15 tables from On-prem to Snowflake. The pipeline is scheduled to run daily, and each time it executes, it truncates all 15 tables before loading the data. However, this process is time-consuming due to the volume of data, especially since our On-prem SQL server is a legacy database.

My questions are as follows:

1. Is there a more efficient approach within Data Factory for this task? What are the best practices recommended for this type of case study?
2. Dataflow is not functioning with the Self-hosted runtime; how can I activate Dataflow for an On-prem database?

I would greatly appreciate any advice on the correct solution for this or pointers to relevant documentation or blogs.