Snowflake

Snowflake Sequence – How to Create and Use it?

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…

Comments Off on Snowflake Sequence – How to Create and Use it?
December 23, 2019
Snowflake

Export Snowflake Table Data to Local CSV format

The exporting tables to local system is one of the common requirements. You may need to export Snowflake table to analyze the data or transport it to a different team. You can export the Snowflake schema in different ways, you can use COPY command, or Snowsql command options. In this articles, we will check how to export Snowflake table data to a local CSV format. Using the COPY command may be the fastest method. But, it will unload tables to S3 location. You need to have an AWS subscription to…

Comments Off on Export Snowflake Table Data to Local CSV format
December 22, 2019
Snowflake

Snowflake Extract Date using Regular Expression Functions

In my other article, we have seen how to extract number or numeric values from a string. The other common requirement is to extract date from a string data. For example, you may get requirement to extract date of birth from the string field. Snowflake supports various date functions to validate a date value. In this article, we will check how to extract date from a string field using Snowflake regular expression functions. Extract Date using Snowflake Regular Expression Functions In my other article, we have discussed on Snowflake regular…

Comments Off on Snowflake Extract Date using Regular Expression Functions
December 21, 2019
Snowflake

Snowflake Extract Numbers using Regular Expression Functions

There are various requirements to extract numbers from string value. This requirement comes when you work with different data source. For example, heterogeneous data may contain many unwanted values and requirements will be to get only numbers or numeric values. Snowflake supports many built-in and regular expression functions. In this article, we will check how to extract numbers or numeric values from a string using Snowflake regular expression functions. Extract Numbers using Snowflake Regular Expression Functions In my other article, we have discussed on Snowflake regular expressions. We will use…

Comments Off on Snowflake Extract Numbers using Regular Expression Functions
December 21, 2019
Snowflake

Snowflake Set Operators: UNION, EXCEPT/MINUS and INTERSECT

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…

Comments Off on Snowflake Set Operators: UNION, EXCEPT/MINUS and INTERSECT
December 18, 2019
Snowflake

Identify and Remove Duplicate Records from Snowflake Table

Snowflake supports primary, foreign key(s) and unique key(s), but, does not enforce them. Snowflake table allows you to insert duplicate rows. There are chances that some application may insert the records multiple times. There are several methods you can use to de-duplicate the snowflake tables. In this article, we will check how to identify and remove duplicate records from Snowflake table. Remove Duplicate Records from Snowflake Table There are many methods that you can use to remove the duplicate records from the Snowflake table. For example, use the DISTINCT keyword…

Comments Off on Identify and Remove Duplicate Records from Snowflake Table
December 18, 2019
Snowflake

Snowflake WITH Clause Syntax, Usage and Examples

Snowflake WITH Clause is an optional clause that always precedes SELECT clause in the query statements or construct. The WITH clause usually contains a sub query that is defined as a temporary table similar to View definition. Each sub query in the WITH clause is associated with the name, an optional list of a column names, and a query that evaluates to a table. The query expression is usually a select statement. In this article, we will check Snowflake WITH clause syntax, usage, types of WITH clause with some examples.…

Comments Off on Snowflake WITH Clause Syntax, Usage and Examples
December 17, 2019
Snowflake

Snowflake Regular Expression Functions and Examples

The regular expressions are commonly used functions in programming languages such as Python, Java, R, etc. The Snowflake regular expression functions identify the precise pattern of the characters in given string. Regular expressions are commonly used in validating strings, for example, extracting numbers from the string values, etc. In this article, we will check the supported Regular expression functions in Snowflake. Snowflake Regular Expression Functions The regular expression functions are string functions that match a given regular expression. These functions are commonly called as a 'regex' functions. Below are some…

Comments Off on Snowflake Regular Expression Functions and Examples
December 17, 2019
General

Teradata Type Conversion Functions and Examples

Teradata is one of the common and widely used MPP database. Just like many relational databases, Teradata supports many useful functions. You can use these functions to covert the value of one data type to another. In this article, will check commonly used Teradata type conversion functions with some examples. The type conversion functions use common calling function i.e. the first argument is the value to be formatted or converted, and the second argument is a template that defines the output or input format. These conversion functions should be used…

Comments Off on Teradata Type Conversion Functions and Examples
November 20, 2019
General

How to Check Integer Type Values in Teradata? Example

Data validation is one of the most important task in the data warehouse environment. Data validation includes, integer type check, count check, etc. For instance, check the row count after data migration. In my other article, we have discussed how to identify the decimal type values. In this article, we will see how to check integer type values in Teradata with some example. Integer type check can also be refereed as a integer data validation. Teradata Integer Type Values Check Just like many other relational databases, Teradata also provides many…

Comments Off on How to Check Integer Type Values in Teradata? Example
November 20, 2019