Snowflake DEA-C01リアルに2025年最新のブレーン問題集で模擬試験問題集
DEA-C01試験問題 リアルなDEA-C01練習問題集
Snowflake DEA-C01 認定試験の出題範囲：
| トピック | 出題範囲 |
|---|---|
| トピック 1 |
|
| トピック 2 |
|
| トピック 3 |
|
| トピック 4 |
|
| トピック 5 |
|
質問 # 18
A company needs to build a data lake in AWS. The company must provide row-level data access and column-level data access to specific teams. The teams will access the data by using Amazon Athena, Amazon Redshift Spectrum, and Apache Hive from Amazon EMR.
Which solution will meet these requirements with the LEAST operational overhead?
- A. Use Amazon S3 for data lake storage. Use AWS Lake Formation to restrict data access by rows and columns. Provide data access through AWS Lake Formation.
- B. Use Amazon Redshift for data lake storage. Use Redshift security policies to restrict data access by rows and columns. Provide data access by using Apache Spark and Amazon Athena federated queries.
- C. Use Amazon S3 for data lake storage. Use Apache Ranger through Amazon EMR to restrict data access by rows and columns. Provide data access by using Apache Pig.
- D. Use Amazon S3 for data lake storage. Use S3 access policies to restrict data access by rows and columns. Provide data access through Amazon S3.
正解：A
解説：
https://docs.aws.amazon.com/lake-formation/latest/dg/cbac-tutorial.html
質問 # 19
A company has five offices in different AWS Regions. Each office has its own human resources (HR) department that uses a unique IAM role. The company stores employee records in a data lake that is based on Amazon S3 storage.
A data engineering team needs to limit access to the records. Each HR department should be able to access records for only employees who are within the HR department's Region.
Which combination of steps should the data engineering team take to meet this requirement with the LEAST operational overhead? (Choose two.)
- A. Use data filters for each Region to register the S3 paths as data locations.
- B. Modify the IAM roles of the HR departments to add a data filter for each department's Region.
- C. Register the S3 path as an AWS Lake Formation location.
- D. Enable fine-grained access control in AWS Lake Formation. Add a data filter for each Region.
- E. Create a separate S3 bucket for each Region. Configure an IAM policy to allow S3 access.Restrict access based on Region.
正解：C、D
解説：
https://docs.aws.amazon.com/lake-formation/latest/dg/data-filters-about.html
https://docs.aws.amazon.com/lake-formation/latest/dg/access-control-fine-grained.html
質問 # 20
A Data Engineer wants to check the status of a pipe named my_pipe. The pipe is inside a database named test and a schema named Extract (case-sensitive).
Which querywill provide the status of the pipe?
- A. SELECT FROM SYSTEM$PIPE_STATUS (,test.,,Extracr,,.ny_pipe, i I
- B. SELECT * FROM SYSTEM$PIPE_STATUS ("test. 'extract' .my_pipe"};
- C. SELECT FROM SYSTEM$PIPE_STATUS (''test.'extract'.my_pipe"i:
- D. SELE2T * FROM SYSTEM$PIPE_STATUS < ' test. "Extract", my_pipe');
正解：D
解説：
Explanation
The query that will provide the status of the pipe is SELECT * FROM
SYSTEM$PIPE_STATUS('test."Extract".my_pipe');. The SYSTEM$PIPE_STATUS function returns information about a pipe, such as its name, status, last received message timestamp, etc. The function takes one argument: the pipe name in a qualified form. The pipe name should include the database name, the schema name, and the pipe name, separated by dots. If any of these names are case-sensitive identifiers, they should be enclosed in double quotes. In this case, the schema name Extract is case-sensitive and should be quoted. The other options are incorrect because they do not follow the correct syntax for the pipe name argument. Option A and B use single quotes instead of double quotes for case-sensitive identifiers. Option D uses double quotes instead of single quotes for non-case-sensitive identifiers.
質問 # 21
A data engineer must ingest a source of structured data that is in .csv format into an Amazon S3 data lake. The .csv files contain 15 columns. Data analysts need to run Amazon Athena queries on one or two columns of the dataset. The data analysts rarely query the entire file.
Which solution will meet these requirements MOST cost-effectively?
- A. Use an AWS Glue PySpark job to ingest the source data into the data lake in .csv format.
- B. Create an AWS Glue extract, transform, and load (ETL) job to read from the .csv structured data source. Configure the job to write the data into the data lake in Apache Parquet format.
- C. Create an AWS Glue extract, transform, and load (ETL) job to read from the .csv structured data source. Configure the job to ingest the data into the data lake in JSON format.
- D. Use an AWS Glue PySpark job to ingest the source data into the data lake in Apache Avro format.
正解：B
質問 # 22
Mark the correct statements about Cache?
- A. The size of the warehouse cache is determined by the compute resources in the ware-house.
- B. Warehouse cache is dropped when the warehouse is suspended, which may result in slower initial performance for some queries after the warehouse is resumed.
- C. For persisted query results of all sizes, the cache expires after 24 hours.
- D. Materialized views are more flexible than, but typically slower than, cached results.
- E. 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.
正解：A、B、C、D、E
解説：
Explanation
How Does Warehouse Caching Impact Queries?
Each warehouse, when running, maintains a cache of table data accessed as queries are processed by the warehouse. This enables improved performance for subsequent queries if they are able to read from the cache instead of from the table(s) in the query. The size of the cache is determined by the compute resources in the warehouse (i.e. the larger the warehouse and, therefore, more compute re-sources in the warehouse), the larger the cache.
This cache is dropped when the warehouse is suspended, which may result in slower initial perfor-mance for some queries after the warehouse is resumed. As the resumed warehouse runs and pro-cesses more queries, the cache is rebuilt, and queries that are able to take advantage of the cache will experience improved performance.
Keep this in mind when deciding whether to suspend a warehouse or leave it running. In other words, consider the trade-off between saving credits by suspending a warehouse versus maintaining the cache of data from previous queries to help with performance.
Using Persisted Query Results
When a query is executed, the result is persisted (i.e. cached) for a period of time. At the end of the time period, the result is purged from the system.
Snowflake uses persisted query results to avoid re-generating results when nothing has changed (i.e. "retrieval optimization"). In addition, you can use persisted query results to post-process the results (e.g. layering a new query on top of the results already calculated).
For persisted query results of all sizes, the cache expires after 24 hours.
Both materialized views and cached query results provide query performance benefits:
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.
Regular views do not cache data, and therefore cannot improve performance by caching.
質問 # 23
A data engineer notices that Amazon Athena queries are held in a queue before the queries run.
How can the data engineer prevent the queries from queueing?
- A. Increase the query result limit.
- B. Configure provisioned capacity for an existing workgroup.
- C. Allow users who run the Athena queries to an existing workgroup.
- D. Use federated queries.
正解：B
解説：
https://aws.amazon.com/blogs/aws/introducing-athena-provisioned-capacity/
質問 # 24
A data engineer runs Amazon Athena queries on data that is in an Amazon S3 bucket. The Athena queries use AWS Glue Data Catalog as a metadata table.
The data engineer notices that the Athena query plans are experiencing a performance bottleneck. The data engineer determines that the cause of the performance bottleneck is the large number of partitions that are in the S3 bucket. The data engineer must resolve the performance bottleneck and reduce Athena query planning time.
Which solutions will meet these requirements? (Choose two.)
- A. Create an AWS Glue partition index. Enable partition filtering.
- B. Transform the data that is in the S3 bucket to Apache Parquet format.
- C. Bucket the data based on a column that the data have in common in a WHERE clause of the user query.
- D. Use the Amazon EMR S3DistCP utility to combine smaller objects in the S3 bucket into larger objects.
- E. Use Athena partition projection based on the S3 bucket prefix.
正解：A、E
解説：
https://aws.amazon.com/blogs/big-data/top-10-performance-tuning-tips-for-amazon-athena/ Optimizing Partition Processing using partition projection Processing partition information can be a bottleneck for Athena queries when you have a very large number of partitions and aren't using AWS Glue partition indexing. You can use partition projection in Athena to speed up query processing of highly partitioned tables and automate partition management. Partition projection helps minimize this overhead by allowing you to query partitions by calculating partition information rather than retrieving it from a metastore. It eliminates the need to add partitions' metadata to the AWS Glue table.
質問 # 25
A company is using Snowpipe to bring in millions of rows every day of Change Data Capture (CDC) into a Snowflake staging table on a real-time basis The CDC needs to get processedand combined with other data in Snowflake and land in a final table as part of the full data pipeline.
How can a Data engineer MOST efficiently process the incoming CDC on an ongoing basis?
- A. Use a create ok replace table as statement that references the staging table and includes all the transformation SQL. Use a task to run the full create or replace table as statement on a scheduled basis
- B. Transform the data during the data load with Snowpipe by modifying the related copy into statement to include transformation steps such as case statements andJOIN'S.
- C. Schedule a task that dynamically retrieves the last time the task was run from information_schema-rask_hiSwOry and use that timestamp to process the delta of the new rows since the last time the task was run.
- D. Create a stream on the staging table and schedule a task that transforms data from the stream only when the stream has data.
正解：D
解説：
Explanation
The most efficient way to process the incoming CDC on an ongoing basis is to create a stream on the staging table and schedule a task that transforms data from the stream only when the stream has data. A stream is a Snowflake object that records changes made to a table, such as inserts, updates, or deletes. A stream can be queried like a table and can provide information about what rows have changed since the last time the stream was consumed. A task is a Snowflake object that can execute SQL statements on a schedule without requiring a warehouse. A task can be configured to run only when certain conditions are met, such as when a stream has data or when another task has completed successfully. By creating a stream on the staging table and scheduling a task that transforms data from the stream, the Data Engineer can ensure that only new or modified rows are processed and that no unnecessary computations are performed.
質問 # 26
A Data Engineer enables a result cache at the session level with the following command:
ALTER SESSION SET USE CACHED RESULT = TRUE;
The Engineer then runs the following select query twice without delay:
The underlying table does not change between executions
What are the results of both runs?
- A. The first and second run returned the same results because sample is deterministic
- B. The first and second run returned the same results, because the specific SEEDvalue was provided.
- C. The first and second run returned different results because the query is evaluated each time it is run.
- D. The first and second run returned differentresults because the query uses *instead of an explicit column list
正解：B
解説：
Explanation
The result cache is enabled at the session level, which means that repeated queries will return cached results if there is no change in the underlying data or session parameters. However, in this case, the result cache is not relevant because the query uses a specific SEED value for sampling, which makes it deterministic. Therefore, both runs will return the same results regardless of caching.
質問 # 27
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. Performance Metadata table
- B. Query Designer
- C. Query Profile
- D. Query Optimizer
正解：C
解説：
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.
質問 # 28
Which one is not the Core benefits of micro-partitioning
- A. Snowflake micro-partitions are derived automatically they do not need to be explicitly defined up-front or maintained by users.
- B. Columns are also compressed individually within micro-partitions.
- C. Columns are stored independently within micro-partitions, often referred to as colum-nar storage.
- D. Micro-partitions can overlap in their range of values, helps data skewing.
- E. Enables extremely efficient DML and fine-grained pruning for faster queries.
正解：D
解説：
Explanation
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 de-termines the most efficient compression algorithm for the columns in each micro-partition.
質問 # 29
A data engineer wants to improve the performance of SQL queries in Amazon Athena that run against a sales data table.
The data engineer wants to understand the execution plan of a specific SQL statement. The data engineer also wants to see the computational cost of each operation in a SQL query.
Which statement does the data engineer need to run to meet these requirements?
- A. EXPLAIN FROM sales;
- B. EXPLAIN ANALYZE FROM sales;
- C. EXPLAIN ANALYZE SELECT * FROM sales;
- D. EXPLAIN SELECT * FROM sales;
正解：C
解説：
https://docs.aws.amazon.com/athena/latest/ug/athena-explain-statement.html
質問 # 30
Which are false statements about Star Schema?
- A. The star schema is an important special case of the snowflake schema and is more effec-tive for handling simpler queries.
- B. Star schemas are denormalized.
- C. The star schema separates business process data into facts, which hold the measurable, quantitative data about a business, and dimensions which are descriptive attributes re-lated to fact data.
- D. Star schema is more flexible in terms of analytical needs compared to Data Vault Mod-elling.
正解：D
質問 # 31
What kind of Snowflake integration is required when defining an external function in Snowflake?
- A. HTTP integration
- B. Notification integration
- C. Security integration
- D. API integration
正解：D
解説：
Explanation
An API integration is required when defining an external function in Snowflake. An API integration is a Snowflake object that defines how Snowflake communicates with an externalservice via HTTPS requests and responses. An API integration specifies parameters such as URL, authentication method, encryption settings, request headers, and timeout values. An API integration is used to create an external function object that invokes the external service from within SQL queries.
質問 # 32
During a security review, a company identified a vulnerability in an AWS Glue job. The company discovered that credentials to access an Amazon Redshift cluster were hard coded in the job script.
A data engineer must remediate the security vulnerability in the AWS Glue job. The solution must securely store the credentials.
Which combination of steps should the data engineer take to meet these requirements? (Choose two.)
- A. Store the credentials in the AWS Glue job parameters.
- B. Store the credentials in AWS Secrets Manager.
- C. Access the credentials from a configuration file that is in an Amazon S3 bucket by using the AWS Glue job.
- D. Store the credentials in a configuration file that is in an Amazon S3 bucket.
- E. Grant the AWS Glue job IAM role access to the stored credentials.
正解：B、E
質問 # 33
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.
質問 # 34
A company receives call logs as Amazon S3 objects that contain sensitive customer information.
The company must protect the S3 objects by using encryption. The company must also use encryption keys that only specific employees can access.
Which solution will meet these requirements with the LEAST effort?
- A. Use server-side encryption with AWS KMS keys (SSE-KMS) to encrypt the objects that contain customer information. Configure an IAM policy that restricts access to the KMS keys that encrypt the objects.
- B. Use an AWS CloudHSM cluster to store the encryption keys. Configure the process that writes to Amazon S3 to make calls to CloudHSM to encrypt and decrypt the objects. Deploy an IAM policy that restricts access to the CloudHSM cluster.
- C. Use server-side encryption with customer-provided keys (SSE-C) to encrypt the objects that contain customer information. Restrict access to the keys that encrypt the objects.
- D. Use server-side encryption with Amazon S3 managed keys (SSE-S3) to encrypt the objects that contain customer information. Configure an IAM policy that restricts access to the Amazon S3 managed keys that encrypt the objects.
正解：A
質問 # 35
Company DEF has a strict security policy that mandates that all data at rest in Amazon S3 must be encrypted. They want to ensure that the encryption keys are managed by AWS, but they also want the flexibility to change the encryption keys when required.
Which of the following encryption methods best meets Company DEF's requirements?
- A. Server-Side Encryption with Amazon S3 Managed Keys (SSE-S3).
- B. Client-Side Encryption with a client-side master key.
- C. Server-Side Encryption with Customer-Provided Keys (SSE-C).
- D. Server-Side Encryption with AWS Key Management Service (SSE-KMS).
正解：D
質問 # 36
Ira a Data Engineer with TESLA IT systems, looking out to Compare Traditional Partitioning vs Snowflake micro-partitions for one of the Snowflake Project implementations. Which one of the following is incorrect understanding of Ira about Micro Partitioning?
- A. All DML operations (e.g. DELETE, UPDATE, MERGE) take advantage of the under-lying micro-partition metadata to facilitate and simplify table maintenance.
- B. In Snowflake, as data is inserted/loaded into a table, clustering metadata is collected and recorded for each micro-partition created during the process.
- C. The micro-partition metadata maintained by Snowflake enables precise pruning of col-umns in micro-partitions at query run-time, including columns containing semi-structured data.
- D. Snowflake stores metadata about all rows stored in a micro-partition, including number of distinct columns.
- E. All data in Snowflake tables is automatically divided into micro-partitions, which are contiguous units of storage compared to traditional partitioning where specialized DDL required.
正解：D
解説：
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 allow 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:
The range of values for each of the columns in the micro-partition.
The number of distinct values.
Additional properties used for both optimization and efficient query processing.
It Never stores number of columns as part of Metadata.
Rest of the statements are correct.
質問 # 37
UDTFs also called a table function, returns zero, one, or multiple rows for each input row?
- A. NO
- B. YES
正解：B
解説：
Explanation
UDFs may be scalar or tabular.
A scalar function returns one output row for each input row. The returned row consists of a single column/value.
A tabular function, also called a table function, returns zero, one, or multiple rows for each input row. A tabular UDF is defined by specifying a return clause that contains the TABLE keyword and specifies the names and data types of the columns in the table results. Tabular UDFs are often called UDTFs (user-defined table functions) or table UDFs.
質問 # 38
Harry using Snowflake Enterprise Edition & decided to scale in/out the Cluster in automatic mode. He needs to configure some warehouses as multi cluster mode and some among them in Standard mode as per needs.
If Harry is using Snowflake Enterprise Edition (or a higher edition), all his warehouses should be configured as multi-cluster warehouses only.
- A. FALSE
- B. TRUE
正解：B
解説：
Explanation
If you are using Snowflake Enterprise Edition (or a higher edition), all your warehouses should be configured as multi-cluster warehouses.
質問 # 39
1.+--------------------------------------------------------------+
2.| SYSTEM$CLUSTERING_INFORMATION('SF_DATA', '(COL1, COL3)') |
3.|--------------------------------------------------------------|
4.| { |
5.| "cluster_by_keys" : "(COL1, COL3)", |
6.| "total_partition_count" : 1156, |
7.| "total_constant_partition_count" : 0, |
8.| "average_overlaps" : 117.5484, |
9.| "average_depth" : 64.0701, |
10.| "partition_depth_histogram" : { |
11.| "00000" : 0, |
12.| "00001" : 0, |
13.| "00002" : 3, |
14.| "00003" : 3, |
15.| "00004" : 4, |
16.| "00005" : 6, |
17.| "00006" : 3, |
18.| "00007" : 5, |
19.| "00008" : 10, |
20.| "00009" : 5, |
21.| "00010" : 7, |
22.| "00011" : 6, |
23.| "00012" : 8, |
24.| "00013" : 8, |
25.| "00014" : 9, |
26.| "00015" : 8, |
27.| "00016" : 6, |
28.| "00032" : 98, |
29.| "00064" : 269, |
30.| "00128" : 698 |
31.| } |
32.| } |
33.+--------------------------------------------------------------+
The Above example indicates that the SF_DATA table is not well-clustered for which of following valid reasons?
- A. High average of overlapping micro-partitions.
- B. High average of overlap depth across micro-partitions.
- C. ALL of the above
- D. 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.
- E. Zero (0) constant micro-partitions out of 1156 total micro-partitions.
正解：C
質問 # 40
......
厳密検証されたDEA-C01試験問題集と解答で無料提供のDEA-C01問題と正解付き：https://www.passtest.jp/Snowflake/DEA-C01-shiken.html
あなたを合格させるDEA-C01問題集無料で最新のSnowflake練習テスト：https://drive.google.com/open?id=1Y6SC7-6wqVDE90d4KHUOP6fp6DFW_Cfd