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…

Continue ReadingWhat are SELECT INTO Alternatives in Snowflake?
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…

Continue ReadingWhat are Different Methods to Create Snowflake Tables?
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…

Continue ReadingSnowflake Unsupported subquery Issue and How to resolve it
Comments Off on Snowflake Unsupported subquery Issue and How to resolve it
November 10, 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…

Continue ReadingHow to Remove Newline Characters from String in Snowflake?
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…

Continue ReadingHow to Remove Spaces in the String in snowflake?
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,…

Continue ReadingConvert Unix epoch time into Snowflake Date
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…

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

Convert Permanent table to Transient Table in Snowflake

Snowflake Transient tables are similar to that of permanent tables only difference is that they don not support fail-safe period. Therefore, cost associated with fail-safe is not applicable to transient tables. You can use transient tables in an ETL design to hold temporary data. In this article, we will check how to convert permanent table to transient table in Snowflake. Transient Table in Snowflake As mentioned earlier, transition tables are similar to managed tables with key difference such as fail-safe is not available. The transient tables are designed for transitory…

Continue ReadingConvert Permanent table to Transient Table in Snowflake
Comments Off on Convert Permanent table to Transient Table in Snowflake
May 2, 2020
Snowflake

Working with Snowflake External Tables and S3 Examples

Snowflake External tables allow you to access files stored in external stage as a regular table. You can join the Snowflake external table with permanent or managed table to get required information or perform the complex transformations involving various tables. The External tables are commonly used to build the data lake where you access the raw data which is stored in the form of file and perform join with existing tables. Snowflake External Tables As mentioned earlier, external tables access the files stored in external stage area such as Amazon…

Continue ReadingWorking with Snowflake External Tables and S3 Examples
Comments Off on Working with Snowflake External Tables and S3 Examples
April 7, 2020
Snowflake

How to Create Snowflake Clustered Tables? Examples

Snowflake cloud data warehouse produces create clustered tables by default. However, as the table size grows and DML occurs on the table, the data in some table rows may no longer cluster optimally on desired dimensions. In this article, we will check how to create Snowflake clustered tables to improve the DML query performance. Snowflake Clustered Tables When you create a table and insert records into the Snowflake tables, inserted rows are grouped into continuous storage such as micro-partitions. But, as the table size grows, data in some column may…

Continue ReadingHow to Create Snowflake Clustered Tables? Examples
Comments Off on How to Create Snowflake Clustered Tables? Examples
April 5, 2020