Redshift

How to Use Nested Window Functions in Amazon Redshift (With Examples)?

Are you struggling with the notorious "nested aggregate or window function" error in Amazon Redshift? While Redshift is a robust data warehousing solution, its limitations around nesting analytical functions can be a roadblock. Fortunately, there are simple and effective SQL workarounds to get your reporting queries running smoothly. Amazon Redshift does not allow you to define the nested window function in your queries. You will have to use alternative methods such as common table expressions (CTEs) or create table subquery to calculate value for windows function and again apply another…

Comments Off on How to Use Nested Window Functions in Amazon Redshift (With Examples)?
July 21, 2021
Snowflake

How to Write Parameterized Queries in Snowflake (Using Session Variables)?

Writing dynamic, reusable code is a cornerstone of efficient data engineering. In Snowflake, learning how to write parameterized queries allows you to build flexible SQL scripts that adapt to changing conditions without requiring constant manual rewrites, ultimately saving you time and reducing maintenance overhead. Page Content Introduction Why Do We Need Parameterized Queries in Snowflake? How to Write Parameterized Queries in Snowflake How to Set and Use Session Variables Using the Snowflake Identifier Function Conclusion Introduction Snowflake supports nearly all the robust features found in legacy relational databases like Oracle…

Comments Off on How to Write Parameterized Queries in Snowflake (Using Session Variables)?
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…

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…

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
Apache Spark

How to Search String in Spark DataFrame? – Scala and PySpark

Being a data engineer, you may work with many different kinds of datasets. You will always get a requirement to filter out or search for a specific string within a data or DataFrame. For example, identify the junk string within a dataset. In this article, we will check how to search a string in Spark DataFrame using different methods. How to Search String in Spark DataFrame? Apache Spark supports many different built in API methods that you can use to search a specific strings in a DataFrame. Following are the…

Comments Off on How to Search String in Spark DataFrame? – Scala and PySpark
June 28, 2021
Apache Spark

How to Find Tables Size in Spark SQL? – Scala Example

Be it relational database, Hive, or Spark SQL, Finding the table size is one of the common requirements. Relational databases such as Snowflake, Teradata, etc support system tables. You can use those system tables to identify the size of tables. But, there are no system tables in Spark SQL. You can make use of the Spark catalog API to find the tables size in the Spark SQL database. Find Tables Size in Spark SQL Starting version 2.0, Spark supports catalog API. It has many useful methods such as listtables, listdatabases,…

Comments Off on How to Find Tables Size in Spark SQL? – Scala Example
June 28, 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