DEA-C01 Certification Exam Dumps Questions in here [Apr-2025]
Updated DEA-C01 Exam Practice Test Questions
NEW QUESTION # 35
A telecommunications company collects network usage data throughout each day at a rate of several thousand data points each second. The company runs an application to process the usage data in real time. The company aggregates and stores the data in an Amazon Aurora DB instance.
Sudden drops in network usage usually indicate a network outage. The company must be able to identify sudden drops in network usage so the company can take immediate remedial actions.
Which solution will meet this requirement with the LEAST latency?
- A. Create an AWS Lambda function within the Database Activity Streams feature of Aurora to detect drops in network usage.
- B. Modify the processing application to publish the data to an Amazon Kinesis data stream. Create an Amazon Managed Service for Apache Flink (previously known as Amazon Kinesis Data Analytics) application to detect drops in network usage.
- C. Replace the Aurora database with an Amazon DynamoDB table. Create an AWS Lambda function to query the DynamoDB table for drops in network usage every minute. Use DynamoDB Accelerator (DAX) between the processing application and DynamoDB table.
- D. Create an AWS Lambda function to query Aurora for drops in network usage. Use Amazon EventBridge to automatically invoke the Lambda function every minute.
Answer: B
NEW QUESTION # 36
Snowflake does not provide which of following set of SQL functions to support retrieving infor-mation about tasks?
- A. TASK_QUERY_HISTORY
- B. TASK_DEPENDENTS
- C. SYSTEM$TASK_DEPENDENTS_ENABLE
- D. SYSTEM$CURRENT_USER_TASK_NAME
- E. TASK_HISTORY
Answer: B
Explanation:
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.
NEW QUESTION # 37
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. 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.
- B. High average of overlapping micro-partitions.
- C. ALL of the above
- D. High average of overlap depth across micro-partitions.
- E. Zero (0) constant micro-partitions out of 1156 total micro-partitions.
Answer: C
NEW QUESTION # 38
An online retail company has an application that runs on Amazon EC2 instances that are in a VPC. The company wants to collect flow logs for the VPC and analyze network traffic.
Which solution will meet these requirements MOST cost-effectively?
- A. Publish flow logs to Amazon CloudWatch Logs. Use an Amazon OpenSearch Service cluster for analytics.
- B. Publish flow logs to Amazon S3 in Apache Parquet format. Use Amazon Athena for analytics.
- C. Publish flow logs to Amazon S3 in text format. Use Amazon Athena for analytics.
- D. Publish flow logs to Amazon CloudWatch Logs. Use Amazon Athena for analytics.
Answer: B
Explanation:
Publishing flow logs to Amazon S3 in Apache Parquet format and using Amazon Athena for analytics (D) is the most cost-effective solution. This approach minimizes storage costs due to the efficient compression of Parquet, and optimizes query performance and cost in Athena due to the reduced data size and optimized columnar storage.
NEW QUESTION # 39
A company has a data lake on AWS. The data lake ingests sources of data from business units.
The company uses Amazon Athena for queries. The storage layer is Amazon S3 with an AWS Glue Data Catalog as a metadata repository.
The company wants to make the data available to data scientists and business analysts.
However, the company first needs to manage fine-grained, column-level data access for Athena based on the user roles and responsibilities.
Which solution will meet these requirements?
- A. Set up AWS Lake Formation. Define security policy-based rules for the users and applications by IAM role in Lake Formation.
- B. Define an IAM resource-based policy for AWS Glue tables. Attach the same policy to IAM user groups.
- C. Create a resource share in AWS Resource Access Manager (AWS RAM) to grant access to IAM users.
- D. Define an IAM identity-based policy for AWS Glue tables. Attach the same policy to IAM roles.
Associate the IAM roles with IAM groups that contain the users.
Answer: A
Explanation:
Lake Formation supports fine-grained access control, including column-level permissions.
NEW QUESTION # 40
A company is planning to migrate on-premises Apache Hadoop clusters to Amazon EMR. The company also needs to migrate a data catalog into a persistent storage solution.
The company currently stores the data catalog in an on-premises Apache Hive metastore on the Hadoop clusters. The company requires a serverless solution to migrate the data catalog.
Which solution will meet these requirements MOST cost-effectively?
- A. Configure an external Hive metastore in Amazon EMR. Migrate the existing on-premises Hive metastore into Amazon EMR. Use Amazon Aurora MySQL to store the company's data catalog.
- B. Configure a new Hive metastore in Amazon EMR. Migrate the existing on-premises Hive metastore into Amazon EMR. Use the new metastore as the company's data catalog.
- C. Use AWS Database Migration Service (AWS DMS) to migrate the Hive metastore into Amazon S3. Configure AWS Glue Data Catalog to scan Amazon S3 to produce the data catalog.
- D. Configure a Hive metastore in Amazon EMR. Migrate the existing on-premises Hive metastore into Amazon EMR. Use AWS Glue Data Catalog to store the company's data catalog as an external data catalog.
Answer: D
Explanation:
https://aws.amazon.com/blogs/big-data/migrate-and-deploy-your-apache-hive-metastore-on- amazon-emr/ Migrating the Hive metastore into Amazon EMR and using AWS Glue Data Catalog as an external catalog provides a balance between leveraging the scalable and managed services of AWS (like EMR and Glue Data Catalog) and ensuring a smooth transition from the on-premises setup. This approach leverages the serverless nature of AWS Glue Data Catalog, minimizing operational overhead and potentially reducing costs compared to managing database servers.
NEW QUESTION # 41
A company currently stores all of its data in Amazon S3 by using the S3 Standard storage class.
A data engineer examined data access patterns to identify trends. During the first 6 months, most data files are accessed several times each day. Between 6 months and 2 years, most data files are accessed once or twice each month. After 2 years, data files are accessed only once or twice each year.
The data engineer needs to use an S3 Lifecycle policy to develop new data storage rules. The new storage solution must continue to provide high availability.
Which solution will meet these requirements in the MOST cost-effective way?
- A. Transition objects to S3 Standard-Infrequent Access (S3 Standard-IA) after 6 months. Transfer objects to S3 Glacier Deep Archive after 2 years.
- B. Transition objects to S3 One Zone-Infrequent Access (S3 One Zone-IA) after 6 months. Transfer objects to S3 Glacier Flexible Retrieval after 2 years.
- C. Transition objects to S3 Standard-Infrequent Access (S3 Standard-IA) after 6 months. Transfer objects to S3 Glacier Flexible Retrieval after 2 years.
- D. Transition objects to S3 One Zone-Infrequent Access (S3 One Zone-IA) after 6 months. Transfer objects to S3 Glacier Deep Archive after 2 years.
Answer: C
NEW QUESTION # 42
Select the Incorrect statement about External Functions in SnowFlake?
- A. An external function does not contain its own code; instead, the external function calls code that is stored and executed outside Snowflake.
- B. An external function is a type of UDF.
- C. Inside Snowflake, the external function is stored as a database object that contains in-formation that Snowflake uses to call the remote service.
- D. Inside Snowflake, the external function is stored as a API Integration object.
Answer: D
NEW QUESTION # 43
In Which Data Modelling Technique, Data Engineer generally refer the terms Hubs & Satellites?
- A. Star Schema
- B. Data Vault
- C. Snowflake Schema
- D. Data Hub
Answer: B
Explanation:
Explanation
In Data Vault modelling, Hubs are entities of interest to the business.
They contain just a distinct list of business keys and metadata about when each key was first loaded and from where.
In Data Vault modelling, Satellites connect to Hubs or Links. They are Point in Time: so we can ask and answer the question, "what did we know when?" Satellites contain data about their parent Hub or Link and metadata about when the data was load-ed, from where, and a business effectivity date.
NEW QUESTION # 44
A company uses Amazon EMR as an extract, transform, and load (ETL) pipeline to transform data that comes from multiple sources. A data engineer must orchestrate the pipeline to maximize performance.
Which AWS service will meet this requirement MOST cost effectively?
- A. Amazon Managed Workflows for Apache Airflow (Amazon MWAA)
- B. Amazon EventBridge
- C. AWS Glue Workflows
- D. AWS Step Functions
Answer: D
Explanation:
Glue Workflows is for Glue job orchestration. C is for orchestration with different AWS services.
NEW QUESTION # 45
A company built a sales reporting system with Python, connecting to Snowflake using the Python Connector.
Based on the user's selections, the system generates the SQL queries needed to fetch the data for the report First it gets the customers that meet the given query parameters (on average 1000 customer records for each report run) and then it loops the customer records sequentially Inside that loop it runs the generated SQL clause for the current customer to get the detailed data for that customer number from the sales data table When the Data Engineer tested the individual SQL clauses they were fast enough (1 second to get the customers 0 5 second to get the sales data for one customer) but the total runtime of the report is too long How can this situation be improved?
- A. Define a clustering key for the sales data table
- B. Increase the size of the virtual warehouse
- C. Increase the number of maximum clusters of the virtual warehouse
- D. Rewrite the report to eliminate the use of the loop construct
Answer: D
Explanation:
Explanation
This option is the best way to improve the situation, as using a loop construct to run SQL queries for each customer is very inefficient and slow. Instead, the report should be rewritten to use a single SQL query that joins the customer and sales data tables and applies the query parameters as filters. This way, the report can leverage Snowflake's parallel processing and optimization capabilities and reduce the network overhead and latency.
NEW QUESTION # 46
To view/monitor the clustering metadata for a table, Snowflake provides which of the following system functions?
- A. SYSTEM$CLUSTERING_KEY_INFORMATION (including clustering depth)
- B. SYSTEM$CLUSTERING_INFORMATION (including clustering depth)
- C. SYSTEM$CLUSTERING_DEPTH
- D. SYSTEM$CLUSTERING_DEPTH_KEY
Answer: B,C
Explanation:
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)');
NEW QUESTION # 47
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. 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.
- C. Snowflake stores metadata about all rows stored in a micro-partition, including number of distinct columns.
- D. 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.
- E. In Snowflake, as data is inserted/loaded into a table, clustering metadata is collected and recorded for each micro-partition created during the process.
Answer: C
Explanation:
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.
NEW QUESTION # 48
Which is the non-supportable JavaScript UDF data types?
- A. Double
- B. Integers
- C. String
- D. Binary
Answer: B
NEW QUESTION # 49
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. Partitions of External tables is managed by External Stage Cloud provider.
- B. Metadata refresh is not required as its Managed implicitly by Snowflake.
- C. There is nothing like adding partitions on External tables.
- D. By default, the metadata is refreshed automatically when the object is created.
- E. The object owner can configure the metadata to refresh automatically when new or updated data files are available in the external stage.
Answer: D,E
Explanation:
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.
NEW QUESTION # 50
Jackie, a Data engineer advised to his data team members about one of the Role highlighting fol-lows points:
1. Avoid Using the <?> Role for Automated Scripts
2. Avoid Using the <?> Role to Create Objects
Which System defined or Custom Role She is mentioning?
- A. SECURITYADMIN
- B. SYSADMIN
- C. ACCOUNTADMIN
- D. USERADMIN
- E. CUSTOM Role
Answer: C
NEW QUESTION # 51
Which system role is recommended for a custom role hierarchy to be ultimately assigned to?
- A. SECURITYADMIN
- B. SYSTEMADMIN
- C. ACCOUNTADMIN
- D. USERADMIN
Answer: A
Explanation:
Explanation
The system role that is recommended for a custom role hierarchy to be ultimately assigned to is SECURITYADMIN. This role has the manage grants privilege on all objects in an account, which allows it to grant access privileges to other roles or revoke them as needed. This role can also create or modify custom roles and assign them to users or other roles. By assigning custom roles to SECURITYADMIN, the role hierarchy can be managed centrally and securely. The other options are not recommended system roles for a custom role hierarchy to be ultimately assigned to. Option A is incorrect because ACCOUNTADMIN is the most powerful role in an account, which has full access to all objects and operations. Assigning custom roles to ACCOUNTADMIN can pose a security risk and should be avoided. Option C is incorrect because SYSTEMADMIN is a role that has full access to all objects in the public schema of the account, but not to other schemas or databases. Assigning custom roles to SYSTEMADMIN can limit the scope and flexibility of the role hierarchy. Option D is incorrect because USERADMIN is a role that can manage users and roles in an account, but not grant access privileges to other objects. Assigning custom roles to USERADMIN can prevent the role hierarchy from controlling access to data and resources.
NEW QUESTION # 52
A retail company stores data from a product lifecycle management (PLM) application in an on- premises MySQL database. The PLM application frequently updates the database when transactions occur.
The company wants to gather insights from the PLM application in near real time. The company wants to integrate the insights with other business datasets and to analyze the combined dataset by using an Amazon Redshift data warehouse.
The company has already established an AWS Direct Connect connection between the on- premises infrastructure and AWS.
Which solution will meet these requirements with the LEAST development effort?
- A. Use the Amazon AppFlow SDK to build a custom connector for the MySQL database to continuously replicate the database changes. Set Amazon Redshift as the destination for the connector.
- B. Run a full load plus CDC task in AWS Database Migration Service (AWS DMS) to continuously replicate the MySQL database changes. Set Amazon Redshift as the destination for the task.
- C. Run a scheduled AWS Glue extract, transform, and load (ETL) job to get the MySQL database updates by using a Java Database Connectivity (JDBC) connection. Set Amazon Redshift as the destination for the ETL job.
- D. Run scheduled AWS DataSync tasks to synchronize data from the MySQL database. Set Amazon Redshift as the destination for the tasks.
Answer: B
Explanation:
https://aws.amazon.com/ko/blogs/apn/change-data-capture-from-on-premises-sql-server-to- amazon-redshift-target/
NEW QUESTION # 53
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. Use federated queries.
- D. Allow users who run the Athena queries to an existing workgroup.
Answer: B
Explanation:
https://aws.amazon.com/blogs/aws/introducing-athena-provisioned-capacity/
NEW QUESTION # 54
Mark a Data Engineer, looking to implement streams on local views & want to use change tracking metadata for one of its Data Loading use case. Please select the incorrect understanding points of Mark with respect to usage of Streams on Views?
- A. 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.
- B. As an alternative to streams, Snowflake supports querying change tracking metadata for views using the CHANGES clause for SELECT statements.
- C. For streams on views, change tracking must be enabled explicitly for the view and un-derlying tables to add the hidden columns to these tables.
- D. Enabling change tracking adds a pair of hidden columns to the table and begins storing change tracking metadata. The values in these hidden CDC data columns provide the input for the stream metadata columns. The columns consume a small amount of stor-age.
- E. Views with GROUP BY & LIMIT Clause are supported by Snowflake.
Answer: E
Explanation:
Explanation
A stream object records data manipulation language (DML) changes made to tables, including in-serts, updates, and deletes, as well as metadata about each change, so that actions can be taken us-ing the changed data. This process is referred to as change data capture (CDC). An individual table stream tracks the changes made to rows in a source table. A table stream (also referred to as simply a "stream") makes a "change table" available of what changed, at the row level, between two transac-tional points of time in a table. This allows querying and consuming a sequence of change records in a transactional fashion.
Streams can be created to query change data on the following objects:
Standard tables, including shared tables.
Views, including secure views
Directory tables
External tables
When created, a stream logically takes an initial snapshot of every row in the source object (e.g. ta-ble, external table, or the underlying tables for a view) by initializing a point in time (called an off-set) as the current transactional version of the object. The change tracking system utilized by the stream then records information about the DML changes after this snapshot was taken. Change rec-ords provide the state of a row before and after the change. Change information mirrors the column structure of the tracked source object and includes additional metadata columns that describe each change event.
Note that a stream itself does not contain any table data. A stream only stores an offset for the source object and returns CDC records by leveraging the versioning history for the source object. 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. Note that for streams on views, change tracking must be enabled explicitly for the view and underlying tables to add the hidden columns to these tables.
Streams on views support both local views and views shared using Snowflake Secure Data Sharing, including secure views. Currently, streams cannot track changes in materialized views.
Views with the following operations are not yet supported:
GROUP BY clauses
QUALIFY clauses
Subqueries not in the FROM clause
Correlated subqueries
LIMIT clauses
Change Tracking:
Change tracking must be enabled in the underlying tables.
Prior to creating a stream on a view, you must enable change tracking on the underlying tables for the view.
Set the CHANGE_TRACKING parameter when creating a view (using CREATE VIEW) or later (using ALTER VIEW).
As an alternative to streams, Snowflake supports querying change tracking metadata for tables or views using the CHANGES clause for SELECT statements. The CHANGES clause enables query-ing change tracking metadata between two points in time without having to create a stream with an explicit transactional offset.
NEW QUESTION # 55
A secure function returns data coming through an inbound share
What will happen if a Data Engineer tries to assign usage privileges on this function to an outbound share?
- A. An error will be returned because the Engineer cannot share data that has already been shared
- B. The Engineer will be able to share the secure function with other accounts
- C. An error will be returned because only views and secure stored procedures can be shared
- D. An error will be returned because only secure functions can be shared with inbound shares
Answer: A
Explanation:
Explanation
An error will be returned because the Engineer cannot share data that has already been shared. A secure function is a Snowflake function that can access data from an inbound share, which is a share that is created by another account and consumed by the current account. A secure function can only be shared with an inbound share, not an outbound share, which is a share that is created by the current account and shared with other accounts. This is to prevent data leakage or unauthorized access to the data from the inbound share.
NEW QUESTION # 56
Which are supported Programming Languages for Creating UDTFs?
- A. Javascript
- B. Java
- C. Node.javascript
- D. Perl
- E. Python
Answer: A,B,E
NEW QUESTION # 57
......
Verified DEA-C01 dumps Q&As 100% Pass in First Attempt Guaranteed Updated Dump: https://drive.google.com/open?id=1p9ssE868EYdruKaba9en5R2oP2SPhOCR
Pass SnowPro Advanced DEA-C01 Exam With 132 Questions: https://www.exams-boost.com/DEA-C01-valid-materials.html