sql - Transformation in Snowflake or Azure data Factory?

Question

I'm very new to Snowflake, so forgive me if the answer is obvious.

I am loading the data from on-prem into Azure using Data Factory, and then ingesting into Snowflake using COPY INTO. However, I need to enable access for some of the transformed data to other platforms, meaning that if I perform transformation in Snowflake, I'll need to create an external table in Azure (essentially pushing this data back to Azure so other platforms can access it).

As we don't particularly want to introduce a new tool, I have two options for our fairly basic transformation:

1. do the transformation in ADF
2. do the transformation in Snowflake in SQL scripts and then create an external table so other teams can access the data using other tools (these platforms don't integrate with Snowflake)

Are there any major drawbacks to option 2 apart from increased storage costs?

I'm trying to weigh up the following: maintenance effort (our team's skills lie in SQL not ADF), cost, and performance.

Any advice would be appreciated.

Answer 1

As stated in the question, there are many possible answers for this scenario - with my favorite being the second one ("do the transformation in Snowflake in SQL scripts and then create an external table so other teams can access the data using other tools").

If you need to make the results of these transformations available on Azure storage, Azure Data Factory supports this natively:

• Copy data from Snowflake that utilizes Snowflake's COPY into [location] command to achieve the best performance. https://docs.microsoft.com/en-us/azure/data-factory/connector-snowflake#supported-capabilities

Or you could manage this inside Snowflake using the same COPY INTO that ADF uses.

Let me add a couple screenshots from the Snowflake webinar "Data Warehouse or Data Lake? How You Can Have Both in a Single Platform":

https://resources.snowflake.com/webinars-thought-leadership/data-warehouse-or-data-lake-how-you-can-have-both-in-a-single-platform-3