Latest [Jul 19, 2024] Snowflake DEA-C01 Real Exam Dumps PDF [Q27-Q42]

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

Q28. A Data Engineer has developed a dashboard that will issue the same SQL select clause to Snowflake every 12 hours.
—will Snowflake use the persisted query results from the result cache provided that the underlying data has not changed^

12 hours

24 hours

14 days

31 days

Q29. What are Invalid rules applicable when using stored procedure contains transaction?

A transaction can be inside a stored procedure, or a stored procedure can be inside a transaction.

You cannot start a transaction before calling the stored procedure, then complete the transaction inside the stored procedure.

You cannot start a transaction inside the stored procedure, then complete the transac-tion after returning from the procedure.

If a transaction is started inside a stored procedure and is still active when the stored procedure finishes, then an error occurs, and the transaction is rolled back.

A transaction inside a stored procedure can include a call to another stored procedure that contains a transaction.

All Rules are Applicable.

Q30. David, a Lead Data engineer with XYZ company looking out to improve query performance & oth-er benefits while working with Tables, Regular Views, MVs and Cached Results.
Which one of the following does not shows key similarities and differences between tables, regular views, cached query results, and materialized views while choosing any of them by David?

Regular views do not cache data, and therefore cannot improve performance by cach-ing.

As with non-materialized views, a materialized view automatically inherits the privileges of its base table.

Cached Query Results: Used only if data has not changed and if query only uses de-terministic functions (e.g. not CURRENT_DATE).

Materialized views are faster than tables because of their “cache” (i.e. the query results for the view); in addition, if data has changed, they can use their “cache” for data that hasn’t changed and use the base table for any data that has changed.

Both materialized views and regular views enhance data security by allowing data to be exposed or hidden at the row level or column level.

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

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

Q33. Does sensitive data in Snowflake is modified in an existing table while applying Masking policies?

YES

NO

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

Q35. Mark the correct Statements with respect to Secure views & its creation in the SnowFlake Account?

For a secure view, internal optimizations can indirectly expose data & the view defini-tion is visible to other users.

Secure views should not be used for views that are defined solely for query conven-ience, such as views created to simplify queries for which users do not need to under-stand the underlying data representation.

To convert an existing view to a secure view and back to a regular view, set/unset the SECURE keyword in the ALTER VIEW or ALTER MATERIALIZED VIEW com-mand.

For non-materialized views, the IS_SECURE column in the Information Schema and Account Usage views identifies whether a view is secure.

The internals of a secure view are not exposed in Query Profile (in the web interface). This is the case even for the owner of the secure view, because non-owners might have access to an owner’s Query Profile.

Explanation
Why Should I Use Secure Views?
For a non-secure view, internal optimizations can indirectly expose data.
Some of the internal optimizations for views require access to the underlying data in the base tables for the view. This access might allow data that is hidden from users of the view to be exposed through user code, such as user-defined functions, or other programmatic methods. Secure views do not utilize these optimizations, ensuring that users have no access to the underlying data.
For a non-secure view, the view definition is visible to other users.
By default, the query expression used to create a standard view, also known as the view definition or text, is visible to users in various commands and interfaces.
For security or privacy reasons, you might not wish to expose the underlying tables or internal struc-tural details for a view. With secure views, the view definition and details are visible only to author-ized users (i.e.
users who are granted the role that owns the view).
When Should I Use a Secure View?
Views should be defined as secure when they are specifically designated for data privacy (i.e. to limit access to sensitive data that should not be exposed to all users of the underlying table(s)).
Secure views should not be used for views that are defined solely for query convenience, such as views created to simplify queries for which users do not need to understand the underlying data representation. Secure views can execute more slowly than non-secure views.
Secure views are defined using the SECURE keyword with the standard DDL for views:
To create a secure view, specify the SECURE keyword in the CREATE VIEW or CREATE MA-TERIALIZED VIEW command.
To convert an existing view to a secure view and back to a regular view, set/unset the SECURE keyword in the ALTER VIEW or ALTER MATERIALIZED VIEW command.
The definition of a secure view is only exposed to authorized users (i.e. users who have been grant-ed the role that owns the view). If an unauthorized user uses any of the following commands or in-terfaces, the view definition is not displayed:
SHOW VIEWS and SHOW MATERIALIZED VIEWS commands.
GET_DDL utility function.
VIEWS Information Schema view.
VIEWS Account Usage view.
For non-materialized views, the IS_SECURE column in the Information Schema and Account Us-age views identifies whether a view is secure.
The internals of a secure view are not exposed in Query Profile (in the web interface). This is the case even for the owner of the secure view, because non-owners might have access to an owner’s Query Profile.

Q36. Bob, a Lead Data Engineer is looking out to get the function definition & queried below statement to check if this function is secure enough to use in his script or not.
select is_secure from information_schema.functions where function_name = ‘JOHNFUNCTION’; From the query output he is sure that, Function is secure UDF, what are the way provided by snow-flake to get the function definition of secure UDF?

He can get the secure UDF definition using GET_DDL utility function.

UDF definition or text, is visible to users via Query Profile (in the web interface).

SHOW FUNCTIONS Commands

Declaring a UDF as “secure” hide the definition from Bob & all the required Definition commands will throw error.

Q37. Snowflake web interface can be used to create users with no passwords or remove passwords from existing users?

TRUE

FALSE

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

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

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

Q41. The Snowpipe API provides REST endpoints for fetching load reports. One of the Endpoint named insertReport helps to retrieves a report of files submitted via insertFiles end point whose contents were recently ingested into a table. A success response (200) contains information about files that have recently been added to the table. Response Looks like below:
1.{
2.”pipe”: “SNOWTESTDB.SFTESTSCHEMA.SFpipe”,
3.”completeResult”: true,
4.”nextBeginMark”: “1_16”,
5.”files”: [
6.{
7.”path”: “data4859992083898.csv”,
8.”stageLocation”: “s3://mybucket/”,
9.”fileSize”: 89,
10.”timeReceived”: “2022-01-31T04:47:41.453Z”,
11.”lastInsertTime”: “2022-01-31T04:48:28.575Z”,
12.”rowsInserted”: 1,
13.”rowsParsed”: 1,
14.”errorsSeen”: 0,
15.”errorLimit”: 1,
16.”complete”: true,
17.”status”: “????”
18.}
19.]
20.}
Which one is the correct value of status string data in the Response Body?

LOADED

LOADED_SUCCESS

LOAD_SUCCESS

SUCCESS

Q42. Mohan, Data engineer works with ALTUSO Company, wants to programmatically check the status of the query. He needs query id to identify each query executed by Snowflake & using Snowflake Connector for Python to execute a query, how he will be able to meet this requirements. Select the best options you will suggest?

He can access the query ID through the sfqid attribute in the Cursor object.
1.cur = con.cursor()
2.cur.execute(“SELECT * FROM snowtable”)
3.print(cur.sfqid)

When he used the Snowflake Connector for Python to execute a query, he can access the query ID through the pyqueryid attribute in the Cursor object.

He needs to query history views to get the queryID as best practices.

Using python connector, snowflake does not support queryID retrieval for both syn-chronous & asynchronous query.