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 Connect Databricks to Snowflake?: A Step-by-Step Guide

Building a modern data stack often means combining the best tools for the job. By connecting Databricks to Snowflake, data teams can seamlessly run complex machine learning workflows and push the finalized datasets directly into their data warehouse for lightning-fast business intelligence reporting. Page Content Introduction Why Connect Databricks and Snowflake? Install the Snowflake Spark Connector on Databricks Create the Snowflake Options Dictionary Write the Databricks DataFrame to a Snowflake Table Connection Troubleshooting Conclusion Introduction Many organizations rely on a hybrid data architecture to handle different stages of their data…

Comments Off on How to Connect Databricks to Snowflake?: A Step-by-Step Guide
June 16, 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

How to Merge Json Objects in Snowflake?

One of the greatest strengths of Snowflake is that it can handle both structured and semi-structured data. Semi-structured data includes JSON and XML. Snowflake allows you to store and query the json or xml data without using any special functions. The built-in function such as merging two or more json object is not available as of now. But, you can make use of JavaScript function by writing Snowflake user defined function. In this article, we will check how to merge two json objects in Snowflake. Merge JSON Objects in Snowflake…

Comments Off on How to Merge Json Objects in Snowflake?
June 1, 2021