Snowflake overview – options, execs, & cons for information warehousing

A knowledge warehouse is the guts of an enormous fashionable group, offering insights into how its programs and customers work and performing as a basis for each quick and long-term methods. Like a coronary heart, it wants your consideration and common checkups. At instances, in response to kind of surprising occasions, it even wants a process to make sure its well being. In a current challenge, our information warehouse wanted simply that. Was Snowflake the treatment the information warehouse referred to as for? Learn the case examine and discover out.

When you ever labored with an information warehouse, you understand simply how advanced it might get whenever you get to deal with a number of databases and tons of legacy code from a wide range of exterior servers.

That’s additionally the place the story begins.

Background – why did we modify our information warehouse answer?

The challenge in query had been utilizing Amazon Redshift for some time as its main information warehouse. For essentially the most half, my workforce thought of it ample. The issues began when it was determined emigrate a Microsoft SQL server database to the warehouse. The database was dispersed, having been situated on a number of servers. With a view to entry it, we have to undergo a bunch of digital machines. Naturally, every digital desktop required new credentials.

To make issues worse, the aged dev, take a look at, and prod servers had been not according to one another. What you discovered on the take a look at server won’t have been the identical as what you bought on manufacturing. 

All of a sudden, we acquired:

  • extra processes,
  • extra information,
  • and extra customers.

Our instrument was not match for the duty at hand. The 2 essential points had been:

  • The dearth of computerized scalability pressured us to resize our assets manually. That took time. Usually, it was simply a number of minutes for a resize, however even that was an excessive amount of for this specific system.
  • Information entry administration was troublesome. Once more, we had been pressured to generate requests manually each time somebody from outdoors of our workforce wanted some information. And the variety of individuals in want of knowledge saved rising. 

The significance of the information warehouse for this particular enterprise was such that we couldn’t sit and repeat: “That is high-quality”.

The problem – looking for an ideal match

We now have entered a slightly lengthy section of choosing the right information warehouse for our scenario. As you’ll be able to see, the duty was large. It additionally had implications for the way forward for the entire system. We needed to take time to investigate our choices and select the most effective answer for this particular situation.

We needed one thing that meets three standards:

  • It has a personalized answer for our specific enterprise case.
  • There’s a potential for bettering the efficiency of our system.
  • It’s user-friendly, making it potential for a number of customers, together with much less skilled DevOps individuals, to make use of it effectively.

I imagine it was our workforce chief that first talked about Snowflake as a potential answer. Following this suggestion, we contacted the Snowflake workforce and acquired a demo. That kickstarted a slightly prolonged technique of figuring out the most effective information warehousing supplier.

I’m not going to elaborate on this course of. The purpose is that now we have chosen Snowflake. Within the subsequent sections, you’re going to search out out if we made the best choice.

What’s Snowflake precisely?

Snowflake is a fashionable and clever database accessible within the cloud. The Snowflake structure is a combination of conventional shared-disk architectures and shared-nothing architectures in a means that provides the simplicity of the previous and the efficiency of the latter.

One take a look at its web page will fill your head with many buzzwords well-liked within the cloud trade. However the factor is – within the case of Snowflake, they aren’t buzzwords in any respect. You actually do get:

  • information sharing,
  • zero-copy cloning,
  • information market,
  • hierarchical information.

What’s extra, Snowflake doesn’t set any limits for the variety of customers or the variety of information processes. You’ll not run out of assets to serve your queries or prospects. Sounds good, doesn’t it?

Cloud agnostic SaaS structure

It positive does, however let’s go away the speculation behind. What precise issues did Snowflake assist resolve?

The answer to information loading

The Redshift code we inherited from the earlier workforce was primarily written in Python, with a contact of Golang. In idea, we might have simply taken the present processes and inserted them into the Snowflake system and be accomplished with it. However that might not give us the development in information loading that we had been searching for.

As a substitute, we selected to make use of Snowpipe – Snowflake’s unique answer to information loading. The configuration was actually easy. All we needed to do was create a storage integration within the Snowpipe. As soon as configured utilizing Snowflake’s pipe, it downloaded information saved in our Amazon S3 cloud companies layer with none points.

We are able to do all that and extra utilizing the system’s UI mixed with a few easy queries. As soon as that is accomplished, all that is still is to maintain the AWS problem, that’s, including an occasion to the bucket and ensuring it falls into a correct SQS (Amazon Easy Queue Service) utilizing a correct ARN (Amazon Useful resource Identify), ultimately triggering the pipeline.

