Get DEA-C01 Actual Free Exam Q&As to Prepare for Your Snowflake Certification [Q20-Q38]

admin • December 10, 2023

Get DEA-C01 Actual Free Exam Q&As to Prepare for Your Snowflake Certification [Q20-Q38]

5/5 - (1 vote)

Get DEA-C01 Actual Free Exam Q&As to Prepare for Your Snowflake Certification

Snowflake Actual Free Exam Questions And Answers

NEW QUESTION 20
A Data Engineer needs to know the details regarding the micro-partition layout for a table named invoice using a built-in function.
Which query will provide this information?

SELECT SYSTEM$CLUSTERING_INTFORMATICII (‘Invoice’ ) ;

SELECT $CLUSTERXNG_INFQRMATION (‘Invoice’)’

CALL SYSTEM$CLUSTERING_INFORMATION (‘Invoice’);

CALL $CLUSTERINS_INFORMATION(‘Invoice’);

Explanation
The query that will provide information about the micro-partition layout for a table named invoice using a built-in function is SELECT SYSTEM$CLUSTERING_INFORMATION(‘Invoice’);. The SYSTEM$CLUSTERING_INFORMATION function returns information about the clustering status of a table, such as the clustering key, the clustering depth, the clustering ratio, the partition count, etc. The function takes one argument: the table name in a qualified or unqualified form. In this case, the table name is Invoice and it is unqualified, which means that it will use the current database and schema as the context. The other options are incorrect because they do not use a valid built-in function for providing information about the micro-partition layout for a table. Option B is incorrect because it uses $CLUSTERING_INFORMATION instead of SYSTEM$CLUSTERING_INFORMATION, which is not a valid function name. Option C is incorrect because it uses CALL instead of SELECT, which is not a valid way to invoke a table function.
Option D is incorrect because it uses CALL instead of SELECT and $CLUSTERING_INFORMATION instead of SYSTEM$CLUSTERING_INFORMATION, which are both invalid.

NEW QUESTION 21
Mark the Correct Statements:
Statement 1. Enable failover for a primary database to one or more accounts in your organization using an ALTER DATABASE … ENABLE FAILOVER TO ACCOUNTS statement.
Statement 2. Enabling failover for a primary database can be done by Data Engineer either before or after a replica of the primary database has been created in a specified account.

Statement 1

Statement 2

Both are False.

Both are Correct.

NEW QUESTION 22
Snowflake web interface can be used to create users with no passwords or remove passwords from existing users?

TRUE

FALSE

Explanation
You cannot use the Snowflake web interface to create users with no passwords or remove pass-words from existing users. You must use CREATE USER or ALTER USER command instead.
Also If you create a user with no password (or alter an existing user and remove their password), this effectively disables Snowflake authentication for the user. Without a password in Snowflake, a user cannot log in using Snowflake authentication and must use federated authentication instead.

NEW QUESTION 23
Dominic, a Data Engineer wants to resume the pipe named stalepipe3 which got stale after 14 days. To do the same, he called the SYSTEM$PIPE_FORCE_RESUME function select sys-tem$pipe_force_resume(‘snowmydb.mysnowschema.stalepipe3′,’staleness_check_override’); Let’s say If the pipe is resumed 16 days after it was paused, what will happened to the event notifi-cation that were received on the first and second days after the pipe was paused?

Snowpipe generally skips any event notifications that were received on the first and second days after the pipe was paused.

Pipe maintains Metadata history of files for 64 days, so in this scenarios Snowpipe pro-cessed all the event notifications that were received for 16 days or so.

Once the Pipe got stale, all the events got purged automatically & pipe needs to be rec-reated with modified properties.

All the events get processed from day 1 if the PURGE properties in the PIPE object definition set to be FALSE initially.

