Free SnowPro Advanced: Data Engineer Certification Exam DEA-C01 Exam Practice Test

1. + +

• | SYSTEM$CLUSTERING_INFORMATION('SF_DATA', '(COL1, COL3)') |
• | |
• | { |
• | "cluster_by_keys" : "(COL1, COL3)", |
• | "total_partition_count" : 1156, |
• | "total_constant_partition_count" : 0, |
• | "average_overlaps" : 117.5484, |
• | "average_depth" : 64.0701, |
• | "partition_depth_histogram" : { |
• | "00000" : 0, |
• | "00001" : 0, |
• | "00002" : 3, |
• | "00003" : 3, |
• | "00004" : 4, |
• | "00005" : 6, |
• | "00006" : 3, |
• | "00007" : 5, |
• | "00008" : 10, |
• | "00009" : 5, |
• | "00010" : 7, |
• | "00011" : 6, |
• | "00012" : 8, |
• | "00013" : 8, |
• | "00014" : 9, |
• | "00015" : 8, |
• | "00016" : 6, |
• | "00032" : 98, |
• | "00064" : 269, |
• | "00128" : 698 |
• | } |
• | } |
• 33. + +
• The Above example indicates that the SF_DATA table is not well-clustered for which of following valid reasons?
• Zero (0) constant micro-partitions out of 1156 total micro-partitions.
• High average of overlapping micro-partitions.
• High average of overlap depth across micro-partitions.
• 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.
• ALL of the above

To help manage STAGE storage costs, Data engineer recommended to monitor stage files and re- move them from the stages once the data has been loaded and the files which are no longer needed. Which option he can choose to remove these files either during data loading or afterwards?

• He can choose to remove stage files during data loading (using the COPY INTO command).
  Files no longer needed, can be removed using the PURGE=TRUE command.
  Files no longer needed, can be removed using the REMOVE command.
  Script can be used during data loading & post data loading with DELETE command.
  Reveal Answer Answer: A, ,B Next Question

  Question 3

  

  Charles, A Lead Data engineer, with ACCOUNTADMIN role wants to configure the time travel for one of the Schema’s object. He setup the MIN_DATA_RETENTION_TIME_IN_DAYS pa-rameter with Value 79 at account level but he figured out that DA-TA_RETENTION_TIME_IN_DAYS is already set with value 81 at account level. What would be the effective minimum data retention period for an object?

  • 90
  • 81
  • 79
  • There is no such MIN_DATA_RETENTION_TIME_IN_DAYS parameter

  Reveal Answer Answer: B Next Question

  Question 4

  

  Which of the following statements is/are incorrect regarding Fail-safe data recovery?

  • Data stored in temporary tables is not recoverable after the table is dropped as they do not have fail-safe.
  • 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.
  • Long-lived tables, such as fact tables, should always be defined as permanent to ensure they are fully protected by Fail-safe.
  • Short-lived tables (i.e. <1 day), such as ETL work tables, can be defined as transient to eliminate Fail-safe costs.
  • 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.

  Reveal Answer Answer: B Next Question

  Question 5

  

  Jeff, a Data Engineer, accessing elements in JSON object in its 3 data loading scripts, he unknow-ingly use the upper case while accessing the elements. e.g.Script 1 --> fruits:apple.sweet Script 2 --> FRUITS:apple.sweet Script 3 --> FRUITS:Apple.SweetWhich are the correct statements?

  • Script 1 & Script 2 traverse path twill be treated same, but Script 2 will not.
  • Script 1,2,3 traverse path will treat in same way.
  • Script 1 & 3 traverse path will be treated in same way.
  • Script 2&3 traverse path will be same.

  Reveal Answer Answer: A Next Question

  Question 6

  

  Which of the following security and governance tools/technologies are known to provide native connectivity to Snowflake? [Select 2]

  • ALTR
  • Bafle
  • BIG Squid
  • Dataiku
  • Zepl

  Reveal Answer Answer: A, ,B Next Question

  Question 7

  

  Which Role inherits the privileges of the USERADMIN role via the system role hierarchy?

  • SYSADMIN
  • SECURITYADMIN
  • PUBLIC
  • CUSTOM ROLE

  Reveal Answer Answer: B Next Question

  Question 8

  

  Regular views do not cache data, and therefore cannot improve performance by caching?

  • TRUE
  • FALSE

  Reveal Answer Answer: A Next Question

  Question 9

  

  Data Engineer is using existing pipe that automates data loads using event notifications, later he figured out the needs to modify pipe properties. For the same, He decided to recreate the pipe as best practice. He followed the below steps for the same.

  • Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is RUN-NING.
  • Recreate the pipe (using CREATE OR REPLACE PIPE).
  • Query the SYSTEM$PIPE_STATUS function and verify that the pipe execution state is RUN-NING. Which are the Missing recommended steps while Recreating Pipes for Automated Data Loads?
  • CREATE OR REPLACE PIPE command will recreate the PIPE successfully.
  • Terminate the existing pipe (using ALTER PIPE … SET PIPE_EXECUTION_TERMINATE = true) before recreation.
  • Pause the pipe (using ALTER PIPE … SET PIPE_EXECUTION_PAUSED = true) Pre & Post recreation & Resume after recreation (using ALTER PIPE … SET PIPE_EXECUTION_PAUSED = false).
  • Force the pipe to resume (using SYSTEM$PIPE_FORCE_RESUME).

  Reveal Answer Answer: C Next Question

  Question 10

  

  Mark the correct statements about Cache?

  • 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.
  • For persisted query results of all sizes, the cache expires after 24 hours.
  • The size of the warehouse cache is determined by the compute resources in the ware-house.
  • Warehouse cache is dropped when the warehouse is suspended, which may result in slower initial performance for some queries after the warehouse is resumed.

  Reveal Answer Answer: A, ,B, ,C, ,D, ,E Next Question
  Page: 1 / 26
  Total Questions: 130

  Previous Page Next Page
  • Search for:
  • Contact Us
  • FAQ
  • Refund Policy
  • About Us
  • Guarantee
  • Login
  Login

  Username or email address *Required

  Password *Required

  Remember me Log in

  Lost your password?

  Register

  Email address *Required

  Password *Required

  Your personal data will be used to support your experience throughout this website, to manage access to your account, and for other purposes described in our privacy policy.

  Register