Snowflake

If you want to learn a lot about how Snowflake works internally, it’s worth spending some time reading the ACM whitepaper that described the service. It describes the overall structure of the service, and then describes how data is stored, followed by a description of how the encryption of the data within the service is accomplished. (I’ll talk more about the management of encryption keys in a later blog post).

2019-11-08

Similar to SQL Server, Snowflake has ways of creating programmable objects. But the way they work, and the way they are created is quite different. Functions Functions are the most similar. You can create them in two ways: Javascript T-SQL I like the idea that you can choose which language to write code in, and that they both end up extending the Snowflake SQL language pretty much the same. Some code is better written in T-SQL and other code is better written in a higher-level language.

2019-11-01

When you are loading data into Snowflake, it’s really important to achieve the maximum parallelism that you can. You want as many files loading in parallel as you have. I mentioned in earlier posts that the number of servers that you have, and the size of each of those servers, will determine the number of processor threads that are available to you from your virtual warehouses. It would be pointless to have 32 processor threads waiting to load your data, and you provide Snowflake with one large file to load.

2019-10-25

In a previous post, I talked about stages. They are internal or external cloud storage locations that you can use the COPY command to copy data into database tables from or use the COPY command to export data from database tables. Now if you are using external stages, they’re just standard storage accounts in AWS (S3), Azure Storage, or Google (GCS). You can use whatever tools you want to get files from other locations (like your local file system) to/from these accounts.

2019-10-18

One thing that I quite like about Snowflake is the way it cleanly works with a wide variety of file formats. Obviously this changes over time but at the time of writing, you could COPY from the following source file formats: CSV JSON AVRO ORC PARQUET XML There are also quite a number of options for configuring how these are used. Apart from the obvious options like record and field delimiters, skipping rows, etc, one of the most important of these options is compression.

2019-10-11

Snowflake has the normal options in its SQL language for using INSERT statements to add data to a table. But it also has bulk options for rapidly adding a lot of data to a table via the COPY command. The same command can be used for bulk export as well. A curious aspect of this though, is that because it’s a cloud-only database system, you can’t just use COPY to get data to/from a local file system.

2019-10-04

There are many things I like about Snowflake. How they handle case and collations is not one of them. There are currently no rich options for handling case like you have in SQL Server, with detailed options around both collations, and case sensitivity. I’ve previously written about how I think that case-sensitivity is a pox on computing. I see absolutely no value in case-sensitivity in business applications, and a significant downside.

2019-09-27

Authentication in Snowflake is based around user identities. That’s very similar to what we have today with SQL Server authentication (i.e. SQL Server logs you on), as opposed to Windows Active Directory authentication (i.e. Windows AD logs you on), or Azure Active Directory authentication (i.e. AAD logs you on). I can create users if I’m a member of either the SECURITYADMIN or ACCOUNTADMIN roles. Snowflake has a single specific password policy:

2019-09-20

Security in Snowflake is quite different to what’s implemented in SQL Server. In SQL Server, objects have owners that can either be specific people, or as occurs when a dbo person creates an object, it is owned by the dbo role. In Snowflake, objects in the database also have owners, but the owners can’t be users, they are always roles. When you create a database, there are four pre-defined roles:

2019-09-13

Like most products, Snowflake comes in a number of editions, and you can see the current editions in the main image above. (Keep in mind that they could always change at any time and to check their site for the current options). First thing I need to say is that I really like the way that most of the SQL code surface is pretty much identical across editions. I wish that was complete coverage but it currently doesn’t include materialized views.

2019-09-06