Explanation
When a pipe is paused, event messages received for the pipe enter a limited retention period. The period is 14 days by default. If a pipe is paused for longer than 14 days, it is considered stale.
To resume a stale pipe, a qualified role must call the SYSTEM$PIPE_FORCE_RESUME function and input the STALENESS_CHECK_OVERRIDE argument. This argument indicates an under-standing that the role is resuming a stale pipe.
For example, resume the stale stalepipe1 pipe in the mydb.myschema database and schema:
select sys-tem$pipe_force_resume(‘mydb.myschema.stalepipe3′,’staleness_check_override’); As an event notification received while a pipe is paused reaches the end of the limited retention pe-riod, Snowflake schedules it to be dropped from the internal metadata. If the pipe is later resumed, Snowpipe processes these older notifications on a best effort basis. Snowflake cannot guarantee that they are processed.
For example, if a pipe is resumed 15 days after it was paused, Snowpipe generally skips any event notifications that were received on the first day the pipe was paused (i.e. that are now more than 14 days old).
If the pipe is resumed 16 days after it was paused, Snowpipe generally skips any event notifications that were received on the first and second days after the pipe was paused. And so on.

NEW QUESTION 24
A Data Engineer is writing a Python script using the Snowflake Connector for Python. The Engineer will use the snowflake. Connector.connect function to connect to Snowflake The requirementsare:
*Raise an exception if the specified database schema or warehouse does not exist
*improve download performance
Whichparameters of the connect function should be used? (Select TWO).

authenticator

arrow_nunber_to_decimal

client_prefetch_threads

client_session_keep_alivs

validate_default_parameters

Explanation
The parameters of the connect function that should be used are client_prefetch_threads and validate_default_parameters. The client_prefetch_threads parameter controls the number of threads used to download query results from Snowflake. Increasing this parameter can improve download performance by parallelizing the download process. The validate_default_parameters parameter controls whether an exception should be raised if the specified database, schema, or warehouse does not exist or is not authorized. Setting this parameter to True can help catch errors early and avoid unexpected results.

NEW QUESTION 25
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.

Explanation
Materialized views are first-class Database objects & rest of the understandings are true.

NEW QUESTION 26
Mark the Correct Statements for the VALIDATION_MODE option used by Data Engineer for Da-ta loading operations in his/her COPY INTO <table> command:

VALIDATION_MODE instructs the COPY command to validate the data files instead of loading them into the specified table; i.e., the COPY command tests the files for er-rors but does not load them.

VALIDATION_MODE option supported these values:
RETURN_n_ROWS,
RETURN_ERRORS,
RETURN_ALL_ERRORS

VALIDATION_MODE does not support COPY statements that transform data during a load. If the parameter is specified, the COPY statement returns an error.

VALIDATION_MODE only support Data loading operation i.e., do not work while da-ta unloading.

Explanation
All the Statements are correct except the statement saying VALIDATION_MODE only support Data loading operation.
VALIDATION_MODE can be used with COPY INTO <location> command as well i.e for data unloading operation.
VALIDATION_MODE = RETURN_ROWS can be used at the time of Data unloading.
This option instructs the COPY command to return the results of the query in the SQL statement instead of unloading the results to the specified cloud storage location. The only supported valida-tion option is RETURN_ROWS. This option returns all rows produced by the query.
When you have validated the query, you can remove the VALIDATION_MODE to perform the unload operation.

NEW QUESTION 27
For enabling non-ACCOUNTADMIN Roles to Perform Data Sharing Tasks, which two glob-al/account privileges snowflake provide?

CREATE SHARE

IMPORT SHARE

REFERENCE USAGE

OPERATE

Explanation
CREATE SHARE
In a provider account, this privilege enables creating and managing shares (for sharing data with consumer accounts).
IMPORT SHARE
In a consumer account, this privilege enables viewing the inbound shares shared with the account. Also enables creating databases from inbound shares; requires the global CREATE DATABASE privilege.
By default, these privileges are granted only to the ACCOUNTADMIN role, ensuring that only ac-count administrators can perform these tasks. However, the privileges can be granted to other roles, enabling the tasks to be delegated to other users in the account.

NEW QUESTION 28
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

