Recent years, Snowflake DB has become more and more popular because of its creative features. A lot of companies who are leaders in their business domain have started their journey with Snowflake, such as Sony, Logitech and Electronic Arts. So, what are these “features” that make Snowflake such attractive and competitive? In this article, I will pick up some points which make me love this database management system. Rather than measuring its performance and abilities, I will focus on the unique features that Snowflake has because it is a true “cloud-based” database.
What I will skip
I will skip some features that have been talked too much everywhere on the Internet, such as the creative architecture, high performance and pricing model. Of course, these are what Snowflake proud with. However, as a consultant, I have seen too many tools and software that were competed with each other in the “existing” dimensions, and no one of them can be guaranteed to be the top one forever.
Specifically, most customers will not likely be interested in its creative architecture, if they are not technique enthusiasts. For the performance, in fact, none of the popular database management systems will be very bad. Finally, the price is not always the key factor that makes people buy.
Therefore, what I am doing here, is trying to let you know what are the features that Snowflake may let you WOW. Also, if applicable, I will compare Snowflake with 2 other popular cloud-based DBMS, Azure Synapse Analytics (former Azure Data Warehouse) and AWS Redshift, to see how Snowflake makes use its “cloud-based” icon.
Zero efforts for development environment
Snowflake is not the only cloud-based DBMS, but it is probably the only one providing such a comprehensive UI that supports all popular web browsers.
Azure Synapse Analytics:
Need to download SQL Server Management Studio
Need to download a third-party SQL client such as SQL Workbench
In your web browser, go to the URL and press enter
It’s not only about downloading management software. Think about we you need to work on a new machine…
Retain your worksheet
I believe I am not the only one who use to open several worksheets with many SQL statements for different purposes. Most of the time I will NEVER shut down the SQL client and my laptop for many days until reaching a milestone of the project, because I just don’t want to lose my SQL statements. I knew I can save them into script files and import next time, but just lazy, and re-import those cause some headache, too.
With Snowflake, be calm down enough to close your browser tab any time. Whenever you open the URL, your worksheets will always be there, even you change to another machine because everything is stored in the cloud.
Change your role
If you are an admin or a guy who owns master permissions, you probably will be frequently asked to grant permissions to other users. When using other DBMS, you will need to do your job and then confirm with the user that you granted permission to, whether the resource is already available.
With Snowflake, simply switch your role to see what permission this role have. This will also let you easily testing whether you have over-granted the permission.
SQL statement execution history
It is not uncommon sometimes we want to see what we have done before during the development. At least I used to write a lot of SQL statements for testing purposes, and then I got the query working, so I deleted the testing code. But later on, I may realise that I want to see what I have actually tested.
With Snowflake, all your query history will be retained for 1 YEAR. Whenever you want to view what you have done before, simply go to the history to find it.
Configure notifications to email and SMS
As a cloud-based DBMS, Snowflake makes it extremely easy to configure your email or SMS notifications.
Azure Synapse Analytics:
Email: ask your IT department to provide you with the SMTP server and configure the white list for you. Then, configure your SMTP in Mail Profile
SMS: configure your Azure Action Group
Need to create a Kinesis Data Firehose Delivery Stream and then configure it
Fill in your email address and/or phone number. Then, enable it.
You can even enable MFA (Multi-Factor Authentication) in 30 seconds. Just provide your phone number, receive a verification code and fill it back.
This is the most WOW feature when I started to explore Snowflake DB. Specifically, with the Standard Edition, you can enable a table with “time travel” and have 1-day data retained no matter how you deleted or updated it. With the Enterprise Edition, the time travel is up to 90-day.
How can we use “time-travel” feature? Easy!
AT (timestamp => '<timestamp>');
Also, I have never seen such expression in any other DBMS:
UNDROP TABLE <table_name>;
Think about any other DBMS, you will need a well-rounded plan for how your data will be backup regularly, just in case for the disaster recovery, and then automate everything. You may need at least 1–3 developers/DBAs to do this job for at least several weeks and test it. But with Snowflake, not necessary at all.
With Snowflake, you can clone a table, a schema or even a database in no time and occupying no space. This is because the cloned table actually create pointers (point to the stored data) but not the actual data. In other words, the cloned table only has the data that is different from its original table.
Why do we need to clone a table?
- When you want to perform some experiments with data but don’t want to hurt it by mistakes.
- When you want to capture data in a snapshot at a certain time.
- When you want to get a separate copy of a table’s history, then use clone together with time travel!
- When you want to share production data with others, but don’t want to directly give access to the production database.
Quick Integration with Cloud Storage
If you use one of the biggest 3 cloud providers: Azure, AWS or GCP, you may use their cheap storage services Blob Storage, S3 or GCS. You can use 1 minute to configure your Snowflake to be able to import data from these storages.
Automatically Data Ingestion (SnowPipe)
Snowflake, as a DBMS, is not designed as a comprehensive ETL tool, but if your data ingestion is that complicated, it definitely will satisfy your requirements, with very quick implementation.
For example, if you have another process to put data from whatever data sources to cloud storage (e.g. Azure Blob Storage), and then what you need to do for automating the data ingestion into Snowflake is:
- Create an Azure Event Grid to emit events when a blob is created in Blob Storage
- In Snowflake, create an “integration” with the Event Grid (can be done in a single SQL command
- In Snowflake, create a SnowPipe to monitor the “integration” and set the destination table
CREATE PIPE <snowpipe_name>
AUTO_INGEST = TRUE
INTEGRATION = <integration_name>
COPY INTO <destination_table>
FILE_FORMAT = (type = '<e.g. JSON>')
Then, whenever there is a new file been created in the Azure Blob Storage, the event will be emitted, and SnowPipe will aware and run
COPY INTO command to ingest the data into the destination table.
If you want to know more about this, please check out one of my previous post here: https://medium.com/@qiuyujx/building-snowpipe-on-azure-blob-storage-using-azure-portal-web-ui-for-snowflake-data-warehouse-f0cdd7997250
Easiest Scheduled SQL Tasks Ever
If you want to have your SQL statement running regularly, I found it is never such as easy before Snowflake was invented. What you simply to do is:
CREATE TASK <task_name>
WAREHOUSE = <warehouse_name>
SCHEDULE = <frequency (e.g.'10 MINTUE')>
Do you still remember how you need to do this in SQL Server? Enable SQL Agent Windows Service, create a new task, and then fill in several pages of forms…
If you want, you can also use SnowPiple and Tasks together to do some complicated ETL, it is not “drag-n-drop” though, but you also don’t need to involve a separated tool, it’s all in your DBMS.
Written by Christopher Tao, Principal Consultant@AtoBI Australia
Follow Christopher on LinkedIn Here