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

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

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

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