Snowflake

Snowflake NULL Functions: A Complete Guide with Examples

Working with dirty data is a daily reality for data engineers and analysts, and missing values are one of the most common culprits. Handling these missing values effectively ensures your reporting and analytics remain accurate and reliable. Lets see how to handle these missing data using Snowflake NULL functions. Page Content Introduction Overview of Snowflake NULL Functions Snowflake NVL and NVL2 Functions COALESCE Function in Snowflake IFNULL Function in Snowflake ZEROIFNULL Function in Snowflake NULLIF Function in Snowflake IS NULL and IS NOT NULL Operators EQUAL_NULL Function in Snowflake IS_NULL_VALUE…

Comments Off on Snowflake NULL Functions: A Complete Guide with Examples
August 3, 2021
Snowflake

How to Create View Dynamically in Snowflake?

The best part about Snowflake is that it supports almost all features that are available in any modern data warehouse. One of such a feature is dynamic SQL. You can execute SQL statements dynamically using Snowflake stored procedures. Snowflake supports JavaScript API to write stored procedures and user-defined functions. Note that, there is a difference between Snowflake stored procedure and UDFs. We will use stored procedures to create a view dynamically in Snowflake. Create View Dynamically in Snowflake In a data warehouse application, you will always get requirement to write…

Comments Off on How to Create View Dynamically in Snowflake?
July 30, 2021
Snowflake

Snowflake Conditional Insert: Multi-Table Syntax and Examples

Are you looking for a faster way to route data into multiple tables without writing separate queries? Using a Snowflake conditional insert allows you to seamlessly distribute data from a single source into various target tables. Page Content Introduction Understanding Multi-Table Inserts in Snowflake Conditional Inserts into Snowflake Tables Conditional Insert Syntax Examples of Conditional Inserts (ALL, FIRST, VALUES, OVERWRITE) Unconditional Inserts into Snowflake Tables Unconditional Insert Syntax and Examples Conclusion Introduction The Snowflake cloud data warehouse offers several advanced features that simplify data engineering workflows, and one standout capability…

Comments Off on Snowflake Conditional Insert: Multi-Table Syntax and Examples
July 28, 2021
Snowflake

How to Drop or Reset All SQL Variables in a Snowflake Session?

Managing session variables in Snowflake can sometimes feel like a juggling act, especially when dealing with complex scripts or parameterized queries. If you have ever needed a quick way to clean the slate without manually unsetting dozens of values, you are in the right place. Page Content Introduction Understanding Snowflake SQL Variables Essential Commands: SET, UNSET, and SHOW Method 1: Drop All Variables Using a Stored Procedure (Recommended) Method 2: Abort the Current Snowflake Session Conclusion Introduction Snowflake allows users to declare SQL session variables to store application-specific environmental variables,…

Comments Off on How to Drop or Reset All SQL Variables in a Snowflake Session?
July 27, 2021
Snowflake

Snowflake LIMIT and OFFSET: Syntax, Use Cases, and Examples

Are you trying to restrict the number of rows returned by your SQL queries without pulling the entire dataset? Using the LIMIT and OFFSET clauses in Snowflake is the most efficient way to achieve this. Page Content Introduction Snowflake LIMIT and OFFSET Overview Snowflake LIMIT Clause in Action Snowflake LIMIT Clause Parameters Snowflake OFFSET Clause for Pagination Snowflake OFFSET Clause Parameters Snowflake OFFSET Without LIMIT Conclusion Introduction When working with massive datasets, fetching every single row isn't always practical or necessary. The LIMIT clause in Snowflake is designed to restrict…

Comments Off on Snowflake LIMIT and OFFSET: Syntax, Use Cases, and Examples
July 26, 2021
Snowflake

How to Query S3 External Files in Snowflake (Step-by-Step Guide)?

You can integrate the cloud storages, such as AWS S3, Azure Blob and GCP cloud storage with Snowflake cloud data warehouse very easily. Snowflake does support external tables, you can create external tables on the top of the files stored on the external storages such as S3, blob or GCP storage. In this article, we will check how to query S3 external files directly in Snowflake. Are you looking to seamlessly access your AWS S3 data without loading it first? Snowflake makes it incredibly easy to query S3 external files…

Comments Off on How to Query S3 External Files in Snowflake (Step-by-Step Guide)?
July 26, 2021
Snowflake

A Comprehensive Guide to Snowflake Error Handling: Procedures and Functions

Handling exceptions in a cloud data warehouse doesn't have to be a headache. By using proper method for Snowflake error handling, you can build resilient data pipelines that gracefully manage failures and bad data without interrupting your workflows. Page Content Introduction Snowflake Stored Procedure Error Handling Error Handling in Snowflake Scripting (SQL) Error Handling in Snowflake JavaScript Stored Procedure Handling Errors in Snowflake User Defined Functions (UDF) Using the TRY_CAST Function for Safe Type Conversions Conclusion Introduction The Snowflake Cloud data warehouse offers robust support for stored procedures, making migration…

2 Comments
July 23, 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