Snowflake Archives - Page 2 of 11

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

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

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

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

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?

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?…

Comments Off

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

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

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

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

June 22, 2021