Basically as per MSFT Docs : https://learn.microsoft.com/en-us/azure/data-factory/connector-snowflake?tabs=data-factory#direct-copy-from-snowflake
If sink data store and format are natively supported by the Snowflake COPY command, you can use the Copy activity to directly copy from Snowflake to sink.
Since Azure SQL DB is not supported, you would have to enable a staging layer of Azure blob storage as below flow:
Snowflake>> Azure blob Staging layer >> Azure SQL database
you need to enable a staging layer property in copy activity.
So with ADF native features, it is not possible to load directly.