Snowflake DEA-C01 Real 2023 Braindumps Mock Exam Dumps
DEA-C01 Exam Questions | Real DEA-C01 Practice Dumps
NEW QUESTION # 21
A Data Engineer wants to check the status of a pipe named my_pipe. The pipe is inside a database named test and a schema named Extract (case-sensitive).
Which querywill provide the status of the pipe?
- A. SELECT FROM SYSTEM$PIPE_STATUS (''test.'extract'.my_pipe"i:
- B. SELECT * FROM SYSTEM$PIPE_STATUS ("test. 'extract' .my_pipe"};
- C. SELECT FROM SYSTEM$PIPE_STATUS (,test.,,Extracr,,.ny_pipe, i I
- D. SELE2T * FROM SYSTEM$PIPE_STATUS < ' test. "Extract", my_pipe');
Answer: D
Explanation:
Explanation
The query that will provide the status of the pipe is SELECT * FROM
SYSTEM$PIPE_STATUS('test."Extract".my_pipe');. The SYSTEM$PIPE_STATUS function returns information about a pipe, such as its name, status, last received message timestamp, etc. The function takes one argument: the pipe name in a qualified form. The pipe name should include the database name, the schema name, and the pipe name, separated by dots. If any of these names are case-sensitive identifiers, they should be enclosed in double quotes. In this case, the schema name Extract is case-sensitive and should be quoted. The other options are incorrect because they do not follow the correct syntax for the pipe name argument. Option A and B use single quotes instead of double quotes for case-sensitive identifiers. Option D uses double quotes instead of single quotes for non-case-sensitive identifiers.
NEW QUESTION # 22
While creating even Secure UDF, snowflake recommended to use randomized identifiers (e.g. gen-erated by UUID_STRING) instead of sequence-generated values?
- A. TRUE
(Correct) - B. FALSE
Answer: A
NEW QUESTION # 23
Which property can be used with ALTER USER command to temporarily disable MFA for the user so that they can log in?
- A. MINS_TO_BYPASS_MFA
- B. SECS_TO_BYPASS_MFA
- C. MINS_TO_SKIP_MFA
- D. HOURS_TO_BYPASS_MFA
Answer: A
Explanation:
Explanation
You can use the following properties for the ALTER USER command to perform these tasks:
MINS_TO_BYPASS_MFA
Specifies the number of minutes to temporarily disable MFA for the user so that they can log in. Af-ter the time passes, MFA is enforced and the user cannot log in without the temporary token gener-ated by the Duo Mobile application.
NEW QUESTION # 24
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?
- A. SELECT SYSTEM$CLUSTERING_INTFORMATICII ('Invoice' ) ;
- B. CALL SYSTEM$CLUSTERING_INFORMATION ('Invoice');
- C. SELECT $CLUSTERXNG_INFQRMATION ('Invoice')'
- D. CALL $CLUSTERINS_INFORMATION('Invoice');
Answer: A
Explanation:
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 # 25
Melissa, Senior Data Engineer, looking out to optimize query performance for one of the Critical Control Dashboard, she found that most of the searches by the users on the control dashboards are based on Equality search on all the underlying columns mostly. Which Best techniques she should consider here?
- A. Melissa can create Indexes & Hints on the searchable columns to speed up Equality search.
- B. A materialized view speeds both equality searches and range searches.
- C. The search optimization service would best fit here as it can be applied to all underlying columns & speeds up equality searches.
(Correct) - D. She can go for clustering on underlying tables which can speedup Equality searches.
Answer: C
Explanation:
Explanation
Clustering a table can speed any of the following, as long as they are on the clustering key:
Range searches.
Equality searches.
However, a table can be clustered on only a single key (which can contain one or more columns or expressions).
The search optimization service speeds equality searches. However, this applies to all the columns of supported types in a table that has search optimization enabled. This is what required here& best fit for purpose.
A materialized view speeds both equality searches and range searches, as well as some sort opera-tions, but only for the subset of rows and columns included in the materialized view.
NEW QUESTION # 26
Which Snowflake objects does the Snowflake Kafka connector use? (Select THREE).
- A. Internal user stage
- B. Internal table stage
- C. Internal named stage
- D. Pipe
- E. Serverless task
- F. Storage integration
Answer: B,C,D
Explanation:
Explanation
The Snowflake Kafka connector uses three Snowflake objects: pipe, internal table stage, and internal named stage. The pipe object is used to load data from an external stage into a Snowflake table using COPY statements. The internal table stage is used to store files that are loaded from Kafka topics into Snowflake using PUT commands. The internal named stage is used to store files that are rejected by the COPY statements due to errors or invalid data. The other options are not objects that are used by the Snowflake Kafka connector.
Option B, serverless task, is an object that can execute SQL statements on a schedule without requiring a warehouse. Option C, internal user stage, is an object that can store files for a specific user in Snowflake using PUT commands. Option F, storage integration, is an object that can enable secure access to external cloud storage services without exposing credentials.
NEW QUESTION # 27
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?
- A. Rewrite the report to eliminate the use of the loop construct
- B. Increase the size of the virtual warehouse
- C. Increase the number of maximum clusters of the virtual warehouse
- D. Define a clustering key for the sales data table
Answer: A
Explanation:
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.
NEW QUESTION # 28
The following code is executed ina Snowflake environment with the default settings:
What will be the result of the select statement?
- A. 1John
- B. John
- C. SQL compilation error object CUSTOMER' does not exist or is not authorized.
- D. 0
Answer: D
NEW QUESTION # 29
Which are false statements about Star Schema?
- A. The star schema separates business process data into facts, which hold the measurable, quantitative data about a business, and dimensions which are descriptive attributes re-lated to fact data.
- B. Star schemas are denormalized.
- C. Star schema is more flexible in terms of analytical needs compared to Data Vault Mod-elling.
- D. The star schema is an important special case of the snowflake schema and is more effec-tive for handling simpler queries.
Answer: C
NEW QUESTION # 30
To troubleshoot data load failure in one of your Copy Statement, Data Engineer have Executed a COPY statement with the VALIDATION_MODE copy option set to RETURN_ALL_ERRORS with reference to the set of files he had attempted to load. Which below function can facilitate analysis of the problematic records on top of the Results produced? [Select 2]
- A. LOAD_ERROR
- B. RESULT_SCAN
- C. LAST_QUERY_ID
- D. Rejected_record
Answer: B,C
Explanation:
Explanation
LAST_QUERY_ID() Function
Returns the ID of a specified query in the current session. If no query is specified, the most recently executed query is returned.
RESULT_SCAN() Function
Returns the result set of a previous command (within 24 hours of when you executed the query) as if the result was a table.
The following example validates a set of files (SFfile.csv.gz) that contain errors. To facilitate analy-sis of the errors, a COPY INTO <location> statement then unloads the problematic records into a text file so they could be analyzed and fixed in the original data files. The statement queries the RESULT_SCAN table.
1.#copy into Snowtable
2.from @SFstage/SFfile.csv.gz
3.validation_mode=return_all_errors;
4.#set qid=last_query_id();
5.#copy into @SFstage/errors/load_errors.txt from (select rejected_record from ta-ble(result_scan($qid))); Note: Other options are not valid functions.
NEW QUESTION # 31
Which of the below concepts/functions helps while implementing advanced Column-level Security?
- A. CURRENT_CLIENT
- B. INVOKER_ROLE
- C. Role Hierarchy
- D. CURRENT_ROLE
Answer: B,C,D
Explanation:
Explanation
Column-level Security supports using Context Functions in the conditions of the masking policy body to enforce whether a user has authorization to see data. To determine whether a user can see data in a given SQL statement, it is helpful to consider:
Masking policy conditions using CURRENT_ROLE target the role in use for the current session.
Masking policy conditions using INVOKER_ROLE target the executing role in a SQL statement.
Role hierarchy
Determine if a specified role in a masking policy condition (e.g. ANALYST custom role) is a lower privilege role in the CURRENT_ROLE or INVOKER_ROLE role hierarchy. If so, then the role returned by the CURRENT_ROLE or INVOKER_ROLE functions inherits the privileges of the specified role.
NEW QUESTION # 32
To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re-move them from the stages once the data has been loaded and the files which are no longer needed. Which option he can choose to remove these files either during data loading or afterwards?
- A. Script can be used during data loading & post data loading with DELETE command.
- B. Files no longer needed, can be removed using the PURGE=TRUE command.
- C. He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- D. Files no longer needed, can be removed using the REMOVE command.
Answer: B,C
Explanation:
Explanation
Managing Data Files
Staged files can be deleted from a Snowflake stage (user stage, table stage, or named stage) using the following methods:
Files that were loaded successfully can be deleted from the stage during a load by specifying the PURGE copy option in the COPY INTO <table> command.
After the load completes, use the REMOVE command to remove the files in the stage.
Removing files ensures they aren't inadvertently loaded again. It also improves load performance, because it reduces the number of files that COPY commands must scan to verify whether existing files in a stage were loaded already.
NEW QUESTION # 33
Charles, A Lead Data engineer, with ACCOUNTADMIN role wants to configure the time travel for one of the Schema's object. He setup the MIN_DATA_RETENTION_TIME_IN_DAYS pa-rameter with Value 79 at account level but he figured out that DA-TA_RETENTION_TIME_IN_DAYS is already set with value 81 at account level. What would be the effective minimum data retention period for an object?
- A. There is no such MIN_DATA_RETENTION_TIME_IN_DAYS parameter
- B. 0
- C. 1
- D. 2
Answer: B
Explanation:
Explanation
A user with the ACCOUNTADMIN role can also set the MIN_DATA_RETENTION_TIME_IN_DAYS at the account level. This parameter setting enforc-es a minimum data retention period for databases, schemas, and tables. Setting MIN_DATA_RETENTION_TIME_IN_DAYS does not alter or replace the DA-TA_RETENTION_TIME_IN_DAYS parameter value. It may, however, change the effective data retention period for objects. When MIN_DATA_RETENTION_TIME_IN_DAYS is set at the ac-count level, the data retention period for an object is determined by MAX(DATA_RETENTION_TIME_IN_DAYS, MIN_DATA_RETENTION_TIME_IN_DAYS).
NEW QUESTION # 34
Select the incorrect statement while working with warehouses?
- A. Resizing a suspended warehouse does not provision any new compute resources for the warehouse.
- B. Resizing a warehouse to a larger size is useful while loading and unloading significant amounts of data.
- C. Resizing a warehouse will have any immediate impact on statements that are currently being executed by the warehouse.
- D. Compute resources waiting to shut down are considered to be in "quiesce" mode.
Answer: C
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 # 35
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.
- A. The SQL command DESCRIBE FUNCTION will list the imported Python packages of the Python User-Defined Function (UDF).
- B. Python packages can access any external endpoints
- C. Querying information__schema .packages will provide a list of supported Python packages and versions
- D. Third-party supported Python packages are locked down to prevent hitting
- E. Python packages can only be loaded in a local environment
Answer: A,B,C
Explanation:
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 # 36
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?
- A. Add the with managed access parameter on the schema
- B. Revoke grant decisions from the user on the table
- C. Grant ownership to the user on the table
- D. Revoke grant decisions from the user on the schema.
Answer: A
Explanation:
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.
NEW QUESTION # 37
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?
- A. As with non-materialized views, a materialized view automatically inherits the privileges of its base table.
- B. Cached Query Results: Used only if data has not changed and if query only uses de-terministic functions (e.g. not CURRENT_DATE).
- C. 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.
- D. Both materialized views and regular views enhance data security by allowing data to be exposed or hidden at the row level or column level.
- E. Regular views do not cache data, and therefore cannot improve performance by cach-ing.
Answer: A
Explanation:
Explanation
Materialized Views, like other database objects (tables, views, UDFs, etc.), are owned by a role and have privileges that can be granted to other roles.
You can grant the following privileges on a materialized view:
SELECT
As with non-materialized views, a materialized view does not automatically inherit the privileges of its base table. You should explicitly grant privileges on the materialized view to the roles that should use that view.
As with non-materialized views, a user who wishes to access a materialized view needs privileges only on the view, not on the underlying object(s) that the view references.
Rest is correct.
NEW QUESTION # 38
A Data Engineer wants to create a new development database (DEV) as a clone of the permanent production database (PROD) There is a requirement to disable Fail-safe for all tables.
Which command will meet these requirements?
- A. CREATE DATABASE DEV
CLONE PROD; - B. CREATE DATABASE DEV
CLONE PROD
FAIL_SAFE=FALSE; - C. CREATE TRANSIENT DATABASE DEV
CLONE RPOD - D. CREATE DATABASE DEV
CLOSE PROD
DATA_RETENTION_TIME_IN_DAYS =0L
Answer: C
Explanation:
Explanation
This option will meet the requirements of creating a new development database (DEV) as a clone of the permanent production database (PROD) and disabling Fail-safe for all tables. By using the CREATE TRANSIENT DATABASE command, the Data Engineer can create a transient database that does not have Fail-safe enabled by default. Fail-safe is a feature in Snowflake that provides additional protection against data loss by retaining historical data for seven days beyond the time travel retention period. Transient databases do not have Fail-safe enabled, which means that they do not incur additional storage costs for historical data beyond their time travel retention period. By using the CLONE option, the Data Engineer can create an exact copy of the PROD database, including its schemas, tables, views, and other objects.
NEW QUESTION # 39
A Data Engineer ran a stored procedure containing various transactions During the execution, the session abruptly disconnected preventing one transactionfrom committing or rolling hark.The transaction was left in a detached state and created a lock on resources
...must the Engineer take to immediately run a new transaction?
- A. Call the system function SYSTEM$CANCEL_TRANSACTION.
- B. Set the transaction abort on error to true in the stored procedure.
- C. Call the system function SYSTEM$ABORT_TRANSACTION.
- D. Set the LOCK_TIMEOUTto FALSE in the stored procedure
Answer: C
Explanation:
Explanation
The system function SYSTEM$ABORT_TRANSACTION can be used to abort a detached transaction that was left in an open state due to a session disconnect or termination. The function takes one argument: the transaction ID of the detached transaction. The function will abort the transaction and release any locks held by it. The other options are incorrect because they do not address the issue of a detached transaction. The system function SYSTEM$CANCEL_TRANSACTION can be used to cancel a running transaction, but not a detached one. The LOCK_TIMEOUT parameter can be used to set a timeout period for acquiring locks on resources, but it does not affect existing locks. The TRANSACTION_ABORT_ON_ERROR parameter can be used to control whether a transaction should abort or continue when an error occurs, but it does not affect detached transactions.
NEW QUESTION # 40
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?
- A.
- B.
- C.
- D.
Answer: B
NEW QUESTION # 41
......
Verified DEA-C01 Exam Dumps Q&As - Provide DEA-C01 with Correct Answers: https://www.ipassleader.com/Snowflake/DEA-C01-practice-exam-dumps.html
Pass Your DEA-C01 Dumps Free Latest Snowflake Practice Tests: https://drive.google.com/open?id=12Q34DOpcXUQk1Dq3gLdbxiL7RT3reIgI