This information loading course of occurs nearly instantaneously. What’s extra, Snowflake routinely manages the entire thing, selecting simply the correct amount of processing energy. And it does so effectively.

Snowpipe service

Do you need to learn much more about information loading utilizing Snowflake? Check out Snowflake documentation.

Large promoting factors of Snowflake

Certainly, Snowflake provided us a reasonably easy answer to our essential downside. However the instrument has many different advantages, which we skilled whereas working with it.

Beneath, you’re going to search out out what the marketed promoting factors of Snowflake actually translate into within the warmth of a challenge.

Supported file codecs

Snowflakes means that you can add information in a bunch of various codecs:

  • JSON,
  • Avro,
  • ORC,
  • Parquet,
  • XML.

Snowflake loaded the information to our staging setting, which was good for our use case. We might select the information we needed to maneuver additional. We used dbt for information modeling. The processing of knowledge labored properly because of all-around good assist for the entire codecs provided by Snowflake.

These codecs embody:

Sort Format
Structured Delimited (CSV, TSV, and many others.)
Semi-structured JSON


Right here is an instance of a Snowflake question that will get worth from a given property:

Zero-copy cloning – how does it work?

I used to be tasked with transferring information from the outdated system to Snowflake. The brand new implementation needed to mimic the habits of the older course of one to at least one. In any other case, all the opposite processes depending on that one would fail. Once I acquired to it, it turned out that the take a look at information brought about some surprising issues. To work it out, I used to be to maneuver information from the manufacturing to a testing server.

And right here comes one other massive professional of utilizing Snowflake – it gives builders with a simple (and free of additional cost!) solution to clone information utilizing the next command:

What does this answer imply for us builders?

For starters, you get immediate entry to information within the new location with out having to pay for it. 

However there’s extra. Let’s say that you want to change some information on manufacturing, however first, you need to take a look at the brand new information to make sure that there shall be no surprises. There isn’t a downside when you have already got a mirror server that’s according to the manufacturing setting. When it’s not the case, the possibilities of doing all this with out issues drop considerably. That’s the place Snowflake involves the rescue.

The fundamental thought is defined within the infographic beneath:

Zero-copy cloning

You clone the manufacturing database after which ship a question to the cloned database. When every thing is alright, you’ll be able to clone it again to manufacturing. The information is overwritten and every thing works as speculated to. Too simple? That’s the way it’s speculated to be.

Information sharing? Don’t fear about information sharing

Snowflake’s information trade is your individual information hub. You’ve full management over who has entry to what and how much actions they will carry out on the information. It proved very helpful in our scenario.

We had a complete separate workforce engaged on a bit of the challenge. That they had entry to our database, however the processed that ruled had been completely different and custom-made for them. The processed moved information in a given format to an AWS S3 storage. Nothing too advanced, however it did generate upkeep prices.

Snowflake solved this downside as properly. Utilizing its information sharing capabilities, we might give entry to simply what we needed (and nothing extra) with only a few clicks.


One other massive asset of Snowflake is its Market.

It primarily consists of assorted units of knowledge that you would be able to clone to your individual information warehouse. You may also clone it on your shopper, or make it accessible to everybody.

Snowflake’s official web site describes it as follows:

“Market provides information scientists, enterprise intelligence and analytics professionals, and everybody who wishes data-driven decision-making, entry to greater than 1100 dwell and ready-to-query information units from over 240 third-party information suppliers and information service suppliers.”

That’s loads of information. However is it actually that helpful? That’s as much as you to determine. One factor is for positive – the sheer quantity of third-party contributions reveals what number of corporations have already taken curiosity in Snowflake and the way a lot potential it has for the longer term.

Visualizing Worksheet Information

Do you want tables, charts, and different information visualizations? When you do, then you’ll nearly positively come to get pleasure from Snowsight.

This information visualization instrument is kind of promising. Relying in your settings, it could visualize nearly every thing you want. You should use cookies and information derived from this supply, visualize engagement and website statistics, viewers engagement and website conversions, personalised content material and advertisements based on your settings, or higher observe outages and shield in opposition to spam fraud and abuse.

