Snowflake

SQL Server temporary stored procedures vs Snowflake anonymous stored procedures

Over the years, I’ve done some work with Snowflake. In fact, I got certified in it at one point, and I’ve stayed across its capabilities. I have friends that work there.

Overally, I found the SQL language that Snowflake offered to be pretty limited, and particularly at the time I spent the most time on it, it seemed to have gaps in what it provided.

One thing that fascinated me though was the idea of an anonymous stored procedure.

2025-04-22

Snowflake SQL for SQL Server Users - UNDROP

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

Snowflake SQL for SQL Server Users - Sequences: The good and the not so good

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

Snowflake SQL for SQL Server Users - SQL Language Differences

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.  I’m planning to work right through the Snowflake SQL language documentation and try everything in it, so you don’t have to.

2020-01-03

Snowflake for SQL Server Users - A Christmas Present

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! And did I mention free? (for SDU Insiders)

2019-12-27

Snowflake for SQL Server Users - Part 20 - Encryption within Snowflake

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. To do that, you create an “encrypted stage” where you tell Snowflake the master encryption key (i.e. the client side key) when creating the stage:

2019-12-20

Snowflake for SQL Server Users - Part 19 - Fail-Safe

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.  It lasts for a non-configurable period of seven days.

2019-12-13

Snowflake for SQL Server Users - Part 18 - Time travel

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

Snowflake for SQL Server Users - Part 17 - Data clustering

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

Snowflake for SQL Server Users - Part 16 - Primary and Foreign Key Constraints

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. Foreign keys can be nullable and are checked when they contain a value.

2019-11-22