Category: SQL

13 November 2020

Fully Qualified Name in GET_DDL

By default, GET_DLL function in Snowflake will return TABLE or VIEW without database and schema path. Please see example below: The result is below: You can see that the table name GET_DDL_TEST returned without Database and Schema info. This could be problematic if user is relying on the output to re-create tables or views, and the newly created tables or views might end up in […]

11 October 2020

How to parse special characters in PARSE_JSON function

Sometimes it can be confusing when trying to parse JSON string, this is no different when using PARSE_JSON function in Snowflake, especially when there are special characters involved. Look at below examples when I need to have backslash inside the key string: We can see that both of 2 and 3 backslashes produced errors, while 4 backslash returned value as “my\\id“, which contains 2 backslashes. […]

3 October 2020

Unable to Change Column Type with Collation

Currently Snowflake does not support the change of COLLATIONs at COLUMN level once table is defined. This is documented on Snowflake’s official documentation as below: Once a table column is defined, you cannot change the collation for the column. In other words, after a column has been created with a particular collation using a CREATE TABLE statement, you cannot use ALTER TABLE to change the collation. Additional Considerations for […]

2 October 2020

Query Failed With Error “Timestamp ‘2020-09-28’ is not recognized”

Most of time when Snowflake query complains about something “is not recognized”, it normally means that Snowflake is not able to CAST a given value to a target type. In my case of “Timestamp ‘2020-09-28’ is not recognized”, it means Snowflake is not able to convert value “2020-09-28” to Timestamp value. This sounds a bit non-sense, as “2020-09-28” is a valid timestamp value, even without […]

2 October 2020

Timestamp Precision Rounding

Currently in Snowflake, it supports using the precision format of FF<N>, where N is a number between 0 to 9, to display precision part of the timestamp value. To demonstrate how it works, please see below examples: By default the output returns only 3 digits, which is up to milliseconds, is because the session level parameter TIMESTAMP_OUTPUT_FORMAT has the default value of “YYYY-MM-DD HH24:MI:SS.FF3 TZHTZM”. […]

27 September 2020

GROUP BY CUBE Has Limit of 7 Elements

Currently if you use GROUP BY CUBE clause in Snowflake, it has the limitation of supporting only 7 elements, or the equivalent of 128 grouping sets. This is documented in below Snowflake’s official documentation: GROUP BY CUBE Usage NotesSnowflake allows up to 7 elements (equivalent to 128 grouping sets) in each cube. This same limitation also applies to both GROPU BY ROLLUP and GROUP BY […]

11 September 2020

The type of column ‘xxx’ conflicts with the type of other columns in the UNPIVOT list.

UNPIVOT is a SQL standard function that allows users to transform columns into rows, and Snowflake also supports it. Please see below example on how to use it: You can see that the result turned the scores that were in each column of a row into seperate rows that still associated with original student ID. This query works well for the columns defined in UNPIVOT […]

23 August 2020

Loading Data into Snowflake

Welcome to my first Snowflake blog post. Since I just started as a Cloud Data Warehouse Engineer 2 weeks ago, Snowflake is also very new to me. I have spent sometime played around with Snowflake interface and learn how to load data from local file system into Snowflake and then query from Snowflake Web UI. I have noticed that though loading data is straight forward, […]