Snowflake: Unique Features

by Tommaso Ferri

Lets take a look to some cool and unique Snowflake Features: let it snow!

This week I took some time to analyze some Snowflake functions that can be really, really useful for a start up or a scale up .

Snowflake as a cloud warehouse is a powerful and cheap solution for small and medium company that wants to start working properly with their data. Is also suitable for larger company because of its relative low cost and for its security features.

There are some killer-features that must be mentioned:

  • Comprehensive Data Protection
  • Time Travel
  • Zero-copy Cloning
  • Query Across Databases

Comprehensive Data Protection.

Snowflake is Safe, with a capital s. When you upload your data, it will be stored across 3 different physical datacenters: even if a datacenter will be "destroyed" your data will be safe. Pretty awesome!

Time travel.

What a feature.

In a Traditional Data Warehouses accidental drops and edits are not recoverable, even if some of your data can be restored, it can't be restored to a specific time and often the process of restoring can be time consuming and very expensive.

With Time travel you can access historical data at any point within a defined period, and you can restore any objects (databases, schemas, warehouses, schemas...) with simple SQL queries that are simple to learn and to write.

You can time travel by:

Time Offset: OFFSET=>time_difference

(Example : SELECT * FROM yourtable AT (OFFSET=> -60*5) ;
this query will show you all the content of your table 5 minutes ago.
-60 is 60 seconds, -60*5 is 5 minutes).
This is really useful if you unintentionally drop some records from your table.

Timestamp: TIMESTAMP=>timestamp

SELECT * FROM yourtable AT (TIMESTAMP => '2021-05-13 10:30:34.068'::timestamp)
This query is using a date-time parameter to travel back in time

Query: STATEMENT=>id

Where ID is the Number of the query that you want to travel to.

If you are using Tableau there is a way to control time travel settings via custom SQL commands. With this line:

SELECT *
FROM "yourdatabase"."yourschema"."yourtable"
AT (OFFSET=> -60*<Parameters.Timetravel Minutes>)

You can quick create the parameter trough the same Custom SQL Window that you can control directly in your sheet or dashboard.

Zero Copy Cloning

Many tasks like testing and quality testing requires copies of your data
that must be copied physically from the original source to the new one.

This process is  time consuming, expensive, and you have to pay for the space that your copy is using.

With zero copy cloning you can create instant copies of any object (Databases,Schemas, and Tables), and it usually takes few seconds ( more for larger tables).
It took  me 726ms to create a complete clone of a 1.1gb table. Pretty awesome.

A Cloned object will not require additional storage: it is created at a metadata level.

Costs: You can do tests and development without any additional storage cost.

Zero Copy Cloning can also be paired with Time Travel: you can create data as it existed in the past.

This is how you clone an object

create OBJECTTYPE   NAME_OF_CLONED_OBJECT
clone
NAME_OF_ORIGINAL_OBJECT;

Example of a table clone:
create table test_table_clone clone test_table

This will create a Clone of the table named test_table named
test_table_clone .

If this looks easy well...it will save you a LOT of time and a lot of money!

Query Across Databases

Traditional databases are both physically and logically separated, because of the separation is impossible to using a single query to utilize data from both the database at the same time. Third Party tools are required for this operation.

In snowflake different databases are physically and logically integrated in one single Snowflake account, and they can be used with no third party tools, in a quick and easy to call SQL Query.

Lets take a look to this Query.

create table jointable as (
select table1.columnname1, columnname 2 , .....
from database1name.schema1name.table1
join database2name.schema2name.table2
on table1.columnjoinkey1 = table2.columnjoinkey2);

I am Joining table1 and table2 (from different databases) on a key column, and saving the result to a new table called Jointable.

In one single SQL Status (wrote in 1 minute) you ended with a table that could have take at least 4 more passages within a traditional warehousing environment.

This results are not actually obtainable with some concurrent technologies!

After all this cool features.....Start to explore and....

LET IT SNOW!