[Jul 21, 2024] Genuine DEA-C01 Exam Dumps New 2024 Snowflake Pratice Exam
New 2024 Realistic DEA-C01 Dumps Test Engine Exam Questions in here
NEW QUESTION # 29
A SQL UDF evaluates an arbitrary SQL expression and returns the result(s) of the expression. Which value type it can returns?
- A. Single Value
- B. Regex
- C. A Set of Rows
- D. Scaler or Tabular depend on input SQL expression
Answer: D
NEW QUESTION # 30
As a Data Engineer, you have requirement to query most recent data from the Large Dataset that reside in the external cloud storage, how would you design your data pipelines keeping in mind fastest time to delivery?
- A. Direct Querying External tables on top of existing data stored in external cloud storage for analysis without first loading it into Snowflake.
- B. Snowpipe can be leveraged with streams to load data in micro batch fashion with CDC streams that capture most recent data only.
- C. External tables with Materialized views can be created in Snowflake.
- D. Data pipelines would be created to first load data into internal stages & then into Per-manent table with SCD Type 2 transformation.
- E. Unload data into SnowFlake Internal data storage using PUT command.
Answer: C
Explanation:
Explanation
In a typical table, the data is stored in the database; however, in an external table, the data is stored in files in an external stage. External tables store file-level metadata about the data files, such as the filename, a version identifier and related properties. This enables querying data stored in files in an external stage as if it were inside a database. External tables can access data stored in any format supported by COPY INTO <table> statements.
External tables are read-only, therefore no DML operations can be performed on them; however, external tables can be used for query and join operations. Views can be created against external ta-bles.
Querying data stored external to the database is likely to be slower than querying native database tables; however, materialized views based on external tables can improve query performance.
Creating External tables enable user for querying existing data stored in external cloud storage for analysis without first loading it into Snowflake. The source of truth for the data remains in the ex-ternal cloud storage.
Data sets materialized in Snowflake via materialized views are read-only.
This solution is especially beneficial to accounts that have a large amount of data stored in external cloud storage and only want to query a portion of the data; for example, the most recent data. Users can create materialized views on subsets of this data for improved query performance.
NEW QUESTION # 31
Mark the Correct Statements:
Statement 1. Enable failover for a primary database to one or more accounts in your organization using an ALTER DATABASE ... ENABLE FAILOVER TO ACCOUNTS statement.
Statement 2. Enabling failover for a primary database can be done by Data Engineer either before or after a replica of the primary database has been created in a specified account.
- A. Both are Correct.
- B. Statement 2
- C. Statement 1
- D. Both are False.
Answer: A
NEW QUESTION # 32
Robert, A Data Engineer, found that Pipe become stale as it was paused for longer than the limited retention period for event messages received for the pipe (14 days by default) & also the previous pipe owner transfers the ownership of this pipe to Robert role while the pipe was paused. How Robert in this case, Resume this stale pipe?
- A. PIPE needs to recreate in this scenario, as pipe already past 14 days of period & stale.
- B. select sys-tem$pipe_force_resume('mydb.myschema.stalepipe','staleness_check_override, ownership_transfer_check_override');
- C. He can apply System function SYSTEM$PIPE_STALE_RESUME with ALTER PIPE statement.
- D. ALTER PIPES ... RESUME statement will resume the pipe.
- E. Robert can use SYSTEM$PIPE_FORCE_RESUME function to resume this stale pipe.
Answer: B
Explanation:
Explanation
When a pipe is paused, event messages received for the pipe enter a limited retention period. The period is 14 days by default. If a pipe is paused for longer than 14 days, it is considered stale.
To resume a stale pipe, a qualified role must call the SYSTEM$PIPE_FORCE_RESUME function and input the STALENESS_CHECK_OVERRIDE argument. This argument indicates an under-standing that the role is resuming a stale pipe.
For example, resume the stale stalepipe1 pipe in the mydb.myschema database and schema:
SELECT SYS-TEM$PIPE_FORCE_RESUME('mydb.myschema.stalepipe1','staleness_check_override'); While the stale pipe was paused, if ownership of the pipe was transferred to another role, then re-suming the pipe requires the additional OWNERSHIP_TRANSFER_CHECK_OVERRIDE argu-ment. For example, resume the stale stalepipe2 pipe in the mydb.myschema database and schema, which transferred to a new role:
SELECT SYS-TEM$PIPE_FORCE_RESUME('mydb.myschema.stalepipe1','staleness_check_override, own-ership_transfer_check_override');
NEW QUESTION # 33
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. Query Optimizer
- C. Performance Metadata table
- 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 # 34
Which methods can be used to create a DataFrame object in Snowpark? (Select THREE)
- A. session.jdbc_connection()
- B. DataFraas.writeO
- C. session.builder()
- D. session.sql()
- E. session,table()
- F. session.read.json{)
Answer: D,E,F
Explanation:
Explanation
The methods that can be used to create a DataFrame object in Snowpark are session.read.json(), session.table(), and session.sql(). These methods can create a DataFrame from different sources, such as JSON files, Snowflake tables, or SQL queries. The other options are not methods that can create a DataFrame object in Snowpark. Option A, session.jdbc_connection(), is a method that can create a JDBC connection object to connect to a database. Option D, DataFrame.write(), is a method that can write a DataFrame to a destination, such as a file or a table. Option E, session.builder(), is a method that can create a SessionBuilder object to configure and build a Snowpark session.
NEW QUESTION # 35
Which Scenario Data engineer decide Materialized views are not useful. Select All that apply.
- A. The view's base table change frequently.
- B. Query results contain a small number of rows and/or columns relative to the base table (the table on which the view is defined).
- C. The query is on an external table (i.e. data sets stored in files in an external stage), which might have slower performance compared to querying native database tables.
- D. Query results contain results that require significant processing.
Answer: A
Explanation:
Explanation
A materialized view is a pre-computed data set derived from a query specification (the SELECT in the view definition) and stored for later use. Because the data is pre-computed, querying a material-ized view is faster than executing a query against the base table of the view. This performance dif-ferencecan be significant when a query is run frequently or is sufficiently complex. As a result, ma-terialized views can speed up expensive aggregation, projection, and selection operations, especially those that run frequently and that run on large data sets.
Materialized views require Enterprise Edition.
Materialized views are designed to improve query performance for workloads composed of com-mon, repeated query patterns. However, materializing intermediate results incurs additional costs. As such, before creating any materialized views, you should consider whether the costs are offset by the savings from re-using these results frequently enough.
Materialized views are particularly useful when:
Query results contain a small number of rows and/or columns relative to the base table (the table on which the view is defined).
Query results contain results that require significant processing, including:
1. Analysis of semi-structured data.
2. Aggregates that take a long time to calculate.
The query is on an external table (i.e. data sets stored in files in an external stage), which might have slower performance compared to querying native database tables.
The view's base table does not change frequently.
NEW QUESTION # 36
Database XYZ has the data_retention_time_in_days parameter set to 7 days and table xyz.public.ABC has the data_retention_time_in_daysset to 10 days.
A Developer accidentally dropped the database containing this single table 8 days ago and just discovered the mistake.
How can the table be recovered?
- A. create table abc_restore clone xyz.public.abc at (offset => -300G*24*3);
- B. Create a Snowflake Support case lo restore the database and tab e from "a i-safe
- C. create -able abc_restore as select * from xyz.public.abc at {offset => -60*60*24*8};
- D. undrop database xyz;
Answer: D
Explanation:
Explanation
The table can be recovered by using the undrop database xyz; command. This command will restore the database that was dropped within the last 14 days, along with all its schemas and tables, including the customer table. The data_retention_time_in_days parameter does not affect this command, as it only applies to time travel queries that reference historical data versions of tables or databases. The other options are not valid ways to recover the table. Option B is incorrect because creating a table as select * from xyz.public.ABC at
{offset => -6060248} will not work, as this query will try to access a historical data version of the ABC table that does not exist anymore after dropping the database. Option C is incorrect because creating a table clone xyz.public.ABC at {offset => -360024*3} will not work, as this query will try to clone a historical data version of the ABC table that does not exist anymore after dropping the database. Option D is incorrect because creating a Snowflake Support case to restore the database and table from fail-safe will not work, as fail-safe is only available for disaster recovery scenarios and cannot be accessed by customers.
NEW QUESTION # 37
The smaller the average depth, the better clustered the table is with regards to the specified column?
- A. TRUE
- B. FALSE
Answer: A
NEW QUESTION # 38
Mark the correct Statements with respect to Secure views & its creation in the SnowFlake Account?
- A. To convert an existing view to a secure view and back to a regular view, set/unset the SECURE keyword in the ALTER VIEW or ALTER MATERIALIZED VIEW com-mand.
- B. Secure views should not be used for views that are defined solely for query conven-ience, such as views created to simplify queries for which users do not need to under-stand the underlying data representation.
- C. The internals of a secure view are not exposed in Query Profile (in the web interface). This is the case even for the owner of the secure view, because non-owners might have access to an owner's Query Profile.
- D. For a secure view, internal optimizations can indirectly expose data & the view defini-tion is visible to other users.
- E. For non-materialized views, the IS_SECURE column in the Information Schema and Account Usage views identifies whether a view is secure.
Answer: A,B,C,E
Explanation:
Explanation
Why Should I Use Secure Views?
For a non-secure view, internal optimizations can indirectly expose data.
Some of the internal optimizations for views require access to the underlying data in the base tables for the view. This access might allow data that is hidden from users of the view to be exposed through user code, such as user-defined functions, or other programmatic methods. Secure views do not utilize these optimizations, ensuring that users have no access to the underlying data.
For a non-secure view, the view definition is visible to other users.
By default, the query expression used to create a standard view, also known as the view definition or text, is visible to users in various commands and interfaces.
For security or privacy reasons, you might not wish to expose the underlying tables or internal struc-tural details for a view. With secure views, the view definition and details are visible only to author-ized users (i.e.
users who are granted the role that owns the view).
When Should I Use a Secure View?
Views should be defined as secure when they are specifically designated for data privacy (i.e. to limit access to sensitive data that should not be exposed to all users of the underlying table(s)).
Secure views should not be used for views that are defined solely for query convenience, such as views created to simplify queries for which users do not need to understand the underlying data representation. Secure views can execute more slowly than non-secure views.
Secure views are defined using the SECURE keyword with the standard DDL for views:
To create a secure view, specify the SECURE keyword in the CREATE VIEW or CREATE MA-TERIALIZED VIEW command.
To convert an existing view to a secure view and back to a regular view, set/unset the SECURE keyword in the ALTER VIEW or ALTER MATERIALIZED VIEW command.
The definition of a secure view is only exposed to authorized users (i.e. users who have been grant-ed the role that owns the view). If an unauthorized user uses any of the following commands or in-terfaces, the view definition is not displayed:
SHOW VIEWS and SHOW MATERIALIZED VIEWS commands.
GET_DDL utility function.
VIEWS Information Schema view.
VIEWS Account Usage view.
For non-materialized views, the IS_SECURE column in the Information Schema and Account Us-age views identifies whether a view is secure.
The internals of a secure view are not exposed in Query Profile (in the web interface). This is the case even for the owner of the secure view, because non-owners might have access to an owner's Query Profile.
NEW QUESTION # 39
Let us say you have List of 50 Source files, which needs to be loaded into Snowflake internal stage. All these Source system files are already Brotli-compressed files. Which statement is correct with respect to Compression of Staged Files?
- A. Auto-detection is not yet supported for Brotli-compressed files; when staging or loading Brotli-compressed files, you must explicitly specify the compression method that was used.
- B. When staging 50 compressed files in a Snowflake stage, the files are automatically com-pressed using gzip.
- C. Snowflake automatically detect Brotli Compression, will skip further compression of all 50 files.
- D. Even though Source files are already compressed, Snowflake do apply default gzip2 Compression to optimize the storage cost.
Answer: A
Explanation:
Explanation
Auto-detection is not yet supported for Brotli-compressed files; when staging or loading Brotli-compressed files, you must explicitly specify the compression method that was used.
To Know more about Compression of Staged Files, please refer the link:
https://docs.snowflake.com/en/user-guide/intro-summary-loading.html#compression-of-staged-files
NEW QUESTION # 40
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. Flatten
- B. Filter
- C. Join
- D. UNION ALL
- E. Aggregate
Answer: E
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 # 41
Which methods will trigger an action that will evaluate a DataFrame? (Select TWO)
- A. DataFrame.col ( )
- B. DateFrame.select ()
- C. DataFrame.show ()
- D. DataFrame.collect ()
- E. DataFrame.random_split ( )
Answer: C,D
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 # 42
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 can query only a single table & Joins, including self-joins, are not supported.
- B. A materialized view cannot include HAVING clauses OR ORDER BY clause.
- C. A materialized views cannot be created on Shared Data.
- D. A materialized views does not support clustering.
- E. Time Travel is not currently supported on materialized views.
- F. You cannot directly clone a materialized view by using the CREATE MATERIAL-IZED VIEW ...
CLONE... command. - G. Truncating a materialized view is not supported.
- H. Context Functions like CURRENT_TIME or CURRENT_TIMESTAMP is not per-mitted.
Answer: B,D
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 # 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. YES
- B. NO
Answer: B
Explanation:
Explanation
Higher the avg_overlap indicates poorly organized clustering.
NEW QUESTION # 44
In Which Data Modelling Technique, Data Engineer generally refer the terms Hubs & Satellites?
- A. Data Vault
- B. Snowflake Schema
- C. Star Schema
- D. Data Hub
Answer: A
Explanation:
Explanation
In Data Vault modelling, Hubs are entities of interest to the business.
They contain just a distinct list of business keys and metadata about when each key was first loaded and from where.
In Data Vault modelling, Satellites connect to Hubs or Links. They are Point in Time: so we can ask and answer the question, "what did we know when?" Satellites contain data about their parent Hub or Link and metadata about when the data was load-ed, from where, and a business effectivity date.
NEW QUESTION # 45
Which privilege are required on an object (i.e. user or role) with USERADMIN Role can modify the object properties?
- A. MANAGE GRANTS
- B. OPEARTE
- C. OWNERSHIP
- D. MODIFY
Answer: C
NEW QUESTION # 46
If the data retention period for a table is less than 90 days, and a stream has not been consumed, Snowflake temporarily extends this period to prevent it from going stale?
- A. TRUE
- B. FALSE
Answer: B
Explanation:
Explanation
If the data retention period for a table is less than 14 days, and a stream has not been consumed, Snowflake temporarily extends this period to prevent it from going stale. The period is extended to the stream's offset, up to a maximum of 14 days by default, regardless of the Snowflake edition for your account. The maximum number of days for which Snowflake can extend the data retention period is determined by the MAX_DATA_EXTENSION_TIME_IN_DAYS parameter value. When the stream is consumed, the extended data retention period is reduced to the default period for the table.
NEW QUESTION # 47
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. client_session_keep_alivs
- B. client_prefetch_threads
- C. authenticator
- D. validate_default_parameters
- E. arrow_nunber_to_decimal
Answer: B,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 # 48
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 # 49
Changing the retention period for your account or individual objects changes the value for all lower-level objects that do not have a retention period explicitly set?
- A. TRUE
- B. FALSE
Answer: A
NEW QUESTION # 50
Which use case would be BEST suited for the search optimization service?
- A. Analysts who need to perform aggregates over high cardinality columns
- B. Data Engineers who create clustered tables with frequent reads against clustering keys
- C. Data Scientists who seek specific JOIN statements with large volumes of data
- D. Business users who need fast response times using highly selective filters
Answer: D
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 # 51
A Data Engineer is investigating a query that is taking a long time to return The Query Profile shows the following:
What step should the Engineer take to increase the query performance?
- A. Rewrite the query using Common Table Expressions (CTEs)
- B. Add additional virtual warehouses.
- C. increasethe size of the virtual warehouse.
- D. Change the order of the joins and start with smaller tables first
Answer: C
Explanation:
Explanation
The step that the Engineer should take to increase the query performance is to increase the size of the virtual warehouse. The Query Profile shows that most of the time was spent on local disk IO, which indicates that the query was reading a lot of data from disk rather than from cache. This could be due to a large amount of data being scanned or a low cache hit ratio. Increasing the size of the virtual warehouse will increase the amount of memory and cache available for the query, which could reduce the disk IO time and improve the query performance. The other options are not likely to increase the query performance significantly. Option A, adding additional virtual warehouses, will not help unless they are used in a multi-cluster warehouse configuration or for concurrent queries. Option C, rewriting the query using Common Table Expressions (CTEs), will not affect the amount of data scanned or cached by the query. Option D, changing the order of the joins and starting with smaller tables first, will not reduce the disk IO time unless it also reduces the amount of data scanned or cached by the query.
NEW QUESTION # 52
Mark the Incorrect Statements with respect to types of streams supported by Snowflake?
- A. Standard streams cannot retrieve update data for geospatial data.
- B. Insert-only Stream supported on external tables only.
- C. An insert-only stream tracks row inserts & Delete ops only
- D. An append-only stream returns the appended rows only and therefore can be much more performant than a standard stream for extract, load, transform (ELT).
Answer: C
Explanation:
Explanation
Standard Stream:
Supported for streams on tables, directory tables, or views. A standard (i.e. delta) stream tracks all DML changes to the source object, including inserts, updates, and deletes (including table trun-cates). This stream type performs a join on inserted and deleted rows in the change set to provide the row level delta. As a net effect, for example, a row that is inserted and then deleted between two transactional points of time in a table is removed in the delta (i.e. is not returned when the stream is queried).
Append-only Stream:
Supported for streams on standard tables, directory tables, or views. An append-only stream tracks row inserts only. Update and delete operations (including table truncates) are not recorded. For ex-ample, if 10 rows are inserted into a table and then 5 of those rows are deleted before the offset for an append-only stream is advanced, the stream records 10 rows.
An append-only stream returns the appended rows only and therefore can be much more performant than a standard stream for extract, load, transform (ELT) and similar scenarios that depend exclu-sively on row inserts. For example, a source table can be truncated immediately after the rows in an append-only stream are consumed, and the record deletions do not contribute to the overhead the next time the stream is queried or consumed.
Insert-only Stream:
Supported for streams on external tables only. An insert-only stream tracks row inserts only; they do not record delete operations that remove rows from an inserted set (i.e. no-ops). For example, in-between any two offsets, if File1 is removed from the cloud storage location referenced by the ex-ternal table, and File2 is added, the stream returns records for the rows in File2 only. Unlike when tracking CDC data for standard tables, Snowflake cannot access the historical records for files in cloud storage.
NEW QUESTION # 53
......
Grab latest Amazon DEA-C01 Dumps as PDF Updated: https://www.testpassed.com/DEA-C01-still-valid-exam.html
Updated Official licence for DEA-C01 Certified by DEA-C01 Dumps PDF: https://drive.google.com/open?id=1MrsClqKuaPoZmtzvhOPUDvUTnK0uDGSO