[Feb-2024] Snowflake DEA-C01 Actual Questions and Braindumps
Pass DEA-C01 Exam with Updated DEA-C01 Exam Dumps PDF 2024
NEW QUESTION # 37
Select the correct usage statements with regards to SQL UDF?
- A. Scalar functions (UDFs) have a limit of 500 input arguments.
- B. When using a query expression in a SQL UDF, do not include a semicolon within the UDF body to terminate the query expression.
- C. The body of a UDF cannot contain DDL statements or any DML statement other than SELECT.
- D. All of above are correct.
- E. You can include only one query expression.
Answer: D
NEW QUESTION # 38
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. FALSE
- B. TRUE
Answer: B
NEW QUESTION # 39
Which use case would be BEST suited for the search optimization service?
- A. Business users who need fast response times using highly selective filters
- B. Data Scientists who seek specific JOIN statements with large volumes of data
- C. Data Engineers who create clustered tables with frequent reads against clustering keys
- D. Analysts who need to perform aggregates over high cardinality columns
Answer: A
Explanation:
Explanation
The use case that would be best suited for the search optimization service is business users who need fast response times using highly selective filters. The search optimization service is a feature that enables faster queries on tables with high cardinality columns by creating inverted indexes on those columns. High cardinality columns are columns that have a large number of distinct values, such as customer IDs, product SKUs, or email addresses. Queries that use highly selective filters on high cardinality columns can benefit from the search optimization service because they can quickly locate the relevant rows without scanning the entire table. The other options are not best suited for the search optimization service. Option A is incorrect because analysts who need to perform aggregates over high cardinality columns will not benefit from the search optimization service, as they will still need to scan all the rows that match the filter criteria. Option C is incorrect because data scientists who seek specific JOIN statements with large volumes of data will not benefit from the search optimization service, as they will still need to perform join operations that may involve shuffling or sorting data across nodes. Option D is incorrect because data engineers who create clustered tables with frequent reads against clustering keys will not benefit from the search optimization service, as they already have an efficient way to organize and access data based on clustering keys.
NEW QUESTION # 40
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 require the warehouse to be available with no delay or lag time.
- C. You have a heavy, steady workload for the warehouse.
- 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 # 41
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. The return message did not produce the same number of rows that it received
- B. External functions do not support multiple rows
- C. The JSON returned by the remote service is not constructed correctly
- D. Nested arrays are not supported in the JSON response
Answer: A
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 # 42
Within a Snowflake account permissions have been defined with custom roles and role hierarchies.
To set up column-level masking using a role in the hierarchy of the current user, what command would be used?
- A. IKVOKER_ROLE
- B. IS_RCLE_IN_SESSION
- C. IS_GRANTED_TO_INVOKER_ROLE
- D. CORRECT_ROLE
Answer: B
Explanation:
Explanation
The IS_ROLE_IN_SESSION function is used to set up column-level masking using a role in the hierarchy of the current user. Column-level masking is a feature in Snowflake that allows users to apply dynamic data masking policies to specific columns based on the roles of the users who access them. The IS_ROLE_IN_SESSION function takes a role name as an argument and returns true if the role is in the current user's session, or false otherwise. The function can be used in a masking policy expression to determine whether to mask or unmask a column value based on the role of the user. For example:
CREATE OR REPLACE MASKING POLICY email_mask AS (val string) RETURNS string -> CASE WHEN IS_ROLE_IN_SESSION('HR') THEN val ELSE REGEXP_REPLACE(val, '(.).(.@.)', '\1****\2') END; In this example, the IS_ROLE_IN_SESSION function is used to create a masking policy for an email column.
The masking policy returns the original email value if the user has the HR role in their session, or returns a masked email value with asterisks if not.
NEW QUESTION # 43
Ron, Snowflake Developer needs to capture change data (insert only) on the source views, for that he follows the below steps:
Enable change tracking on the source views & its underlying tables.
Inserted the data via Scripts scheduled with the help of Tasks.
then simply run the below Select statements.
1.select *
2.from test_table
3.changes(information => append_only)
4.at(timestamp => (select current_timestamp()));
Select the Correct Query Execution Output option below:
- A. Select statement complied but gives erroneous results.
- B. No Error reported, select command gives Changed records with Metadata columns as change tracking enabled on the Source views & its underlying tables.
- C. Select query will fail with error: 'SQL compilation error-Incorrect Keyword "Chang-es()" found'
- D. Developer missed to create stream on the source table which can further query to cap-ture DML records.
Answer: B
Explanation:
Explanation
As an alternative to streams, Snowflake supports querying change tracking metadata for tables or views using the CHANGES clause for SELECT statements. The CHANGES clause enables query-ing change tracking metadata between two points in time without having to create a stream with an explicit transactional offset.
To Know more about Snowflake CHANGES clause, please refer the mentioned link:
https://docs.snowflake.com/en/sql-reference/constructs/changes
NEW QUESTION # 44
The following code is executed ina Snowflake environment with the default settings:
What will be the result of the select statement?
- A. 0
- B. John
- C. 1John
- D. SQL compilation error object CUSTOMER' does not exist or is not authorized.
Answer: A
NEW QUESTION # 45
Which methods will trigger an action that will evaluate a DataFrame? (Select TWO)
- A. DataFrame.random_split ( )
- B. DateFrame.select ()
- C. DataFrame.col ( )
- D. DataFrame.collect ()
- E. DataFrame.show ()
Answer: D,E
Explanation:
Explanation
The methods that will trigger an action that will evaluate a DataFrame are DataFrame.collect() and DataFrame.show(). These methods will force the execution of any pending transformations on the DataFrame and return or display the results. The other options are not methods that will evaluate a DataFrame. Option A, DataFrame.random_split(), is a method that will split a DataFrame into two or more DataFrames based on random weights. Option C, DataFrame.select(), is a method that will project a set of expressions on a DataFrame and return a new DataFrame. Option D, DataFrame.col(), is a method that will return a Column object based on a column name in a DataFrame.
NEW QUESTION # 46
Mark the correct statements about Cache?
- A. For persisted query results of all sizes, the cache expires after 24 hours.
- B. 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.
- C. The size of the warehouse cache is determined by the compute resources in the ware-house.
- D. Materialized views are more flexible than, but typically slower than, cached results.
- E. Warehouse cache is dropped when the warehouse is suspended, which may result in slower initial performance for some queries after the warehouse is resumed.
Answer: A,B,C,D,E
Explanation:
Explanation
How Does Warehouse Caching Impact Queries?
Each warehouse, when running, maintains a cache of table data accessed as queries are processed by the warehouse. This enables improved performance for subsequent queries if they are able to read from the cache instead of from the table(s) in the query. The size of the cache is determined by the compute resources in the warehouse (i.e. the larger the warehouse and, therefore, more compute re-sources in the warehouse), the larger the cache.
This cache is dropped when the warehouse is suspended, which may result in slower initial perfor-mance for some queries after the warehouse is resumed. As the resumed warehouse runs and pro-cesses more queries, the cache is rebuilt, and queries that are able to take advantage of the cache will experience improved performance.
Keep this in mind when deciding whether to suspend a warehouse or leave it running. In other words, consider the trade-off between saving credits by suspending a warehouse versus maintaining the cache of data from previous queries to help with performance.
Using Persisted Query Results
When a query is executed, the result is persisted (i.e. cached) for a period of time. At the end of the time period, the result is purged from the system.
Snowflake uses persisted query results to avoid re-generating results when nothing has changed (i.e. "retrieval optimization"). In addition, you can use persisted query results to post-process the results (e.g. layering a new query on top of the results already calculated).
For persisted query results of all sizes, the cache expires after 24 hours.
Both materialized views and cached query results provide query performance benefits:
Materialized views are more flexible than, but typically slower than, cached results.
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.
Regular views do not cache data, and therefore cannot improve performance by caching.
NEW QUESTION # 47
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 # 48
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 # 49
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 # 50
To support Time Travel, Which of the following SQL extensions/parameters/commands have been implemented?
- A. STATEMENT_ID (identifier for statement, e.g. query ID)
- B. UNDROP command for tables, schemas, and databases.
- C. ONSET (time difference in seconds from the present time)
- D. STATEMENT (identifier for statement, e.g. query ID)
- E. OFFSET (time difference in seconds from the present time)
- F. AT | BEFORE clause which can be specified in the CREATE ... CLONE commands.
Answer: B,D,E,F
NEW QUESTION # 51
If you need to connect to Snowflake using a BI tool or technology, which of the following BI tools and technologies are known to provide native connectivity to Snowflake?
- A. SELECT STAR
- B. SISENSE
- C. PROTEGRITY
- D. ALATION
Answer: B
Explanation:
Explanation
SISENSE is BI tools and technologies which is known to provide native connectivity to Snowflake, Rest of the options given are security & governance tools supported by SnowFlake.
Business intelligence (BI) tools enable analyzing, discovering, and reporting on data to help execu-tives and managers make more informed business decisions. A key component of any BI tool is the ability to deliver data visualization through dashboards, charts, and other graphical output.
For More details around supported BI Tools in Snowflake Ecosystem, do refer the link below:
https://docs.snowflake.com/en/user-guide/ecosystem-bi
NEW QUESTION # 52
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 # 53
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 Designer
- B. Performance Metadata table
- C. Query Optimizer
- D. Query Profile
Answer: D
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 # 54
Assuming that the session parameter USE_CACHED_RESULT is set to false, what are characteristics of Snowflake virtual warehouses in terms of the use of Snowpark?
- A. Creating a DataFrame from a staged file with the read () method will start a virtual warehouse
- B. Transforming a DataFrame with methods like replace () will start a virtual warehouse -
- C. Calling a Snowpark stored procedure to query the database with session, call () will start a virtual warehouse
- D. Creating a DataFrame from a table will start a virtual warehouse
Answer: D
Explanation:
Explanation
Creating a DataFrame from a table will start a virtual warehouse because it requires reading data from Snowflake. The other options will not start a virtual warehouse because they either operate on local data or use an existing session to query Snowflake.
NEW QUESTION # 55
The following is returned fromSYSTEMCLUSTERING_INFORMATION () for a tablenamed orders with adate column named O_ORDERDATE:
What does the total_constant_partition_count value indicate about this table?
- A. The table is not clustered well on O_ORDERDATE, as there are 493 micro-partitions where the range of values in that column overlap with every other micro partition in the table.
- B. The data inO_ORDERDATEdoes not change very often as there are 493 micro-partitionscontaining rows where that column has not been modified since the row was created
- C. The data inO_ORDERDATEhas a very low cardinality as there are 493 micro-partitions where there is only a single distinct value in that column for all rows in the micro-partition
- D. The table is clustered very well on_ORDERDATE, as there are 493 micro-partitions that could not be significantly improved by reclustering
Answer: A
Explanation:
Explanation
The total_constant_partition_count value indicates the number of micro-partitions where the clustering key column has a constant value across all rows in the micro-partition. However, this does not necessarily mean that the table is clustered well on that column, as there could be other micro-partitions where the range of values in that column overlap with each other. This is the case for the orders table, as the clustering depth is 1, which means that every micro-partition overlaps with every other micro-partition on O_ORDERDATE. This indicates that the table is not clustered well on O_ORDERDATE and could benefit from reclustering.
NEW QUESTION # 56
......
Latest DEA-C01 Pass Guaranteed Exam Dumps with Accurate & Updated Questions: https://torrentpdf.practicedump.com/DEA-C01-exam-questions.html