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:

do the transformation in ADF
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.

What are the platforms that don't integrate with Snowflake? Ideally, you'd just leave the data in Snowflake table, so I'm genuinely interested in what doesn't connect directly. — Mike Walton
My understanding is that Snowflake has good data sharing capabilities. Why not leave the data in snowflake and utilise them? — Nick.McDermaid
@MikeWalton - the transformation will create some business logic that needs to be shared across numerous teams, who will not necessarily be using Snowflake. Therefore, I need the transformed data to be stored in Azure, so that whoever wants to use the data can have access without being forced to access it through Snowflake. In terms of the platforms, currently Qlik is being used by one team but, in general, I don't want to force the use of Snowflake or be tied to only use platforms that integrate with it — the_james

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2021-01-05T21:45:07

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

Transformation in Snowflake or Azure data Factory?

1 Answers