10 Things I Like About Snowflake — from a SQL Server Migrant

As a data engineer, I’ve spent much of my career working with SQL Server, managing data pipelines and extracting insights within its familiar environment. However, a recent shift in my career focus has steered me towards a new challenge: migrating on-premise SQL Server data to Snowflake’s cloud-native platform. This transition not only involves adopting new technology but also requires a fresh perspective as I explore the capabilities of Snowflake’s data warehouse.

In this blog, I’ll walk you through 10 key discoveries that have shaped my journey into Snowflake. From simplified Group By ALL commands to dynamic PIVOT operations, each revelation highlights Snowflake’s potential to revolutionise data engineering practices and push the boundaries of what’s possible in data management.

So, let’s dive into my top 10 discoveries with Snowflake, each one shedding light on its capabilities and how it can revolutionise data engineering.

1. GROUP BY ALL

In SQL Server, we often need to explicitly mention all non-aggregated columns in the GROUP BY clause. Snowflake simplifies this with GROUP BY ALL, streamlining complex aggregations by eliminating the need for explicit column naming.

2. Reusing calculated columns in WHERE clause

In SQL Server, when we calculate values in the SELECT statement, we often need to repeat those calculations in the WHERE clause. Snowflake simplifies this by allowing direct use of calculated column aliases from the SELECT statement in the WHERE clause, making the code cleaner and more straightforward.

3. Dynamic PIVOT

In SQL Server, pivoting a table requires specifying columns, and making it dynamic often involves complex scripting or dynamic SQL. In Snowflake, using “ANY” in the IN clause simplifies dynamic pivoting, eliminating the need for convoluted workarounds.

4. QUALIFY

In SQL Server, applying a filter after a window function calculation often requires a CTE or subquery. Snowflake simplifies this with the QUALIFY clause, acting like a WHERE filter for window functions, providing more control over the results.

5. SELECT * EXCLUDE

Sample SQL Server approach from StackOverflow

In SQL Server, dealing with large tables makes excluding specific columns from the SELECT query challenging. Snowflake simplifies this with “SELECT * EXCLUDE,” providing an easy and straightforward way to exclude columns, enhancing efficiency and simplifying queries.

6. SELECT COUNT_IF()

In SQL Server, counting occurrences with conditions often involves complex CASE statements within COUNT(). Snowflake simplifies this with COUNT_IF(), offering a shorter and more straightforward approach to count rows based on specific criteria.

7. SELECT * ILIKE

Sample SQL Server approach from StackOverflow

In SQL Server, selecting columns based on a pattern from the column name requires cumbersome dynamic SQL scripting. In Snowflake, however, we can easily select specific columns based on a pattern using the ILIKE operator, streamlining the process without the need for additional scripting or transformations.

8. GET_DDL() FUNCTION

-- SNOWFLAKE
SELECT GET_DDL('TABLE', 'TABLENAME');

In SQL Server, obtaining the Data Definition Language (DDL) for database objects typically involves manual extraction from SSMS, lacking a straightforward programmatic approach. Snowflake simplifies this task with the GET_DDL() function, enabling seamless retrieval of DDL statements for tables, views, and other database objects.

9. TABLE command

In SQL Server, data retrieval often relies on the SELECT * FROM statement as the standard approach. Snowflake offers the TABLE command as an alternative, providing an additional option that enhances flexibility and readability in querying tables, although it’s more of a nice-to-know feature rather than a critical one.

10. CTE gotcha

based from this LinkedIn post

In SQL Server, Common Table Expressions (CTEs) are primarily utilised as view-like objects for organising complex queries. However, Snowflake expands the utility of CTEs beyond this conventional role, allowing for more dynamic and iterative query constructions.

-- SNOWFLAKE

/* CTE as scalar value */
WITH cte_obj AS (
  {
    'database': 'Snowflake',
    'theme': 'Snow'
  }
)
SELECT 
  * 
FROM table 
WHERE
 database = cte_obj:database
 and theme = cte_obj:theme ;


/* CTE as alias to any expression */
WITH cte_exp AS (
  col1 + col2
)
SELECT
  cte_exp
FROM table;

In Snowflake, CTEs can be employed for assigning scalar values or using them as expressions, providing greater flexibility in query design.

Switching from SQL Server to Snowflake has been eye-opening. As I keep exploring Snowflake’s possibilities, I look forward to discovering more tips and tricks to make data management even smoother. Snowflake’s user-friendly features and powerful tools highlight the constant evolution of data tech, paving the way for endless innovation and growth.

Thanks for reading!