In a Snowflake, a transaction is a set of SQL statements, both reads and writes on database objects, that are processed as a unit. All the statements in the transaction are either committed or undone using rolled back as a single unit. Similar to other databases such as Redshift, Netezza, etc, Snowflake transactions guarantees ACID properties. In this article, we will discuss about the Snowflake transaction management using BEGIN, COMMIT and ROLLBACK. Snowflake Transaction Management The default automatic commit behavior of the Snowflake database causes each SQL command that runs…

The INTERVAL data types in Snowflake are handy and widely used types when working with date, time variables. Snowflake supports interval types in the same way as other PostgreSQL databases such as Netezza, Redshift, Vertica, Oracle, etc. In this article, we will check interval types with some common use cases. Snowflake Interval Data Types You can express interval types as a combination of the INTERVAL keyword with a numeric quantity and a supported date part; for example: INTERVAL '1 days' or INTERVAL '10 minutes'. The Snowflake INTERVAL functions are commonly…

The derived columns in Snowflake are columns that are derived from the previously calculated columns in same SELECT statement. In this article, we will check how to reuse previously computed or calculated column in Snowflake. These kind of columns are sometime called lateral column alias. What are derived columns? Derived columns or computed columns are virtual columns that are not physically stored in the table. Their values are re-calculated every time they are referenced in a query. In other word, Derived columns are columns that you derive from other previously…

The split string is one of the common requirements in many relational database and programming languages. For example, get username from the string. Many RDBMS provides the functions or methods to split string on a delimiter and extract required text from an array. In this article, we will check Snowflake functions to split string on a delimiter. Snowflake Split String In many relational databases such as Netezza, PostgreSQL, etc, you can use array functions to extract records from split string result. But, Snowflake supports many useful split functions that you…

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…