SQL
21 February 2021

TIMESTAMP Function Returns Different Results When Sorted By Different Direction

Recently, I was working on an issue for customer who faced a weird behaviour that TIMESTAMP conversion function returned different results if different sorting direction was used, meaning, results are different if using ASC vs DESC. To illustrate the issue, please see my test case below: Create a table with testing data: 2. Run below query using Ascending order, the timestamp conversion will fail for […]

SQL
18 February 2021

File Pruning for External Tables

Currently Snowflake does not support pruning External Table files. To prove it, I have performed below test. Assume that I have an external S3 stage setup correctly, which points to an external S3 location with a few CSV files, I create below external table: The stage @ERICLIN_S3_STAGE/test contains a list of CSV files And each file contains a few lines of data, whose content is […]

SQL
25 January 2021

Decimal Values are Converted to Integer Values

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

SQL
20 January 2021

Query Against Parquet File failed with error “Not yet implemented: Unsupported encoding”

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

SQL
7 December 2020

Stored Procedure with Date parameter failed with error: “Unsupported type for binding argument 2”

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

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

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

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

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

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