Snowflake DSA-C02 Online Practice Questions and Exam Preparation

Snowflake DSA-C02 Online Questions & Answers

• Question 51:

  Data Scientist used streams in ELT (extract, load, transform) processes where new data inserted in-to a staging table is tracked by a stream. A set of SQL statements transform and insert the stream contents into a set of production tables. Raw data is coming in the JSON format, but for analysis he needs to transform it into relational columns in the production tables. Which of the following Data transformation SQL function he can used to achieve the same?

  A. He could not apply Transformation on Stream table data.
  B. lateral flatten()
  C. METADATA$ACTION ()
  D. Transpose()
  B. lateral flatten()

  ---

  Explanation/Reference:

  To know about lateral flatten SQL Function, please refer: https://docs.snowflake.com/en/sql-reference/constructs/join-lateral#example-of-using- lateral-with-flatten

• Question 52:

  Which one is not the types of Feature Engineering Transformation?

  A. Scaling
  B. Encoding
  C. Aggregation
  D. Normalization
  C. Aggregation

  ---

  Explanation/Reference:

  What is Feature Engineering?

  Feature engineering is the process of transforming raw data into features that are suitable for ma-chine learning models. In other words, it is the process of selecting, extracting, and transforming the most relevant features from the available

  data to build more accurate and efficient machine learning models.

  The success of machine learning models heavily depends on the quality of the features used to train them. Feature engineering involves a set of techniques that enable us to create new features by combining or transforming the existing

  ones. These techniques help to highlight the most important pat-terns and relationships in the data, which in turn helps the machine learning model to learn from the data more effectively.

  What is a Feature?

  In the context of machine learning, a feature (also known as a variable or attribute) is an individual measurable property or characteristic of a data point that is used as input for a machine learning al-gorithm. Features can be numerical,

  categorical, or text-based, and they represent different aspects of the data that are relevant to the problem at hand. For example, in a dataset of housing prices, features could include the number of bedrooms, the square footage, the location,

  and the age of the property. In a dataset of customer demographics, features could include age, gender, income level, and occupation. The choice and quality of features are critical in machine learning, as they can greatly impact the accuracy and performance of the model.

  Why do we Engineer Features?

  We engineer features to improve the performance of machine learning models by providing them with relevant and informative input data. Raw data may contain noise, irrelevant information, or missing values, which can lead to inaccurate or

  biased model predictions. By engineering features, we can extract meaningful information from the raw data, create new variables that capture important patterns and relationships, and transform the data into a more suitable format for

  machine learning algorithms. Feature engineering can also help in addressing issues such as overfitting, underfitting, and high di-mensionality. For example, by reducing the number of features, we can prevent the model from be-coming too

  complex or overfitting to the training data. By selecting the most relevant features, we can improve the model's accuracy and interpretability. In addition, feature engineering is a crucial step in preparing data for analysis and decision- making

  in various fields, such as finance, healthcare, marketing, and social sciences. It can help uncover hidden insights, identify trends and patterns, and support data-driven decision-making.

  We engineer features for various reasons, and some of the main reasons include:

  Improve User Experience: The primary reason we engineer features is to enhance the user experience of a product or service. By adding new features, we can make the product more intuitive, efficient, and user-friendly, which can increase

  user satisfaction and engagement. Competitive Advantage: Another reason we engineer features is to gain a competitive advantage in the marketplace. By offering unique and innovative features, we can differentiate our product from

  competitors and attract more customers. Meet Customer Needs: We engineer features to meet the evolving needs of customers. By analyzing user feedback, market trends, and customer behavior, we can identify areas where new features

  could enhance the product's value and meet customer needs. Increase Revenue: Features can also be engineered to generate more revenue. For example, a new feature that streamlines the checkout process can increase sales, or a feature

  that provides additional functionality could lead to more upsells or cross-sells. Future-Proofing: Engineering features can also be done to future-proof a product or service. By an-ticipating future trends and potential customer needs, we can

  develop features that ensure the product remains relevant and useful in the long term.

  Processes Involved in Feature Engineering

  Feature engineering in Machine learning consists of mainly 5 processes: Feature Creation, Feature Transformation, Feature Extraction, Feature Selection, and Feature Scaling. It is an iterative process that requires experimentation and

  testing to find the best combination of features for a given problem. The success of a machine learning model largely depends on the quality of the features used in the model.

  Feature Transformation

  Feature Transformation is the process of transforming the featuresinto a more suitable representation for the machine learning model. This is done to ensure that the model can effectively learn from the data.

  Types of Feature Transformation:

  Normalization: Rescaling the features to have a similar range, such as between 0 and 1, to prevent some features from dominating others.

  Scaling: Rescaling the features to have a similar scale, such as having a standard deviation of 1, to make sure the model considers all features equally. Encoding: Transforming categorical features into a numerical representation. Examples

  are one-hot encoding and label encoding.

  Transformation: Transforming the features using mathematical operations to change the distribution or scale of the features. Examples are logarithmic, square root, and reciprocal transformations.

