Snowflake DEA-C01 SnowPro Advanced Data Engineer Certification Online Training
Snowflake DEA-C01 Online Training
The questions for DEA-C01 were last updated at Jan 06,2025.
- Exam Code: DEA-C01
- Exam Name: SnowPro Advanced Data Engineer Certification
- Certification Provider: Snowflake
- Latest update: Jan 06,2025
Data Engineer identified use case where he decided to use materialized view for query performance.
Which one is not the limitation he must be aware of before using MVs in their use case?
- A . Truncating a materialized view is not supported.
- B . Time Travel is not currently supported on materialized views.
- C . You cannot directly clone a materialized view by using the CREATE MATERIAL-IZED VIEW … CLONE… command.
- D . A materialized view can query only a single table & Joins, including self-joins, are not supported.
- E . A materialized views does not support clustering.
- F . A materialized views cannot be created on Shared Data.
- G . A materialized view cannot include HAVING clauses OR ORDER BY clause.
- H . Context Functions like CURRENT_TIME or CURRENT_TIMESTAMP is not per-mitted.
Data Engineer identified use case where he decided to use materialized view for query performance.
Which one is not the limitation he must be aware of before using MVs in their use case?
- A . Truncating a materialized view is not supported.
- B . Time Travel is not currently supported on materialized views.
- C . You cannot directly clone a materialized view by using the CREATE MATERIAL-IZED VIEW … CLONE… command.
- D . A materialized view can query only a single table & Joins, including self-joins, are not supported.
- E . A materialized views does not support clustering.
- F . A materialized views cannot be created on Shared Data.
- G . A materialized view cannot include HAVING clauses OR ORDER BY clause.
- H . Context Functions like CURRENT_TIME or CURRENT_TIMESTAMP is not per-mitted.
group by m.item_id;
Step 3: After 1 hour, he decided to temporarily suspend the use (and maintenance) of the DataReportMV materialized view for cost saving purpose.
alter materialized view DataReportMV suspend;
Please select what Alex is doing wrong here?
- A . A materialized view, DataReportMV does not support Join operations, so Step 2 would be failed & he cannot proceed further.
- B . Materialized view on top of External tables is not supported feature.
- C . Once DataReportMV got suspended, any query on the top of the view will generate error like: Failure during expansion of view ‘DATAREPORTMV’: SQL compilation error: Materialized view DataReportMV is invalid.
- D . There is no command like suspend for temporarily suspension of Materialized views, Step 3 will
give error like invalid Suspend command. - E . Alex is doing everything correct.
David, a Lead Data engineer with XYZ company looking out to improve query performance & other benefits while working with Tables, Regular Views, MVs and Cached Results.
Which one of the following does not shows key similarities and differences between tables, regular views, cached query results, and materialized views while choosing any of them by David?
- A . Regular views do not cache data, and therefore cannot improve performance by caching.
- B . As with non-materialized views, a materialized view automatically inherits the privileges of its base table.
- C . Cached Query Results: Used only if data has not changed and if query only uses deterministic functions (e.g. not CURRENT_DATE).
- D . Materialized views are faster than tables because of their “cache” (i.e. the query results for the
view); in addition, if data has changed, they can use their “cache” for data that hasn’t changed and use the base table for any data that has changed. - E . Both materialized views and regular views enhance data security by allowing data to be exposed or hidden at the row level or column level.
Melissa, Senior Data Engineer, looking out to optimize query performance for one of the Critical Control Dashboard, she found that most of the searches by the users on the control dashboards are based on Equality search on all the underlying columns mostly.
Which Best techniques she should consider here?
- A . She can go for clustering on underlying tables which can speedup Equality searches.
- B . A materialized view speeds both equality searches and range searches.
- C . The search optimization service would best fit here as it can be applied to all underlying columns & speeds up equality searches. (Correct)
- D . Melissa can create Indexes & Hints on the searchable columns to speed up Equality search.
Search optimization works best to improve the performance of a query when the following conditions are true: [Select All that apply]
- A . The table is not clustered.
- B . The table is frequently queried on columns other than the primary cluster key.
- C . Search Query uses Equality predicates (for example, <column_name> = <constant>) OR Predicates that use IN.
- D . Search Query uses Sort Operations.
Regular views do not cache data, and therefore cannot improve performance by caching?
- A . TRUE
- B . FALSE
Mark the correct statements about Cache?
- A . Materialized views are more flexible than, but typically slower than, cached results.
- 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 . For persisted query results of all sizes, the cache expires after 24 hours.
- D . The size of the warehouse cache is determined by the compute resources in the ware-house.
- 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.
Marko, a Data Engineer is using Snowpipe for data loading in micro batches for one of the Finance Data workloads. There are set of files he attempted to load into the snowflake table using Snow-pipe. While monitoring he found that there are set of files has multiple issue, He queried the COPY_HISTORY view & checked the STATUS column which indicates whether a particular set of files was loaded, partially loaded, or failed to load.
But he wants to view all errors in the files along with Load status, how he can check all errors?
- A . He can check RETURN_ALL_ERROR_MESSAGE column in the COPY_HISTORY view which can provides a reason and view all errors in the files.
- B . He can view all errors in the files, by executing a COPY INTO <table> statement with the VALIDATION_ERROR_MODE copy option set to RE-TURN_ALL_PIPE_ERRORS.
- C . Marko can look out for FIRST_ERROR_MESSAGE column in the COPY_HISTORY view which can provides a reason why a file partially loaded or failed for all the files.
- D . He can view all errors in the files, by executing a COPY INTO <table> statement with the VALIDATION_MODE copy option set to RETURN_ALL_ERRORS.
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 . He can apply System function SYSTEM$PIPE_STALE_RESUME with ALTER PIPE statement.
- C . Robert can use SYSTEM$PIPE_FORCE_RESUME function to resume this stale pipe.
- D . select sys-tem$pipe_force_resume (‘mydb.myschema.stalepipe’,’staleness_check_override, ownership_transfer_check_override’);
- E . ALTER PIPES … RESUME statement will resume the pipe.
Latest DEA-C01 Dumps Valid Version with 100 Q&As
Latest And Valid Q&A | Instant Download | Once Fail, Full Refund