Explanation
External tables do not have data retention period applicable.
Good to Understand other Options available.
DATA_RETENTION_TIME_IN_DAYS
Type: Object (for databases, schemas, and tables) – Can be set for Account – Database – Schema – Table Description: Number of days for which Snowflake retains historical data for performing Time Trav-el actions (SELECT, CLONE, UNDROP) on the object. A value of 0 effectively disables Time Travel for the specified database, schema, or table.
Values:
0 or 1 (for Standard Edition)
0 to 90 (for Enterprise Edition or higher)
Default:
1
MAX_DATA_EXTENSION_TIME_IN_DAYS
Type: Object (for databases, schemas, and tables) – Can be set for Account – Database – Schema – Table Description: Maximum number of days for which Snowflake can extend the data retention period for tables to prevent streams on the tables from becoming stale. By default, if the DA-TA_RETENTION_TIME_IN_DAYS setting for a source table is less than 14 days, and a stream has not been consumed, Snowflake temporarily extends this period to the stream’s offset, up to a maximum of 14 days, regardless of the Snowflake Edition for your account. The MAX_DATA_EXTENSION_TIME_IN_DAYS parameter enables you to limit this automatic ex-tension period to control storage costs for data retention or for compliance reasons.
This parameter can be set at the account, database, schema, and table levels. Note that setting the parameter at the account or schema level only affects tables for which the parameter has not already been explicitly set at a lower level (e.g. at the table level by the table owner). A value of 0 effective-ly disables the automatic extension for the specified database, schema, or table.
Values:
0 to 90 (i.e. 90 days) – a value of 0 disables the automatic extension of the data retention period. To increase the maximum value for tables in your account, Client needs to contact Snowflake Sup-port.
Default:14

NEW QUESTION 29
Marko, a Data Engineer is using Snowpipe for data loading in micro batches for one of the Finance Data workloads. There are set of files he attempted to load into the snowflake table using Snow-pipe. While monitoring he found that there are set of files has multiple issue, He queried the COPY_HISTORY view & checked the STATUS column which indicates whether a particular set of files was loaded, partially loaded, or failed to load. But he wants to view all errors in the files along with Load status, how he can check all errors?

He can check RETURN_ALL_ERROR_MESSAGE column in the COPY_HISTORY view which can provides a reason and view all errors in the files.

He can view all errors in the files, by executing a COPY INTO <table> statement with the VALIDATION_ERROR_MODE copy option set to RE-TURN_ALL_PIPE_ERRORS.

Marko can look out for FIRST_ERROR_MESSAGE column in the COPY_HISTORY view which can provides a reason why a file partially loaded or failed for all the files.

He can view all errors in the files, by executing a COPY INTO <table> statement with the VALIDATION_MODE copy option set to RETURN_ALL_ERRORS.

Explanation
The STATUS column indicates whether a particular set of files was loaded, partially loaded, or failed to load.
The FIRST_ERROR_MESSAGE column provides a reason when an attempt partial-ly loaded or failed.
Note that if a set of files has multiple issues, the FIRST_ERROR_MESSAGE column only indi-cates the first error encountered. To view all errors in the files, execute a COPY INTO <table> statement with the VALIDATION_MODE copy option set to RETURN_ALL_ERRORS. The VALIDATION_MODE copy option instructs a COPY statement to validate the data to be loaded and return results based on the validation option specified. No data is loaded when this copy option is specified.

NEW QUESTION 30
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

Explanation
External tokenization is a feature in Snowflake that allows users to replace sensitive data values with tokens that are generated and managed by an external service. External tokenization allows the preservation of analytical values after de-identification, such as preserving the format, length, or range of the original values.
This way, users can perform analytics on the tokenized data without compromising the security or privacy of the sensitive data.

NEW QUESTION 31
Which are the two ways to access elements in a JSON object?

Use SemiColon notation to traverse a path in a JSON object:
<col-umn>:<level1_element>;<level2_element>;<level3_element>.

use Curly bracket notation to traverse the path in an object:
<col-umn>{‘<level1_element>’}{‘<level2_element>’}.

Use dot notation to traverse a path in a JSON object:
<col-umn>:<level1_element>.<level2_element>.<level3_element>.

use bracket notation to traverse the path in an object:
<col-umn>[‘<level1_element>’][‘<level2_element>’].

NEW QUESTION 32
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

Explanation
The characteristics of Snowpark Python packages are:
Third-party packages can be registered as a dependency to the Snowpark session using the session.import() method.
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.
These characteristics indicate how Snowpark Python packages can be imported, inspected, and verified in Snowflake. The other options are not characteristics of Snowpark Python packages. Option B is incorrect because Python packages can be loaded in both local and remote environments using Snowpark. Option C is incorrect because third-party supported Python packages are not locked down to prevent hitting external endpoints, but rather restricted by network policies and security settings.

NEW QUESTION 33
Snowpipe API provides a REST endpoint for defining the list of files to ingest that Informs Snow-flake about the files to be ingested into a table. A successful response from this endpoint means that Snowflake has recorded the list of files to add to the table. It does not necessarily mean the files have been ingested. What is name of this Endpoint?