• Question 53:

  Which object records data manipulation language (DML) changes made to tables, including inserts, updates, and deletes, as well as metadata about each change, so that actions can be taken using the changed data of Data Science Pipelines?

  A. Task
  B. Dynamic tables
  C. Stream
  D. Tags
  E. Delta
  F. OFFSET
  C. Stream

  ---

  Explanation/Reference:

  A stream object records data manipulation language (DML) changes made to tables, including inserts, updates, and deletes, as well as metadata about each change,so that actions can be taken using the changed data. This process is referred to as change data capture (CDC). An individual table stream tracks the changes made to rows in a source table. A table stream (also referred to as simply a "stream") makes a "change table" available of what changed, at therow level, between two transactional points of time in a table. This allows querying and consuming a sequence of change records in a transactional fashion. Streams can be created to query change data on the following objects: Standard tables, including shared tables. Views, including secure views Directory tables Event tables

• Question 54:

  Which command manually triggers a single run of a scheduled task (either a standalone task or the root task in a DAG) independent of the schedule defined for the task?

  A. RUN TASK
  B. CALL TASK
  C. EXECUTE TASK
  D. RUN ROOT TASK
  C. EXECUTE TASK

  ---

  Explanation/Reference:

  The EXECUTE TASK command manually triggers a single run of a scheduled task (either a standalone task or the root task in a DAG) independent of the schedule defined for the task. A successful run of a roottask triggers a cascading run of child tasks in the DAG as their precedent task completes, as though the root task had run on its defined schedule. This SQL command is useful for testing new or modified standalone tasks and DAGs before you enable them to execute SQL code in production. Call this SQL command directly in scripts or in stored procedures. In addition, this command sup-ports integrating tasks in external data pipelines. Any third-party services that can authenticate into your Snowflake account and authorize SQL actions can execute the EXECUTE TASK command to run tasks.

• Question 55:

  Select the Data Science Tools which are known to provide native connectivity to Snowflake?

  A. Denodo
  B. DvSUM
  C. DiYotta
  D. HEX
  D. HEX

  ---

  Explanation/Reference:

  Hex -- collaborative data science and analytics platform Denodo -- data virtualization and federation platform DvSum -- data catalog and data intelligence platform Diyotta -- data integration and migration

• Question 56:

  Which of the following Snowflake parameter can be used to Automatically Suspend Tasks which are running Data science pipelines after specified Failed Runs?

  A. SUSPEND_TASK
  B. SUSPEND_TASK_AUTO_NUM_FAILURES
  C. SUSPEND_TASK_AFTER_NUM_FAILURES
  D. There is none as such available.
  C. SUSPEND_TASK_AFTER_NUM_FAILURES

  ---

  Explanation/Reference:

  Automatically Suspend Tasks After Failed Runs

  Optionally suspend tasks automatically after a specified number of consecutive runs that either fail or time out. This feature can reduce costs by suspending tasks that consume Snowflake credits but fail to run to completion. Failed task runs

  include runs in which the SQL code in the task body either produces a user error or times out. Task runs that are skipped, canceled, or that fail due to a sys-tem error are considered indeterminate and are not included in the count of failed

  task runs.

  Set the SUSPEND_TASK_AFTER_NUM_FAILURES = num parameter on a standalone task or the root task in a DAG. When the parameter is set to a value greater than 0, the following behavior applies to runs of the standalone task or DAG:

  Standalone tasks are automatically suspended after the specified number of consecutive task runs either fail or time out.

  The root task is automatically suspended after the run of any single task in a DAG fails or times out the specified number of times in consecutive runs. The parameter can be set when creating a task (using CREATE TASK) or later (using

  ALTER TASK). The setting applies to tasks that rely on either Snowflake-managed compute resources (i.e. serverless compute model) or user-managed compute resources (i.e. a virtual warehouse).

  The SUSPEND_TASK_AFTER_NUM_FAILURES parameter can also be set at the account, database, or schema level. The setting applies to all standalone or root tasks contained in the modified object. Note that explicitly setting the

  parameter at a lower (i.e. more granular) level overrides the parameter value set at a higher level.

