Dec 29, 2023 Step by Step Guide to Prepare for DEA-C01 Exam BrainDumps
SnowPro Advanced DEA-C01 Real Exam Questions and Answers FREE Updated on 2023
NEW QUESTION # 29
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. Files no longer needed, can be removed using the PURGE=TRUE command.
- B. He can choose to remove stage files during data loading (using the COPY INTO <table> command).
- C. Script can be used during data loading & post data loading with DELETE command.
- D. Files no longer needed, can be removed using the REMOVE command.
Answer: A,B
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 # 30
Which is the non-supportable JavaScript UDF data types?
- A. Integers
- B. String
- C. Double
- D. Binary
Answer: A
NEW QUESTION # 31
Snowflake supports using key pair authentication for enhanced authentication security as an alterna-tive to basic authentication (i.e. username and password). Select the list of SnowFlake Clients sup-port the same?
[Select All that Apply]
- A. SnowSQL
- B. Go Driver
- C. SnowFlake Connector for Spark
- D. Node.js
- E. SnowCD
Answer: A,B,C,D
NEW QUESTION # 32
Snowflake web interface can be used to create users with no passwords or remove passwords from existing users?
- A. FALSE
- B. TRUE
Answer: A
Explanation:
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 # 33
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. Regular views do not cache data, and therefore cannot improve performance by cach-ing.
- C. Both materialized views and regular views enhance data security by allowing data to be exposed or hidden at the row level or column level.
- D. 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.
- E. Cached Query Results: Used only if data has not changed and if query only uses de-terministic functions (e.g. not CURRENT_DATE).
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 # 34
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. Set the LOCK_TIMEOUTto FALSE in the stored procedure
- B. Set the transaction abort on error to true in the stored procedure.
- C. Call the system function SYSTEM$CANCEL_TRANSACTION.
- D. Call the system function SYSTEM$ABORT_TRANSACTION.
Answer: D
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 # 35
Data Engineer is using existing pipe that automates data loads using event notifications, later he figured out the needs to modify pipe properties. For the same, He decided to recreate the pipe as best practice. He followed the below steps for the same.
1. Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is RUN-NING.
2. Recreate the pipe (using CREATE OR REPLACE PIPE).
3. Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is RUN-NING.
Which are the Missing recommended steps while Recreating Pipes for Automated Data Loads?
- A. Pause the pipe (using ALTER PIPE ... SET PIPE_EXECUTION_PAUSED = true) Pre & Post recreation & Resume after recreation (using ALTER PIPE ... SET PIPE_EXECUTION_PAUSED = false).
- B. Force the pipe to resume (using SYSTEM$PIPE_FORCE_RESUME).
- C. Terminate the existing pipe (using ALTER PIPE ... SET PIPE_EXECUTION_TERMINATE = true) before recreation.
- D. CREATE OR REPLACE PIPE command will recreate the PIPE successfully.
Answer: A
Explanation:
Explanation
Recreating a pipe (using a CREATE OR REPLACE PIPE statement) is necessary to modify most pipe properties.
Recreating Pipes for Automated Data Loads
When recreating a pipe that automates data loads using event notifications, it's recommended that Data Engineer complete the following steps:
1. Pause the pipe (using ALTER PIPE ... SET PIPE_EXECUTION_PAUSED = true).
2. Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is PAUSED.
3. Recreate the pipe (using CREATE OR REPLACE PIPE).
4. Pause the pipe again.
5. Review the configuration steps for your cloud messaging service to ensure the settings are still accurate.
6. Query the SYSTEM$PIPE_STATUS function again and verify that the pipe execution state is RUNNING.
NEW QUESTION # 36
Regular views do not cache data, and therefore cannot improve performance by caching?
- A. FALSE
- B. TRUE
Answer: B
Explanation:
Explanation
Regular views do not cache data, and therefore cannot improve performance by caching.
NEW QUESTION # 37
At what isolation level are Snowflake streams?
- A. Read committed
- B. Read uncommitted
- C. Snapshot
- D. Repeatable read
Answer: D
Explanation:
Explanation
The isolation level of Snowflake streams is repeatable read, which means that each transaction sees a consistent snapshot of data that does not change during its execution. Streams use time travel internally to provide this isolation level and ensure that queries on streams return consistent results regardless of concurrent transactions on their source tables.
NEW QUESTION # 38
Which one is not the Core benefits of micro-partitioning
- A. Columns are also compressed individually within micro-partitions.
- B. Enables extremely efficient DML and fine-grained pruning for faster queries.
- C. Columns are stored independently within micro-partitions, often referred to as colum-nar storage.
- D. Micro-partitions can overlap in their range of values, helps data skewing.
- E. Snowflake micro-partitions are derived automatically they do not need to be explicitly defined up-front or maintained by users.
Answer: D
Explanation:
Explanation
The benefits of Snowflake's approach to partitioning table data include:
In contrast to traditional static partitioning, Snowflake micro-partitions are derived automatically; they don't need to be explicitly defined up-front or maintained by users.
As the name suggests, micro-partitions are small in size (50 to 500 MB, before compression), which enables extremely efficient DML and fine-grained pruning for faster queries.
Micro-partitions can overlap in their range of values, which, combined with their uniformly small size, helps prevent skew.
Columns are stored independently within micro-partitions, often referred to as columnar storage. This enables efficient scanning of individual columns; only the columns referenced by a query are scanned.
Columns are also compressed individually within micro-partitions. Snowflake automatically de-termines the most efficient compression algorithm for the columns in each micro-partition.
NEW QUESTION # 39
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?
- A. All DML operations (e.g. DELETE, UPDATE, MERGE) take advantage of the under-lying micro-partition metadata to facilitate and simplify table maintenance.
- B. 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.
- C. In Snowflake, as data is inserted/loaded into a table, clustering metadata is collected and recorded for each micro-partition created during the process.
- D. 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.
- E. Snowflake stores metadata about all rows stored in a micro-partition, including number of distinct columns.
Answer: E
Explanation:
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.
NEW QUESTION # 40
Which privilege are required on an object (i.e. user or role) with USERADMIN Role can modify the object properties?
- A. MANAGE GRANTS
- B. OWNERSHIP
- C. MODIFY
- D. OPEARTE
Answer: B
NEW QUESTION # 41
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. 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.
- C. Using python connector, snowflake does not support queryID retrieval for both syn-chronous & asynchronous query.
- D. He needs to query history views to get the queryID as best practices.
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 # 42
A CSV file around 1 TB in size is generated daily on an on-premise server A corresponding table. Internal stage, and file format have already been created in Snowflake to facilitate the data loading process How can the process of bringing the CSV file into Snowflake be automated using the LEAST amount of operational overhead?
- A. On the on-premise server schedule a SQL file to run using SnowSQL that executes a PUT to push a specific file to the internal stage Create a task that executes once a day m Snowflake and runs a OOPY WTO statement that references the internal stage Schedule the task to start after the file lands in the internal stage
- B. On the on-premise server schedule a SQL file to run using SnowSQL that executes a PUT to push a specific file to the internal stage. Create a pipe that runs a copy into statement that references the internal stage Snowpipe auto-ingest will automatically load the file from the internal stage when the new file lands in the internal stage.
- C. On the on premise server schedule a Python file that uses the Snowpark Python library. The Python script will read the CSV data into a DataFrame and generate an insert into statement that will directly load into the table The script will bypass the need to move a file into an internal stage
- D. Create a task in Snowflake that executes once a day and runs a copy into statement that references the internal stage The internal stage will read the files directly from the on-premise server and copy the newest file into the table from the on-premise server to the Snowflake table
Answer: B
Explanation:
Explanation
This option is the best way to automate the process of bringing the CSV file into Snowflake with the least amount of operational overhead. SnowSQL is a command-line tool that can be used to execute SQL statements and scripts on Snowflake. By scheduling a SQL file that executes a PUT command, the CSV file can be pushed from the on-premise server to the internal stage in Snowflake. Then, by creating a pipe that runs a COPY INTO statement that references the internal stage, Snowpipe can automatically load the file from the internal stage into the table when it detects a new file in the stage. This way, there is no need to manually start or monitor a virtual warehouse or task.
NEW QUESTION # 43
SYSTEM$CLUSTERING_INFORMATION functions returns clustering information, including average clustering depth, for a table based on one or more columns in the table. The function returns a JSON object containing average_overlaps name/value pairs. Does High average_overlaps indicates well organized Clustering?
- A. NO
- B. YES
Answer: A
Explanation:
Explanation
Higher the avg_overlap indicates poorly organized clustering.
NEW QUESTION # 44
Which query will show a list of the 20 most recent executions of a specified task kttask, that have been scheduled within the last hour that have ended or are stillrunning's.
- A.
- B.
- C.
- D.
Answer: C
NEW QUESTION # 45
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:
- A. VALIDATION_MODE only support Data loading operation i.e., do not work while da-ta unloading.
- B. 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.
- C. VALIDATION_MODE does not support COPY statements that transform data during a load. If the parameter is specified, the COPY statement returns an error.
- D. VALIDATION_MODE option supported these values:
RETURN_n_ROWS,
RETURN_ERRORS,
RETURN_ALL_ERRORS
Answer: B,C,D
Explanation:
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 # 46
Data Engineer is looking out to delete staged files automatically/periodically when the data is suc-cessfully loaded into tables by the Snowpipe. For achieving the same, which options/command is best suited: [Select 2]
- A. To remove staged files that no longer needed, periodically DELETE command can be executed to delete the files.
- B. PURGE option can be set as True in the COPY INTO Command embedded in PIPE objects definition.
- C. REMOVE_STAGE_FILES option can be set as True in the COPY INTO Command embedded in PIPE objects definition.
- D. To remove staged files that no longer needed, periodically REMOVE command can be executed to delete the files.
Answer: B,D
Explanation:
Explanation
Deleting Staged Files After Snowpipe Loads the Data
Pipe objects do not support the PURGE copy option. Snowpipe cannot delete staged files automat-ically when the data is successfully loaded into tables.
To remove staged files that you no longer need, It is recommended to periodically executing the REMOVE command to delete the files.
Alternatively, configure any lifecycle management features provided by cloud storage service pro-vider.
NEW QUESTION # 47
External Function is a type of UDF & can be Scaler or Tabular?
- A. FALSE
- B. TRUE
Answer: A
Explanation:
Explanation
External functions must be scalar functions. A scalar external function returns a single value for each input row.
NEW QUESTION # 48
Search optimization works best to improve the performance of a query when the following condi-tions are true:[Select All that apply]
- A. The table is frequently queried on columns other than the primary cluster key.
- B. Search Query uses Equality predicates (for example, <column_name> = <constant>) OR Predicates that use IN.
- C. Search Query uses Sort Operations.
- D. The table is not clustered.
Answer: A,B,D
Explanation:
Explanation
Materialized Views works best for search query performance in case of Sort Operations. For Rest of the points Search optimization works best to improve query performance.
NEW QUESTION # 49
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. SQL Compilation error: "Incorrect Syntax 'before' while creating database"
- B. Time Travel Statement query time is beyond the retention time of few current child (e.g., a table) of the Database entity.
- C. Time Travel Statement query time is at or before the point in time when the object was created.
- D. CREATE DATABASE query fails due to compilation error as it do not support state-ment keyword.
Answer: B,C
NEW QUESTION # 50
......
Ultimate Guide to Prepare DEA-C01 Certification Exam for SnowPro Advanced: https://www.examprepaway.com/Snowflake/braindumps.DEA-C01.ete.file.html
DEA-C01 Ultimate Study Guide: https://drive.google.com/open?id=1DtM1pBNf42c7mwnXCEtM8ZNwXwtLwTQC