The extract function in Snowflake extracts specified date or time part from the date, time, or timestamp fields. This function retrieves the sub part such as day, month, year,etc. The extract function is equivalent to date_part() function. In this article, we will check how to use extract and date_part function to get specified sub part. Snowflake Extract Function The extract function is very useful when you are working with various data sources. Sometimes, you may get requirement to extract part of timestamp field such as date and pass it to…

The Snowflake cloud data warehouse works with heterogeneous data sets. You may work with huge amount of data. It is a common requirement to recover the deleted rows or records. During the clean up process, the developer may delete the data accidentally. In this article, we will check how to recover deleted rows in Snowflake using time travel settings. Snowflake Recover Deleted Rows As mentioned, you can recover the deleted records. Snowflake uses the time travel setting to store the historical data. Snowflake Time Travel The Snowflake Time Travel enables…

Most of the analytical databases such as Netezza, Teradata, Oracle, Vertica allow you to use windows function to calculate running total or average. In this article, we will check how to use analytic functions with windows specification to calculate Snowflake Cumulative Sum (running total) or cumulative average with some examples. Snowflake Cumulative SUM and AVERAGE The cumulative sum or running total is one of the interesting problems in the databases where you have to calculate the sum or average using current result and previous (or next) row value. Snowflake Cumulative…

A Sequence is a named object in an individual Snowflake schema and database. These sequences are used to generate unique numbers. You can use sequence to generate unique numbers that can be used as surrogate key values for primary key values. Snowflake Sequence Overview A sequence value can represent a 64-bit two’s complement integer. You can use it wherever you would use numeric values. Snowflake supports user sequences for the four integer types: byteint, smallint, integer, and bigint. For example, you can use sequences in unique columns, primary key columns, etc. You…

Snowflake set operators are used to combine similar resultset from two or more SELECT statements. The data type of the two or more result sets should also match, otherwise explicit type cast has to be used to convert the result set to required data types. Types of Snowflake Set Operators Cloud data warehouse supports the three types of set operators: UNION and UNION ALLINTERSECT EXCEPT or MINUS Snowflake Set Operators Syntax Below is the syntax of Set operators in Snowflake: query { UNION [ ALL ] | INTERSECT | EXCEPT…