Snowflake

Overall the SQL language for Snowflake is somewhat of a subset of what’s available in SQL Server with T-SQL. But there are some commands that really are useful. UNDROP is one of them. I’ve talked in earlier posts about how Snowflake stores data in immutable micropartitions, and in other posts mentioned the concept of timetravel. Well, an added advantage of having older data still accessible is that you can quickly recover from “incidents”.

2020-01-17

I’ve been working to convert our SQL Down Under sample databases like PopkornKraze across to Snowflake. One of the areas that I ran into real challenges with was sequences. The good news The syntax for creating and accessing them is basic enough. CREATE SEQUENCE "SequenceName"; is enough. (Note the double-quotes are mine as I don’t want the name auto-wrapped to SEQUENCENAME). And there are some of the same options that SQL Server has:

2020-01-10

I posted last time that I’d completed my Part 1 eBook on Core Concepts. The Part 2 eBook is on the differences in the SQL language between Snowflake and SQL Server. This next series of blog posts will focus on the query language, particularly on DML (data manipulation language). DDL (data definition language i.e. CREATE, ALTER, DROP) differences will be covered in the Part 3 series on administration. Snowflake has quite a rich SQL language but it’s quite different to the T-SQL offered by SQL Server in many ways.

2020-01-03

I’ve had a lot of great feedback on my series of blog posts about Snowflake, and explaining the concepts for an existing SQL Server audience. Just out for Christmas is a new free eBook for our SDU Insiders. I’m preparing a three part series on Snowflake for SQL Server users: Part 1: Core Concepts (available now) Part 2: SQL Language (coming soon) Part 3: Administration (sometime after that :-)) You can get part one now!

2019-12-27

As with most products today, Snowflake has substantial handling of encryption to protect client data. All editions claim to provide “Always-on enterprise grade encryption in transit and at rest”. In Transit First, for connections, HTTPS and TLS 1.2 are used throughout the system. If the customer is using external stages (holding locations in cloud-storage accounts), the data can be encrypted while stored in the stage, and then travel encrypted into Snowflake.

2019-12-20

In the previous post in this series, I talked about how historical data in Snowflake can be accessed by a concept called Time Travel. But Time Travel isn’t the only way to get to historical data. In Snowflake, Fail-safe is a system that provides protection against system failures and other nasty events like hardware failures or serious security breaches. Immediately after the Time Travel retention period ends, a Fail-safe period begins.

2019-12-13

One really important concept in data warehouses is the idea of versioning. Imagine that I have customers broken into a set of business categories. And then I change which customers are in which categories. For so many transactional systems, if I run a sales report for last year, the customers would now appear in the current business categories, rather than the ones they were part of last year. In Kimbal-based designs, that’s part of the rationale for what are called Slowly-Changing Dimensions (SCDs).

2019-12-06

In SQL Server, most tables benefit from having a clustering key i.e. the column or columns that the table is logically sorted by. (Note: much old training material and documentation used to say “physical order” and that’s never been true). To do that, SQL Server stores the data within the pages in a sorted way, and maintains a doubly-linked logical chain of pages. In addition, it maintains pointers to the data by creating an index on it.

2019-11-29

The Usual Situation In general database terminology, a primary key is one or more columns that can be used to identify a particular row in a table. The key needs to be unique, and it can’t be null. An example would be a CustomerID or CustomerKey in a Customers table. A foreign key is one or more columns that refer to a key in another table. A common example would be a CustomerID column in an Orders table.

2019-11-22

Snowflake has a richer set of options for how tables are created, than we have in SQL Server. CREATE TABLE Variants As well as a CREATE TABLE statement as you would already be familiar with in SQL Server, Snowflake offers these variants: CREATE TABLE tablename AS SELECT This is basically similar to a SELECT INTO in SQL Server. It executes the SELECT query and creates a table from the results (including the data).

2019-11-15