DEA-C01 Dumps PDF - DEA-C01 Real Exam Questions Answers [Q14-Q29] : Free Exams Dumps Materials : http://exams.dumpsmaterials.com

NO.14 A SQL UDF evaluates an arbitrary SQL expression and returns the result(s) of the expression. Which value type it can returns?

Single Value

A Set of Rows

Scaler or Tabular depend on input SQL expression

Regex

NO.15 While running an external function, me following error message is received:
Error:function received the wrong number of rows
What iscausing this to occur?

External functions do not support multiple rows

Nested arrays are not supported in the JSON response

The JSON returned by the remote service is not constructed correctly

The return message did not produce the same number of rows that it received

NO.16 A Data Engineer needs to ingest invoice data in PDF format into Snowflake so that the data can be queried and used in a forecasting solution.
….. recommended way to ingest this data?

Use Snowpipe to ingest the files that land in an external stage into a Snowflake table

Use a COPY INTO command to ingest the PDF files in an external stage into a Snowflake table with a VARIANT column.

Create an external table on the PDF files that are stored in a stage and parse the data nto structured data

Create a Java User-Defined Function (UDF) that leverages Java-based PDF parser libraries to parse PDF data into structured data

NO.17 How Data Engineer can do Monitoring of Files which are Staged Internally during Continuous data pipelines loading process? [Select all that apply]

She Can Monitor the files using Metadata maintained by Snowflake i.e. file-name,last_modified date etc.

Snowflake retains historical data for COPY INTO commands executed within the pre-vious 14 days.

She can Monitor the status of each COPY INTO <table> command on the History tab page of the classic web interface.

She can use the DATA_LOAD_HISTORY Information Schema view to retrieve the history of data loaded into tables using the COPY INTO command.

She can use the DATA_VALIDATE function to validate the data files She have loaded and can retrieve any errors encountered during the load.

NO.18 When would a Data engineer use table with the flatten function instead of the lateral flatten combination?

When TABLE with FLATTENrequires another source in the from clause to refer to

WhenTABLE with FLATTENrequires no additional source m the from clause to refer to

Whenthe LATERALFLATTENcombination requires no other source m the from clause to refer to

When table withFLATTENis acting like a sub-query executed for each returned row

NO.19 Which two Account usage views can be used for auditing Dynamic data masking purpose?

MASKING POLICIES

POLICY_REFERENCES

DYNAMIC MASKING POLICIES

DYNAMIC POLICY_REFERENCES

NO.20 Elon, a Data Engineer, needs to Split Semi-structured Elements from the Source files and load them as an array into Separate Columns.
Source File:
1.+———————————————————————-+
2.| $1 |
3.|———————————————————————-|
4.| {“mac_address”: {“host1”: “197.128.1.1”,”host2″: “197.168.0.1”}}, |
5.| {“mac_address”: {“host1”: “197.168.2.1”,”host2″: “197.168.3.1”}} |
6.+———————————————————————-+ Output: Splitting the Machine Address as below.
1.COL1 | COL2 |
2.|———-+———-|
3.| [ | [ |
4.| “197”, | “197”, |
5.| “128”, | “168”, |
6.| “1”, | “0”, |
7.| “1” | “1” |
8.| ] | ] |
9.| [ | [ |
10.| “197”, | “197”, |
11.| “168”, | “168”, |
12.| “2”, | “3”, |
13.| “1” | “1” |
14.| ] | ]
Which SnowFlake Function can Elon use to transform this semi structured data in the output for-mat?

CONVERT_TO_ARRAY

SPLIT

GROUP_BY_CONNECT

NEST

NO.21 Mark a Data Engineer, looking to implement streams on local views & want to use change tracking metadata for one of its Data Loading use case. Please select the incorrect understanding points of Mark with respect to usage of Streams on Views?

For streams on views, change tracking must be enabled explicitly for the view and un-derlying tables to add the hidden columns to these tables.

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.

Views with GROUP BY & LIMIT Clause are supported by Snowflake.

As an alternative to streams, Snowflake supports querying change tracking metadata for views using the CHANGES clause for SELECT statements.

Enabling change tracking adds a pair of hidden columns to the table and begins storing change tracking metadata. The values in these hidden CDC data columns provide the input for the stream metadata columns. The columns consume a small amount of stor-age.