You should use it to visualise your information (e.g. website statistics to grasp consumer flows and different processes in your software), put it aside, and make it simply accessible to any customers, together with non-technical ones.

The advertising division will certainly discover it helpful, however the devs additionally ended up utilizing it loads. It proved sufficient for making visualizing our information high quality assessments in real-time.

What we achieved because of Snowflake

On the finish of the day, transferring our information warehouse to Snowflake proved to be an excellent transfer. It’s not that the answer is inherently superior to Redshift. It simply turned out to be a greater match for our challenge. That’s on account of various elements:

Our server has develop into extra environment friendly and scalable

  • Snowflake’s distinctive strategy to computerized scaling made it simpler for us to make use of simply the correct amount of assets. It took extra effort to do the identical with Redshift, particularly on the subject of Microsoft SQL Server.
  • Only for take a look at functions, I examined how lengthy it takes to implement a resize for a contemporary Redshift cluster. It’s about quarter-hour. That’s loads when your system handles massive visitors and runs out of assets.

Whether or not quarter-hour is suitable or not relies on the character of your corporation. We couldn’t afford it. Another answer to Snowflake could be to purchase an even bigger cluster earlier than it’s wanted. Nonetheless, in that case, your server prices will go up and you’ll pay for unused assets.

Our information visualization capability improved

  • The simply shareable information visualizations provided by Snowflake are actually helpful to us. Since our app requires fast response time, this can be a welcome change. Earlier than, we additionally might get all the information, however it took a lot time to search out and distribute it throughout the workforce.

The information is extra constant

  • The flexibility to course of information within the JSON format and the boldness that the information we load is similar as the information that leads to our S3 Bucket are each massive execs of utilizing Snowflake. Afterward, all it takes is to ship a request to SQL to mannequin your information and also you’re all set.

The information is extra accessible

  • The versatile entry choices make it simpler to distribute simply sufficient entry to all of the related people who they should full their duties. The information is offered in real-time.

The setup is easier

  • Because of the challenge, we removed a bunch of servers, every of which used a special know-how for database administration, and changed all of them with Snowflake.
  • This allowed us to realize full consistency between all of the completely different environments: Growth, Testing, Acceptance, and Manufacturing. Naturally, it didn’t occur in a single day. Total, information administration turned simpler, which in flip influenced the standard of knowledge delivered.

How did our DevOps workforce develop into one of many greatest of its sort in Poland? Find out about this and extra cloud info!

Not all roses – when is Snowflake NOT the best answer?

As you’ll be able to see, Snowflake actually made a distinction for us. However simply so that you don’t assume that it’s a good answer (there is no such thing as a such factor), I’m going to go over a few cons that I seen throughout my expertise with this service:

  • Snowflake entails using cloud suppliers corresponding to AWS, GCP, or Azure. It means that you’re going to be extremely depending on them. In case of a malfunction, you’ll need to attend for them to repair it on their finish and there isn’t loads it is possible for you to to do about it.
  • The Snowflake information cloud is extremely scalable, however it doesn’t present information limits for its computing or storage capability. Once you use the pay-as-you-go mannequin, prices could rack up. 
  • The service is gaining reputation fairly shortly, however the group isn’t that massive but. Once you come throughout some challenges, you would possibly come to the belief that no one has ever come throughout or written about them but. 

These aren’t dealbreakers, however they positively show a degree that Snowflake is the optimum answer just for very particular use circumstances: programs that require nice scalability and adaptability, even at the price of larger prices of use and upkeep.

The Snowflake information platform – classes realized

And that’s it for our expertise with the Snowflake digital warehouse. I’m leaving you with a few ideas.

  • Once I look again on the state of our system earlier than the migration, I’m actually impressed that we managed to drag all of it off. There have been so many processes, applied sciences and conflicts, and inconsistencies. However Snowflake made the entire course of fairly nice.
  • It gave us the chance to rethink our strategy to cloud storage and redo lots of our information processing workflows. Snowpipe and DBT proved very helpful. They made it simpler to mannequin and deploy our information, vastly bettering our database storage and processing capabilities.
  • The expertise with Snowflake’s information warehouse made me extra desirous about information analytics used as a part of cloud companies, which signifies that not solely did it make our system higher, however it made us extra captivated with what we do.

And if this isn’t cause to at all times search for a greater, extra becoming various for the duties at hand, I don’t know what’s!


Leave a Reply

Your email address will not be published.