[Q38-Q59] Latest DEA-C01 Exam with Accurate SnowPro Advanced: Data Engineer Certification Exam PDF Questions [Apr 23, 2024]

ExamBoosts April 23, 2024 DEA-C01 > Snowflake 0

Rate this post

[Apr 23, 2024] Latest DEA-C01 Exam with Accurate SnowPro Advanced: Data Engineer Certification Exam PDF Questions

Practice To DEA-C01 – ExamBoosts Remarkable Practice On your SnowPro Advanced: Data Engineer Certification Exam Exam

Q38. In efforts to recover the dropped child tables within schema named SCV_SCHEMA by Data Engi-neer, She found that DATA_RETENTION_TIME_IN_DAYS parameter set with value 45 days at Schema level &the data retention period for child tables explicitly set at 85 days. What will happen when she will try to run undrop table command on Child tables to recover them on the 50th day as-suming SCV_SCHEMA is already dropped on 45th day?

To honor the data retention period for child tables, She will ab able to recover the child tables on 50th day as DATA_RETENTION_TIME_IN_DAYS is explicitly set with higher retention value.

When a schema is already dropped, the data retention period for child tables, if explicit-ly set to be different from the retention of the schema, is not honoured. So UNDROP command will fail to run on
50th day for Child tables recovery.

Child tables can be recovered using Fail-Safe SQL commands.

Data Engineer needs to first recover the Schema & then Child tables will automatically be recovered irrespective of Retention Inheritance.

Explanation
Dropped Containers and Object Retention Inheritance
Currently, when a database is dropped, the data retention period for child schemas or tables, if ex-plicitly set to be different from the retention of the database, is not honored. The child schemas or tables are retained for the same period of time as the database.
Similarly, when a schema is dropped, the data retention period for child tables, if explicitly set to be different from the retention of the schema, is not honored. The child tables are retained for the same period of time as the schema.
To honor the data retention period for these child objects (schemas or tables), drop them explicitly before you drop the database or schema.

Q39. Jeff, a Data Engineer, accessing elements in JSON object in its 3 data loading scripts, he unknow-ingly use the upper case while accessing the elements. e.g.
Script 1 –> fruits:apple.sweet
Script 2 –> FRUITS:apple.sweet
Script 3 –> FRUITS:Apple.Sweet
Which are the correct statements?

Script 1 & Script 2 traverse path twill be treated same, but Script 2 will not.

Script 1,2,3 traverse path will treat in same way.

Script 1 & 3 traverse path will be treated in same way.

Script 2&3 traverse path will be same.

Explanation
There are two ways to access elements in a JSON object:
Dot Notation
Bracket Notation
Regardless of which notation you use, the column name is case-insensitive but element names are case-sensitive.

Q40. A Data Engineer wants to centralize grant management to maximize security. A user needs ownership on a table m a new schema However, this user should not have the ability to make grant decisions What is the correct way to do this?

Grant ownership to the user on the table

Revoke grant decisions from the user on the table

Revoke grant decisions from the user on the schema.

Add the with managed access parameter on the schema

Explanation
The with managed access parameter on the schema enables the schema owner to control the grant and revoke privileges on the objects within the schema. This way, the user who owns the table cannot make grant decisions, but only the schema owner can. This is the best way to centralize grant management and maximize security.

Q41. Mark the Correct Statements:
Statement 1. Snowflake’s zero-copy cloning feature provides a convenient way to quickly take a “snapshot” of any table, schema, or database.
Statement 2. Data Engineer can use zero-copy cloning feature for creating instant backups that do not incur any additional costs (until changes are made to the cloned object).

Statement 1

Statement 2

Both are False.

Statement 1 & 2 are correct.

Explanation
Snowflake’s zero-copy cloning feature provides a convenient way to quickly take a “snapshot” of any table, schema, or database and create a derived copy of that object which initially shares the underlying storage. This can be extremely useful for creating instant backups that do not incur any additional costs (until changes are made to the cloned object).
For example, when a clone is created of a table, the clone utilizes no data storage because it shares all the existing micro-partitions of the original table at the time it was cloned; however, rows can then be added, deleted, or updated in the clone independently from the original table. Each change to the clone results in new micro-partitions that are owned exclusively by the clone and are protect-ed through CDP.

Q42. Select the Incorrect statement about External Functions in SnowFlake?

An external function is a type of UDF.

An external function does not contain its own code; instead, the external function calls code that is stored and executed outside Snowflake.

Inside Snowflake, the external function is stored as a API Integration object.

Inside Snowflake, the external function is stored as a database object that contains in-formation that Snowflake uses to call the remote service.

Q43. Harry using Snowflake Enterprise Edition & decided to scale in/out the Cluster in automatic mode. He needs to configure some warehouses as multi cluster mode and some among them in Standard mode as per needs.
If Harry is using Snowflake Enterprise Edition (or a higher edition), all his warehouses should be configured as multi-cluster warehouses only.

