Snowflake

How Snowflake Internally Handles Updates? – Explanation

When you load data into Snowflake, Snowflake reorganizes that data into micro partition and stores into its internal optimized, compressed, columnar format. Snowflake stores this optimized data in cloud storage. Snowflake uses S3, Blob storage or GCP cloud storage. However, all these storages are immutable. Obviously the question would be how Snowflake internally performs or handles updates when you execute the update command? How Snowflake Internally Handles Updates? Many people would have thought of this question when they were going through Snowflake architecture. It is a complex question. Snowflake uses…

Comments Off on How Snowflake Internally Handles Updates? – Explanation
June 1, 2021
Snowflake

Snowflake Array Functions – Syntax and Examples

It is very common practice to store values in the form of an array in the databases. Without a doubt, Snowflake supports many array functions. You can use these array manipulation functions to manipulate the array types. In this article, we will check how to work with Snowflake Array Functions, syntax and examples to manipulate array types. Snowflake Array Functions Following is the list of Snowflake array functions with brief descriptions: Array FunctionsDescriptionARRAY_AGGFunction returns the input values, pivoted into an ARRAY.ARRAY_APPENDThis function returns an array containing all elements from the…

Comments Off on Snowflake Array Functions – Syntax and Examples
May 26, 2021
Snowflake

What are SELECT INTO Alternatives in Snowflake?

In my other post, I have discussed about what are different methods to create Snowflake table. There are many database specific syntaxes that are not supported in Snowflake yet. One of such syntax is SELECT INTO. The databases such as SQL Server, Reshift, Teradata, etc. supports SELECT INTO clause to create new table and insert the resulting rows from the query into it. In this article, we will check what are SELECT INTO alternatives in Snowflake with some examples. SELECT INTO Alternatives in Snowflake In the databases such as SQL…

Comments Off on What are SELECT INTO Alternatives in Snowflake?
April 16, 2021
Snowflake

What are Different Methods to Create Snowflake Tables?

In my other posts, I have discussed on how to create Snowflake clustered tables, creating external tables in Snowflake, etc. In this article, we will check what are different methods to create Snowflake tables with some basic examples. Different Methods to Create Snowflake Tables During database development, developer create a table such as permanent, temporary or transient tables as per the requirement. Developers usually create tables using DDL such as “CREATE TABLE” statement. But, sometimes you may need to use different methods such as creating a copy of an existing…

Comments Off on What are Different Methods to Create Snowflake Tables?
April 16, 2021
Snowflake

Snowflake Unsupported subquery Issue and How to resolve it

Snowflake provides rich support of subqueries. But, some scalar subqueries that are available in the relational databases such as Oracle are not supported in Snowflake yet. We have to identify the alternate methods for such a subqueries. In this article, we will check Snowflake unsupported subquery issue and how to resolve it? Snowflake Unsupported subquery Issue SQL compilation error: Unsupported subquery type cannot be evaluated You are here because you might have got above error message during SQL migration from databases such as Oracle. For example, consider following Oracle query…

Comments Off on Snowflake Unsupported subquery Issue and How to resolve it
November 10, 2020
Redshift

Redshift vs Snowflake – Key Differences

Data is the lifeline for many companies. Be it is an advertising company or decision making company, data plays an important role in each and every step. To understand and utilize all this data, data warehouses have become an essential part of modern business. There are many data warehouse appliance such as Teradata, Vertica, Greenplum, etc are available in the market. But, nowadays, organizations are more inclined towards cloud data warehouses such as Snowflake, Redshift, Azure SQL Synapse, Google cloud Spanner, etc. In this article, we will check key differences…

Comments Off on Redshift vs Snowflake – Key Differences
September 14, 2020
Snowflake

How to Remove Newline Characters from String in Snowflake?

If you store a string as a variable or a column in relational databases such SQL Server , then it can contain line breaks or newline (\n) characters. But, these newline characters have to be removed in pre-processing steps. In this article, we will check how to remove newline characters from a string or text in Snowflake. Remove Newline Characters from String in Snowflake Removing newline (\n), carriage return (\r) or any special characters is the common pre-processing step before storing records in any relational databases. Many databases supports built-in…

Comments Off on How to Remove Newline Characters from String in Snowflake?
September 10, 2020
Snowflake

How to Remove Spaces in the String in snowflake?

In a data warehouse, you will receive data from multiple sources. You may have to pre-process the data before loading it to target table. The pre-process step such as removing white spaces from data is commonly used. In this article, we will check how to remove spaces in a string using Snowflake built-in functions. Remove Spaces in a String in snowflake Nowadays, data is required everywhere. Many organizations automatically capture the data using tools or machines. Machines may introduce the unwanted data such as white space when it captures the…

Comments Off on How to Remove Spaces in the String in snowflake?
September 8, 2020
Snowflake

Convert Unix epoch time into Snowflake Date

Many relational databases such as PostgreSQL support functions to convert Unix epoch time into a date or timestamp value. But, there are no built-in functions available in Snowflake that you can use to convert epoch time. You will have to use an alternate approach to convert epoch format. In this article, we will check how to convert Unix epoch time into the Snowflake date with some examples. Convert Unix epoch time into Snowflake Date The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1,…

Comments Off on Convert Unix epoch time into Snowflake Date
September 7, 2020
Snowflake

Handle Cursor in Snowflake Stored Procedures – Examples

Snowflake stored procedures are used to encapsulate the data migration, data validation and business specific logic's. Stored procedure also handles an exceptions if any in your data or custom exception handling. The relational databases such as Oracle, Redshift, Netezza, etc. supports cursor variables. In this article, we will check how to handle cursor variable in Snowflake stored procedures with an example. Handle Cursor in Snowflake Stored Procedures In a relational database, cursors are extensively used in stored procedures to loop through the records from SELECT statements. Stored procedures encapsulate the business logic. For…

Comments Off on Handle Cursor in Snowflake Stored Procedures – Examples
May 2, 2020