General

DBT – Export Snowflake Table to S3 Bucket

Are you looking to move your data out of Snowflake and into an AWS S3 bucket using dbt? While dbt is built primarily for transforming data inside your warehouse, you can still use it to unload data with a simple workaround. This guide will show you exactly how to export a DBT Snowflake table to an S3 bucket using custom dbt macros. DBT - Export Snowflake Table to S3 Bucket Page Content Introduction Set Up a Snowflake External Stage Step1: Create Snowflake External Stage Step 2: Write a dbt Macro…

Continue ReadingDBT – Export Snowflake Table to S3 Bucket
Comments Off on DBT – Export Snowflake Table to S3 Bucket
September 30, 2022
Snowflake

Snowflake Scripting Control Structures – IF, WHILE, FOR, REPEAT, LOOP

In my other Snowflake article, we have checked how JavaScript control structure works in Snowflake JavaScript stored procedures. Snowflake Scripting makes it incredibly easy to manage conditional logic and iterations directly within your stored procedures and anonymous blocks. By mastering Snowflake scripting control structures like IF, WHILE, FOR, REPEAT, and LOOP, you can seamlessly migrate existing PL/SQL logic and build robust data pipelines. Let's dive into how these branching and looping structures work with simple, real-world examples. Snowflake Scripting Control Structures Page Content Introduction Snowflake Scripting Control Structures Branching Structures:…

Continue ReadingSnowflake Scripting Control Structures – IF, WHILE, FOR, REPEAT, LOOP
2 Comments
September 21, 2022
GCP BigQuery

Google BigQuery GROUP BY CUBE: Native Example and Alternative

If you are looking to perform multi-dimensional data analysis, the Google BigQuery GROUP BY CUBE function is exactly what you need. While developers previously had to rely on a complex UNION ALL alternative, BigQuery now natively supports this advanced aggregation feature. In this post, we will look at how to use the new native CUBE function and also review the legacy alternative for older codebases. Google BigQuery GROUP BY CUBE Page Content Introduction SQL GROUP BY CUBE in Google BigQuery GROUP BY CUBE BigQuery Alternative Using UNION ALL Conclusion Introduction…

Continue ReadingGoogle BigQuery GROUP BY CUBE: Native Example and Alternative
Comments Off on Google BigQuery GROUP BY CUBE: Native Example and Alternative
September 15, 2022
GCP BigQuery

Google BigQuery GROUPING SETS: Native Example and Alternative

If you are looking to perform multi-dimensional data analysis, the Google BigQuery GROUPING SETS function is exactly what you need. While developers previously had to rely on a complex UNION ALL workaround, BigQuery now natively supports this advanced aggregation feature. In this post, we will look at how to use the new native GROUPING SETS function and also review the legacy alternative for older codebases. Google BigQuery Grouping Sets Page Content Introduction SQL GROUPING SETS in Google BigQuery GROUPING SETS BigQuery Alternative Using UNION ALL Conclusion Introduction In my other…

Continue ReadingGoogle BigQuery GROUPING SETS: Native Example and Alternative
Comments Off on Google BigQuery GROUPING SETS: Native Example and Alternative
September 15, 2022
Snowflake

Oracle DML LOG ERROR Alternative in Snowflake

Are you looking for an Oracle DML LOG ERROR alternative in Snowflake? When migrating your databases, you might realize you miss Oracle's native error-logging features. In this post, we will walk through the best workarounds for Snowflake DML error handling so you can seamlessly capture bad records using simple scripting and Snowpark. Page Content Introduction Oracle DML Error Logging Snowflake Script Error Logging to Capture Reject Rows Snowpark Error Handling to Capture Reject Rows Conclusion Introduction Oracle database offers very friendly error handling options that make life easier for developers…

Continue ReadingOracle DML LOG ERROR Alternative in Snowflake
Comments Off on Oracle DML LOG ERROR Alternative in Snowflake
August 29, 2022
Redshift

Amazon Redshift Delete with Join Syntax and Examples

Data is an integral part of decision making system. Many application reply on a data to make business decisions. In the relational database world, data is stored in the form of tables. Today, we have unlimited storage, but cost is also high to manage storage. Data is collected over the time and it may or may not be accurate. You may have to clean the data by deleting unwanted records or purge the old data from a system that is outdated. In this article, we will discuss Amazon Redshift delete…

Continue ReadingAmazon Redshift Delete with Join Syntax and Examples
Comments Off on Amazon Redshift Delete with Join Syntax and Examples
July 6, 2022
Redshift

Redshift WHERE Clause with Multiple Columns

Migrating from databases like Teradata or Oracle to Amazon Redshift often means looking for familiar SQL patterns. One common challenge developers face is filtering data using multiple columns at once. Fortunately, you can now easily use an Amazon Redshift WHERE clause with multiple columns to get the exact data you need. Page Content Introduction How to Filter Multiple Columns in Redshift (Syntax & Example) Conclusion Introduction When writing Redshift SQL queries, the WHERE clause helps you filter records so you only retrieve matching rows from a table. While older versions…

Continue ReadingRedshift WHERE Clause with Multiple Columns
Comments Off on Redshift WHERE Clause with Multiple Columns
July 6, 2022
Redshift

How to Create a Materialized View in Redshift?

Are your complex queries slowing down your dashboard reporting? You can easily fix this performance issue by storing precomputed results. Let's explore how to create a materialized view in Redshift to speed up your data warehouse. Materialized View in Redshift Page Content Introduction What is a Materialized View in Redshift? Redshift View vs Materialized View Redshift Materialized View Syntax and Example How to Refresh and Drop Materialized Views Redshift Materialized View Limitations Conclusion Introduction Many organizations use Amazon Redshift as their primary data warehouse for dashboards and reporting. If your…

Continue ReadingHow to Create a Materialized View in Redshift?
Comments Off on How to Create a Materialized View in Redshift?
June 16, 2022
Apache Spark

How to Use Spark SQL REPLACE on DataFrame?

Similar to the DataFrame COALESCE function, REPLACE function is one of the important functions that you will use to manipulate string data. Replace function is one of the widely used function in SQL. You can use the replace function to replace values. In this article, we will check how to use Spark SQL replace function on an Apache Spark DataFrame with an example. Spark SQL REPLACE Spark SQL REPLACE on DataFrame In a SQL, replace function removes all occurrences of a specified substring, and optionally replaces them with another string.…

Continue ReadingHow to Use Spark SQL REPLACE on DataFrame?
Comments Off on How to Use Spark SQL REPLACE on DataFrame?
June 16, 2022
General

QUALIFY Clause in Oracle: Native Example and Alternative

If you are moving your database from Teradata to Oracle, you might be searching for a QUALIFY Clause in Oracle. While older versions needed a workaround to filter ordered analytical functions, the highly anticipated QUALIFY clause has officially been introduced in Oracle 26. Below, we will check how to use this new feature and the best fallback method if you are still on an older Oracle version. Page Content Introduction QUALIFY Clause in Oracle 26 QUALIFY Clause Alternative in Oracle Older Versions Conclusion Introduction When writing SQL queries, filtering the…

Continue ReadingQUALIFY Clause in Oracle: Native Example and Alternative
Comments Off on QUALIFY Clause in Oracle: Native Example and Alternative
May 19, 2022