By: Koen Verbeeck
Conclusion
In this tutorial, we went through the basic features of Snowflake, a cloud-only data warehouse provider. We saw that it brings some innovations to the table, such as instantaneously scalable compute warehouses, time travel for every table and a couple of SQL syntax features which SQL Server might be jealous of.
But of course there’s much more. We did not cover more advanced features of Snowflake, such as security and authorization, data sharing, stored procedures, functions and so on. The Snowflake website has good documentation on all of these features, there’s Snowflake University for on-demand training and there is also certification.
Even though Snowflake is easy to work with and has some exciting features, there are also a couple of downsides:
- Stored procedures (which are relatively new in Snowflake) are a combination of JavaScript and SQL. In the beginning, UDF’s (user-defined functions) were JavaScript only, but now SQL only is supported as well.
- There’s currently no dynamic SQL. This makes it harder to script things out.
- The community around Snowflake is quite small and not as vivid as the SQL Server community. Snowflake is of course still a young company.
- The app ecosystem around Snowflake can also use some growth. If you’re used to working with SQL Server, you’re actually kind of spoiled. We have SSMS, Azure Data Studio, multiple vendors that support automating back-ups, database devops, source control, schema compare and so on. With Snowflake, this ecosystem is much smaller and in some areas even non-existing. Chances are you have to build something yourself.
- Snowflake is zero-management, but also zero options. There are no indexes you can create, no primary keys or foreign keys enforcement (you can create them but they are not checked), no query hints et cetera.
As mentioned, Snowflake is quite young so we can expect to see growth in those areas. Snowflake is an interesting product which certainly shows lots of promise.
Additional Information
- Like most cloud offerings, Snowflake is continuously evolving. Take a look at the release notes to see what changes each month.
- Here at MSSQLTips.com there are other tutorials about SQL and SQL Server. You can find them all here.
