Handle all integrity checks in ETL, Snowflake is too Lazy for a reason

Snowflake, the blazing fast data warehouse tool, admits that it doesn't perform any integrity checks and asks you to handle them all in your ETL code. Though it does make Snowflake perform very fast, it is also a pain for the developers to handle them all. 

Snowflake allows duplicate records in a table with Primary Key

Snowflake doesn't enforce primary keys on a table. You can define a primary key on a table for your reference purpose. But it's not enforced, which means the table does allow duplicate records based on Primary Key

Snowflake doesn't check for Foreign key constraints

You can define a foreign Key in Snowflake. its only for the notational purpose. It is not strictly enforced. So you will have to handle all the parent-child relationships in your ETL while loading the table

Snowflake doesn't warn you for incorrect dates

One user reported He has inserted some rows, with a date field with Invalid date (i.e. 2018-09-31) meaning no 31st day in September, but Snowflake accepted it and inserted as 2018-10-01(next valid date) to the table. So the lesson learned, Validate dates before you load them into Snowflake

Snowflake is case sensitive

Weirdly enough, Snowflake is case sensitive. It doesn't bother to convert the case. So all the reporting users beware,

WHERE STATE = 'OHIO'; 

is not the same as 

WHERE STATE = 'Ohio';

Remember to refresh your views after you modify your table structure in Snowflake:

Did you add that column to that table, remember to refresh (Rerun view DDL) the view. (Even if it is a SELECT * VIEW). Snowflake doesn't refresh the view when a table definition updates.

Creating a new Snowflake Table?  Remember to provide your grant again

Even if you have provided a database level grant, you will have to rerun the grant, every time you create a new table

These are few of the nuances that you have to deal with in Snowflake, But the performance that you get for the tradeoff that you are making is well worth it. 

What are few of the weird things that you noticed in Snowflake, tell us in the comments!

Mike-Barn

posted on 30 Oct 18