Snowflake’s new quality-of-life SQL feature: SELECT * EXCLUDE

In early November, Snowflake released a number of SQL updates for their platform as part of the recently published 6.37 release. As a continued Snowflake Services Elite Partner, DAS42 consistently stays up to date with Snowflake’s weekly updates to ensure our teams are delivering quality solutions. This recent release included a long-awaited quality-of-life feature: SELECT * EXCLUDE.

What is the new SELECT * EXCLUDE feature?

With this new update, the classic SELECT * statement, which queries the database and retrieves a set of rows, has been expanded to include a new keyword, EXCLUDE. As per the documentation, the new EXCLUDE keyword can be used to exclude specific columns from the result of the SELECT *. The syntax for excluding one or more columns is as follows:

SELECT * EXCLUDE <col_name> ... ;

SELECT * EXCLUDE (<col_name>, <col_name>, ...) ;

While at a glance this change may seem relatively minor, data engineers and analysts using Snowflake can use it to save time and make SQL queries more readable. In situations where you need most of a table’s columns in your query, but not all of them, SELECT * EXCLUDE will be a helpful tool. Below is an example using the ORDERS table from the Snowflake sample data. Let’s say you are writing a query for a report, and as part of it you need all fields from the ORDERS table except the ‘O_CLERK’ and the ‘O_COMMENT’. 

Prior to the recent release, this could only be accomplished by listing out every desired field in the SELECT statement:

SELECT o_orderkey, o_custkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_shippriorityFROM snowflake_sample_data.tpch_sf1.orders;

The result of that query should include only the fields specified:

With the new functionality provided by the EXCLUDE keyword, we can now produce the same result with less SQL: 

SELECT * EXCLUDE (o_clerk, o_comment) 
FROM snowflake_sample_data.tpch_sf1.orders;

Our new query result should have the same fields as the old one, with the two specified fields being excluded:

With the example above in mind, you can imagine a situation where we are dealing with a table that has dozens of fields, and all but one or two of these fields may be needed. In a situation like this, SELECT * EXCLUDE can be used to optimize the process, saving Snowflake users time and making their SQL queries more readable.

When not to use SELECT * EXCLUDE

One thing to note: when using the SELECT * EXCLUDE feature, context is important. At times an explicit list of field names in the select clause is more desirable – for example, when a table schema is expected to change by adding fields, or when a query is being shared in documentation and you want to ensure that those reading can see specifically which columns are included. 

SELECT * EXCLUDE – The bottom line

Overall, in the right contexts, the new SELECT * EXCLUDE feature saves time when making SQL queries and improves readability. We appreciate the feature updates being provided by the Snowflake team. They consistently take in customer feedback, as is evidenced by “quality of life” updates like SELECT * EXCLUDE and the recent ARRAY_DISTINCT function.