[May-2024] Check your preparation for Snowflake DEA-C01 On-Demand Exam [Q35-Q55]

QUESTION 35
You can execute zero, one, or more transactions inside a stored procedure?

TRUE

FALSE

QUESTION 36
Which is the non-supportable JavaScript UDF data types?

Integers

String

Binary

Double

QUESTION 37
Which ones are the false statements about Materialized Views?

Snowflake does not allow standard DML (e.g. INSERT, UPDATE, DELETE) on ma-terialized views.

Snowflake does not allow users to truncate materialized views.

Materialized views are first-class account objects.

A materialized view can also be used as the data source for a subquery.

Materialized views can be secure views.

Clustering a subset of the materialized views on a table tends to be more cost-effective than clustering the table itself.

QUESTION 38
Company A and Company B both have Snowflake accounts. Company A’s account is hosted on a different cloud provider and region than Company B’s account Companies A and B are not in the same Snowflake organization.
How can Company A share data with Company B? (Select TWO).

Create a share within Company A’s account and add Company B’s account as a recipient of that share

Create a share within Company A’s account, and create a reader account that is a recipient of the share Grant Company B access to the reader account

Use database replication to replicate Company A’s data into Company B’s account Create a share within Company B’s account and grant users within Company B’s account access to the share

Create a new account within Company A’s organization in the same cloud provider and region as Company B’s account Use database replication to replicate Company A’s data to the new account Create a share within the new account and add Company B’s account as a recipient of that share

Create a separate database within Company A’s account to contain only those data sets they wish to share with Company B Create a share within Company A’s account and add all the objects within this separate database to the share Add Company B’s account as a recipient of the share

Explanation
The ways that Company A can share data with Company B are:
Create a share within Company A’s account and add Company B’s account as a recipient of that share:
This is a valid way to share data between different accounts on different cloud platforms and regions.
Snowflake supports cross-cloud and cross-region data sharing, which allows users to create shares and grant access to other accounts regardless of their cloud platform or region. However, this option may incur additional costs for network transfer and storage replication.
Create a separate database within Company A’s account to contain only those data sets they wish to share with Company B Create a share within Company A’saccount and add all the objects within this separate database to the share Add Company B’s account as a recipient of the share: This is also a valid way to share data between different accounts on different cloud platforms and regions. This option is similar to the previous one, except that it uses a separate database to isolate the data sets that need to be shared. This can improve security and manageability of the shared data. The other options are not valid because:
Create a share within Company A’s account, and create a reader account that is a recipient of the share Grant Company B access to the reader account: This option is not valid because reader accounts are not supported for cross-cloud or cross-region data sharing. Reader accounts are Snowflake accounts that can only consume data from shares created by their provider account. Reader accounts must be on the same cloud platform and region as their provider account.
Use database replication to replicate Company A’s data into Company B’s account Create a share within Company B’s account and grant users within Company B’s account access to the share: This option is not valid because database replication cannot be used for cross-cloud or cross-region data sharing.
Database replication is a feature in Snowflake that allows users to copy databases across accounts within the same cloud platform and region. Database replication cannot copy databases across different cloud platforms or regions.
Create a new account within Company A’s organization in the same cloud provider and region as Company B’s account Use database replication to replicate Company A’s data to the new account Create a share within the new account and add Company B’s account as a recipient of that share: This option is not valid because it involves creating a new account within Company A’s organization, which may not be feasible or desirable for Company A. Moreover, this option is unnecessary, as Company A can directly share data with Company B without creating an intermediate account.

QUESTION 39
For SQL UDFs, The invoker of the function need not have access to the objects referenced in the function definition, but only needs the privilege to use the function?

TRUE

FALSE

QUESTION 40
A Data Engineer enables a result cache at the session level with the following command:
ALTER SESSION SET USE CACHED RESULT = TRUE;
The Engineer then runs the following select query twice without delay:

The underlying table does not change between executions
What are the results of both runs?

The first and second run returned the same results because sample is deterministic

The first and second run returned the same results, because the specific SEEDvalue was provided.

The first and second run returned different results because the query is evaluated each time it is run.

The first and second run returned differentresults because the query uses *instead of an explicit column list

QUESTION 41
What is the purpose of the BUILD_FILE_URL function in Snowflake?

It generates an encrypted URL foe accessing a file in a stage.

It generates a staged URL for accessing a file in a stage.

It generates a permanent URL for accessing files in a stage.

It generates a temporary URL for accessing a file in a stage.

