Redshift

What is STARTS_WITH Function Alternative in Redshift?

The relational databases such as Oracle and cloud databases such as BigQuery, Snowflake supports starts with function. The function name may be different, but the functionality is same. For example, Oracle and BigQuery provides STARTS_WITH function and Snowflake contains STARTSWITH function. The Amazon Redshift does not provide STARTS_WITH function. In my other article, What is ENDS_WITH Function Alternative in Redshift, we have seen ends_with alternative in Redshift. In this article, we will check what is STARTS_WITH function alternative in AWS Redshift? STARTS_WITH Function In Redshift Not all the string functions…

Comments Off on What is STARTS_WITH Function Alternative in Redshift?
November 30, 2021
General

How to Generate Teradata Object DDL (Tables, Views, UDFs, SPs, & Macros)?

Extracting Data Definition Language (DDL) scripts from your Teradata environment doesn't have to be a frustrating manual process. Whether you are migrating databases, backing up schemas, or setting up version control, knowing how to quickly generate DDL for your objects is an essential database administration skill. Page Content Introduction Generating Table DDL in Teradata Extracting View DDL in Teradata Exporting Stored Procedure DDLs via BTEQ Generating User-Defined Function (UDF) DDLs Extracting Teradata Macro DDLs Conclusion Introduction The Teradata database remains one of the most powerful and widely used relational databases…

Comments Off on How to Generate Teradata Object DDL (Tables, Views, UDFs, SPs, & Macros)?
October 8, 2021
Snowflake

How to Find the Most Queried Table in Snowflake (Using ACCESS_HISTORY)

Do you want to optimize your Snowflake data warehouse and identify which datasets drive the most value for your team? Discovering the most queried table in Snowflake is one of the best ways to understand user behavior, audit access, and manage compute costs effectively. Let’s dive into how you can extract this table usage data using native system views. Page Content Introduction Understanding Snowflake System Views The Snowflake ACCESS_HISTORY View Query to Find the Most Queried Table Conclusion Introduction As a fully managed cloud data warehouse solution, Snowflake takes away…

Comments Off on How to Find the Most Queried Table in Snowflake (Using ACCESS_HISTORY)
August 12, 2021
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