[Nov 09, 2023] New Snowflake DEA-C01 Dumps with Test Engine and PDF (New Questions)
Pass Your DEA-C01 Exam Easily - Real DEA-C01 Practice Dump Updated
NEW QUESTION # 37
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 # 38
While running an external function, me following error message is received:
Error:function received the wrong number of rows
What iscausing this to occur?
- A. Nested arrays are not supported in the JSON response
- B. The JSON returned by the remote service is not constructed correctly
- C. The return message did not produce the same number of rows that it received
- D. External functions do not support multiple rows
Answer: C
Explanation:
Explanation
The error message "function received the wrong number of rows" is caused by the return message not producing the same number of rows that it received. External functions require that the remote service returns exactly one row for each input row that it receives from Snowflake. If the remote service returns more or fewer rows than expected, Snowflake will raise an error and abort the function execution. The other options are not causes of this error message. Option A is incorrect because external functions do support multiple rows as long as they match the input rows. Option B is incorrect because nested arrays are supported in the JSON response as long as they conform to the return type definition of the external function. Option C is incorrect because the JSON returned by the remote service may be constructed correctly but still produce a different number of rows than expected.
NEW QUESTION # 39
To advance the offset of a stream to the current table version without consuming the change data in a DML operation, which of the following operations can be done by Data Engineer? [Select 2]
- A. Insert the current change data into a temporary table. In the INSERT statement, query the stream but include a WHERE clause that filters out all of the change data (e.g. WHERE 0 = 1).
- B. Delete the offset using STREAM properties SYSTEM$RESET_OFFSET( <stream_id> )
- C. A stream advances the offset only when it is used in a DML transaction, so none of the options works without consuming the change data of table.
- D. using the CREATE OR REPLACE STREAM syntax, Recreate the STREAM
Answer: A,D
Explanation:
Explanation
When created, a stream logically takes an initial snapshot of every row in the source object (e.g. ta-ble, external table, or the underlying tables for a view) by initializing a point in time (called an off-set) as the current transactional version of the object. The change tracking system utilized by the stream then records information about the DML changes after this snapshot was taken. Change rec-ords provide thestate of a row before and after the change. Change information mirrors the column structure of the tracked source object and includes additional metadata columns that describe each change event.
Note that a stream itself does not contain any table data. A stream only stores an offset for the source object and returns CDC records by leveraging the versioning history for the source object.
A new table version is created whenever a transaction that includes one or more DML statements is committed to the table.
In the transaction history for a table, a stream offset is located between two table versions. Query-ing a stream returns the changes caused by transactions committed after the offset and at or before the current time.
Multiple queries can independently consume the same change data from a stream without changing the offset.
A stream advances the offset only when it is used in a DML transaction. This behavior applies to both explicit and autocommit transactions. (By default, when a DML statement is execut-ed, an autocommit transaction is implicitly started and the transaction is committed at the comple-tion of the statement. This behavior is controlled with the AUTOCOMMIT parameter.) Querying a stream alone does not advance its offset, even within an explicit transaction; the stream contents must be consumed in a DML statement.
To advance the offset of a stream to the current table version without consuming the change data in a DML operation, complete either of the following actions:
Recreate the stream (using the CREATE OR REPLACE STREAM syntax).
Insert the current change data into a temporary table. In the INSERT statement, query the stream but include a WHERE clause that filters out all of the change data (e.g. WHERE 0 = 1).
NEW QUESTION # 40
Data Engineer identified use case where he decided to use materialized view for query perfor-mance. Which one is not the limitation he must be aware of before using MVs in their use case?
- A. A materialized view cannot include HAVING clauses OR ORDER BY clause.
- B. A materialized views does not support clustering.
- C. A materialized views cannot be created on Shared Data.
- D. Time Travel is not currently supported on materialized views.
- E. A materialized view can query only a single table & Joins, including self-joins, are not supported.
- F. Truncating a materialized view is not supported.
- G. Context Functions like CURRENT_TIME or CURRENT_TIMESTAMP is not per-mitted.
- H. You cannot directly clone a materialized view by using the CREATE MATERIAL-IZED VIEW ...
CLONE... command.
Answer: A,B
Explanation:
Explanation
Defining a clustering key on a materialized view is supported and can increase performance in many situations. However, it also adds costs.
If you cluster both the materialized view(s) and the base table on which the materialized view(s) are defined, you can cluster the materialized view(s) on different columns from the columns used to cluster the base table.
You can create a materialized view on shared data.
Also You can use Snowflake's data sharing feature to share a materialized view.
Rest all are correct.
NEW QUESTION # 41
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. Declaring a UDF as "secure" hide the definition from Bob & all the required Definition commands will throw error.
- B. He can get the secure UDF definition using GET_DDL utility function.
- C. UDF definition or text, is visible to users via Query Profile (in the web interface).
- D. SHOW FUNCTIONS Commands
Answer: A
NEW QUESTION # 42
You can execute zero, one, or more transactions inside a stored procedure?
- A. TRUE
- B. FALSE
Answer: A
NEW QUESTION # 43
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. LAST_QUERY_ID
- B. LOAD_ERROR
- C. RESULT_SCAN
- D. Rejected_record
Answer: A,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 # 44
While creating even Secure UDF, snowflake recommended to use randomized identifiers (e.g. gen-erated by UUID_STRING) instead of sequence-generated values?
- A. FALSE
- B. TRUE
(Correct)
Answer: B
NEW QUESTION # 45
While working with Multi Cluster Warehouses, Select the incorrect understanding of Data Engineer about its usage?
- A. When choosing the minimum number of clusters for a multi-cluster warehouse keep the default value as
1. - B. When choosing the maximum number of clusters for a multi-cluster warehouse set its value as large as possible.
- C. Multi-cluster warehouses are designed specifically for handling queuing and perfor-mance issues related to large numbers of concurrent users and/or queries.
- D. Unless you have a specific requirement for running in Maximized mode, multi-cluster warehouses should be configured to run in Auto-scale mode, which enables Snowflake to automatically start and stop clusters as needed.
- E. Multi-cluster warehouses generally improve query performance, particularly for larger, more complex queries.
Answer: E
Explanation:
Explanation
Resizing a warehouse generally improves query performance, particularly for larger, more complex queries.
Multi-cluster warehouses are designed specifically for handling queuing and performance issues re-lated to large numbers of concurrent users and/or queries. In addition, multi-cluster warehouses can help automate this process if your number of users/queries tend to fluctuate.
Rest all are correct understanding.
NEW QUESTION # 46
Which Role that is dedicated to user and role management only?
- A. SECURITYADMIN
- B. ORGADMIN
- C. SYSADMIN
- D. PUBLIC
- E. USERADMIN
Answer: E
NEW QUESTION # 47
If external software i.e. TIBCO, exports Data fields enclosed in quotes but inserts a leading space before the opening quotation character for each field, How Snowflake handle it? [Select 2]
- A. Snowflake automatically handles leading spaces by trimming implicitly & removes the quotation marks enclosing each field.
- B. Snowflake reads the leading space rather than the opening quotation character as the beginning of the field and the quotation characters are interpreted as string data.
(Correct) - C. COPY command trims the leading space and removes the quotation marks enclosing each field
1.copy into SFtable
2.from @%SFtable
3.file_format = (type = csv trim_space=true field_optionally_enclosed_by = '0x22'); - D. field_optionally_enclosed_by option along with TRIM_IF function in COPY INTO statement can be used to handle this scenario successfully.
Answer: C
Explanation:
Explanation
If your external software exports fields enclosed in quotes but inserts a leading space before the opening quotation character for each field, Snowflake reads the leading space rather than the open-ing quotation character as the beginning of the field. The quotation characters are interpreted as string data.
Use the TRIM_SPACE file format option to remove undesirable spaces during the data load.
NEW QUESTION # 48
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: B
NEW QUESTION # 49
UDTFs also called a table function, returns zero, one, or multiple rows for each input row?
- A. NO
- B. YES
Answer: B
Explanation:
Explanation
UDFs may be scalar or tabular.
A scalar function returns one output row for each input row. The returned row consists of a single column/value.
A tabular function, also called a table function, returns zero, one, or multiple rows for each input row. A tabular UDF is defined by specifying a return clause that contains the TABLE keyword and specifies the names and data types of the columns in the table results. Tabular UDFs are often called UDTFs (user-defined table functions) or table UDFs.
NEW QUESTION # 50
When created, a stream logically takes an initial snapshot of every row in the source object and the contents of a stream change as DML statements execute on the source table.
A Data Engineer, Sophie Created a view that queries the table and returns the CURRENT_USER and CURRENT_TIMESTAMP values for the query transaction. A Stream has been created on views to capture CDC.
Tony, another user inserted the data e.g.
insert into <table> values (1),(2),(3);
Emily, another user also inserted the data e.g.
insert into <table> values (4),(5),(6);
What will happened when Different user queries the same stream after 1 hour?
- A. All the Six Records would be displayed with CURRENT_USER & CUR-RENT_TIMESTAMP while querying Streams.
- B. All the 6 records would be shown with METADATA$ACTION as 'INSERT' out of which 3 records would be displayed with username 'Tony' & rest 3 records would be displayed with username 'Emily'.
- C. All the Six records would be displayed with User 'Sohpie' Who is the owner of the View.
- D. User would be displayed with the one who queried during the session, but Recorded timestamp would be of past 1 hour i.e. actual records insertion time.
Answer: A
Explanation:
Explanation
When User queries the stream, the stream returns the username for the user. The stream also returns the current timestamp for the query transaction in each row, NOT the timestamp when each row was inserted.
NEW QUESTION # 51
A large table with 200 columns contains two years of historical data. When queried. the table is filtered on a single day Below is the Query Profile:
Using a size 2XL virtual warehouse, this query look over an hour to complete What will improve the query performance the MOST?
- A. Increase the number of clusters in the virtual warehouse
- B. Implement the search optimization service on the table
- C. Add a date column as a cluster key on the table
- D. increase the size of the virtual warehouse.
Answer: C
Explanation:
Explanation
Adding a date column as a cluster key on the table will improve the query performance by reducing the number of micro-partitions that need to be scanned. Since the table is filtered on a single day, clustering by date will make the query more selective and efficient.
NEW QUESTION # 52
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. Set the FORCE option to load all files, ignoring load metadata if it exists.
- B. 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.
- C. 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.
- D. To load files whose metadata has expired, set the LOAD_UNCERTAIN_FILES copy option to true.
Answer: D
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 # 53
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?
- A. 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. - B. Child tables can be recovered using Fail-Safe SQL commands.
- C. Data Engineer needs to first recover the Schema & then Child tables will automatically be recovered irrespective of Retention Inheritance.
- D. 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.
Answer: A
Explanation:
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.
NEW QUESTION # 54
When would a Data engineer use table with the flatten function instead of the lateral flatten combination?
- A. When TABLE with FLATTENrequires another source in the from clause to refer to
- B. WhenTABLE with FLATTENrequires no additional source m the from clause to refer to
- C. When table withFLATTENis acting like a sub-query executed for each returned row
- D. Whenthe LATERALFLATTENcombination requires no other source m the from clause to refer to
Answer: A
Explanation:
Explanation
The TABLE function with the FLATTEN function is used to flatten semi-structured data, such as JSON or XML, into a relational format. The TABLE function returns a table expression that can be used in the FROM clause of a query. The TABLE function with the FLATTEN function requires another source in the FROM clause to refer to, such as a table, view, or subquery that contains the semi-structured data. For example:
SELECT t.value:city::string AS city, f.value AS population FROM cities t, TABLE(FLATTEN(input => t.value:population)) f; In this example, the TABLE function with the FLATTEN function refers to the cities table in the FROM clause, which contains JSON data in a variant column named value. The FLATTEN function flattens the population array within each JSON object and returns a table expression with two columns: key and value.
The query then selects the city and population values from the table expression.
NEW QUESTION # 55
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 # 56
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: D
NEW QUESTION # 57
Streams record the differences between two offsets. If a row is added and then updated in the cur-rent offset, what will be the value of METADATA$ISUPDATE Columns in this scenario?
- A. FALSE
- B. UPDATE
- C. TRUE
- D. INSERT
Answer: A
Explanation:
Explanation
Stream Columns
A stream stores an offset for the source object and not any actual table columns or data. When que-ried, a stream accesses and returns the historic data in the same shape as the source object (i.e. the same column names and ordering) with the following additional columns:
METADATA$ACTION
Indicates the DML operation (INSERT, DELETE) recorded.
METADATA$ISUPDATE
Indicates whether the operation was part of an UPDATE statement. Updates to rows in the source object are represented as a pair of DELETE and INSERT records in the stream with a metadata column METADATA$ISUPDATE values set to TRUE.
METADATA$ROW_ID
Specifies the unique and immutable ID for the row, which can be used to track changes to specific rows over time.
Note that streams record the differences between two offsets. If a row is added and then updated in the current offset, the delta change is a new row. The METADATA$ISUPDATE row records a FALSE value.
NEW QUESTION # 58
As part of Table Designing, Data Engineer added a timestamp column that inserts the current timestamp as the default value as records are loaded into a table. The intent is to capture the time when eachrecord was loaded into the table; however, the timestamps are earlier than the LOAD_TIME column values returned by COPY_HISTORY view (Account Usage). What could be reason of this issue?
- A. LOAD_TIME column values returned by COPY_HISTORY view (Account Usage) gives the same time as returned by CURRENT_TIMESTAMP.
- B. The reason is, CURRENT_TIMESTAMP is evaluated when the load operation is com-piled in cloud services rather than when the record is inserted into the table (i.e. when the transaction for the load operation is committed).
- C. CURRENT_TIMESTAMP values might be different due to query gets executed in warehouse located in different region.
- D. It might be possible that Cloud Provider hosted on Snowflake belongs to region having server time zone lagging Cluster time zone of warehouse where queries get processed & committed.
Answer: B
Explanation:
Explanation
The reason timestamps are earlier than the LOAD_TIME column values which is returned by COPY_HISTORY view (Account Usage) is that CURRENT_TIMESTAMP is evaluated when the load operation is compiled in cloud services rather than when the record is inserted into the table (i.e. when the transaction for the load operation is committed).
NEW QUESTION # 59
1.+--------------------------------------------------------------+
2.| SYSTEM$CLUSTERING_INFORMATION('SF_DATA', '(COL1, COL3)') |
3.|--------------------------------------------------------------|
4.| { |
5.| "cluster_by_keys" : "(COL1, COL3)", |
6.| "total_partition_count" : 1156, |
7.| "total_constant_partition_count" : 0, |
8.| "average_overlaps" : 117.5484, |
9.| "average_depth" : 64.0701, |
10.| "partition_depth_histogram" : { |
11.| "00000" : 0, |
12.| "00001" : 0, |
13.| "00002" : 3, |
14.| "00003" : 3, |
15.| "00004" : 4, |
16.| "00005" : 6, |
17.| "00006" : 3, |
18.| "00007" : 5, |
19.| "00008" : 10, |
20.| "00009" : 5, |
21.| "00010" : 7, |
22.| "00011" : 6, |
23.| "00012" : 8, |
24.| "00013" : 8, |
25.| "00014" : 9, |
26.| "00015" : 8, |
27.| "00016" : 6, |
28.| "00032" : 98, |
29.| "00064" : 269, |
30.| "00128" : 698 |
31.| } |
32.| } |
33.+--------------------------------------------------------------+
The Above example indicates that the SF_DATA table is not well-clustered for which of following valid reasons?
- A. High average of overlap depth across micro-partitions.
- B. ALL of the above
- C. Most of the micro-partitions are grouped at the lower-end of the histogram, with the majority of micro-partitions having an overlap depth between 64 and 128.
- D. Zero (0) constant micro-partitions out of 1156 total micro-partitions.
- E. High average of overlapping micro-partitions.
Answer: B
NEW QUESTION # 60
Which are the Cloud Platforms that Support Calling an External Function?
- A. AWS only
- B. AWS,GCP,AZURE
- C. GCP
- D. AWS & AZURE
Answer: B
NEW QUESTION # 61
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 # 62
......
TorrentValid just published the Snowflake DEA-C01 exam dumps!: https://www.torrentvalid.com/DEA-C01-valid-braindumps-torrent.html
For your comfort, TorrentValid provides you the convenience of free SnowPro Advanced braindumps demo: https://drive.google.com/open?id=1vyqGvxiGzsQ5QTPcERQHqxiKkskmqx7T