1 / 7

DEA-C01 SnowPro Advanced Data Engineer Exam Questions

By taking the PassQuestion DEA-C01 SnowPro Advanced Data Engineer Exam Questions, you will be able to prepare yourself for the certification exam and increase your chances of passing it on the first attempt.

karonchen
Télécharger la présentation

DEA-C01 SnowPro Advanced Data Engineer Exam Questions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Pass Snowflake DEA-C01 Exam with Real Questions Snowflake DEA-C01 Exam SnowPro Advanced Data Engineer Certification https://www.passquestion.com/DEA-C01.html 35% OFF on All, Including DEA-C01 Questions and Answers Pass Snowflake DEA-C01 Exam with PassQuestion DEA-C01 questions and answers in the first attempt. https://www.passquestion.com/ 1 / 7

  2. 1.Which are the valid options for the validation_mode parameter in the COPY command A. RETURN_<n>_ROWS B. RETURN_ERROR C. RETURN_ERRORS D. RETURN_ALL_ERRORS Answer: A, C, D Explanation VALIDATION_MODE = RETURN_n_ROWS | RETURN_ERRORS | RETURN_ALL_ERRORS String (constant) that instructs the COPY command to validate the data files instead of loading them into the specified table; i.e. the COPY command tests the files for errors but does not load them. The command validates the data to be loaded and returns results based on the validation option specified: Supported Values Notes RETURN_n_ROWS (e.g. RETURN_10_ROWS) Validates the specified number of rows, if no errors are encountered; otherwise, fails at the first error encountered in the rows. RETURN_ERRORS Returns all errors (parsing, conversion, etc.) across all files specified in the COPY statement. RETURN_ALL_ERRORS Returns all errors across all files specified in the COPY statement, including files with errors that were partially loaded during an earlier load because the ON_ERROR copy option was set to CONTINUE during the load. https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html#optional-parameters 2.FORMAT_NAME and TYPE are mutually exclusive in the COPY command. A. TRUE B. FALSE Answer: A Explanation FILE_FORMAT = ( FORMAT_NAME = 'file_format_name' ) or FILE_FORMAT = ( TYPE = CSV | JSON | AVRO | ORC | PARQUET | XML [ ... ] ) Specifies the format of the data files to load: FORMAT_NAME = 'file_format_name' Specifies an existing named file format to use for loading data into the table. The named file format determines the format type (CSV, JSON, etc.), as well as any other format options, for the data files. For more information, see CREATE FILE FORMAT. TYPE = CSV | JSON | AVRO | ORC | PARQUET | XML [ ... ] Specifies the type of files to load into the table. If a format type is specified, then additional format-specific options can be specified. For more details, see Format Type Options (in this topic). Note FORMAT_NAME and TYPE are mutually exclusive; specifying both in the same COPY command might result in unexpected behavior. 3.Which of the below mentioned compression techniques are applicable for CSV file format? A. GZIP 2 / 7

  3. B. BZ2 C. BROTLI D. ZSTD E. DEFLATE F. RAW_DEFLATE G. LZIP Answer: A, B, C, D, E, F Explanation https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html#type-csv Supported Values Notes AUTO Compression algorithm detected automatically, except for Brotli-compressed files, which cannot currently be detected automatically. If loading Brotli-compressed files, explicitly use BROTLI instead of AUTO. GZIP BZ2 BROTLI Must be specified when loading Brotli-compressed files. ZSTD Zstandard v0.8 (and higher) supported. DEFLATE Deflate-compressed files (with zlib header, RFC1950). RAW_DEFLATE Raw Deflate-compressed files (without header, RFC1951). NONE Data files to load have not been compressed. 4.Snowflake charges a per-byte fee when users transfer data from your snowflake account into cloud storages in another region on the same cloud platform or into cloud storage in another cloud platform A. TRUE B. FALSE Answer: A Explanation https://docs.snowflake.com/en/user-guide/billing-data-transfer.html#understanding-snowflake-data-transf er-billing Cloud providers apply data egress charges in either of the following use cases: Data is transferred from one region to another within the same cloud platform. Data is transferred out of the cloud platform. To recover these expenses, Snowflake charges a per-byte fee when users transfer data from your Snowflake account (hosted on AWS, Google Cloud Platform, or Microsoft Azure) into cloud storage in another region on the same cloud platform, or into cloud storage in another cloud platform. The amount charged per byte depends on the region where your Snowflake account is hosted. For data transfer pricing, see the pricing guide (on the Snowflake website): 3 / 7

  4. 5.In which of the below use cases does Snowflake applies data egress charge? A. Unloading data from Snowflake B. Database replication C. External functions D. Loading data into Snowflake Answer: A, B, C Explanation Data Transfer Billing Use Cases Snowflake currently applies data egress charges only in the following use cases: Unloading Data from Snowflake Using COPY INTO <location> to unload data to cloud storage in a region or cloud platform different from where your Snowflake account is hosted. Database Replication Replicating data to a Snowflake account in a region or cloud platform different from where your primary (origin) Snowflake account is hosted. External Functions AWS: Data transfers sent from your Snowflake account are billed at the cross-cloud platform rate regardless of the cloud platform that hosts your Snowflake account or the region in which your account is located. Data sent via API Gateway Private Endpoints incurs PrivateLink charges for both ingress and egress. Azure: Data transfers within the same region are free, and therefore there are no charges for Snowflake to pass on to the account. https://docs.snowflake.com/en/user-guide/billing-data-transfer.html#data-transfer-billing-use-cases 6.Which of the below transformations are supported by Snowflake while loading a table using the COPY statement? A. Column reordering B. Column renaming C. Column omission D. Casts E. Truncating text strings that exceed the target column length Answer: A, C, D, E Explanation Simple Transformations During a Load Snowflake supports transforming data while loading it into a table using the COPY command. Options include: 1. Column reordering 2. Column omission 3. Casts 4. Truncating text strings that exceed the target column length There is no requirement for your data files to have the same number and ordering of columns as your target table. 4 / 7

  5. 7.Which of the below function can a task use to see whether a stream contains change data for a table? A. SYSTEM$STREAM_HAS_DATA B. SYSTEM#STREAM_HAS_DATA C. SYSTEM_HAS_STREAM_DATA Answer: A Explanation Whenever a TASK is used to ingest data from stream and then perform a DML operation, it is a best practice to check whether STREAM has data using the SYSTEM$STREAM_HAS_DATA function. I learnt it the hard way. By mistake I created a task in development and forgot to disable it, the STREAM check was not there. The task was running every 10 minutes and consumed all available credits in 2 days. To avoid these situations, in addition to check for the STREAM data, it is also a good practice to setup resource monitors at lower percentages. For example at 30%, 50%, 70% and 90%. --------------------------- Additional Explanation from the snowflake documentation Tasks may optionally use table streams to provide a convenient way to continuously process new or changed data. A task can transform new or changed rows that a stream surfaces. Each time a task is scheduled to run, it can verify whether a stream contains change data for a table (using SYSTEM$STREAM_HAS_DATA) and either consume the change data or skip the current run if no change data exists. 8.A stream contains table data A. True B. False Answer: B Explanation Note that a stream itself does not contain any table data. A stream only stores an offset for the source table and returns CDC records by leveraging the versioning history for the source table. When the first stream for a table is created, a pair of hidden columns are added to the source table and begin storing change tracking metadata. These columns consume a small amount of storage. The CDC records returned when querying a stream rely on a combination of the offset stored in the stream and the change tracking metadata stored in the table. https://docs.snowflake.com/en/user-guide/streams.html#overview-of-table-streams 9.Which of the below SQLs will you run to validate any loads of the pipe within the last hour? A. select * from table(validate_pipe_load( pipe_name=>'data_engineer_pipe', start_time=>dateadd(hour, -1, current_timestamp()))); B. select * from table(pipe_load_status( pipe_name=>'data_engineer_pipe', start_time=>dateadd(hour, -1, current_timestamp()))); C. select * from table(check_pipe_load( pipe_name=>'data_engineer_pipe', start_time=>dateadd(hour, -1, current_timestamp()))); Answer: A 5 / 7

  6. Explanation https://docs.snowflake.com/en/sql-reference/functions/validate_pipe_load.html#validate-pipe-load VALIDATE_PIPE_LOAD This table function can be used to validate data files processed by Snowpipe within a specified time range. The function returns details about any errors encountered during an attempted data load into Snowflake tables. 10.For how many days does the COPY_HISTORY retain data loading history A. 10 B. 15 C. 14 Answer: C Explanation https://docs.snowflake.com/en/sql-reference/functions/copy_history.html#copy-history COPY_HISTORY This table function can be used to query Snowflake data loading history along various dimensions within the last 14 days. The function returns load activity for both COPY INTO <table> statements and continuous data loading using Snowpipe. The table function avoids the 10,000 row limitation of the LOAD_HISTORY View. The results can be filtered using SQL predicates. 11.Each micro partition contains between 50 mb and 500 MB of uncompressed data A. TRUE B. FALSE Answer: A Explanation What are Micro-partitions? All data in Snowflake tables is automatically divided into micro-partitions, which are contiguous units of storage. Each micro-partition contains between 50 MB and 500 MB of uncompressed data (note that the actual size in Snowflake is smaller because data is always stored compressed). Groups of rows in tables are mapped into individual micro-partitions, organized in a columnar fashion. This size and structure allows for extremely granular pruning of very large tables, which can be comprised of millions, or even hundreds of millions, of micro-partitions. Snowflake stores metadata about all rows stored in a micro-partition, including: 1. The range of values for each of the columns in the micro-partition. 2. The number of distinct values. 3. Additional properties used for both optimization and efficient query processing. 12.Which of the below are benefits of micro partitioning? A. Micro partitions are derived automatically B. Micro partitions need to be maintained by users C. Micro partitions enables extremely efficient DML and fine-grained pruning for faster queries D. Columns are stored independently within micro-partitions E. Columns are compressed individually within micro-partitions Answer: A, C, D, E 6 / 7

  7. Explanation Benefits of Micro-partitioning The benefits of Snowflake’s approach to partitioning table data include: In contrast to traditional static partitioning, Snowflake micro-partitions are derived automatically; they don’t need to be explicitly defined up-front or maintained by users. As the name suggests, micro-partitions are small in size (50 to 500 MB, before compression), which enables extremely efficient DML and fine-grained pruning for faster queries. Micro-partitions can overlap in their range of values, which, combined with their uniformly small size, helps prevent skew. Columns are stored independently within micro-partitions, often referred to as columnar storage. This enables efficient scanning of individual columns; only the columns referenced by a query are scanned. Columns are also compressed individually within micro-partitions. Snowflake automatically determines the most efficient compression algorithm for the columns in each micro-partition. https://docs.snowflake.com/en/user-guide/tables-clustering-micropartitions.html#benefits-of-micro-partitio nin g 13.Snowflake does not prune micro-partitions based on a predicate with a subquery A. TRUE B. FALSE Answer: A Explanation Not all predicate expressions can be used to prune. For example, Snowflake does not prune micro-partitions based on a predicate with a subquery, even if the subquery results in a constant. Question 14: Which of the below micro-partition metadata is maintained by snowflake 14.You have just created a table in snowflake. There are now rows in the table. What will be the clustering depth of the table? A. 0 B. 1 C. 2 D. 3 Answer: A Explanation A table with no micro-partitions (i.e. an unpopulated/empty table) has a clustering depth of 0. 7 / 7

More Related