Snowflake

Snowflake Nested Window Functions and Examples

Snowflake supports many useful windows or analytical functions. Many reporting queries use the analytic functions such as cumulative sum and average. But, whenever you try to call an analytics function within another analytics function, you will end up with an error such as "may not be nested inside another window function.". In this article, we will check how to use the nested window functions in Snowflake with an alternate example. Snowflake does not allow you to define the nested window function. You will have to use alternative methods such as…

Comments Off on Snowflake Nested Window Functions and Examples
July 8, 2021
Snowflake

How to Print SQL Query in Snowflake Stored Procedure?

As explained in my other article, to support migration from other relational databases, Snowflake supports the stored procedures. Snowflake uses JavaScript as a procedural language. It provides many features including control structures - branching, looping, Dynamic SQL, error handling, etc. But, JavaScript API, does not provide any print statement support to display the content of variable or SQL query itself. In this article, we will check how to print SQL query in Snowflake stored procedure. Print SQL Query in Snowflake Stored Procedure Snowflake stored procedure support many useful JavaScript API's.…

Comments Off on How to Print SQL Query in Snowflake Stored Procedure?
June 22, 2021
Snowflake

How to Add an Identity Column to an Existing Snowflake Table?

Need to add an auto-incrementing primary key to a Snowflake table that already has data? It's a common requirement, but Snowflake's architecture makes it a bit tricky out of the box. Let's walk through the exact steps and SQL commands you need to get this done without losing your existing records. Page Content Introduction The Challenge: Adding an Identity Column to a Populated Table Step-by-Step Workaround: Adding the Identity Column Conclusion Introduction Thanks to its unique architecture, Snowflake handles constraints a bit differently than legacy relational databases. It doesn't strictly…

Comments Off on How to Add an Identity Column to an Existing Snowflake Table?
June 21, 2021
Snowflake

How to Create Parameterized Views in Snowflake (Methods & Best Practices)?

Are you trying to pass dynamic filters into your Snowflake views but hitting a wall? While Snowflake doesn't offer a feature explicitly named "parameterized views," you can easily achieve this dynamic functionality using a couple of clever workarounds. Page Content Introduction Why Do We Need Parameterized Views? Method 1: Simulating Parameterized Views Using Session Variables Using the Snowflake IDENTIFIER() Function Method 2: Using UDTFs (The Modern Alternative) Conclusion Introduction A standard database view acts as a virtual table, encapsulating your business logic by joining multiple tables and keeping complex SQL…

Comments Off on How to Create Parameterized Views in Snowflake (Methods & Best Practices)?
June 17, 2021
Snowflake

How to Update a JSON Field in a Snowflake VARIANT Column?

Working with semi-structured data like JSON is one of Snowflake's strongest capabilities. However, modifying a specific JSON field nested inside a VARIANT column can sometimes trip up even experienced data engineers. In this guide, we'll walk you through the simplest way to update or replace JSON values natively in Snowflake. Page Content Introduction Updating or Replacing JSON Fields in Snowflake Using the Snowflake OBJECT_INSERT Function Example: Updating a JSON Field in a VARIANT Column Conclusion Introduction Data updates are a standard operation across all relational databases. As a leading cloud…

Comments Off on How to Update a JSON Field in a Snowflake VARIANT Column?
June 15, 2021
Snowflake

How to Combine Two or More Arrays in Snowflake (Using ARRAY_CAT)?

Working with semi-structured data in Snowflake often means dealing with arrays. Whether you are transforming raw JSON or consolidating lists, knowing how to properly combine arrays in Snowflake is an essential skill for any data engineer or analyst. Page Content Introduction Why Merge Arrays in Snowflake? The Snowflake ARRAY_CAT Function Test Data Setup Examples: Merging Array Columns and Variables How to Combine Three or More Arrays in Snowflake Conclusion Introduction Snowflake stands out as a powerful data warehouse because it natively supports semi-structured data formats like JSON, XML, and arrays…

Comments Off on How to Combine Two or More Arrays in Snowflake (Using ARRAY_CAT)?
June 10, 2021
Snowflake

How to Get the First Row of Each Group in Snowflake Using Window Functions?

When building data warehouse reports, you'll often need to find the top record within specific categories, like identifying the highest-paid employee in each department. If you're wondering how to get the first row of each group in Snowflake, you're in the right place. In this guide, we'll walk through the most efficient SQL window functions to handle this common scenario without slowing down your queries. Page Content Introduction Setting Up the Test Data Method 1: Using ROW_NUMBER to Select the First Row Method 2: Using FIRST_VALUE to Get the First…

Comments Off on How to Get the First Row of Each Group in Snowflake Using Window Functions?
June 9, 2021
Snowflake

Snowflake CONCAT Function and Operator: A Complete Guide with Examples

Working with data often means pulling pieces of information together from different places. Whether you need to merge first and last names or combine city and state columns for a reporting dashboard, knowing how to properly concatenate strings in Snowflake is an essential skill for any data professional. Page Content Introduction Snowflake CONCAT Function and Operator Overview How to Use the Snowflake CONCAT Function Snowflake CONCAT Function Examples Nested CONCAT Functions in Snowflake Using the Snowflake CONCAT_WS Function The Snowflake CONCAT Operator (||) Handling NULL Values When Concatenating Strings Conclusion…

Comments Off on Snowflake CONCAT Function and Operator: A Complete Guide with Examples
June 8, 2021
Snowflake

Snowflake Array Functions – Syntax and Examples

It is very common practice to store values in the form of an array in the databases. Without a doubt, Snowflake supports many array functions. You can use these array manipulation functions to manipulate the array types. In this article, we will check how to work with Snowflake Array Functions, syntax and examples to manipulate array types. Snowflake Array Functions Following is the list of Snowflake array functions with brief descriptions: Array FunctionsDescriptionARRAY_AGGFunction returns the input values, pivoted into an ARRAY.ARRAY_APPENDThis function returns an array containing all elements from the…

Comments Off on Snowflake Array Functions – Syntax and Examples
May 26, 2021
Snowflake

What are SELECT INTO Alternatives in Snowflake?

In my other post, I have discussed about what are different methods to create Snowflake table. There are many database specific syntaxes that are not supported in Snowflake yet. One of such syntax is SELECT INTO. The databases such as SQL Server, Reshift, Teradata, etc. supports SELECT INTO clause to create new table and insert the resulting rows from the query into it. In this article, we will check what are SELECT INTO alternatives in Snowflake with some examples. SELECT INTO Alternatives in Snowflake In the databases such as SQL…

Comments Off on What are SELECT INTO Alternatives in Snowflake?
April 16, 2021