27 November 2020

How to Paginate SHOW TABLES Result

Pagination on query results is straightforward enough, and it is so common that most of SQL developers would need to do it on weekly, if not on daily basis. Paginate the resultset of SHOW TABLES query however, is not as common and might not be known to most developers, because of the different syntax, plus its different behaviour than other query resultsets. Let me demonstrate […]

26 November 2020

How to Track Table Ownership Change

We know that it is important to keep track of table ownerships to understand who created and owned the tables in the system. Snowflake keeps such records in multiple places. So depending on your needs, the methods might be different. In this post, I will show you 2 ways that you can get such information and the pros and cons of each. Method 1: SNOWFLAKE.ACCOUNT_USAGE.TABLES […]

25 November 2020

Constraints Not Enforced

If you come from RDBMS, we all know that it is important to have constraints in your database, so that your data integrity will be maintained, like Primary Key, Foreign Key, Unique Indexing etc. However, in Snowflake world, it is very different. Though Snowflake Supports constraints, like the ones I mentioned above, they are NOT enforced. Please refer to Snowflake’s official doc regarding this topic: […]

19 November 2020

Recursive Query with CTE – How it works

Recursive query in SQL can be as useful as recursive functions that developers use all the time. Snowflake has documentations on how to use CTE to construct recursive queries. Example can be found here: Working with CTEs. It uses a simple company employees hierarchy to demonstrate how to run recursive queries, and it also describes in a bit of detail on how it works. However, […]

13 November 2020

ALTER TABLE … RECLUSTER is Classified as DML, not DDL

We all know that ALTER TABLE is a DDL (Data Definition Language), not a DML (Data Manipulation Language), because ALTER TABLE only changes metadata information about a table, like adding a new column, or changing existing column names.. If you are unsure about the differences between DDL and DML, please refer to this page on StackOverflow for details: What are DDL and DML? I will […]

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

Key Based Authentication Failed with “JWT token is invalid” Error

Sometimes user can face “JWT token is invalid” error when trying to connect to Snowflake via SnowSQL using key based authentication. To capture this error, logging needs to be enabled for SnowSQL via command line. Use below sample command to redirect logs to a file with DEBUG level logging: And check the log file under /tmp/snowsql.log. There are at least two possible causes that can […]

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 […]