Snowflake

Snowflake Dynamic SQL Queries and Examples

When you migrate the data from other relational databases such as Netezza, Oracle, Redshift, etc, you will find the many queries which are generated on the fly and executed. These kind of queries are called dynamic queries. In the data warehouse, there may be a situation where you want to generate dynamic SQL queries based on your requirement. In this article, we will check how to generate and execute dynamic SQL queries in Snowflake with an example. Snowflake Dynamic SQL Queries Snowflake supports generating and executing dynamic queries in stored…

Continue ReadingSnowflake Dynamic SQL Queries and Examples
Comments Off on Snowflake Dynamic SQL Queries and Examples
January 17, 2020
Snowflake

Snowflake Stored Procedures, Syntax, Limitations and Examples

To support migration from other relational databases, Snowflake supports the stored procedures. In this article, we will check Snowflake stored procedures, syntax, usage and restrictions with some examples. Stored procedure combines the complex SQL business logic with procedural statements. Snowflake Stored Procedures Overview Snowflake stored procedures are used to encapsulate the data migration, data validation and business specific logic's and same time handle the exceptions if any in your data or custom exception handling. For example, suppose that you want to clean up a database by deleting data older than a…

Continue ReadingSnowflake Stored Procedures, Syntax, Limitations and Examples
Comments Off on Snowflake Stored Procedures, Syntax, Limitations and Examples
January 17, 2020
Snowflake

Snowflake Cross Database Access, Syntax and Examples

The cross database access does allows you to access the objects such as tables, view, synonyms, functions that are available on the same cloud warehouse server. In this article, we will check Snowflake cross database access and it's restrictions. Snowflake Cross Database Access Using cross database access method, you can refer the object present in another database. For example, you can refer function present in another database and use result in the current database. select demo_db..isnumeric(to_variant('hello')) is_numeric; +------------+ | IS_NUMERIC | |------------| | False | +------------+ You can use cross…

Continue ReadingSnowflake Cross Database Access, Syntax and Examples
Comments Off on Snowflake Cross Database Access, Syntax and Examples
January 16, 2020
Snowflake

Snowflake User Defined Functions, Syntax and Examples

An UDF evaluates an arbitrary SQL expression and returns the result(s) of the expression. Snowflake supports creating user defined function. You can create custom user-defined functions (UDF) using either SQL statements or Java script program. In this article, we will check Snowflake user defined functions, types, syntax, examples and how to create them. Snowflake User Defined Functions The custom user defined functions are bound to the database. But, fortunately, Snowflake supports cross database access. You can create custom user defined functions in one centralized database and access from all other databases.…

Continue ReadingSnowflake User Defined Functions, Syntax and Examples
Comments Off on Snowflake User Defined Functions, Syntax and Examples
January 16, 2020
Snowflake

Snowflake isnumeric Alternative and Examples

The data warehouse combines the data from various sources. Sometimes, data will be corrupted or erroneous values are introduced. For example, you may need to filter out non-numeric values from the salary field. In this article, we will check what is the isnumeric function alternative available in Snowflake. We will also check methods that you can use to validate data or expression. Snowflake isnumeric Function Alternative In a relational database such as SQL Server, isnumeric function is available as a built-in numeric function. But, as of now, Snowflake does not…

Continue ReadingSnowflake isnumeric Alternative and Examples
Comments Off on Snowflake isnumeric Alternative and Examples
January 15, 2020
Snowflake

Snowflake DECODE Function Syntax and Examples

Snowflake cloud data warehouse supports the DECODE function which is similar to other relational databases such as Redhift, Netezza, Oracle, SQL Server, etc. The DECODE function is used to implement simple case statements. i.e. you can use it to implement simple if-then-else statement. This command is similar to the Snowflake CASE statement. Snowflake SQL DECODE Function The DECODE function compares the select expression to each search expression in order. As soon as a search expression matches the selection expression, the corresponding result expression is returned. Snowflake SQL DECODE Function Syntax…

Continue ReadingSnowflake DECODE Function Syntax and Examples
Comments Off on Snowflake DECODE Function Syntax and Examples
January 15, 2020
Snowflake

Difference Between Snowflake Stored Procedure and UDFs – SP vs UDFs

You can use both stored procedure and user defined functions to encapsulate the business logic and achieve specific tasks. However, there are important differences between UDFs and stored procedures. In this article, we will check the difference between Snowflake stored Procedure and user defined functions (UDFs). Difference Between Snowflake Stored Procedure and UDFs The Snowflake stored procedure vs user defined function is an interesting comparison. Both are used to achieve certain functionalities that are not possible using built-in functions available in Snowflake. Following are some import comparison between Snowflake stored…

Continue ReadingDifference Between Snowflake Stored Procedure and UDFs – SP vs UDFs
Comments Off on Difference Between Snowflake Stored Procedure and UDFs – SP vs UDFs
January 14, 2020
Snowflake

Snowflake Transaction Management – BEGIN, COMMIT, ROLLBACK

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…

Continue ReadingSnowflake Transaction Management – BEGIN, COMMIT, ROLLBACK
Comments Off on Snowflake Transaction Management – BEGIN, COMMIT, ROLLBACK
January 14, 2020
Snowflake

Snowflake Date Format Conversion Functions and Examples

The date data types are one of the complicated types in the relational databases. Date types stores year, month, days, hours, minutes, seconds and nanoseconds. The date types also store the timezone information. Snowflake provides many date conversion functions, you can use those to format the date type. Snowflake Date and Time Data Types The Snowflake Date format includes four data types, and are used to store the date, time with timestamp details: DATE: You can use the date type to store year, month, day. TIME: You can use time…

Continue ReadingSnowflake Date Format Conversion Functions and Examples
Comments Off on Snowflake Date Format Conversion Functions and Examples
January 12, 2020
Snowflake

Commonly used Snowflake Date Functions and Examples

The date functions are most commonly used functions in the data warehouse. You can use date functions to manipulate the date expressions or variables containing date and time value. For example, get the current date, subtract date values, etc. In this article, we will check what are commonly used date functions in the Snowflake cloud data warehouse. Many applications use date functions to manipulate the date and time data types. Each date value contains the century, year, month, day, hour, minute, second and milliseconds. Hence, date types are highly formatted…

Continue ReadingCommonly used Snowflake Date Functions and Examples
Comments Off on Commonly used Snowflake Date Functions and Examples
January 11, 2020