Latest Verified & Correct Snowflake DEA-C01 Questions & Answers Daily Updated
100% Pass Guaranteed Download SnowPro Advanced Exam PDF Q&A
NEW QUESTION # 18
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. 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.
- B. CURRENT_TIMESTAMP values might be different due to query gets executed in warehouse located in different region.
- C. 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).
- D. LOAD_TIME column values returned by COPY_HISTORY view (Account Usage) gives the same time as returned by CURRENT_TIMESTAMP.
Answer: C
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 # 19
Which functions will compute a 'fingerprint' over an entire table, query result, or window to quickly detect changes to table contents or query results? (Select TWO).
- A. HASH_AGG_COMPARE (*)
- B. HASH COMPARE(*)
- C. HASH (*)
- D. HASH_AGG(*)
- E. HASH_AGG(<expr>, <expr>)
Answer: D,E
Explanation:
Explanation
The functions that will compute a 'fingerprint' over an entire table, query result, or window to quickly detect changes to table contents or query results are:
HASH_AGG(*): This function computes a hash value over all columns and rows in a table, query result, or window. The function returns a single value for each group defined by a GROUP BY clause, or a single value for the entire input if no GROUP BY clause is specified.
HASH_AGG(<expr>, <expr>): This function computes a hash value over two expressions in a table, query result, or window. The function returns a single value for each group defined by a GROUP BY clause, or a single value for the entire input if no GROUP BY clause is specified. The other functions are not correct because:
HASH (*): This function computes a hash value over all columns in a single row. The function returns one value per row, not one value per table, query result, or window.
HASH_AGG_COMPARE (): This function compares two hash values computed by HASH_AGG() over two tables or query results and returns true if they are equal or false if they are different. The function does not compute a hash value itself, but rather compares two existing hash values.
HASH COMPARE(): This function compares two hash values computed by HASH() over two rows and returns true if they are equal or false if they are different. The function does not compute a hash value itself, but rather compares two existing hash values.
NEW QUESTION # 20
Which two Account usage views can be used for auditing Dynamic data masking purpose?
- A. POLICY_REFERENCES
- B. MASKING POLICIES
- C. DYNAMIC MASKING POLICIES
- D. DYNAMIC POLICY_REFERENCES
Answer: A,B
NEW QUESTION # 21
Can Masking policies be applied to virtual columns?
- A. FALSE
- B. TRUE
Answer: A
NEW QUESTION # 22
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$FILENAME
- B. METADATA$COLUMNNAME
- C. METADATA$ROW_NUMBER
- D. METADATA$FILEPATH
Answer: A
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 # 23
Elon, a Data Engineer, needs to Split Semi-structured Elements from the Source files and load them as an array into Separate Columns.
Source File:
1.+----------------------------------------------------------------------+
2.| $1 |
3.|----------------------------------------------------------------------|
4.| {"mac_address": {"host1": "197.128.1.1","host2": "197.168.0.1"}}, |
5.| {"mac_address": {"host1": "197.168.2.1","host2": "197.168.3.1"}} |
6.+----------------------------------------------------------------------+ Output: Splitting the Machine Address as below.
1.COL1 | COL2 |
2.|----------+----------|
3.| [ | [ |
4.| "197", | "197", |
5.| "128", | "168", |
6.| "1", | "0", |
7.| "1" | "1" |
8.| ] | ] |
9.| [ | [ |
10.| "197", | "197", |
11.| "168", | "168", |
12.| "2", | "3", |
13.| "1" | "1" |
14.| ] | ]
Which SnowFlake Function can Elon use to transform this semi structured data in the output for-mat?
- A. GROUP_BY_CONNECT
- B. NEST
- C. SPLIT
- D. CONVERT_TO_ARRAY
Answer: C
NEW QUESTION # 24
If using a JavaScript UDF in a masking policy, Data Engineer needs to ensure the data type of the column, UDF, and masking policy match irrespective of case-sensitivity?
- A. FALSE
- B. TRUE
Answer: A
Explanation:
Explanation
Please note JavaScript is case sensitive but if we are using a JavaScript UDF in a masking policy, ensure the data type of the column, UDF, and masking policy match.
NEW QUESTION # 25
The following code is executed ina Snowflake environment with the default settings:
What will be the result of the select statement?
- A. 1John
- B. 0
- C. John
- D. SQL compilation error object CUSTOMER' does not exist or is not authorized.
Answer: B
NEW QUESTION # 26
Snowflake computes and adds partitions based on the defined partition column expressions when an external table metadata is refreshed.
What are the Correct Statements to configure Partition metadata refresh in case of External Tables?
- A. Metadata refresh is not required as its Managed implicitly by Snowflake.
- B. Partitions of External tables is managed by External Stage Cloud provider.
- C. The object owner can configure the metadata to refresh automatically when new or updated data files are available in the external stage.
- D. By default, the metadata is refreshed automatically when the object is created.
- E. There is nothing like adding partitions on External tables.
Answer: C,D
Explanation:
Explanation
Snowflake strongly recommend partitioning your external tables, which requires that your underly-ing data is organized using logical paths that include date, time, country, or similar dimensions in the path.
Partitioning divides your external table data into multiple parts using partition columns.
An external table definition can include multiple partition columns, which impose a multi-dimensional structure on the external data.
Partitions are stored in the external table metadata.
Benefits of partitioning include improved query performance.
Because the external data is partitioned into separate slices/parts, query response time is faster when processing a small part of the data instead of scanning the entire data set.
Based on your individual use cases, you can either:
Add new partitions automatically by refreshing an external table that defines an expression for each partition column.
Add new partitions manually.
Partition columns are defined when an external table is created, using the CREATE EXTERNAL TABLE ...
PARTITION BY syntax.
After an external table is created, the method by which partitions are added cannot be changed.
Partitions Added Automatically
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 partition column.
The CREATE EXTERNAL TABLE syntax for adding partitions automatically based on expres-sions is as follows:
CREATE EXTERNAL TABLE
<table_name>
( <part_col_name> <col_type> AS <part_expr> )
[ , ... ]
[ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
Snowflake computes and adds partitions based on the defined partition column expressions when an external table metadata is refreshed.
By default, the metadata is refreshed automatically when the object is created.
In addition, the object owner can configure the metadata to refresh automatically when new or up-dated data files are available in the external stage.
The owner can alternatively refresh the metadata manually by executing the ALTER EXTERNAL TABLE ...
REFRESH command.
The metadata for an external table can be refreshed automatically using the event notification ser-vice for your cloud storage service.
NEW QUESTION # 27
John, Data Engineer, do have technical requirements to refresh the External tables Metadata period-ically or in auto mode, which approach John can take to meet this technical specification?
- A. He can create a task that executes an ALTER EXTERNAL TABLE ... REFRESH statement every 5 minutes.
- B. Snowflake implicitly take care this Infrastructure needs, as underlying warehouse layer internally manage the refresh. No action needed from John.
- C. External table cannot be scheduled via Snowflake Tasks, 3rd party tools/scripts needs to be used provided by External cloud storage provider.
- D. John can use AUTO_REFRESH parameter if the underlying External Cloud host sup-ports this for External tables.
Answer: A,D
Explanation:
Explanation
Both Option A & B are correct.
For Refreshing External Table Metadata on a Auto Mode, John can use the AUTO_REFRESH pa-rameter properties of External table.When an external table is created, the AUTO_REFRESH pa-rameter is set to TRUE by default.
When an external table is created, the AUTO_REFRESH parameter is set to TRUE by default.
Snowflake recommend that you accept this default value for external tables that reference data files in either Amazon S3 or Microsoft Azure stages.
However, the automatic refresh option is not available currently for external tables that reference Google Cloud Storage stages.
For these external tables, manually refreshing the metadata on a schedule can be useful.
The following example refreshes the metadata for an external table named snowdb.snowschema.snow_ext_table (using ALTER EXTERNAL TABLE ... REFRESH) on a schedule.
-- Create a task that executes an ALTER EXTERNAL TABLE ... REFRESH statement every 5 minutes.
1.CREATE TASK snow_ext_table_refresh_task
2.WAREHOUSE=mywh
3.SCHEDULE='5 minutes'
4.AS
5.ALTER EXTERNAL TABLE snowmydb.snowmyschema.snow_ext_table REFRESH;
NEW QUESTION # 28
What is the purpose of the BUILD_FILE_URL function in Snowflake?
- A. It generates a permanent URL for accessing files in a stage.
- B. It generates an encrypted URL foe accessing a file in a stage.
- C. It generates a staged URL for accessing a file in a stage.
- D. It generates a temporary URL for accessing a file in a stage.
Answer: C
Explanation:
Explanation
The BUILD_FILE_URL function in Snowflake generates a temporary URL for accessing a file in a stage. The function takes two arguments: the stage name and the file path. The generated URL is valid for 24 hours and can be used to download or view the file contents. The other options are incorrect because they do not describe the purpose of the BUILD_FILE_URL function.
NEW QUESTION # 29
At what isolation level are Snowflake streams?
- A. Read committed
- B. Snapshot
- C. Read uncommitted
- 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 # 30
A Data Engineer is writing a Python script using the Snowflake Connector for Python. The Engineer will use the snowflake. Connector.connect function to connect to Snowflake The requirementsare:
*Raise an exception if the specified database schema or warehouse does not exist
*improve download performance
Whichparameters of the connect function should be used? (Select TWO).
- A. authenticator
- B. arrow_nunber_to_decimal
- C. client_prefetch_threads
- D. validate_default_parameters
- E. client_session_keep_alivs
Answer: C,D
Explanation:
Explanation
The parameters of the connect function that should be used are client_prefetch_threads and validate_default_parameters. The client_prefetch_threads parameter controls the number of threads used to download query results from Snowflake. Increasing this parameter can improve download performance by parallelizing the download process. The validate_default_parameters parameter controls whether an exception should be raised if the specified database, schema, or warehouse does not exist or is not authorized. Setting this parameter to True can help catch errors early and avoid unexpected results.
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. Node.js
- B. SnowFlake Connector for Spark
- C. Go Driver
- D. SnowCD
- E. SnowSQL
Answer: A,B,C,E
NEW QUESTION # 32
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. FALSE
- B. TRUE
Answer: B
NEW QUESTION # 33
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 # 34
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 needs to query history views to get the queryID as best practices.
- B. Using python connector, snowflake does not support queryID retrieval for both syn-chronous & asynchronous query.
- C. 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) - D. 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.
Answer: C
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 # 35
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. ALATION
- D. PROTEGRITY
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 # 36
Which use case would be BEST suited for the search optimization service?
- A. Data Engineers who create clustered tables with frequent reads against clustering keys
- B. Analysts who need to perform aggregates over high cardinality columns
- C. Business users who need fast response times using highly selective filters
- D. Data Scientists who seek specific JOIN statements with large volumes of data
Answer: C
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 # 37
Which of the following statements is/are incorrect regarding Fail-safe data recovery?
- A. Long-lived tables, such as fact tables, should always be defined as permanent to ensure they are fully protected by Fail-safe.
- B. Short-lived tables (i.e. <1 day), such as ETL work tables, can be defined as transient to eliminate Fail-safe costs.
- C. If downtime and the time required to reload lost data are factors, permanent tables, even with their added Fail-safe costs, may offer a better overall solution than transient tables.
- D. Data stored in temporary tables is not recoverable after the table is dropped as they do not have fail-safe.
- E. Historical data in transient tables can be recovered by Snowflake due to Operation fail-ure after the Time Travel retention period ends using Fail-safe.
Answer: E
NEW QUESTION # 38
What is a characteristic of the use of binding variables in JavaScript stored procedures in Snowflake?
- A. Only JavaScript variables of type number, string and sf Date can be bound
- B. Users are restricted from binding JavaScript variables because they create SQL injection attack vulnerabilities
- C. All types of JavaScript variables can be bound
- D. All Snowflake first-class objects can be bound
Answer: A
Explanation:
Explanation
A characteristic of the use of binding variables in JavaScript stored procedures in Snowflake is that only JavaScript variables of type number, string and sf Date can be bound. Binding variables are a way to pass values from JavaScript variables to SQL statements within a stored procedure. Binding variables can improve the security and performance of the stored procedure by preventing SQL injection attacks and reducing the parsing overhead. However, not all types of JavaScript variables can be bound. Only the primitive types number and string, and the Snowflake-specific type sf Date, can be bound. The other options are incorrect because they do not describe a characteristic of the use of binding variables in JavaScript stored procedures in Snowflake. Option A is incorrect because authenticator is not a type of JavaScript variable, but a parameter of the snowflake.connector.connect function. Option B is incorrect because arrow_number_to_decimal is not a type of JavaScript variable, but a parameter of the snowflake.connector.connect function. Option D is incorrect because users are not restricted from binding JavaScript variables, but encouraged to do so.
NEW QUESTION # 39
Michael, a Data Engineer Running a Data query to achieve Union of Data sets coming from Multi-ple data sources, later he figured out that Data processing query is taking more time than expected. He started analyzing the Query performance using query profile interface. He discovered & realized that he used UNION when the UNION ALL semantics was sufficient.
Which Extra Data Processing Operator Michael figured out while doing query profile analysis in this case which helps him to identify this performance bottlenecks?
- A. Aggregate
- B. Join
- C. Flatten
- D. Filter
- E. UNION ALL
Answer: A
Explanation:
Explanation
In SQL, it is possible to combine two sets of data with either UNION or UNION ALL constructs. The difference between them is that UNION ALL simply concatenates inputs, while UNION does the same, but also performs duplicate elimination.
A common mistake is to use UNION when the UNION ALL semantics are sufficient. These que-ries show in Query Profile as a UnionAll operator with an extra Aggregate operator on top (which performs duplicate elimination).
To Know more about Data Processing Operators, please do refer:
https://docs.snowflake.com/en/user-guide/ui-query-profile#operator-types
NEW QUESTION # 40
......
DEA-C01 PDF Dumps Are Helpful To produce Your Dreams Correct QA's: https://pass4sure.testpdf.com/DEA-C01-practice-test.html
