Creating a Snowflake Database Schema and Tables

By: Koen Verbeeck

Overview

Enough with the theory, let’s get our hands dirty.

In this part of the tutorial, we’ll see some different methods for creating database objects. If you’re have some experience with SQL Server, most of the DDL will seem very familiar.

Create Database Objects

With the following DDL statement we can create a new database in Snowflake:

CREATE OR REPLACE DATABASE TEST;

If the database already exists, it will be removed and a new database with the name TEST will be created. A more prudent way of creating a database can be done with the IF NOT EXISTS syntax:

CREATE DATABASE TEST IF NOT EXISTS;

This will only create the database if it doesn’t exist yet. The “OR REPLACE” and “IF NOT EXISTS” syntax can be used on most database objects.

Like in SQL Server, objects can be grouped together using schemas. If you want good code portability between Snowflake and SQL Server, it might not be a bad idea to create a schema called DBO:

CREATE SCHEMA DBO;

SQL Server has dbo in lowercase, but Snowflake has the habit putting everything in uppercase. If you really want dbo in lowercase, you need to quote the name:

CREATE SCHEMA "dbo";

By default, every database has also the schemas PUBLIC and INFORMATION_SCHEMA. In the latter you can find metadata information about the tables and columns of the database (such as the names, data types et cetera).

Now we can create a table as well. Again, the syntax is very familiar.

CREATE TABLE dbo.HelloWorld(
     ID INT AUTOINCREMENT NOT NULL
    ,MESSAGE STRING NOT NULL);

A couple notes about data types:

• STRING, TEXT and VARCHAR are identical. There’s no VARCHAR. The string data types store by default Unicode. There’s no performance difference in defining a length or just the full-length VARCHAR(16777216). The same amount of bytes are used. If you specify VARCHAR(50), the length of 50 does act as a check constraint on the length of the data input.
• As in SQL Server, you have the NUMERIC and FLOAT data types. Unlike SQL Server, there’s no difference between INT, INTEGER, BIGINT, SMALLINT, TINYINT and BYTEINT in Snowflake, they’re all equal to NUMERIC(38,0). Storage is only affected by scale, not by the precision.
• There are data types for storing semi-structured data: ARRAY, VARIANT and OBJECT. When dealing with data like XML and JSON, you store them for example in a VARIANT column.

Creating Copies of Database Objects

Sometimes you want to create a copy of an existing database object. For example for back-up purposes or for deploying the object from one environment to another. If you want to keep the data, the CLONE feature is very powerful. It creates a zero-copy clone of the original object, meaning it’s a metadata-only operation, the data itself is not clones. Only pointers are created, but the cloned object is updateable and can be changed at will. This is similar to SQL Server’s database snapshots, except that database snapshots are read-only.

Suppose we put some data in the table we created earlier.

INSERT INTO dbo.HelloWorld(Message)
SELECT 'Hello'
UNION ALL
SELECT 'MSSQLTips!';

When we issue the following statement, a clone of this table is created, with all the data in it:

CREATE TABLE dbo.HelloWorldClone CLONE dbo.HelloWorld;

If you don’t want to keep the data, you can use the LIKE feature:

CREATE TABLE dbo.HelloWorldLike LIKE dbo.HelloWorld;

Additional Information

• For more information about cloning, check out this page.
• The documentation of CREATE TABLE.
• If you’d like to know more about database snapshots in SQL Server: SQL Database Snapshots for Reporting, Auditing and Data Recovery.