TRUE

FALSE

Explanation
If you are using Snowflake Enterprise Edition (or a higher edition), all your warehouses should be configured as multi-cluster warehouses.

Q44. Can Masking policies be applied to virtual columns?

TRUE

FALSE

Q45. While creating External function, Which Database object required with at least ACCOUNTAD-MIN privileges?

STORAGE Integration

SECURITY Integration

API Integration

None of the above required.

Q46. 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

Q47. 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.

Q48. A company built a sales reporting system with Python, connecting to Snowflake using the Python Connector.
Based on the user’s selections, the system generates the SQL queries needed to fetch the data for the report First it gets the customers that meet the given query parameters (on average 1000 customer records for each report run) and then it loops the customer records sequentially Inside that loop it runs the generated SQL clause for the current customer to get the detailed data for that customer number from the sales data table When the Data Engineer tested the individual SQL clauses they were fast enough (1 second to get the customers 0 5 second to get the sales data for one customer) but the total runtime of the report is too long How can this situation be improved?

Increase the size of the virtual warehouse

Increase the number of maximum clusters of the virtual warehouse

Define a clustering key for the sales data table

Rewrite the report to eliminate the use of the loop construct

Explanation
This option is the best way to improve the situation, as using a loop construct to run SQL queries for each customer is very inefficient and slow. Instead, the report should be rewritten to use a single SQL query that joins the customer and sales data tables and applies the query parameters as filters. This way, the report can leverage Snowflake’s parallel processing and optimization capabilities and reduce the network overhead and latency.

Q49. 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()

Explanation
The methods that can be used to create a DataFrame object in Snowpark are session.read.json(), session.table(), and session.sql(). These methods can create a DataFrame from different sources, such as JSON files, Snowflake tables, or SQL queries. The other options are not methods that can create a DataFrame object in Snowpark. Option A, session.jdbc_connection(), is a method that can create a JDBC connection object to connect to a database. Option D, DataFrame.write(), is a method that can write a DataFrame to a destination, such as a file or a table. Option E, session.builder(), is a method that can create a SessionBuilder object to configure and build a Snowpark session.

Q50. Which callback function is required within a JavaScript User-Defined Function (UDF) for it to execute successfully?

initialize ()

processRow ()

handler

finalize ()

Explanation
The processRow () callback function is required within a JavaScript UDF for it to execute successfully. This function defines how each row of input data is processed and what output is returned. The other callback functions are optional and can be used for initialization, finalization, or error handling.

Q51. Data Engineer wants to analyze query performance & looking out for profiling information, He went to Query/Operator Details also called Profile Overview of Query Profile Interface & searching for statistics attributes around I/O. Which of the following information he can’t get from there?

Percentage scanned from cache – the percentage of data scanned from the local disk cache.

Bytes written – bytes written (e.g. when loading into a table).

External bytes scanned – bytes read from an external object, e.g. a stage.

Bytes sent over the wireframe – amount of data sent over the wireframe

Bytes read from result – bytes read from the result object.

Explanation
To help you analyze query performance, Query/Operator Details panel also called Profile overview panel provides two classes of profiling information:
Execution time, broken down into categories
Detailed statistics
Apart from Option a Bytes sent over the wireframe – amount of data sent over the wireframe , Rest of the Statistics Information provided by Query/Operator details in the Query Profile Inter-face.
To Know More about the Query/Operator Details options , please refer the link:
https://docs.snowflake.com/en/user-guide/ui-query-profile#query-operator-details

Q52. Find out the odd one out:

1. Bulk Data Load: Loads are always performed in a single transaction.
2. SnowPipe: Loads are combined or split into a single or multiple transactions based on the number and size of the rows in each data file.

1. Bulk Data Load: Requires a user-specified warehouse to execute COPY statements.
2. SnowPipe: Uses Snowflake-supplied compute resources.

1. Bulk Data Load: Billed for the amount of time each virtual warehouse is active.
2. SnowPipe: Billed according to the compute resources used in the Snowpipe ware-house while loading the files.

1. Bulk Data Load: Load history Stored in the metadata of the target table for 365 days.
2. SnowPipe: Load history Stored in the metadata of the pipe for 64 days.

Explanation
Bulk data load
Load History Stored in the metadata of the target table for 64 days. Available upon completion of the COPY statement as the statement output.
Snowpipe
Load History Stored in the metadata of the pipe for 14 days. Must be requested from Snowflake via a REST endpoint, SQL table function, or ACCOUNT_USAGE view.
Rest are correct statements.

Q53. For the most efficient and cost-effective Data load experience, Data Engineer needs to inconsider-ate which of the following considerations?

Split larger files into a greater number of smaller files, maximize the processing over-head for each file.
(Correct)

Enabling the STRIP_OUTER_ARRAY file format option for the COPY INTO <ta-ble> command to remove the outer array structure and load the records into separate table rows.

