Question: I am looking to move files from a couple of file servers (accessed via SFTP) into Snowflake directly using the Python Snowflake connector. 

I have considered using Data Factory (ADF) to pull the files into blob storage and then to create an Azure stage to move the data into Snowflake. However, I am curious to see if there is a way to do this without storing the data in blob storage? In other words, can I move the files directly into Snowflake without storing them elsewhere first? I don't see a way to do this in ADF without storing in Blob Storage and the Snowflake documentation indicates that stages can be created via Azure/AWS or from a local file system, but can this be done for a file server that needs accessed via SFTP? I've been working on this with the Python connector but it still seems like the data must be stored somewhere else first (either local hard drive or blob storage) before it can be staged into Snowflake.

Answer: 
Assuming you want to use Snowflake's bulk-load mechanisms (so `COPY` entire files, not `INSERT` single rows), the file always needs to end up in the blob storage at some moment.

* you can move the files from SFTP to blob storage yourself and then use a `COPY` in Snowflake
* you can download the files to local filesystem, and then use Snowflake's `PUT` command to copy the files to the staging location, and then use `COPY` again

There is no way to create a stage that would point to SFTP.

Some more info [here](https://docs.snowflake.net/manuals/user-guide/intro-summary-loading.html#data-file-details).

Note: In theory you *could* fetch the data from SFTP e.g. in Python and then use `INSERT` to insert the rows through SQL. Then you do not use the stage, but it's more complex and certainly not worth the effort.
In the last couple of months, Azure Data Factory now has native integration with Snowflake via the Snowflake connector. So the current answer would be that there is a connector that you can use in the ADF portal.

*ADF Now Supports Data Integration with Snowflake*:

* <https://techcommunity.microsoft.com/t5/azure-data-factory/adf-now-supports-data-integration-with-snowflake/ba-p/1447370>

*The Snowflake Connector is supported for a Copy Activity with source/sink matrix table, mapping data flow, and lookup activity*:

* <https://learn.microsoft.com/en-us/azure/data-factory/connector-snowflake>