Explanation

What is a Window?

A window is a group of related rows. For example, a window might be defined based on timestamps, with all rows in the same month grouped in the same window. Or a window might be defined based on location, with all rows from a particular city grouped in the same window.

A window can consist of zero, one, or multiple rows. For simplicity, Snowflake documentation usually says that a window contains multiple rows.

What is a Window Function?

A window function is any function that operates over a window of rows.

A window function is generally passed two parameters:

A row. More precisely, a window function is passed 0 or more expressions. In almost all cases, at least one of those expressions references a column in that row. (Most window functions require at least one column or expression, but a few window functions, such as some rank-related functions, do not required an explicit column or expression.)

A window of related rows that includes that row. The window can be the entire table, or a subset of the rows in the table.

For non-window functions, all arguments are usually passed explicitly to the function, for example:

MY_FUNCTION(argument1, argument2, …)

Window functions behave differently; although the current row is passed as an argument the normal way, the window is passed through a separate clause, called an OVER clause. The syntax of the OVER clause is documented later.

LISTAGG

Returns the concatenated input values, separated by the delimiter string.

Window function

1. LISTAGG( [ DISTINCT ] < expr1 > [, < delimiter > ] )

2. [ WITHIN GROUP ( < orderby_clause > ) ]

3. OVER ( [ PARTITION BY < expr2 > ] )

HASH_AGG

Returns an aggregate signed 64-bit hash value over the (unordered) set of input rows. HASH_AGG never returns NULL, even if no input is provided. Empty input “hashes” to 0.

Window function

HASH_AGG( [ DISTINCT ] < expr > [ , < expr2 > ... ] ) OVER ( [ PARTITION BY < expr3 > ] )

HASH_AGG(*) OVER ( [ PARTITION BY < expr3 > ] )

Explanation

Data visualization is the visual depiction of data through the use of graphs, plots, and informational graphics. Its practitioners use statistics and data science to convey the meaning behind data in ethical and accurate ways.

The Supervised Machine Learning algorithm can be broadly classified into Regression and Classification Algorithms. In Regression algorithms, we have predicted the output for continuous values, but to predict the categorical values, we need Classification algorithms.

What is the Classification Algorithm?

The Classification algorithm is a Supervised Learning technique that is used to identify the category of new observations on the basis of training data. In Classification, a program learns from the given dataset or observations and then classifies new observation into a number of classes or groups. Such as, Yes or No, 0 or 1, Spam or Not Spam, cat or dog, etc. Classes can be called as targets/labels or categories.

Unlike regression, the output variable of Classification is a category, not a value, such as "Green or Blue", "fruit or animal", etc. Since the Classification algorithm is a Supervised learning technique, hence it takes labeled input data, which means it contains input with the corresponding output.

In classification algorithm, a discrete output function(y) is mapped to input variable(x).

y=f(x) , where y = categorical output

The best example of an ML classification algorithm is Email Spam Detector.

The main goal of the Classification algorithm is to identify the category of a given dataset, and these algorithms are mainly used to predict the output for the categorical data.

The algorithm which implements the classification on a dataset is known as a classifier. There are two types of Classifications:

Binary Classifier: If the classification problem has only two possible outcomes, then it is called as Binary Classifier.

Examples: YES or NO, MALE or FEMALE, SPAM or NOT SPAM, CAT or DOG, etc.

Multi-class Classifier: If a classification problem has more than two outcomes, then it is called as Multi-class Classifier.

Example: Classifications of types of crops, Classification of types of music.

Binary classification in deep learning refers to the type of classification where we have two class labels – one normal and one abnormal. Some examples of binary classification use:

· To detect whether email is spam or not

· To determine whether or not a patient has a certain disease in medicine.

· To determine whether or not quality specifications were met when it comes to QA (Quality Assurance).

For example, the normal class label would be that a patient has the disease, and the abnormal class label would be that they do not, or vice-versa.

As is with every other type of classification, it is only as good as the binary classification dataset that it has – or, in other words, the more training and data it has, the better it is.

Explanation

Secure Data Sharing lets you share selected objects in a database in your account with other Snow-flake accounts. You can share the following Snowflake database objects:

Tables

External tables

Secure views

Secure materialized views

Secure UDFs

Snowflake enables the sharing of databases through shares, which are created by data providers and “imported” by data consumers.

Explanation

Options for Sharing in Snowflake

You can share data in Snowflake using one of the following options:

· a Listing, in which you offer a share and additional metadata as a data product to one or more ac-counts,

· a Direct Share, in which you directly share specific database objects (a share) to another account in your region,

· a Data Exchange, in which you set up and manage a group of accounts and offer a share to that group.

Explanation

Vectorized Python UDFs let you define Python functions that receive batches of input rows as Pandas DataFrames and return batches of results as Pandas arrays or Series. You call vectorized Py-thon UDFs the same way you call other Python UDFs.

Advantages of using vectorized Python UDFs compared to the default row-by-row processing pat-tern include:

The potential for better performance if your Python code operates efficiently on batches of rows.

Less transformation logic required if you are calling into libraries that operate on Pandas Data-Frames or Pandas arrays.

When you use vectorized Python UDFs:

You do not need to change how you write queries using Python UDFs. All batching is handled by the UDF framework rather than your own code.

As with non-vectorized UDFs, there is no guarantee of which instances of your handler code will see which batches of input.

Explanation

Data processing and analysis cannot happen without data profiling—reviewing source data for con-tent and quality. As data gets bigger and infrastructure moves to the cloud, data profiling is increasingly important.

What is data profiling?

Data profiling is the process of reviewing source data, understanding structure, content and interrelationships, and identifying potential for data projects.

Data profiling is a crucial part of:

· Data warehouse and business intelligence (DW/BI) projects—data profiling can uncover data quality issues in data sources, and what needs to be corrected in ETL.

· Data conversion and migration projects—data profiling can identify data quality issues, which you can handle in scripts and data integration tools copying data from source to target. It can also un-cover new requirements for the target system.

· Source system data quality projects—data profiling can highlight data which suffers from serious or numerous quality issues, and the source of the issues (e.g. user inputs, errors in interfaces, data corruption).

Data profiling involves:

· Collecting descriptive statistics like min, max, count and sum.

· Collecting data types, length and recurring patterns.

· Tagging data with keywords, descriptions or categories.

· Performing data quality assessment, risk of performing joins on the data.

· Discovering metadata and assessing its accuracy.

· Identifying distributions, key candidates, foreign-key candidates, functional dependencies, embedded value dependencies, and performing inter-table analysis.