In the fast-evolving landscape of modern data warehousing, Snowflake stands as a prominent force, offering a robust platform for efficient data storage and management. Snowflake is committed to keeping its product cutting-edge and user-friendly by continuously releasing updates. In this series, we'll break down the latest features in the simplest way possible, complete with practical use cases.
In this instalment, we focus on four of the most recent SQL advancements introduced by Snowflake in 2023.
1. EXCLUDE/RENAME
In the realm of warehouse layer tables, dealing with an extensive list of columns can be challenging, especially when you need to exclude some or alias others. Enter EXCLUDE and RENAME, two lifesavers in this scenario.
- **EXCLUDE** allows you to specify columns to exclude from the result set.
- **RENAME** enables you to specify column aliases for the result set.
Syntax:
Note: If you want to use both EXCLUDE and RENAME in same SQL, EXCLUDE should always be specified before RENAME.
Use Case:
Imagine an employee table with nearly 200 columns. When you're preparing data for reporting in the mart layer, you want to exclude four columns and rename three. Instead of listing all 200 columns, you can use EXCLUDE and RENAME wisely with less efforts.
2. MIN_BY and MAX_BY
Sometimes, you need to find the minimum or maximum value of one column and return a corresponding value from another. In such cases, MIN_BY and MAX_BY aggregate functions come to the rescue, simplifying code, and reducing developer overhead.
Syntax:
Use Case:
Consider a table with cities worldwide and their air quality index. You want to find the city with the best and worst air quality index, not necessarily caring about the actual min or max values. MIN_BY and MAX_BY functions are perfect for this scenario.
3. BANKER'S ROUNDING
Standard rounding functions often round values up if the decimal part is .5 or greater and down if it's less than .5. Snowflake introduces two ROUNDING MODES, as a third parameter in ROUND function. The parameters are as follows:
- 'HALF_TO_EVEN'
- 'HALF_AWAY_FROM_ZERO'(It gives us default mode of rounding.)
Syntax:
Use Case:
Banker’s rounding is particularly useful for financial or statistical data processing, ensuring unbiased results when dealing with slightly skewed data. Note that data types with floating-point numbers are not supported for this function.
You can observe the output for each parameter in below screenshot.
4. GROUP BY ALL
When using aggregate functions (sum, avg, etc.) in the SELECT clause, you typically need to include all the columns in the GROUP BY clause that are also present in the SELECT clause. This can become cumbersome with a long list of columns or complex expressions. GROUP BY ALL simplifies your code by allowing you to specify it instead of listing all column names or numbers.
Syntax:
Use Case:
Imagine analyzing sales data by product, month, city, area, and more. GROUP BY ALL streamlines your code and reduces the need to specify every column individually.
These enhancements by Snowflake have made life incredibly convenient for SQL users, empowering them to work more efficiently and effectively. Stay tuned for more updates in this series!