Snowflake’s SELECT * EXCLUDE: A Time-Saving SQL Feature

Snowflake, the cloud-based data warehouse, offers a range of features that elevate traditional SQL experiences. One such feature is the SELECT * EXCLUDE syntax – a convenient tool for streamlining your queries and saving you valuable time.

The Old Way: Listing Columns Manually

Imagine you have a table with a multitude of columns. You want to retrieve almost all the data except for several specific columns. Traditionally, in SQL, you’d have to list every desired column in your SELECT statement:

SQL

SELECT column1, column2, column4, column5, — (and so on)

FROM my_table;

Use code with caution.

content_copy

This approach can become especially tedious when dealing with wide tables.

Enter SELECT * EXCLUDE.

Snowflake’s SELECT * EXCLUDE syntax offers a more elegant and efficient solution. Let’s see how it simplifies that same query:

SQL

SELECT * EXCLUDE (column3, column6) 

FROM my_table;

Use code with caution.

content_copy

This statement fetches all columns from my_table except for ‘column3’ and ‘column6’. It’s concise, readable, and a real time-saver!

Key Use Cases

Let’s look at some practical applications of SELECT * EXCLUDE:

• Exploratory Analysis: When exploring a dataset initially, excluding a few potentially sensitive or irrelevant columns lets you focus on the core data.
• Checksums for Wide Tables: Do you need to calculate checksums for wide tables to ensure data integrity during changes? SELECT * EXCLUDE lets you easily remove non-essential columns for checksum generation.
• Quick Prototyping: Speed up development by excluding columns you’ll address later in the refinement process.

Important Considerations

• Performance: There’s no significant performance difference between using SELECT * EXCLUDE and explicitly listing columns. Rest assured, convenience doesn’t come at the cost of speed.
• Dynamic Exclusion: You can’t use variables directly within the EXCLUDE clause. However, you can prepare your SQL statement dynamically to work around this.

Beyond EXCLUDE: SELECT * RENAME

Snowflake extends its SELECT * enhancements with the RENAME option. This lets you simultaneously rename columns:

SQL

SELECT * 

    EXCLUDE (old_column) 

    RENAME (new_column AS renamed_old_column) 

FROM my_table; 

Use code with caution.

content_copy

Embrace Efficiency

Snowflake’s SELECT * EXCLUDE is a small but mighty addition to your SQL toolkit. Understanding how and when to use it will streamline your data workflows and write more concise queries in Snowflake.