You could indeed use ADF to copy the data from cloud to on-prem.

However, depending on the size of the data, this can take a while.

I use the same pattern, but for aggregated processed data, which is not an issue at all.

You could also look at Azure Synapse Serverless or Delta Sharing, or even SQL Hybrid.

But if the data MUST be on-prem, a copy seems the only way.

In that case I'd try to use Polybase for the copy (I think recent versions of Sql Server can use this).

But I would strongly suggest to not move raw data to on-prem.

There are several possibilities to query cloud data using SQL (maybe not SQL server but Azure Synapse Serverless also uses t-sql f.e.)