In this series of posts, we'll be discussing some "unique" behaviors of the Snowflake analytical base, which you won't find in the documentation.

Today, we will talk about a few subtleties of Snowflake's behavior.

Keywords

For instance, the LIMIT keyword, which is not actually a keyword. Because of this, it can be used as an alias!

SELECT LIMIT.* FROM t LIMIT

And even like this:

SELECT * FROM t LIMIT LIMIT 10

Cool, right? But actually, this is nothing compared to the fact that in Spark the word FROM is not a keyword.

There, you can do this:

SELECT FROM FROM FROM FROM FROM

Nonstrictness of the values of some function parameters

PREVIOUSDAY/NEXTDAY

The function returns the previous/next selected day of the week relative to the specified date.

For example, the next Friday:

SELECT NEXT_DAY(CURRENT_DATE(), 'FR');

Instead of 'FR', it can be anything that starts with 'FR':

'France'
'Frozen'
'Friday I am in love'

Now, you can leave easter eggs for your colleagues in your queries 🙂

Collation rules are described in the format language[_country][-specifier …]

And everything would be fine - take ISO639-1 for languages, ISO3166-1 for countries. Yes, but who cares about these little things?

SELECT 'foo' COLLATE 'trololo'

How did I find out about this?

Accidentally, after spending an hour searching for how to find the available list of collation. 😮‍💨