Snowflake

How to Handle Duplicate Records in Snowflake Insert?

Snowflake does not enforce constraints on tables. Handling duplicate records in Snowflake insert is one of the common requirements while performing a data load. Snowflake supports many methods to identify and remove duplicate records from the table. In this article, we will check how to handle duplicate records in the Snowflake insert statement. It is basically one of the alternative methods to enforce the primary key constraints on Snowflake table. Handle Duplicate Records in Snowflake Insert Snowflake allows you to identify a column as a primary key, but it doesn't…

Comments Off on How to Handle Duplicate Records in Snowflake Insert?
July 14, 2021
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 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 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

What are Different Methods to Create Snowflake Tables?

In my other posts, I have discussed on how to create Snowflake clustered tables, creating external tables in Snowflake, etc. In this article, we will check what are different methods to create Snowflake tables with some basic examples. Different Methods to Create Snowflake Tables During database development, developer create a table such as permanent, temporary or transient tables as per the requirement. Developers usually create tables using DDL such as “CREATE TABLE” statement. But, sometimes you may need to use different methods such as creating a copy of an existing…

Comments Off on What are Different Methods to Create Snowflake Tables?
April 16, 2021
Snowflake

How to Remove Newline Characters from String in Snowflake?

If you store a string as a variable or a column in relational databases such SQL Server , then it can contain line breaks or newline (\n) characters. But, these newline characters have to be removed in pre-processing steps. In this article, we will check how to remove newline characters from a string or text in Snowflake. Remove Newline Characters from String in Snowflake Removing newline (\n), carriage return (\r) or any special characters is the common pre-processing step before storing records in any relational databases. Many databases supports built-in…

Comments Off on How to Remove Newline Characters from String in Snowflake?
September 10, 2020