• Question 57:

  Which ones are the key actions in the data collection phase of Machine learning included? Choose 2.

  A. Label
  B. Ingest and Aggregate
  C. Probability
  D. Measure
  A. Label
  B. Ingest and Aggregate

  ---

  Explanation/Reference:

  The key actions in the data collection phase include:

  Label: Labeled data is the raw data that was processed by adding one or more meaningful tags so that a model can learn from it. It will take some work to label it if such information is missing (manually or automatically).

  Ingest and Aggregate: Incorporating and combining data from many data sources is part of data collection in AI.

  Data collection

  Collecting data for training the ML model is the basic step in the machine learning pipeline. The predictions made by ML systems can only be as good as the data on which they have been trained. Following are some of the problems that can

  arise in data collection:

  Inaccurate data. The collected data could be unrelated to the problem statement. Missing data. Sub-data could be missing. That could take the form of empty values in columns or missing images for some class of prediction. Data imbalance.

  Some classes or categories in the data may have a disproportionately high or low number of corresponding samples. As a result, they risk being under- represented in the model.

  Data bias. Depending on how the data, subjects and labels themselves are chosen, the model could propagate inherent biases on gender, politics, age or region, for example.

  Data bias is difficult to detect and remove.

  Several techniques can be applied to address those problems:

  Pre-cleaned, freely available datasets. If the problem statement (for example, image classification, object recognition) aligns with a clean, pre-existing, properly formulated dataset, then take ad-vantage of existing, open-source expertise. Web

  crawling and scraping. Automated tools, bots and headless browsers can crawl and scrape websites for data.

  Private data. ML engineers can create their own data. This is helpful when the amount of data required to train the model is small and the problem statement is too specific to generalize over an open-source dataset.

  Custom data. Agencies can create or crowdsource the data for a fee.

• Question 58:

  How do you handle missing or corrupted data in a dataset?

  A. Drop missing rows or columns
  B. Replace missing values with mean/median/mode
  C. Assign a unique category to missing values
  D. All of the above
  D. All of the above

• Question 59:

  Which of the Following is not type of Windows function in Snowflake? Choose 2.

  A. Rank-related functions.
  B. Window frame functions.
  C. Aggregation window functions.
  D. Association functions.
  C. Aggregation window functions.
  D. Association functions.

  ---

  Explanation/Reference:

  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.

• Question 60:

  Which are the following additional Metadata columns Stream contains that could be used for creating Efficient Data science Pipelines and helps in transforming only the New/Modified data only? Choose 3.

  A. METADATA$ACTION
  B. METADATA$FILE_ID
  C. METADATA$ISUPDATE
  D. METADATA$DELETE
  E. METADATA$ROW_ID
  A. METADATA$ACTION
  C. METADATA$ISUPDATE
  E. METADATA$ROW_ID

  ---

  Explanation/Reference:

  A stream stores an offset for the source object and not any actual table columns or data. When que-ried, a stream accesses and returns the historic data in the same shape as the source object (i.e. the same column names and ordering) with the following additional columns: METADATA$ACTION Indicates the DML operation (INSERT, DELETE) recorded. METADATA$ISUPDATE Indicates whether the operation was part of an UPDATE statement. Updates to rows in the source object are represented as a pair of DELETE and INSERT records inthe stream with a metadata column METADATA$ISUPDATE values set to TRUE. Note that streams record the differences between two offsets. If a row is added and then updated in the current offset, the delta change is a new row. The METADATA$ISUPDATE row records a FALSE value. METADATA$ROW_ID Specifies the unique and immutable ID for the row, which can be used to track changes to specific rows over time.