Explanation
A stream object records data manipulation language (DML) changes made to tables, including in-serts, updates, and deletes, as well as metadata about each change, so that actions can be taken us-ing 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 the row level, between two transac-tional 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
External tables
When created, a stream logically takes an initial snapshot of every row in the source object (e.g. ta-ble, external table, or the underlying tables for a view) by initializing a point in time (called an off-set) as the current transactional version of the object. The change tracking system utilized by the stream then records information about the DML changes after this snapshot was taken. Change rec-ords provide the state of a row before and after the change. Change information mirrors the column structure of the tracked source object and includes additional metadata columns that describe each change event.
Note that a 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, a pair of 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 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.
Views with the following operations are not yet supported:
GROUP BY clauses
QUALIFY clauses
Subqueries not in the FROM clause
Correlated subqueries
LIMIT clauses
Change Tracking:
Change tracking must be enabled in the underlying tables.
Prior to creating a stream on a view, you must enable change tracking on the underlying tables for the view.
Set the CHANGE_TRACKING parameter when creating a view (using CREATE VIEW) or later (using ALTER VIEW).
As an alternative to streams, Snowflake supports querying change tracking metadata for tables or views using the CHANGES clause for SELECT statements. The CHANGES clause enables query-ing change tracking metadata between two points in time without having to create a stream with an explicit transactional offset.

NO.22 UDTFs also called a table function, returns zero, one, or multiple rows for each input row?

YES

NO

NO.23 When using the CURRENT_ROLE and CURRENT_USER functions with secure views that will be shared to other Snowflake accounts, Snowflake returns a NULL value for these functions?

FALSE

TRUE

NO.24 Select the Correct statements with regard to using Federated authentication/SSO?

Snowflake supports using MFA in conjunction with SSO to provide additional levels of security.

Snowflake supports multiple audience values (i.e. Audience or Audience Restriction Fields) in the SAML 2.0 assertion from the identity provider to Snowflake.

Snowflake supports SSO with Private Connectivity to the Snowflake Service for Snow-flake accounts on Amazon Web Services (AWS), Microsoft Azure, and Google Cloud Platform.

Snowflake supports using SSO with organizations, and you can use the corresponding URL in the SAML2 security integration.

NO.25 A Data Engineer is building a pipeline to transform a 1 TD tab e by joining it with supplemental tables The Engineer is applying filters and several aggregations leveraging Common TableExpressions (CTEs) using a size Medium virtual warehouse in a single query in Snowflake.
After checking the Query Profile, what is the recommended approach to MAXIMIZE performance of this query if the Profile shows data spillage?

Enable clustering on the table

Increase the warehouse size

Rewrite the query to remove the CTEs.

Switch to a multi-cluster virtual warehouse

NO.26 What are characteristics of Snowpark Python packages? (Select THREE).
Third-party packages can be registered as a dependency to the Snowpark session using the session, import () method.

Python packages can access any external endpoints

Python packages can only be loaded in a local environment

Third-party supported Python packages are locked down to prevent hitting

The SQL command DESCRIBE FUNCTION will list the imported Python packages of the Python User-Defined Function (UDF).

Querying information__schema .packages will provide a list of supported Python packages and versions

NO.27 Mark the incorrect statement in case Data engineer using the COPY INTO <table> command to load data from files into Snowflake tables?

For Data loading of files with semi-structured file formats (JSON, Avro, etc.), the only supported character set is UTF-16.

For loading data from all semi-structured supported file formats (JSON, Avro, etc.), as well as unloading data, UTF-8 is the only supported character set.

For Local environment, Files are first copied (“staged”) to an internal (Snowflake) stage, then loaded into a table.

UTF-32 & UTF-16 both encoding character sets supported for loading data from de-limited files (CSV, TSV, etc.)

NO.28 In one of your created Schema, you have been required to create Internal Stages, what are the In-correct considerations you can noticed from the below options? [Select All that Apply]

User stages can be altered or dropped just like Table Stage.

Table stage type is designed to store files that are staged and managed by one or more users but only loaded into a single table.

A named internal stage type can store files that are staged and managed by one or more users and loaded into one or more tables.

A table stage is available for each table created in Snowflake.

NO.29 Data Engineer Loading File named snowdata.tsv in the /datadir directory from his local machine to Snowflake stage and try to prefix the file with a folder named tablestage, please mark the correct command which helps him to load the files data into snowflake internal Table stage?

put file://c:datadirsnowdata.tsv @~/tablestage;

put file://c:datadirsnowdata.tsv @%tablestage;

put file://c:datadirsnowdata.tsv @tablestage;

put file:///datadir/snowdata.tsv @%tablestage;