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
Snowflake

How Snowflake Internally Handles Updates? – Explanation

When you load data into Snowflake, Snowflake reorganizes that data into micro partition and stores into its internal optimized, compressed, columnar format. Snowflake stores this optimized data in cloud storage. Snowflake uses S3, Blob storage or GCP cloud storage. However, all these storages are immutable. Obviously the question would be how Snowflake internally performs or handles updates when you execute the update command? How Snowflake Internally Handles Updates? Many people would have thought of this question when they were going through Snowflake architecture. It is a complex question. Snowflake uses…

Comments Off on How Snowflake Internally Handles Updates? – Explanation
June 1, 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