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.
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:
The Snowflake Connector is supported for a Copy Activity with source/sink matrix table, mapping data flow, and lookup activity: