100% Reliable Microsoft DEA-C01 Exam Dumps Test Pdf Exam Material
Based on Official Syllabus Topics of Actual Snowflake DEA-C01 Exam
NEW QUESTION # 50
Data Engineer looking out for quick tool for understanding the mechanics of queries & need to know more about the performance or behaviour of a particular query.
He should go to which feature of snowflake which can help him to spot typical mistakes in SQL query expressions to identify potential performance bottlenecks and improvement opportunities?
- A. Query Profile
- B. Query Designer
- C. Performance Metadata table
- D. Query Optimizer
Answer: A
Explanation:
Explanation
Query Profile, available through the classic web interface, provides execution details for a query. For the selected query, it provides a graphical representation of the main components of the pro-cessing plan for the query, with statistics for each component, along with details and statistics for the overall query.
Query Profile is a powerful tool for understanding the mechanics of queries. It can be used whenev-er you want or need to know more about the performance or behavior of a particular query. It is de-signed to help you spot typical mistakes in SQL query expressions to identify potential performance bottlenecks and improvement opportunities.
NEW QUESTION # 51
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?
- A. TRUE
- B. FALSE
Answer: A
NEW QUESTION # 52
You as Data engineer might want to consider disabling auto-suspend for a warehouse if?
- A. You require the warehouse to be available with delay.
- B. You have a heavy, steady workload for the warehouse.
- C. You require the warehouse to be available with no delay or lag time.
- D. You have a low, fluctuating workload for the warehouse.
Answer: B,C
Explanation:
Explanation
Automating Warehouse Suspension
Data Engineer might want to consider disabling auto-suspend for a warehouse if:
He/She have a heavy, steady workload for the warehouse.
He/She require the warehouse to be available with no delay or lag time. Warehouse provisioning is generally very fast (e.g. 1 or 2 seconds); however, depending on the size of the warehouse and the availability of compute resources to provision, it can take longer.
If he/she chose to disable auto-suspend, He/she must carefully consider the costs associated with running a warehouse continually, even when the warehouse is not processing queries. The costs can be significant, especially for larger warehouses (X-Large, 2X-Large, etc.).
To disable auto-suspend, Engineer must explicitly select Never in the web interface, or specify 0 or NULL in SQL.
NEW QUESTION # 53
The following CREATE DATABASE command creates a clone of a database snowmy_db i.e.
Create database pods_db clone snowmy_db
before (statement => '7e5d0cb9-005e-94e6-b058-k8f5b37c5725');
What are possible reason of failing cloning operation for this database?
- A. Time Travel Statement query time is beyond the retention time of few current child (e.g., a table) of the Database entity.
- B. SQL Compilation error: "Incorrect Syntax 'before' while creating database"
- C. CREATE DATABASE query fails due to compilation error as it do not support state-ment keyword.
- D. Time Travel Statement query time is at or before the point in time when the object was created.
Answer: A,D
NEW QUESTION # 54
Partition columns optimize query performance by pruning out the data files that do not need to be scanned (i.e.
partitioning the external table). Which pseudocolumn of External table evaluate as an expression that parses the path and/or filename information.
- A. METADATA$ROW_NUMBER
- B. METADATA$COLUMNNAME
- C. METADATA$FILENAME
- D. METADATA$FILEPATH
Answer: C
Explanation:
Explanation
METADATA$FILENAME
A pseudocolumn that identifies the name of each staged data file included in the external table, in-cluding its path in the stage.
An external table creator defines partition columns in a new external table as expressions that parse the path and/or filename information stored in the METADATA$FILENAME pseudocolumn. A partition consists of all data files that match the path and/or filename in the expression for the parti-tion column.
NEW QUESTION # 55
Select the incorrect statements regarding Clustering depth?
- A. It helps Monitoring the clustering "health" of a large table, particularly over time as DML is performed on the table.
- B. Clustering depth can be used for determining whether a large table would benefit from explicitly defining a clustering key.
- C. A table with no micro-partitions (i.e. an unpopulated/empty table) has a clustering depth of 1.
(Correct) - D. The clustering depth for a populated table measures the average depth (1 or greater) of the overlapping micro-partitions for specified columns in a table. The smaller the aver-age depth, the better clustered the table is with regards to the specified columns.
Answer: C
Explanation:
Explanation
A table with no micro-partitions (i.e. an unpopulated/empty table) has a clustering depth of 0.
NEW QUESTION # 56
Which ones are the false statements about Materialized Views?
- A. Snowflake does not allow standard DML (e.g. INSERT, UPDATE, DELETE) on ma-terialized views.
- B. A materialized view can also be used as the data source for a subquery.
- C. Clustering a subset of the materialized views on a table tends to be more cost-effective than clustering the table itself.
- D. Snowflake does not allow users to truncate materialized views.
- E. Materialized views can be secure views.
- F. Materialized views are first-class account objects.
Answer: F
Explanation:
Explanation
Materialized views are first-class Database objects & rest of the understandings are true.
NEW QUESTION # 57
A SQL UDF evaluates an arbitrary SQL expression and returns the result(s) of the expression. Which value type it can returns?
- A. Single Value
- B. A Set of Rows
- C. Regex
- D. Scaler or Tabular depend on input SQL expression
Answer: D
NEW QUESTION # 58
To support Time Travel, Which of the following SQL extensions/parameters/commands have been implemented?
- A. ONSET (time difference in seconds from the present time)
- B. OFFSET (time difference in seconds from the present time)
- C. STATEMENT (identifier for statement, e.g. query ID)
- D. AT | BEFORE clause which can be specified in the CREATE ... CLONE commands.
- E. STATEMENT_ID (identifier for statement, e.g. query ID)
- F. UNDROP command for tables, schemas, and databases.
Answer: B,C,D,F
NEW QUESTION # 59
As Data Engineer, you have been asked to access data held in AWS Glacier Deep Archive storage class for Historical Data Analysis, which one is the correct statement to recommend?
- A. You cannot access data held in archival cloud storage classes that requires restoration before it can be retrieved.
- B. We can simply access AWS Glacier Deep Archive storage External Stage data using PUT command.
- C. Upload (i.e. stage) files to your cloud storage account using the tools provided by the cloud storage service.
- D. Data can be accessed from External stage using AWS Private link in this case.
- E. Loading data from AWS cloud storage services is supported regardless of the cloud platform that hosts your Snowflake account.
Answer: A
Explanation:
Explanation
External stage
References data files stored in a location outside of Snowflake. Currently, the following cloud stor-age services are supported:
Amazon S3 buckets
Google Cloud Storage buckets
Microsoft Azure containers
The storage location can be either private/protected or public.
You cannot access data held in archival cloud storage classes that requires restoration before it can be retrieved. These archival storage classes include, for example, the Amazon S3 Glacier Flexible Retrieval or Glacier Deep Archive storage class, or Microsoft Azure Archive Storage.
NEW QUESTION # 60
Which Role inherits the privileges of the USERADMIN role via the system role hierarchy?
- A. PUBLIC
- B. SYSADMIN
- C. CUSTOM ROLE
- D. SECURITYADMIN
Answer: D
NEW QUESTION # 61
Select the correct usage statements with regards to SQL UDF?
- A. The body of a UDF cannot contain DDL statements or any DML statement other than SELECT.
- B. All of above are correct.
- C. Scalar functions (UDFs) have a limit of 500 input arguments.
- D. You can include only one query expression.
- E. When using a query expression in a SQL UDF, do not include a semicolon within the UDF body to terminate the query expression.
Answer: B
NEW QUESTION # 62
Select the incorrect statement while working with warehouses?
- A. Compute resources waiting to shut down are considered to be in "quiesce" mode.
- B. Resizing a warehouse to a larger size is useful while loading and unloading significant amounts of data.
- C. Resizing a suspended warehouse does not provision any new compute resources for the warehouse.
- D. Resizing a warehouse will have any immediate impact on statements that are currently being executed by the warehouse.
Answer: D
Explanation:
Explanation
Resizing a warehouse doesn't have any impact on statements that are currently being executed by the warehouse. When resizing to a larger size, the new compute resources, once fully provisioned, are used only to execute statements that are already in the warehouse queue, as well as all future statements submitted to the warehouse.
NEW QUESTION # 63
Mark the correct Statements with respect to Secure views & its creation in the SnowFlake Account?
- A. For a secure view, internal optimizations can indirectly expose data & the view defini-tion is visible to other users.
- B. 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.
- C. 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.
- D. 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.
- E. For non-materialized views, the IS_SECURE column in the Information Schema and Account Usage views identifies whether a view is secure.
Answer: B,C,D,E
Explanation:
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.
NEW QUESTION # 64
Pascal, a Data Engineer, have requirement to retrieve the 10 most recent executions of a specified task (completed, still running, or scheduled in the future) scheduled within the last hour, which of the following is the correct SQL Code ?
- A. 1.select *
2.from table(information_schema.task_history(
3.scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
4.result_limit => 10,query_id IS NOT NULL
5.task_name=>'MYTASK')); - B. 1.select *
2.from table(information_schema.task_history(
3.scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
4.result_limit => 10,
5.task_name=>'MYTASK')); - C. 1.select *
2.from table(information_schema.task_history(
3.scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
4.result_limit => 11,
5.task_name=>'MYTASK') WHERE query_id IS NOT NULL); - D. 1.select *
2.from table(information_schema.task_history(
3.scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
4.result_limit => 10,
5.task_name=>'MYTASK') WHERE query_id IS NOT NULL);
Answer: B
Explanation:
Explanation
To retrieve only tasks that are completed or still running, filter the query using WHERE query_id IS NOT NULL.
NEW QUESTION # 65
What are Invalid rules applicable when using stored procedure contains transaction?
- A. All Rules are Applicable.
- B. A transaction can be inside a stored procedure, or a stored procedure can be inside a transaction.
- C. 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.
- D. You cannot start a transaction before calling the stored procedure, then complete the transaction inside the stored procedure.
- E. You cannot start a transaction inside the stored procedure, then complete the transac-tion after returning from the procedure.
- F. A transaction inside a stored procedure can include a call to another stored procedure that contains a transaction.
Answer: A
NEW QUESTION # 66
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?
- A. 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) - B. He needs to query history views to get the queryID as best practices.
- C. 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.
- D. Using python connector, snowflake does not support queryID retrieval for both syn-chronous & asynchronous query.
Answer: A
Explanation:
Explanation
Retrieving the Snowflake Query ID
A query ID identifies each query executed by Snowflake. When you use the Snowflake Connector for Python to execute a query, you can access the query ID through the sfqid attribute in the Cursor object:
1.# Retrieving a Snowflake Query ID
2.cur = con.cursor()
3.cur.execute("SELECT * FROM testtable")
4.print(cur.sfqid)
NEW QUESTION # 67
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.
- A. TRUE
- B. FALSE
Answer: A
Explanation:
Explanation
If you are using Snowflake Enterprise Edition (or a higher edition), all your warehouses should be configured as multi-cluster warehouses.
NEW QUESTION # 68
Data engineer designed the data pipelines using Snowpipe to load data files into Snowflake tables, what will happen in case few files with same name but modified data are queued for reloading?
- A. Snowpipe uses file loading metadata associated with each table object, so no metadata available to prevent duplication.
- B. Data will be reloaded as files are modified & its associated metadata also changed. But Snowflake handle implicitly deduplication.
- C. Snowpipe uses file loading metadata associated with each pipe object to prevent reload-ing the same files (and duplicating data) in a table.
- D. eTAG is changed for Files even they are having same name, so data will be duplicated in SnowFlake tables.
Answer: C
Explanation:
Explanation
Snowflake uses file loading metadata to prevent reloading the same files (and duplicating data) in a table.
Snowpipe prevents loading files with the same name even if they were later modified (i.e. have a different eTag).
The file loading metadata is associated with the pipe object rather than the table. As a result:
Staged files with the same name as files that were already loaded are ignored, even if they have been modified, e.g. if new rows were added or errors in the file were corrected.
Truncating the table using the TRUNCATE TABLE command does not delete the Snowpipe file loading metadata.
NEW QUESTION # 69
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?
- A. UDF definition or text, is visible to users via Query Profile (in the web interface).
- B. He can get the secure UDF definition using GET_DDL utility function.
- C. Declaring a UDF as "secure" hide the definition from Bob & all the required Definition commands will throw error.
- D. SHOW FUNCTIONS Commands
Answer: C
NEW QUESTION # 70
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?
- A. To load files whose metadata has expired, set the LOAD_UNCERTAIN_FILES copy option to true.
- B. 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.
- C. Set the FORCE option to load all files, ignoring load metadata if it exists.
- D. 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.
Answer: A
Explanation:
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
NEW QUESTION # 71
Steven created the task, what additional privileges required by Steven on the task so that he can suspend or resume the tasks?
- A. Steven must have SUSPEND privilege on the task so that he can suspend or resume the task.
- B. Steven is already owner of the task; he can execute the task & suspend/resume the task without any additional privileges.
- C. Steven needs to have Global Managed RESUME privilege by TASK administrator.
- D. In addition to the task owner, a Steven Role must have OPERATE privilege on the task so that he can suspend or resume the task.
Answer: D
Explanation:
Explanation
In addition to the task ownership privilege, a role that has the OPERATE privilege on the task can suspend or resume the task.
NEW QUESTION # 72
Clones can be cloned, with no limitations on the number or iterations of clones that can be created (e.g. you can create a clone of a clone of a clone, and so on), which results in a n-level hierarchy of cloned objects, each with their own portion of shared and independent data storage?
- A. TRUE
- B. FALSE
Answer: A
NEW QUESTION # 73
......
Free DEA-C01 Dumps are Available for Instant Access: https://www.2pass4sure.com/SnowPro-Advanced/DEA-C01-actual-exam-braindumps.html
View All DEA-C01 Actual Exam Questions Answers and Explanations for Free: https://drive.google.com/open?id=1e7Au4B7Oz8aPbcMLe0TMjGTEffCuGmRV