合格させるDEA-C01ブレーン問題集で更新されたのは認定サンプル問題 [Q15-Q39]

合格させるDEA-C01ブレーン問題集で更新されたのは認定サンプル問題

オンラインDEA-C01テストブレーン問題集とテストエンジン

質問 # 15
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. External tables with Materialized views can be created in Snowflake.
• C. Snowpipe can be leveraged with streams to load data in micro batch fashion with CDC streams that capture most recent data only.
• 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.

正解：B

解説：
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.

質問 # 16
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. FALSE
• B. TRUE

正解：A

解説：
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.

質問 # 17
In efforts to recover the dropped child tables within schema named SCV_SCHEMA by Data Engi-neer, She found that DATA_RETENTION_TIME_IN_DAYS parameter set with value 45 days at Schema level &the data retention period for child tables explicitly set at 85 days. What will happen when she will try to run undrop table command on Child tables to recover them on the 50th day as-suming SCV_SCHEMA is already dropped on 45th day?

• A. Data Engineer needs to first recover the Schema & then Child tables will automatically be recovered irrespective of Retention Inheritance.
• B. Child tables can be recovered using Fail-Safe SQL commands.
• C. When a schema is already dropped, the data retention period for child tables, if explicit-ly set to be different from the retention of the schema, is not honoured. So UNDROP command will fail to run on
  50th day for Child tables recovery.
• D. To honor the data retention period for child tables, She will ab able to recover the child tables on 50th day as DATA_RETENTION_TIME_IN_DAYS is explicitly set with higher retention value.

正解：C

解説：
Explanation
Dropped Containers and Object Retention Inheritance
Currently, when a database is dropped, the data retention period for child schemas or tables, if ex-plicitly set to be different from the retention of the database, is not honored. The child schemas or tables are retained for the same period of time as the database.
Similarly, when a schema is dropped, the data retention period for child tables, if explicitly set to be different from the retention of the schema, is not honored. The child tables are retained for the same period of time as the schema.
To honor the data retention period for these child objects (schemas or tables), drop them explicitly before you drop the database or schema.

質問 # 18
Streams cannot be created to query change data on which of the following objects? [Select All that Apply]

• A. Query Log Tables
• B. Directory tables
• C. Standard tables, including shared tables.
• D. External tables
• E. Views, including secure views

正解：A

解説：
Explanation
Streams supports all the listed objects except Query Log tables.

質問 # 19
What are Invalid rules applicable when using stored procedure contains transaction?

• A. If a transaction is started inside a stored procedure and is still active when the stored procedure finishes, then an error occurs, and the transaction is rolled back.
• B. All Rules are Applicable.
• C. You cannot start a transaction before calling the stored procedure, then complete the transaction inside the stored procedure.
• D. A transaction can be inside a stored procedure, or a stored procedure can be inside a transaction.
• E. You cannot start a transaction inside the stored procedure, then complete the transac-tion after returning from the procedure.
• F. A transaction inside a stored procedure can include a call to another stored procedure that contains a transaction.

正解：B

質問 # 20
Mark the incorrect statement in case Data engineer using the COPY INTO <table> command to load data from files into Snowflake tables?

• A. UTF-32 & UTF-16 both encoding character sets supported for loading data from de-limited files (CSV, TSV, etc.)
• B. For Data loading of files with semi-structured file formats (JSON, Avro, etc.), the only supported character set is UTF-16.
• C. For loading data from all semi-structured supported file formats (JSON, Avro, etc.), as well as unloading data, UTF-8 is the only supported character set.
• D. For Local environment, Files are first copied ("staged") to an internal (Snowflake) stage, then loaded into a table.

正解：B

解説：
Explanation
For Data Loading of delimited files (CSV, TSV, etc.), the default character set is UTF-8. To use any other characters sets, you must explicitly specify the encoding to use for loading.
For semi-structured file formats (JSON, Avro, etc.), the only supported character set is UTF-8.
Rest of the statements are correct.

質問 # 21
A Data Engineer is building a set of reporting tables to analyze consumer requests by region for each of the Data Exchange offerings annually, as well as click-through rates for each listing Which views are needed MINIMALLY as data sources?

• A. SNOWFLAKE.ACCOUNT_USAGE.DATA _TRANSFER_HISTORY
• B. SNOWFLAKE.DATA_SHARING_USAGE.LISTING_CONSOKE>TION_DAILY
• C. SNOWFLAKE- DATA_SHARING_USAGE - LISTING_EVENTS_BAILY
• D. SNOWFLAKE. DATA_SHARING_USAGE. LISTING_TELEMETRY_DAILy

正解：B

解説：
Explanation
The SNOWFLAKE.DATA SHARING _USAGE.LISTING_CONSOKE>TION_DAILY view provides information about consumer requests by region for each of the Data Exchange offeringsannually, as well as click-through rates for each listing. This view is the minimal data source needed for building the reporting tables. The other views are not relevant for this use case.

