DWgeek.com

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

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

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

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

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

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

Redshift

Redshift Nested Window Functions and Examples

Amazon Redshift cloud data warehouse supports many useful windows or analytical functions. Many Redshift reporting queries use the analytic functions such as cumulative sum and average. But, because of Redshift limitation, you cannot call an analytics function within another. Whenever you try to call an analytics function within another analytics function, you will end up with an error such as "Invalid operation: aggregate function calls may not have nested aggregate or window function". In this article, we will check how to use the nested window functions in Amazon Redshift with…

Comments Off

July 21, 2021