Snowflake is like that friend who’s always got something new up their sleeve! Snowflake is all about staying up-to-date and cool, regularly spicing up its lineup of awesome features to keep up with the changing times in the world of data. So, in this blog post, get ready for a front-row seat to the show as I walk you through these cool updates and show you exactly how to use them to make your data game even stronger. We’ll be covering the following SQL functions:

• EXCLUDE
• RENAME
• REPLACE
• ILIKE
• GROUP BY ALL

Throughout this blog post, we will reference Snowflake’s Tasty Bytes dataset. To learn more about how to set up your Tasty Bytes data, here’s a handy Snowflake quickstart guide: An Introduction to Tasty Bytes.

EXCLUDE

In Snowflake, you have the option of using the EXCLUDE keyword within your SELECT statement to give certain columns the slip, ensuring they stay out of your query results. This trick comes in handy especially when you’re dealing with a large number of columns and only want to give the boot to a select few, making your query outcomes cleaner and more focused. For example, let’s look at the following SQL:

SELECT * FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU;

This dataset contains 11 columns. Typically, if you want to exclude a handful of columns, you’d have to painstakingly list out the chosen columns within your SELECT statement. Now, with EXCLUDE, you can simply select all columns and exclude the one you don’t want in your query results. Let’s say you don’t want your query to contain MENU_TYPE_ID , you can simply add EXCLUDE followed by the MENU_TYPE_ID:

SELECT * EXCLUDE MENU_TYPE_ID 
FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU;

If you want to exclude multiple columns at once, all you have to do is put those columns together within a set of trusty brackets:

SELECT * EXCLUDE (MENU_TYPE_ID, MENU_ITEM_ID) 
FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU;

RENAME

Snowflake also allows you to rename specific columns in your SELECT statement. Think of it as a sibling to the exclude feature we talked about earlier; it functions in a similar manner:

SELECT * RENAME TRUCK_BRAND_NAME AS TRUCK_NAME 
FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU;

Just like you’d gather columns within brackets to exclude them for renaming, you can apply the same principle:

SELECT * RENAME (TRUCK_BRAND_NAME AS TRUCK_NAME, MENU_ITEM_NAME AS ITEM_NAME) 
FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU;

You can also combine them and use EXCLUDE and RENAME in the same statement, for example:

SELECT * 
   EXCLUDE (CC_CLOSED_DATE_SK, CC_CALL_CENTER_ID) 
   RENAME (CC_REC_START_DATE AS CC_START_DATE, CC_REC_END_DATE AS CC_END_DATE) 
FROM SNOWFLAKE_SAMPLE_DATA.TPCDS_SF100TCL.CALL_CENTER LIMIT 1000;

REPLACE

In your Snowflake toolkit, there’s another handy function called REPLACE, which lets you seamlessly switch up the values of particular columns right within your statement. Just as with EXCLUDE and RENAME, this comes to the rescue when you’re aiming to tweak the values of one or more columns in a targeted manner:

SELECT * 
   REPLACE (UPPER(MENU_TYPE) AS MENU_TYPE) 
FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU;

You can combine EXCLUDE, REPLACE and RENAME; however, EXCLUDE needs to be the first keyword, followed by REPLACE then RENAME, for example:

SELECT * 
   EXCLUDE (MENU_TYPE_ID, MENU_ITEM_ID) 
   REPLACE (UPPER(MENU_TYPE) AS MENU_TYPE) 
   RENAME (TRUCK_BRAND_NAME AS TRUCK_NAME, MENU_ITEM_NAME AS ITEM_NAME) 
FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU;

ILIKE

ILIKE allows matching of strings based on comparison with a pattern and now joins the party in your SELECT statement just like EXCLUDE RENAME and REPLACE. Imagine the dataset we explored earlier. Now, suppose we’re aiming to simplify things by retaining only those column names that include the string “MENU.” The process is as straightforward as follows:

SELECT * 
   ILIKE '%MENU%' 
FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU;

You can also combine ILIKE with all the keywords we mentioned before, except EXCLUDE. For example, let’s test this out by specifying ILIKE, REPLACE and RENAME in the same SELECT statement:

SELECT * 
   ILIKE '%MENU%' 
   REPLACE (UPPER(MENU_TYPE) AS MENU_TYPE) 
   RENAME (MENU_TYPE AS ITEM_TYPE, MENU_ITEM_NAME AS ITEM_NAME)
FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU;

GROUP BY ALL

This new function liberates you from the task of explicitly listing every column in your GROUP BY statement. Let’s kick off by setting the stage with a straightforward product sales table and insert into it some mock data to get things rolling:

CREATE OR REPLACE TABLE PRODUCT_SALES (
    PRODUCT_ID NUMBER
    ,PRICE NUMBER
    ,QUANTITY NUMBER
    ,STORE_NAME STRING
    ,COUNTRY STRING
);

INSERT INTO PRODUCT_SALES
VALUES
(1, '50', 3, 'Store 1', 'Australia')
,(1, '50', 1, 'Store 2', 'Australia')
,(1, '50', 2, 'Store 3', 'New Zealand')
,(2, '25', 1, 'Store 1', 'Australia')
,(2, '25', 6, 'Store 2', 'Australia')
,(2, '25', 1, 'Store 4', 'Australia')
,(3, '15', 4, 'Store 4', 'Australia')
,(4, '33', 1, 'Store 4', 'Australia');

Suppose we’re aiming to capture the sales (PRODUCT * QUANTITY) across different stores and countries. In such a scenario, the benefit of utilising GROUP BY ALL becomes apparent – it spares us the effort of individually listing each column in the group by statement, as demonstrated in the following instance:

SELECT
    STORE_NAME
    ,COUNTRY
    ,SUM(PRICE * QUANTITY) AS SALES
FROM PRODUCT_SALES
GROUP BY ALL
;

While this example may appear straightforward, consider the potential convenience when dealing with a large number of columns within your SELECT statement. Just envision the efficiency boost that awaits in such scenarios.

You can also put GROUP BY ALL to work alongside the keywords we explored earlier. For instance:

SELECT * 
   ILIKE '%TYPE%' 
   REPLACE (UPPER(MENU_TYPE) AS MENU_TYPE) 
   RENAME (MENU_TYPE AS TYPE) 
 , COUNT(*) AS RECORD_COUNT 
FROM FROSTBYTE_TASTY_BYTES.RAW_POS.MENU GROUP BY ALL 
;

Considerations

Be aware that when specifying a combination of keywords after SELECT * you cannot specify both ILIKE and EXCLUDE.

When specifying EXCLUDE with REPLACE or RENAME you must specify EXCLUDE before any other keyword:

SELECT * EXCLUDE ... REPLACE ...

SELECT * EXCLUDE ... RENAME ...

SELECT * EXCLUDE ... REPLACE ... RENAME ...

When specifying ILIKE with REPLACE or RENAME , you must specify ILIKE before any other keyword:

SELECT * ILIKE ... REPLACE

SELECT * ILIKE ... RENAME ...

SELECT * ILIKE... REPLACE ... RENAME

When specifying REPLACE and RENAME, you must specify REPLACE first:

SELECT * REPLACE ... RENAME ...

Wrap Up

Thanks for taking the time to learn more about Snowflake’s new features! We’ve introduced some awesome functions like EXCLUDE, RENAME, REPLACE, ILIKE and GROUP BY ALL, each ready to turbocharge your data management. Snowflake’s got your back with these slick functions, making data challenges a breeze. Now, armed with these tricks, you’re all set to dive into your data world with newfound confidence!