質問 # 22
When would a Data engineer use table with the flatten function instead of the lateral flatten combination?

• A. Whenthe LATERALFLATTENcombination requires no other source m the from clause to refer to
• B. When TABLE with FLATTENrequires another source in the from clause to refer to
• C. WhenTABLE with FLATTENrequires no additional source m the from clause to refer to
• D. When table withFLATTENis acting like a sub-query executed for each returned row

正解：B

解説：
Explanation
The TABLE function with the FLATTEN function is used to flatten semi-structured data, such as JSON or XML, into a relational format. The TABLE function returns a table expression that can be used in the FROM clause of a query. The TABLE function with the FLATTEN function requires another source in the FROM clause to refer to, such as a table, view, or subquery that contains the semi-structured data. For example:
SELECT t.value:city::string AS city, f.value AS population FROM cities t, TABLE(FLATTEN(input => t.value:population)) f; In this example, the TABLE function with the FLATTEN function refers to the cities table in the FROM clause, which contains JSON data in a variant column named value. The FLATTEN function flattens the population array within each JSON object and returns a table expression with two columns: key and value.
The query then selects the city and population values from the table expression.

質問 # 23
Regular views do not cache data, and therefore cannot improve performance by caching?

• A. FALSE
• B. TRUE

正解：B

解説：
Explanation
Regular views do not cache data, and therefore cannot improve performance by caching.

質問 # 24
Which Role that is dedicated to user and role management only?

• A. USERADMIN
• B. SECURITYADMIN
• C. SYSADMIN
• D. PUBLIC
• E. ORGADMIN

正解：A

質問 # 25
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. By default, the metadata is refreshed automatically when the object is created.
• B. There is nothing like adding partitions on External tables.
• C. Metadata refresh is not required as its Managed implicitly by Snowflake.
• D. The object owner can configure the metadata to refresh automatically when new or updated data files are available in the external stage.
• E. Partitions of External tables is managed by External Stage Cloud provider.

正解：A、D

解説：
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.

質問 # 26
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 cannot include HAVING clauses OR ORDER BY clause.
• B. A materialized view can query only a single table & Joins, including self-joins, are not supported.
• C. A materialized views cannot be created on Shared Data.
• D. Context Functions like CURRENT_TIME or CURRENT_TIMESTAMP is not per-mitted.
• E. You cannot directly clone a materialized view by using the CREATE MATERIAL-IZED VIEW ...
  CLONE... command.
• F. Time Travel is not currently supported on materialized views.
• G. Truncating a materialized view is not supported.
• H. A materialized views does not support clustering.

正解：A、H

解説：
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.

質問 # 27
A large table with 200 columns contains two years of historical data. When queried. the table is filtered on a single day Below is the Query Profile:

Using a size 2XL virtual warehouse, this query look over an hour to complete What will improve the query performance the MOST?

• A. Add a date column as a cluster key on the table
• B. Implement the search optimization service on the table
• C. increase the size of the virtual warehouse.
• D. Increase the number of clusters in the virtual warehouse

正解：A

解説：
Explanation
Adding a date column as a cluster key on the table will improve the query performance by reducing the number of micro-partitions that need to be scanned. Since the table is filtered on a single day, clustering by date will make the query more selective and efficient.

質問 # 28
Which output is provided by both theSYSTEM$CLUSTERING_DEPTHfunction and theSYSTEM$CLUSTERING_INFORMATIONfunction?

• A. notes
• B. average_depth
• C. average_overlaps
• D. total_partition_count

正解：B

解説：
Explanation
The output that is provided by both the SYSTEM$CLUSTERING_DEPTH function and the SYSTEM$CLUSTERING_INFORMATION function is average_depth. This output indicates the average number of micro-partitions that contain data for a given column value or combinationof column values. The other outputs are not common to both functions. The notes output is only provided by the SYSTEM$CLUSTERING_INFORMATION function and it contains additional information or recommendations about the clustering status of the table. The average_overlaps output is only provided by the SYSTEM$CLUSTERING_DEPTH function and it indicates the average number of micro-partitions that overlap with other micro-partitions for a given column value or combination of column values. The total_partition_count output is only provided by the SYSTEM$CLUSTERING_INFORMATION function and it indicates the total number of micro-partitions in the table.

質問 # 29
When using the CURRENT_ROLE and CURRENT_USER functions with secure views that will be shared to other Snowflake accounts, Snowflake returns a NULL value for these functions?

• A. FALSE
• B. TRUE

正解：B

解説：
Explanation
When using the CURRENT_ROLE and CURRENT_USER functions with secure views that will be shared to other Snowflake accounts, Snowflake returns a NULL value for these functions. The reason is that the owner of the data being shared does not typically control the users or roles in the account with which the view is being shared.

質問 # 30
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. When staging 50 compressed files in a Snowflake stage, the files are automatically com-pressed using gzip.
• B. 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.
• 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.