Amazon Kinesis Firehose can be convenient way to aggregate and batch data files which also allows defining both the desired file size, called the buffer size, and the wait interval after which a new file is sent, called the buffer interval.

When preparing your delimited text (CSV) files for loading, the number of columns in each row should be consistent.

if the “null” values in your files indicate missing values and have no other special mean-ing, Snowflake recommend setting the file format option STRIP_NULL_VALUES to TRUE when loading the semi-structured data file.

Explanation
Split larger files into a greater number of smaller files to distribute the load among the compute re-sources in an active warehouse. This would minimize the processing overhead rather than maximize it.
Rest is recommended Data loading considerations.

Q54. Ira a Data Engineer with TESLA IT systems, looking out to Compare Traditional Partitioning vs Snowflake micro-partitions for one of the Snowflake Project implementations. Which one of the following is incorrect understanding of Ira about Micro Partitioning?

All data in Snowflake tables is automatically divided into micro-partitions, which are contiguous units of storage compared to traditional partitioning where specialized DDL required.

All DML operations (e.g. DELETE, UPDATE, MERGE) take advantage of the under-lying micro-partition metadata to facilitate and simplify table maintenance.

Snowflake stores metadata about all rows stored in a micro-partition, including number of distinct columns.

The micro-partition metadata maintained by Snowflake enables precise pruning of col-umns in micro-partitions at query run-time, including columns containing semi-structured data.

In Snowflake, as data is inserted/loaded into a table, clustering metadata is collected and recorded for each micro-partition created during the process.

Explanation
What are Micro-partitions?
All data in Snowflake tables is automatically divided into micro-partitions, which are contiguous units of storage. Each micro-partition contains between 50 MB and 500 MB of uncompressed data (note that the actual size in Snowflake is smaller because data is always stored compressed). Groups of rows in tables are mapped into individual micro-partitions, organized in a columnar fashion. This size and structure allow for extremely granular pruning of very large tables, which can be comprised of millions, or even hundreds of millions, of micro-partitions.
Snowflake stores metadata about all rows stored in a micro-partition, including:
The range of values for each of the columns in the micro-partition.
The number of distinct values.
Additional properties used for both optimization and efficient query processing.
It Never stores number of columns as part of Metadata.
Rest of the statements are correct.

Q55. 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

Explanation
The recommended way to ingest invoice data in PDF format into Snowflake is to create a Java User-Defined Function (UDF) that leverages Java-based PDF parser libraries to parse PDF data into structured data. This option allows for more flexibility and control over how the PDF data is extracted and transformed. The other options are not suitable for ingesting PDF data into Snowflake. Option A and B are incorrect because Snowpipe and COPY INTO commands can only ingest files that are in supported file formats, such as CSV, JSON, XML, etc. PDF files are not supported by Snowflake and will cause errors or unexpected results.
Option C is incorrect because external tables can only query files that are in supported file formats as well.
PDF files cannot be parsed by external tables and will cause errors or unexpected results.

Q56. Can the same column be specified in both a Dynamic data masking policy signature and a row ac-cess policy signature at the same time?

YES

Q57. A new customer table is created by a data pipeline in a Snowflake schema where MANAGED ACCESSenabled.
…. Can gran access to the CUSTOMER table? (Select THREE.)

The role that owns the schema

The role that owns the database

The role that owns the customer table

The SYSADMIN role

The SECURITYADMIN role

The USERADMIN role with the manage grants privilege

Explanation
The roles that can grant access to the CUSTOMER table are the role that owns the schema, the role that owns the database, and the SECURITYADMIN role. These roles have the ownership or the manage grants privilege on the schema or the database level, which allows them to grant access to any object within them. The other options are incorrect because they do not have the necessary privilege to grant access to the CUSTOMER table. Option C is incorrect because the role that owns the customer table cannot grant access to itself or to other roles. Option D is incorrect because the SYSADMIN role does not have the manage grants privilege by default and cannot grant access to objects that it does not own. Option F is incorrect because the USERADMIN role with the manage grants privilege can only grant access to users and roles, not to tables.

Q58. Which methods will trigger an action that will evaluate a DataFrame? (Select TWO)

DataFrame.random_split ( )

DataFrame.collect ()

DateFrame.select ()

DataFrame.col ( )

DataFrame.show ()

Explanation
The methods that will trigger an action that will evaluate a DataFrame are DataFrame.collect() and DataFrame.show(). These methods will force the execution of any pending transformations on the DataFrame and return or display the results. The other options are not methods that will evaluate a DataFrame. Option A, DataFrame.random_split(), is a method that will split a DataFrame into two or more DataFrames based on random weights. Option C, DataFrame.select(), is a method that will project a set of expressions on a DataFrame and return a new DataFrame. Option D, DataFrame.col(), is a method that will return a Column object based on a column name in a DataFrame.

Q59. 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.

Loading …