Snowflake Transformation

Snowflake has many advantages:

  • No database administration
  • No indexes, sort keys, distribution styles, or column compressions
  • Easy scaling
  • Simple data types
  • Amazing processing power and data throughput

Migration from Redshift to Snowflake

Please share your migration tips with us.

Limits

  • Snowflake queries are limited to 3,600 seconds by default.
  • Queries containing comments longer than 8192 characters will segfault.
  • Constraints (like PRIMARY KEY or UNIQUE) are defined, but not enforced.

Best Practices

Case Sensitivity

Unlike Redshift or MySQL, Snowflake is case sensitive. All unquoted table/column names are converted to upper case while quoted names keep their case.

So if you want to create the following table,

-- creates table FOOTABLE
CREATE TABLE footable (...);

all of these commands will work

SELECT * FROM FOOTABLE;
SELECT * FROM "FOOTABLE";
SELECT * FROM footable;

while this one will not:

-- table footable not found!
SELECT * FROM "footable";

Be especially careful when setting up input and output mappings. In the KBC UI, it is necessary to enter all table names using the exact casing because all table names referenced by mappings are automatically quoted by KBC.

When writing your transformation script, we recommend quoting all table names as well.

Timestamp Columns

By default, Snowflake uses the DY, DD MON YYYY HH24:MI:SS TZHTZM format when converting the timestamp column to a character string.

This means that if you create a table in a transformation which uses a timestamp column,

CREATE TABLE "ts_test" AS (SELECT CURRENT_TIMESTAMP AS "ts");

the table value will come out as Wed, 19 Oct 2016 01:24:21 -0700 in Storage. If you want to output it in a different format, you have to cast the column to a string first, for example:

CREATE TABLE "out" AS
    (SELECT TO_CHAR("ts", 'YYYY-MM-DD HH:MI:SS') AS "ts" FROM "ts_test");

Do not use ALTER SESSION queries to modify the default timestamp format, as the loading and unloading sessions are separate from your transformation/sandbox session and the format may change unexpectedly.

Important: Snowflake works with time zones (and Daylight Savings Time), requiring you to distinguish between various conversion functions:

SELECT
    -- yields 2013-03-10 02:12:00.000 +0000
    TO_TIMESTAMP_NTZ('10.3.2013 2:12', 'DD.MM.YYYY HH:MI'), 
    -- yields 2013-03-10 03:12:00.000 -0700
    TO_TIMESTAMP_TZ('10.3.2013 2:12', 'DD.MM.YYYY HH:MI'),  
    -- yields 2013-03-10 03:12:00.000 -0700
    TO_TIMESTAMP('10.3.2013 2:12', 'DD.MM.YYYY HH:MI');