Today, I have learnt that Snowflake will convert decimal values to integer values if all trailings are zeros. Please see below examples: It returns: The same applies to PARSE_JSON: It returns: Instead of: After researching with our team, it turned out that it was expected behaviour. In fact, Snowflake has been behaving this way for a long time. However, for the PARSE_JSON, it was recently […]
Recently I was dealing with an issue that a Snowflake query against Parquet file in internal Staging failed with error as below: After researching online, I figured out that it was nothing to do with Snowflake, it was because user used AWS Cost and Usage Report (CUR) tool to generate the Parquet file, which contains a version of Parquet Schema that is currently NOT supported by Snowflake. This […]
It is common that we will need to perform Date transformations inside Stored Procedures in SQL. Recently I have encountered an error that Snowflake does not like the format that is generated by Javascript inside Stored Procedure code, and resulted an error. See below example: To execute this Stored Procedure, simply run below query: And we can see it will fail with below error: Execution […]
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 […]
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 […]
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: […]
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, […]
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 […]
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 […]
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. […]