Snowflake overview – features, pros, & cons for data warehousing

A knowledge warehouse is the guts of a giant trendy 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 latest undertaking, our information warehouse wanted simply that. Was Snowflake the treatment the information warehouse known as for? Learn the case research and discover out.

If you happen to ever labored with an information warehouse, you recognize simply how advanced it might get once you get to deal with a number of databases and tons of legacy code from quite a lot of exterior servers.

That’s additionally the place the story begins.

Background – why did we alter our information warehouse resolution?

The undertaking in query had been utilizing Amazon Redshift for some time as its main information warehouse. For probably the most half, my group 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. To be able 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, check, and prod servers had been not in step with one another. What you discovered on the check server won’t have been the identical as what you bought on manufacturing. 

Instantly, we obtained:

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

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

• The dearth of automated scalability compelled us to resize our assets manually. That took time. Sometimes, it was simply a number of minutes for a resize, however even that was an excessive amount of for this explicit system.
• Information entry administration was troublesome. Once more, we had been compelled to generate requests manually each time somebody from exterior of our group wanted some information. And the variety of folks in want of knowledge stored rising. 

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

The problem – looking for an ideal match

We now have entered a relatively lengthy section of choosing the right information warehouse for our state of affairs. As you’ll be able to see, the duty was huge. It additionally had implications for the way forward for the entire system. We needed to take time to investigate our choices and select the very best resolution for this particular situation.

We wished one thing that meets three standards:

• It has a custom-made resolution for our explicit enterprise case.
• There’s a potential for enhancing the efficiency of our system.
• It’s user-friendly, making it potential for a number of customers, together with much less skilled DevOps folks, to make use of it effectively.

I consider it was our group chief that first talked about Snowflake as a potential resolution. Following this suggestion, we contacted the Snowflake group and obtained a demo. That kickstarted a relatively prolonged technique of figuring out the very best information warehousing supplier.

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

What’s Snowflake precisely?

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

One have 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 clients. Sounds good, doesn’t it?

It certain does, however let’s depart the speculation behind. What precise issues did Snowflake assist resolve?

The answer to information loading

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

As an alternative, we selected to make use of Snowpipe – Snowflake’s unique resolution 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 will do all that and extra utilizing the system’s UI mixed with a few easy queries. As soon as that is executed, all that is still is to care for the AWS challenge, 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), finally triggering the pipeline.

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

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

Large promoting factors of Snowflake

Certainly, Snowflake supplied us a reasonably easy resolution to our principal 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 undertaking.

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 atmosphere, which was good for our use case. We may select the information we wished to maneuver additional. We used dbt for information modeling. The processing of knowledge labored nicely due to all-around good help for the entire codecs supplied by Snowflake.

These codecs embrace:

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 shifting information from the outdated system to Snowflake. The brand new implementation needed to mimic the habits of the older course of one to 1. In any other case, all the opposite processes depending on that one would fail. Once I obtained to it, it turned out that the check information precipitated 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 huge professional of utilizing Snowflake – it offers builders with a straightforward (and free of additional cost!) approach to clone information utilizing the next command:

What does this resolution imply for us builders?

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

However there’s extra. Let’s say that you might want to exchange some information on manufacturing, however first, you need to check the brand new information to make sure that there might be no surprises. There isn’t any downside when you have already got a mirror server that’s in step with the manufacturing atmosphere. When it’s not the case, the probabilities of doing all this with out problems drop considerably. That’s the place Snowflake involves the rescue.

The fundamental thought is defined within the infographic under:

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

Information sharing? Don’t fear about information sharing

Snowflake’s information change is your individual information hub. You’ve full management over who has entry to what and what sort of actions they will carry out on the information. It proved very helpful in our state of affairs.

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

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

Market

One other huge 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 too can clone it to your shopper, or make it accessible to everybody.

Snowflake’s official web site describes it as follows:

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

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

Visualizing Worksheet Information

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

This information visualization instrument is sort of promising. Relying in your settings, it may possibly visualize nearly all the pieces you want. You should utilize cookies and information derived from this supply, visualize engagement and website statistics, viewers engagement and website conversions, customized content material and adverts based on your settings, or higher observe outages and shield in opposition to spam fraud and abuse.

You should utilize it to visualise your information (e.g. website statistics to grasp person flows and different processes in your utility), 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 so much. It proved satisfactory for making visualizing our information high quality exams in real-time.

What we achieved due to Snowflake

On the finish of the day, shifting 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 undertaking. That’s resulting from a lot of components:

Our server has grow to be extra environment friendly and scalable

• Snowflake’s distinctive strategy to automated 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 relating to Microsoft SQL Server.
• Only for check functions, I examined how lengthy it takes to implement a resize for a contemporary Redshift cluster. It’s about quarter-hour. That’s so much when your system handles huge site visitors and runs out of assets.

Whether or not quarter-hour is appropriate or not is dependent upon the character of your corporation. We couldn’t afford it. An alternate resolution to Snowflake could be to purchase a much bigger cluster earlier than it’s wanted. Nevertheless, 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 supplied by Snowflake are actually helpful to us. Since our app requires fast response time, this can be a welcome change. Earlier than, we additionally may get all the information, nevertheless it took a lot time to search out and distribute it throughout the group.

The information is extra constant

• The power to course of information within the JSON format and the boldness that the information we load is identical as the information that leads to our S3 Bucket are each huge 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 those who they should full their duties. The information is out there in real-time.

The setup is less complicated

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

How did our DevOps group grow to be one of many greatest of its form in Poland? Find out about this and extra cloud information!

Not all roses – when is Snowflake NOT the correct resolution?

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 resolution (there isn’t any such factor), I’m going to go over a few cons that I seen throughout my expertise with this service:

• Snowflake entails the usage of cloud suppliers akin to AWS, GCP, or Azure. It means that you’re going to be extremely depending on them. In case of a malfunction, you have to to attend for them to repair it on their finish and there isn’t so much it is possible for you to to do about it.
• The Snowflake information cloud is extremely scalable, nevertheless it doesn’t present information limits for its computing or storage capability. If 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 huge but. If you come throughout some challenges, you would possibly come to the conclusion that no one has ever come throughout or written about them but. 

These usually are not dealbreakers, however they undoubtedly show a degree that Snowflake is the optimum resolution just for very particular use instances: programs that require nice scalability and suppleness, even at the price of increased prices of use and upkeep.

The Snowflake information platform – classes discovered

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 a lot of our information processing workflows. Snowpipe and DBT proved very helpful. They made it simpler to mannequin and deploy our information, vastly enhancing our database storage and processing capabilities.
• The expertise with Snowflake’s information warehouse made me extra fascinated 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 a superb cause to at all times search for a greater, extra becoming various for the duties at hand, I don’t know what’s!