Redshift

Redshift Nested Window Functions and Examples

Amazon Redshift cloud data warehouse supports many useful windows or analytical functions. Many Redshift reporting queries use the analytic functions such as cumulative sum and average. But, because of Redshift limitation, you cannot call an analytics function within another. Whenever you try to call an analytics function within another analytics function, you will end up with an error such as "Invalid operation: aggregate function calls may not have nested aggregate or window function". In this article, we will check how to use the nested window functions in Amazon Redshift with…

Continue ReadingRedshift Nested Window Functions and Examples
Comments Off on Redshift Nested Window Functions and Examples
July 21, 2021
Snowflake

How to Write Parameterized Queries in Snowflake?

Snowflake supports almost all features that are available in legacy relational databases such as Oracle, Teradata, etc. Along with those features, Snowflake provides many extensions that will allow you to write efficient queries. One of such features is ability to SET and pass variables to the query, In my other articles, we have discussed how to create parameterized views in Snowflake. In this article, we will check how to write parameterized queries in Snowflake with the help of the SET command. Why Do We Need Queries with Parameters in Snowflake?…

Continue ReadingHow to Write Parameterized Queries in Snowflake?
Comments Off on How to Write Parameterized Queries in Snowflake?
July 20, 2021
Snowflake

How to Get Row Count of Database Tables in Snowflake?

Counting the number of records from the database tables is one of the mandatory checks when you migrate data from one server to another. Snowflake is very rich in metadata information. It captures many useful information such as details about tables, columns, views, etc. In this article, we will write few useful queries to get row count of Snowflake database tables. Get Row Count of Database Tables in Snowflake You can look for object metadata information either in INFROMATION_SCHEMA for a particular database or utilize the ACCOUNT_USAGE that Snowflake provides…

Continue ReadingHow to Get Row Count of Database Tables in Snowflake?
Comments Off on How to Get Row Count of Database Tables in Snowflake?
July 16, 2021
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…

Continue ReadingHow to Handle Duplicate Records in Snowflake Insert?
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…

Continue ReadingSnowflake Nested Window Functions and Examples
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.…

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

How to Add an Identity Column to Existing Snowflake Table?

Because of its unique architecture, Snowflake does not support column constraints such as primary, foreign or unique key column. The only constraint supported is NOT NULL. But, many traditional database architects would like to keep primary keys and populate with unique key values. You can define the identity columns or create sequences and add sequence value as a default. But, you can't add an identity column to the existing table with data. In This article, we will check how to add an identity column to the existing Snowflake table. Add…

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

How to Create Parameterized Views in Snowflake?

A view allows the result of a query to be accessed as if it were a table. It will encapsulate the business logic by combining two or more tables. It can also refer other views. Views serve a many different purposes, including combining tables or views, segregating, and protecting the underlying data. For example, you can create separate views that meet the needs of different types of employees, such as doctors and nurses at a hospital. In this article, we will check how to create parameterized views in Snowflake. Though,…

Continue ReadingHow to Create Parameterized Views in Snowflake?
Comments Off on How to Create Parameterized Views in Snowflake?
June 17, 2021
Snowflake

How to Update JSON field in Snowflake Variant Column?

The update is a common operation in any relational databases. A Snowflake, a leading cloud data warehouse supports some unique features such as built-in support for semi structured data. Snowflake support many built-in functions that allow you yo manipulate semi-structured data, such as JSON and XML data. Its universal data type VARIANT allows you to store semi-structured data including parquet. In this article, we will check how to update JSON field in Snowflake. Update or Replace JSON field in Snowflake Snowflake support functionalities that are present in almost all relational…

Continue ReadingHow to Update JSON field in Snowflake Variant Column?
Comments Off on How to Update JSON field in Snowflake Variant Column?
June 15, 2021
Snowflake

How to combine two arrays in Snowflake?

Snowflake is the one of the databases that combines many useful functions from other relational databases such as Oracle, PostgreSQL, Teradata, etc. Like many other relational databases, Snowflake support many array functions. In this article, we will check how to combine/merge/concatenate two or more arrays in Snowflake. Combine Two Arrays in Snowflake Snowflake allows you to deal with many different kinds of data sets. For example, you can work with JSON, XML or array variables with an ease. As Snowflake integrates many heterogeneous data sets, you may get requirement such…

Continue ReadingHow to combine two arrays in Snowflake?
Comments Off on How to combine two arrays in Snowflake?
June 10, 2021