DSA-C02 Questions and Answers

Explanation

From the perspective of a user running a SQL statement, an external function behaves like any other UDF . External functions follow these rules:

External functions return a value.

External functions can accept parameters.

An external function can appear in any clause of a SQL statement in which other types of UDF can appear. For example:

1.select my_external_function_2(column_1, column_2)

2.from table_1;

1.select col1

2.from table_1

3.where my_external_function_3(col2) < 0;

1.create view view1 (col1) as

2.select my_external_function_5(col1)

3.from table9;

An external function can be part of a more complex expression:

1.select upper(zipcode_to_city_external_function(zipcode))

2.from address_table;

The returned value can be a compound value, such as a VARIANT that contains JSON.

External functions can be overloaded; two different functions can have the same name but different signatures (different numbers or data types of input parameters).

Explanation

Data sharing is only supported between Snowflake accounts. As a data provider, you might want to share data with a consumer who does not already have a Snowflake account or is not ready to be-come a licensed Snowflake customer.

To facilitate sharing data with these consumers, you can create reader accounts. Reader accounts (formerly known as “read-only accounts”) provide a quick, easy, and cost-effective way to share data without requiring the consumer to become a Snowflake customer.

Each reader account belongs to the provider account that created it. As a provider, you use shares to share databases with reader accounts; however, a reader account can only consume data from the provider account that created it.

So, Data Sharing is possible between Snowflake & Non-snowflake accounts via Reader Account.

Explanation

Partial dependence plots (PDP) is a useful tool for gaining insights into the relationship between features and predictions. It helps us understand how different values of a particular feature impact model’s predictions.

Explanation

Streams on views support both local views and views shared using Snowflake Secure Data Sharing, including secure views. Currently, streams cannot track changes in materialized views.

stream itself does not contain any table data. A stream only stores an offset for the source object and returns CDC records by leveraging the versioning history for the source object. When the first stream for a table is created, several hidden columns are added to the source table and begin storing change tracking metadata. These columns consume a small amount of storage. The CDC records returned when querying a stream rely on a combination of the offset stored in the stream and the change tracking metadata stored in the table. Note that for streams on views, change tracking must be enabled explicitly for the view and underlying tables to add the hidden columns to these tables.

Streams support repeatable read isolation. In repeatable read mode, multiple SQL statements within a transaction see the same set of records in a stream. This differs from the read committed mode supported for tables, in which statements see any changes made by previous statements executed within the same transaction, even though those changes are not yet committed.

The delta records returned by streams in a transaction is the range from the current position of the stream until the transaction start time. The stream position advances to the transaction start time if the transaction commits; otherwise it stays at the same position.

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 ] [, ] )

2.[ WITHIN GROUP ( <orderby_clause> ) ]

3.OVER ( [ PARTITION BY ] )

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 ] [ , ... ] ) OVER ( [ PARTITION BY ] )

HASH_AGG(*) OVER ( [ PARTITION BY ] )

Explanation

Jupyter Notebook is a web-based interactive computational environment.

The command used to install Jupyter Notebook is pip install jupyter.

The command used to start Jupyter Notebook is jupyter notebook.

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

Window Functions

A window function operates on a group (“window”) of related rows.

Each time a window function is called, it is passed a row (the current row in the window) and the window of rows that contain the current row. The window function returns one output row for each input row. The output depends on the individual row passed to the function and the values of the other rows in the window passed to the function.

Some window functions are order-sensitive. There are two main types of order-sensitive window functions:

Rank-related functions.

Window frame functions.

Rank-related functions list information based on the “rank” of a row. For example, if you rank stores in descending order by profit per year, the store with the most profit will be ranked 1; the second-most profitable store will be ranked 2, etc.

Window frame functions allow you to perform rolling operations, such as calculating a running total or a moving average, on a subset of the rows in the window.

Explanation

By using ethnicity as a sensitive field, and comparing disparity between selection rates and performance metrics for each ethnicity value, you can evaluate the fairness of the model.

Explanation

To return the contents of a DataFrame as a Pandas DataFrame, use the to_pandas method.

For example:

1.>>> python_df = session.create_dataframe(["a", "b", "c"])

2.>>> pandas_df = python_df.to_pandas()

Explanation

Query and process data with a DataFrame object. Refer to Working with DataFrames in Snowpark Python.

Convert custom lambdas and functions to user-defined functions(UDFs) that you can call to process data.

Write a user-defined tabular function (UDTF) that processes data and returns data in a set of rows with one or more columns.

Write a stored procedure that you can call to process data, or automate with a task to build a data pipeline.

Explanation

The drop_duplicates() method removes duplicate rows.

dataframe.drop_duplicates(subset, keep, inplace, ignore_index)

Remove duplicate rows from the DataFrame:

1.import pandas as pd

2.data = {

3."name": ["Peter", "Mary", "John", "Mary"],

4."age": [50, 40, 30, 40],

5."qualified": [True, False, False, False]

6.}

7.

8.df = pd.DataFrame(data)

9.newdf = df.drop_duplicates()

Explanation

Supervised learning methodology applies conditional probability of all the variables with respective the dependent variable and generally conditional probability of variables is nothing but a basic method of estimating the statistics for few random experiments.

Conditional probability is thus the likelihood of an event or outcome occurring based on the occurrence of some other event or prior outcome. Two events are said tobe independent if one event occurring does not affect the probability that the other event will occur.

Explanation

Leave-one-out cross-validation (LOO cross-validation) is not suitable for very large datasets due to the fact that this validation technique requires one model for every sample in the training set to be created and evaluated.

Cross validation

It is a technique to evaluate a machine learning model and it is the basis for whole class of model evaluation methods. The goal of cross-validation is to test the model's ability to predict new data that was not used in estimating it. It works by the idea of splitting dataset into number of subsets, keep a subset aside, train the model, and test the model on the holdout subset.

Leave-one-out cross validation

Leave-one-out cross validation is K-fold cross validation taken to its logical extreme, with K equal to N, the number of data points in the set. That means that N separate times, the function approximator is trained on all the data except for one point and a prediction is made for that point. As be-fore the average error is computed and used to evaluate the model. The evaluation given by leave-one-out cross validation is very expensive to compute at first pass.