SQL Server to Snowflake Use Case: Demonstrating SSIS Ingest & Transformations - Part 2
By Puneet Lakhanpal | Sr. Data Architect
In Part 1 of the SQL Server to Snowflake Use Case, a client example was discussed around how Snowflake can help accelerate their current SQL Server data transformations while keeping the costs low. The article also explored how it can enable better analytics and reporting through Live Tableau connections to Snowflake. For this evaluation, an SSIS based data ingestion and transformation framework was designed to help the client utilize their existing investments in their ETL frameworks, focusing on the problem they were trying to solve—how to bring down transformation time to a reasonable time frame while keeping the costs low. Lastly, Part 1 went into the details of how the SSIS ingestion pipeline was designed into Snowflake.
In Part 2 of this Use Case, we go into the details of how the existing SQL statements were reutilized with minimal change to perform transformations in Snowflake. As you will learn, there are several ways this transformation architecture could be enhanced, such as integrating secrets with Azure Key Vault. This article focuses on how we avoided re-writing SQL server stored procedure scripts into Snowflake’s native JavaScript code and achieved blazing transformation performance—not just technically—but also in a way that allowed the customer to keep their existing ETL investments. We did this by enhancing their existing code base in both SSIS ingest and transformations, which is discussed in-depth in the article.
Part 2 also shows how we brought down the data transformation time from 34 hours in SQL Server to 4.33 minutes in Snowflake, at a fraction of the cost, through traditional tools like SSIS. This equated to around $18.12 in Snowflake’s Business Critical Edition following Snowflake’s Cost calculator ($4 per credit).
Additionally, the client was able to perform much more complicated calculations within reasonable latencies from Tableau over a live connection to Snowflake, when compared with connections to Tableau Extracts or Hadoop.
We believe it was only possible due to the breadth of features Snowflake’s data platform provides, which always accelerates any customer’s journey to the Cloud. If we haven’t said it enough yet, we value our partnership with Snowflake and always look forward to helping customers on their journey from data to insights.
To learn more about how Trace3’s Cloud Services Group can help you visit: https://www.trace3.com/cloud-solutions and don’t forget to download Part 2 for the full Use Case!
In Part 1 of the SQL Server to Snowflake Use Case, a client example was discussed around how Snowflake can help accelerate their current SQL Server data transformations while keeping the costs low. The article also explored how it can enable better analytics and reporting through Live Tableau connections to Snowflake. For this evaluation, an SSIS based data ingestion and transformation framework was designed to help the client utilize their existing investments in their ETL frameworks, focusing on the problem they were trying to solve—how to bring down transformation time to a reasonable time frame while keeping the costs low. Lastly, Part 1 went into the details of how the SSIS ingestion pipeline was designed into Snowflake.
In Part 2 of this Use Case, we go into the details of how the existing SQL statements were reutilized with minimal change to perform transformations in Snowflake. As you will learn, there are several ways this transformation architecture could be enhanced, such as integrating secrets with Azure Key Vault. This article focuses on how we avoided re-writing SQL server stored procedure scripts into Snowflake’s native JavaScript code and achieved blazing transformation performance—not just technically—but also in a way that allowed the customer to keep their existing ETL investments. We did this by enhancing their existing code base in both SSIS ingest and transformations, which is discussed in-depth in the article.
Part 2 also shows how we brought down the data transformation time from 34 hours in SQL Server to 4.33 minutes in Snowflake, at a fraction of the cost, through traditional tools like SSIS. This equated to around $18.12 in Snowflake’s Business Critical Edition following Snowflake’s Cost calculator ($4 per credit).
Additionally, the client was able to perform much more complicated calculations within reasonable latencies from Tableau over a live connection to Snowflake, when compared with connections to Tableau Extracts or Hadoop.
We believe it was only possible due to the breadth of features Snowflake’s data platform provides, which always accelerates any customer’s journey to the Cloud. If we haven’t said it enough yet, we value our partnership with Snowflake and always look forward to helping customers on their journey from data to insights.
To learn more about how Trace3’s Cloud Services Group can help you visit: https://www.trace3.com/cloud-solutions and don’t forget to download Part 2 for the full Use Case!
Tags:
Data Management