This article is the first in a planned series on what it’s like to have Snowflake as your data warehouse/data lake. In teaching “Zero 2 Snowflake” workshops, I have found that people have difficulty wrapping their heads around what it means to have a groundbreaking technology that is truly “designed for the cloud” database.
It’s easy to get involved in the technical details of the architecture and miss the proverbial forest for the trees. This post describes how Snowflake’s architecture spells an end to run ETL at night and on the weekends and how it drastically reduces the time taken for ETL processes to run.
The Snowflake features that help achieve all these are:
Immutable data storage
Pointers to the data
On-demand compute clusters that you only pay for as you use
Separation of data from computing
Performance scales near linearly by increasing the size of your cluster
Why have companies been running ETL processes at night and on the weekend for all these years? The ETL process would run on the same computers running the reports. Also, for traditional databases, writes and reads contend with each other. Nobody wants the ETL process to bog down the system.
Snowflake data is stored in an immutable database. There are only writes. Here, updates are new data that is written while pointers to this data are changed. This enables another great Snowflake feature, time travel, that won’t be addressed in this blog. From an ETL perspective, the lack of contention between the writes and updates allows ETL to be run at any time.
The ability to spin up or resume a compute cluster (Snowflake calls them compute warehouses) at any time and the fact that compute scales independently of storage means that “regular business use” of the data is handled by different compute clusters than ETL. When it’s time to run ETL, all you have to do is spin up a cluster just for the ETL. This takes only seconds, and Snowflake doesn’t charge when you aren’t using a compute cluster.
If an ETL process would take ten hours with a 1cpu “cluster,” it should take five hours with a 2-node cluster, two-and-a-half hours with a 4-node cluster, and so on. The cost of the cluster for use doubles each time as well. But you are only paying for the cluster when you are using it. So, running a ten-hour process on a 1cpu “cluster” costs the same as running a five-minute process on a 128-node cluster. During development, you can test to find the “point of no further advantage” or at what size the cluster is optimum. Though, one can scale without paying for 365x24 costs for that capacity.
Who in ETL hasn’t come to work in the morning only to find that a six-hour ETL processed failed during the night? The task of fixing the bug and then rerunning the load without impacting the business might be impossible. There isn’t time to rerun before the users come in, expecting to run their reports. With Snowflake, you could shorten the time a rerun would take and rerun the process without impacting the users running reports.
This radical ability to scale performance and only pay for what you use has made Snowflake data warehouse “the” place to do all the processing that can be done. In essence, ETL has become ELT – Extract, Load, and then Transform.
You can move your data into Snowflake, then transform it with the massive scale and cost advantages of the Snowflake cloud database. But that’s a story for another blog. For now, living with Snowflake means sparing the ETL developer from working so many nights and weekends.
Contact Us Today
What Is an SQL Query Engine? SQL query engine architecture was designed to allow users to query a variety of data sources within a single query. While early SQL-based query engines such as Apache Hive allowed analysts to cut through the clutter of analytical data, they found running SQL analytics on multi-petabyte data warehouses to be a time-intensive process that was difficult to visualize and hard to scale.Explore
A Winning Base for Successful Digital Transformations When it comes to developing a successful digital strategy, it is not just corporations planning to maximize the benefits of data assets and technology-focused initiatives. The Government of Western Australia recently unveiled four key priorities for digital reform in its new Digital Strategy for 2021-2025.Explore
Engage Your Workforce with a Modern Employee Intranet Solution The employee intranet has changed significantly since it was first introduced in the early 1990s. What started as HTML-based static portals have now evolved into intuitive communication tools complete with search engines, user profiles, blogs, event planners, and more. Today, many organizations are taking a second look at employee intranets to bridge gaps between teams, build company culture, centralize information, increase productivity, and improve workflow.Explore
Adopting emerging cloud technologies, consolidating resources, and improving processes is the key. “IT no longer just supports corporate operations as it traditionally has but is fully participating in business value delivery. Not only does this shift IT from a back-office role to the front of business, but it also changes the source of funding from an overhead expense that is maintained, monitored, and sometimes cut, to the thing that drives revenue,” said John-David Lovelock, research vice president at Gartner.Explore
Deliver Powerful Insights Instantaneously with Federated Queries - No Matter Where Your Data Resides The concept of federated queries isn’t new. Facebook PrestoDB popularized the idea of distributed structured query language (SQL) query engines in 2013. Over the years, AWS, Google, Microsoft, and many others in the industry have accelerated the adoption of a distributed query engine model within their products. For example, AWS developed Amazon Athena on top of the Presto code base, while Google’s BigQuery is based on Cloud SQL.Explore
What is Unstructured Data? Almost 80% of the data that enterprises and organizations collect is unstructured - data without a set record format or structure. Unstructured data includes data such as emails, web pages, PDFs, documents, customer feedback, in-app reviews, social media, video files, audio files, and images.Explore