REST endpoints –> insertReport

REST endpoints –> loadHistoryScan

REST endpoints –> ingestfiles

REST endpoints–> insertfiles

Explanation
The Snowpipe API provides a REST endpoint for defining the list of files to ingest.
Endpoint: insertFiles
Informs Snowflake about the files to be ingested into a table. A successful response from this end-point means that Snowflake has recorded the list of files to add to the table. It does not necessarily mean the files have been ingested. For more details, see the response codes below.
In most cases, Snowflake inserts fresh data into the target table within a few minutes.
To Know more about SnowFlake Rest API used for Data File ingestion, do refer:
https://docs.snowflake.com/en/user-guide/data-load-snowpipe-rest-apis.html#data-file-ingestion

NEW QUESTION 34
What is a characteristic of the use of binding variables in JavaScript stored procedures in Snowflake?

All types of JavaScript variables can be bound

All Snowflake first-class objects can be bound

Only JavaScript variables of type number, string and sf Date can be bound

Users are restricted from binding JavaScript variables because they create SQL injection attack vulnerabilities

Explanation
A characteristic of the use of binding variables in JavaScript stored procedures in Snowflake is that only JavaScript variables of type number, string and sf Date can be bound. Binding variables are a way to pass values from JavaScript variables to SQL statements within a stored procedure. Binding variables can improve the security and performance of the stored procedure by preventing SQL injection attacks and reducing the parsing overhead. However, not all types of JavaScript variables can be bound. Only the primitive types number and string, and the Snowflake-specific type sf Date, can be bound. The other options are incorrect because they do not describe a characteristic of the use of binding variables in JavaScript stored procedures in Snowflake. Option A is incorrect because authenticator is not a type of JavaScript variable, but a parameter of the snowflake.connector.connect function. Option B is incorrect because arrow_number_to_decimal is not a type of JavaScript variable, but a parameter of the snowflake.connector.connect function. Option D is incorrect because users are not restricted from binding JavaScript variables, but encouraged to do so.

NEW QUESTION 35
While creating even Secure UDF, snowflake recommended to use randomized identifiers (e.g. gen-erated by UUID_STRING) instead of sequence-generated values?

TRUE
(Correct)

FALSE

NEW QUESTION 36
Which are supported Programming Languages for Creating UDTFs?

Python

Node.javascript

Javascript

Java

Perl

NEW QUESTION 37
Which privilege are required on an object (i.e. user or role) with USERADMIN Role can modify the object properties?

OPEARTE

MANAGE GRANTS

OWNERSHIP

MODIFY

NEW QUESTION 38
As Data Engineer, you have requirement to Load set of New Product Files containing Product rele-vant information into the Snowflake internal tables, Later you analyzed that some of the Source files are already loaded in one of the historical batch & for that you have prechecked Metadata col-umn LAST_MODIFIED date for a staged data file & found out that LAST_MODIFIED date is older than 64 days for few files and the initial set of data was loaded into the table more than 64 days earlier, Which one is the best approach to Load Source data files with expired load metadata along with set of files whose metadata might be available to avoid data duplication?

Since the initial set of data for the table (i.e. the first batch after the table was created) was loaded, we can simply use the COPY INTO command to load all the product files with the known load status irrespective of their column LAST_MODIFIED date values.

The COPY command cannot definitively determine whether a file has been loaded al-ready if the LAST_MODIFIED date is older than 64 days and the initial set of data was loaded into the table more than 64 days earlier (and if the file was loaded into the table, that also occurred more than 64 days earlier). In this case, to prevent accidental reload, the command skips the product files by default.

Set the FORCE option to load all files, ignoring load metadata if it exists.

To load files whose metadata has expired, set the LOAD_UNCERTAIN_FILES copy option to true.

Explanation
To load files whose metadata has expired, set the LOAD_UNCERTAIN_FILES copy option to true. The copy option references load metadata, if available, to avoid data duplication, but also at-tempts to load files with expired load metadata.
Alternatively, set the FORCE option to load all files, ignoring load metadata if it exists. Note that this option reloads files, potentially duplicating data in a table.
Please refer the Example as mentioned in the link below:
https://docs.snowflake.com/en/user-guide/data-load-considerations-load.html#loading-older-files

Loading …