QUESTION 42
PARTITION_TYPE = USER_SPECIFIED must be used when you prefer to add and remove par-titions selectively rather than automatically adding partitions for all new files in an external storage location that match an expression?

TRUE

FALSE

QUESTION 43
Which methods can be used to create a DataFrame object in Snowpark? (Select THREE)

session.read.json{)

session.builder()

DataFraas.writeO

session.jdbc_connection()

session,table()

session.sql()

QUESTION 44
A Data Engineer is investigating a query that is taking a long time to return The Query Profile shows the following:

What step should the Engineer take to increase the query performance?

Add additional virtual warehouses.

increasethe size of the virtual warehouse.

Rewrite the query using Common Table Expressions (CTEs)

Change the order of the joins and start with smaller tables first

QUESTION 45
A Data Engineer is working on a continuous data pipeline which receives data from Amazon Kinesis Firehose and loads the data into a staging table which will later be used in the data transformation process The average file size is 300-500 MB.
The Engineer needs to ensure that Snowpipe is performant while minimizing costs.
How can this be achieved?

Increase the size of the virtual warehouse used by Snowpipe.

Split the files before loading them andset the SIZE_LIMIT option to 250 MB.

Change the file compression size and increase the frequency of the Snowpipe loads

Decrease the buffer size to trigger delivery of files sized between 100 to 250 MB in Kinesis Firehose

QUESTION 46
Which of the following System keeps the following characteristics?
a. It will keep in it all the raw data.
b. Generally, the users of it is data scientists and data developers.
c. Flat architecture
d. Highly agile

Data Warehouse

Data Mart

Data Lake

Data Hub

QUESTION 47
You as Data engineer might want to consider disabling auto-suspend for a warehouse if?

You have a low, fluctuating workload for the warehouse.

You have a heavy, steady workload for the warehouse.

You require the warehouse to be available with delay.

You require the warehouse to be available with no delay or lag time.

QUESTION 48
Which are supported Programming Languages for Creating UDTFs?

Python

Node.javascript

Javascript

Java

Perl

QUESTION 49
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.)

QUESTION 50
Which of the following statements is/are incorrect regarding Fail-safe data recovery?

Data stored in temporary tables is not recoverable after the table is dropped as they do not have fail-safe.

Historical data in transient tables can be recovered by Snowflake due to Operation fail-ure after the Time Travel retention period ends using Fail-safe.

Long-lived tables, such as fact tables, should always be defined as permanent to ensure they are fully protected by Fail-safe.

Short-lived tables (i.e. <1 day), such as ETL work tables, can be defined as transient to eliminate Fail-safe costs.

If downtime and the time required to reload lost data are factors, permanent tables, even with their added Fail-safe costs, may offer a better overall solution than transient tables.

QUESTION 51
The JSON below is stored in a variant column named v in a table named jCustRaw:

Which query will return one row per team member (stored in the teamMembers array) along all of the attributes of each team member?

QUESTION 52
What is a characteristic of the use of external tokenization?

Secure data sharing can be used with external tokenization

External tokenization cannot be used with database replication

Pre-loading of unmasked data is supported with external tokenization

External tokenization allows (he preservation of analytical values after de-identification

QUESTION 53
Which Function would Data engineer used to recursively resume all tasks in Chain of Tasks rather than resuming each task individually (using ALTER TASK … RESUME)?

SYSTEM$TASK_DEPENDENTS

SYSTEM$TASK_DEPENDENTS_ENABLE

SYSTEM$TASK_DEPENDENTS_RESUME

SYSTEM$TASK_RECURSIVE_ENABLE

QUESTION 54
Let us say you have List of 50 Source files, which needs to be loaded into Snowflake internal stage. All these Source system files are already Brotli-compressed files. Which statement is correct with respect to Compression of Staged Files?

Even though Source files are already compressed, Snowflake do apply default gzip2 Compression to optimize the storage cost.

Snowflake automatically detect Brotli Compression, will skip further compression of all 50 files.

Auto-detection is not yet supported for Brotli-compressed files; when staging or loading Brotli-compressed files, you must explicitly specify the compression method that was used.

When staging 50 compressed files in a Snowflake stage, the files are automatically com-pressed using gzip.

QUESTION 55
Stuart, a Lead Data Engineer in MACRO Data Company created streams on set of External tables. He has been asked to extend the data retention period of the stream for 90 days, which parameter he can utilize to enable this extension?

MAX_DATA_EXTENSION_TIME_IN_DAYS

DATA_RETENTION_TIME_IN_DAYS

DATA_EXTENSION_TIME_IN_DAYS

None of the above