正解：B

解説：
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

質問 # 31
To view/monitor the clustering metadata for a table, Snowflake provides which of the following system functions?

• A. SYSTEM$CLUSTERING_DEPTH_KEY
• B. SYSTEM$CLUSTERING_KEY_INFORMATION (including clustering depth)
• C. SYSTEM$CLUSTERING_DEPTH
• D. SYSTEM$CLUSTERING_INFORMATION (including clustering depth)

正解：C、D

解説：
Explanation
SYSTEM$CLUSTERING_DEPTH:
Computes the average depth of the table according to the specified columns (or the clustering key defined for the table). The average depth of a populated table (i.e. a table containing data) is always 1 or more. The smaller the average depth, the better clustered the table is with regards to the speci-fied columns.
Calculate the clustering depth for a table using two columns in the table:
SELECT SYSTEM$CLUSTERING_DEPTH('TPCH_PRODUCT', '(C2, C9)');
SYSTEM$CLUSTERING_INFORMATION:
Returns clustering information, including average clustering depth, for a table based on one or more columns in the table.
SELECT SYSTEM$CLUSTERING_INFORMATION('SAMPLE_TABLE', '(col1, col3)');

質問 # 32
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. arrow_nunber_to_decimal
• B. client_session_keep_alivs
• C. authenticator
• D. client_prefetch_threads
• E. validate_default_parameters

正解：D、E

解説：
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.

質問 # 33
Which Role inherits the privileges of the USERADMIN role via the system role hierarchy?

• A. SECURITYADMIN
• B. CUSTOM ROLE
• C. SYSADMIN
• D. PUBLIC

正解：A

質問 # 34
Given the table sales which has a clustering key of column CLOSED_DATE which table function will return the average clustering depth for the SALES_REPRESENTATIVEcolumn for the North American region?

• A.
• B.
• C.
• D.

正解：D

解説：
Explanation
The table function SYSTEM$CLUSTERING_DEPTH returns the average clustering depth for a specified column or set of columns in a table. The function takes two arguments: the table name and the column name(s). In this case, the table name is sales and the column name is SALES_REPRESENTATIVE. The function also supports a WHERE clause to filter the rows for which the clustering depth is calculated. In this case, the WHERE clause is REGION = 'North America'. Therefore, the function call in Option B will return the desired result.

質問 # 35
At what isolation level are Snowflake streams?

• A. Snapshot
• B. Repeatable read
• C. Read uncommitted
• D. Read committed

正解：B

解説：
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.

質問 # 36
Pascal, a Data Engineer, have requirement to retrieve the 10 most recent executions of a specified task (completed, still running, or scheduled in the future) scheduled within the last hour, which of the following is the correct SQL Code ?

• A. 1.select *
  2.from table(information_schema.task_history(
  3.scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
  4.result_limit => 10,
  5.task_name=>'MYTASK') WHERE query_id IS NOT NULL);
• B. 1.select *
  2.from table(information_schema.task_history(
  3.scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
  4.result_limit => 11,
  5.task_name=>'MYTASK') WHERE query_id IS NOT NULL);
• C. 1.select *
  2.from table(information_schema.task_history(
  3.scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
  4.result_limit => 10,
  5.task_name=>'MYTASK'));
• D. 1.select *
  2.from table(information_schema.task_history(
  3.scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
  4.result_limit => 10,query_id IS NOT NULL
  5.task_name=>'MYTASK'));

正解：C

解説：
Explanation
To retrieve only tasks that are completed or still running, filter the query using WHERE query_id IS NOT NULL.

質問 # 37
Can Masking policies be applied to virtual columns?

• A. FALSE
• B. TRUE

正解：A

質問 # 38
Snowflake does not provide which of following set of SQL functions to support retrieving infor-mation about tasks?

• A. SYSTEM$CURRENT_USER_TASK_NAME
• B. TASK_HISTORY
• C. TASK_QUERY_HISTORY
• D. SYSTEM$TASK_DEPENDENTS_ENABLE
• E. TASK_DEPENDENTS

正解：E

解説：
Explanation
SYSTEM$CURRENT_USER_TASK_NAME
Returns the name of the task currently executing when invoked from the statement or stored proce-dure defined by the task.
SYSTEM$TASK_DEPENDENTS_ENABLE
Recursively resumes all dependent tasks tied to a specified root task.
TASK_DEPENDENTS
This table function returns the list of child tasks for a given root task in a DAG of tasks.
TASK_HISTORY
This table function can be used to query the history of task usage within a specified date range.

質問 # 39
......

リアルSnowflake DEA-C01試験問題集には正解67問題と解答があります：https://jp.fast2test.com/DEA-C01-premium-file.html

Snowflake DEA-C01認定のリアル2023年最新の模擬試験：https://drive.google.com/open?id=1igPjquK_4qgR-jpIH6_KfJgrQ4XHrKqk