DEA-C01 Dumps By Pros – 1st Attempt Guaranteed Success [Q109-Q127]

Rate this post

DEA-C01 Dumps By Pros – 1st Attempt Guaranteed Success

100% Guarantee Download DEA-C01 Exam Dumps PDF Q&A

Snowflake DEA-C01 Exam Syllabus Topics:

Topic	Details
Topic 1	Data Movement: Snowflake Data Engineers and Software Engineers are assessed on their proficiency to load, ingest, and troubleshoot data in Snowflake. It evaluates skills in building continuous data pipelines, configuring connectors, and designing data sharing solutions.
Topic 2	Performance Optimization: This topic assesses the ability to optimize and troubleshoot underperforming queries in Snowflake. Candidates must demonstrate knowledge in configuring optimal solutions, utilizing caching, and monitoring data pipelines. It focuses on ensuring engineers can enhance performance based on specific scenarios, crucial for Snowflake Data Engineers and Software Engineers.
Topic 3	Storage and Data Protection: The topic tests the implementation of data recovery features and the understanding of Snowflake’s Time Travel and micro-partitions. Engineers are evaluated on their ability to create new environments through cloning and ensure data protection, highlighting essential skills for maintaining Snowflake data integrity and accessibility.
Topic 4	Data Transformation: The SnowPro Advanced: Data Engineer exam evaluates skills in using User-Defined Functions (UDFs), external functions, and stored procedures. It assesses the ability to handle semi-structured data and utilize Snowpark for transformations. This section ensures Snowflake engineers can effectively transform data within Snowflake environments, critical for data manipulation tasks.
Topic 5	Security: The Security topic of the DEA-C01 test covers the principles of Snowflake security, including the management of system roles and data governance. It measures the ability to secure data and ensure compliance with policies, crucial for maintaining secure data environments for Snowflake Data Engineers and Software Engineers.

Q109. A company has a data warehouse that contains a table that is named Sales. The company stores the table in Amazon Redshift. The table includes a column that is named city_name. The company wants to query the table to find all rows that have a city_name that starts with “San” or
“El”.
Which SQL query will meet this requirement?

Select * from Sales where city_name ~ ‘$(San|El)*’;

Select * from Sales where city_name ~ ‘^(San|El)*’;

Select * from Sales where city_name ~’$(San&El)*’;

Select * from Sales where city_name ~ ‘^(San&El)*’;

Q110. For evolving schema and high compatibility, which data format should be chosen for downstream analytics?

CSV

JSON

Parquet

Avro

Q111. A company is building a new application that ingests CSV files into Amazon Redshift. The company has developed the frontend for the application.
The files are stored in an Amazon S3 bucket. Files are no larger than 5 MB.
A data engineer is developing the extract, transform, and load (ETL) pipeline for the CSV files.
The data engineer configured a Redshift cluster and an AWS Lambda function that copies the data out of the files into the Redshift cluster.
Which additional steps should the data engineer perform to meet these requirements?

Configure the bucket to send S3 event notifications to Amazon EventBridge. Configure an EventBridge rule that matches S3 new object created events. Set the Lambda function as the target.

Configure the $3 bucket to send S3 event notifications to an Amazon Simple Queue Service (Amazon SQS) queue. Configure the Lambda function to process the queue.

Configure AWS Database Migration Service (AWS DMS) to stream new S3 objects to a data stream in Amazon Kinesis Data Streams. Set the Lambda function as the target of the data stream.

Configure an Amazon EventBridge rule that matches S3 new object created events. Set an Amazon Simple Queue Service (Amazon SQS) queue as the target of the rule. Configure the Lambda function to process the queue.

Q112. A data engineer needs Amazon Athena queries to finish faster. The data engineer notices that all the files the Athena queries use are currently stored in uncompressed .csv format. The data engineer also notices that users perform most queries by selecting a specific column.
Which solution will MOST speed up the Athena query performance?

Change the data format from .csv to JSON format. Apply Snappy compression.

Compress the .csv files by using Snappy compression.

Change the data format from .csv to Apache Parquet. Apply Snappy compression.

Compress the .csv files by using gzip compression.

Q113. Which methods will trigger an action that will evaluate a DataFrame? (Select TWO)

DataFrame.random_split ( )

DataFrame.collect ()

DateFrame.select ()

DataFrame.col ( )

DataFrame.show ()

Q114. Data engineer designed the data pipelines using Snowpipe to load data files into Snowflake tables, what will happen in case few files with same name but modified data are queued for reloading?

Data will be reloaded as files are modified & its associated metadata also changed. But Snowflake handle implicitly deduplication.

eTAG is changed for Files even they are having same name, so data will be duplicated in SnowFlake tables.

Snowpipe uses file loading metadata associated with each table object, so no metadata available to prevent duplication.

Snowpipe uses file loading metadata associated with each pipe object to prevent reload-ing the same files (and duplicating data) in a table.

Q115. A company is migrating a legacy application to an Amazon S3 based data lake. A data engineer reviewed data that is associated with the legacy application. The data engineer found that the legacy data contained some duplicate information.
The data engineer must identify and remove duplicate information from the legacy application data.
Which solution will meet these requirements with the LEAST operational overhead?

Write a custom extract, transform, and load (ETL) job in Python. Use the DataFrame.drop_duplicates() function by importing the Pandas library to perform data deduplication.

Write an AWS Glue extract, transform, and load (ETL) job. Use the FindMatches machine learning (ML) transform to transform the data to perform data deduplication.

Write a custom extract, transform, and load (ETL) job in Python. Import the Python dedupe library. Use the dedupe library to perform data deduplication.

Write an AWS Glue extract, transform, and load (ETL) job. Import the Python dedupe library. Use the dedupe library to perform data deduplication.

Q116. Ryan, a Data Engineer, accidently drop the Share named SF_SHARE which results in immediate access revoke for all the consumers (i.e., accounts who have created a database from that SF_SHARE). What action he can take to recover the dropped Share?

By Executing UNDROP command he could possibly recover the dropped Share SF_SHARE & its associated Databases for immediate consumer access.

He can recreate a share with the same name as a previous share which does restore the databases created (by any consumers) from the share SF_SHARE.

A dropped share cannot be restored. The share must be created again using the CRE-ATE SHARE command and then configured using GRANT <privilege> … TO SHARE and ALTER SHARE.

Consumer accounts that have created databases from the share will still be able to que-ry these databases as Share is separate securable object & it’s still accessible using time travel feature.

Q117. A company is creating a new data pipeline to populate a data lake. A data analyst needs to prepare and standardize the data before a data engineering team can perform advanced data transformations. The data analyst needs a solution to process the data that does not require writing new code.
Which solution will meet these requirements with the LEAST operational effort?

Use Python and Pandas in an AWS Glue Studio notebook. Ensure that the data engineers add additional transformations to complete the pipeline.

Use Amazon SageMaker Canvas and SageMaker Data Wrangler to write to a new dataset.
Ensure that the data engineers add additional transformations to complete the pipeline by using AWS Glue.

Use AWS Glue Studio with data preparation recipe transformations. Ensure that the data engineers add additional transformations to complete the pipeline.

Create a document that includes the data preparation rules. Ensure that the data engineers implement the rules in AWS Glue.

Q118. A company is developing a log streaming pipeline that uses Amazon Data Firehose. The pipeline streams Amazon CloudWatch Logs data to an Amazon S3 bucket. The company’s analytics team needs to use the data in audits. The pipeline must deliver only the relevant logs to the S3 bucket in a compatible format for the team’s analysis. Which solution will meet these requirements and maintain reliable performance?

Set the S3 bucket rules to allow logs from only specific timestamp ranges. Create an AWS Lambda function that converts the log files to the desired format. Use an S3 trigger to invoke the Lambda function.

Create a subscription filter in the CloudWatch Logs log group that uses the Firehose delivery stream as the destination. Create an AWS Lambda function that converts the log files to the desired format. Configure Firehose to invoke the Lambda function.

Create a subscription filter in the CloudWatch Logs log group. Configure the filter to monitor the Firehose stream. Create an AWS Lambda function to convert the log files to the desired format.
Configure Firehose to invoke the Lambda function.

Tag the CloudWatch Logs log groups that the analytics team needs. Configure Firehose to ingest only the tagged log groups. Configure Firehose to write the output in the desired format.

Q119. Data Engineer try to load data from external stage using Snowpipe & later find out that some Set of Files Not Loaded. To debug the issue, she used COPY_HISTORY function & cross verified that its output indicates a subset of files was not loaded. What is possible reason of arising this situation in both REST API call and Auto-Ingest methods? [Select 2]

External event-driven functionality is used to call the REST APIs, and a backlog of da-ta files already existed in the external stage before the events were configured.

An event notification failure prevented a set of files from getting queued.

Files modified and staged again after 14 days and Snowpipe ignores modified files that are staged again.

A backlog of data files already existed in the external stage do not have any impact on Load failure, as this is well managed by serverless SnowPipe

Q120. A data engineer needs to optimize the performance of a data pipeline that handles retail orders.
Data about the orders is ingested daily into an Amazon S3 bucket.
The data engineer runs queries once each week to extract metrics from the orders data based the order date for multiple date ranges. The data engineer needs an optimization solution that ensures the query performance will not degrade when the volume of data increases.
Which solution will meet this requirement MOST cost-effectively?

Partition the data based on order date. Use Amazon Athena to query the data.

Partition the data based on order date. Use Amazon Redshift to query the data.

Partition the data based on load date. Use Amazon EMR to query the data.

Partition the data based on load date. Use Amazon Aurora to query the data.

Q121. A company is setting up a new Amazon SageMaker Unified Studio domain. Each of the company’s business units needs isolated control over its own assets, projects, and metadata.
Specific datasets must be shareable with other business units upon approval. The company also requires centralized user authentication and identity mapping. Which solution will meet these requirements?

Configure each business unit as a domain unit with delegated ownership and fine-grained permissions policies. Give users the ability to share assets across domain units with explicit access control. Assign API keys to users for authentication to access the domain portal.

Configure business units as separate domain units with owner permissions. Restrict projects exclusively to owners to prevent data sharing between domains. Configure AWS IAM Identity Center for centralized authentication. Map user profiles to their respective domain units.

Configure business units to be represented as separate domains. Establish isolated environments with no shared administrative policies. Configure AWS IAM Identity Center for centralized authentication. Delegate administration at the domain level.

Configure each business unit as a separate domain unit to manage permissions on assets, projects, and metadata. Configure AWS IAM Identity Center for centralized authentication. Map user profiles to their respective domain units. Enable cross-business unit sharing through access requests. Instruct domain unit owners to approve or deny the requests.

Q122. A sales company uses AWS Glue ETL to collect, process, and ingest data into an Amazon S3 bucket. The AWS Glue pipeline creates a new file in the S3 bucket every hour. File sizes vary from 200 KB to 300 KB. The company wants to build a sales prediction model by using data from the previous 5 years. The historic data includes 44,000 files.
The company builds a second AWS Glue ETL pipeline by using the smallest worker type. The second pipeline retrieves the historic files from the S3 bucket and processes the files for downstream analysis. The company notices significant performance issues with the second ETL pipeline.
The company needs to improve the performance of the second pipeline.
Which solution will meet this requirement MOST cost-effectively?

Use a larger worker type.

Increase the number of workers in the AWS Glue ETL jobs.

Use the AWS Glue DynamicFrame grouping option.

Enable AWS Glue auto scaling.

Q123. A data engineer needs to securely transfer 5 TB of data from an on-premises data center to an Amazon S3 bucket. Approximately 5% of the data changes every day. Updates to the data need to be regularly proliferated to the S3 bucket. The data includes files that are in multiple formats.
The data engineer needs to automate the transfer process and must schedule the process to run periodically.
Which AWS service should the data engineer use to transfer the data in the MOST operationally efficient way?

AWS DataSync

AWS Glue

AWS Direct Connect

Amazon S3 Transfer Acceleration

Q124. Which use case would be BEST suited for the search optimization service?

Analysts who need to perform aggregates over high cardinality columns

Business users who need fast response times using highly selective filters

Data Scientists who seek specific JOIN statements with large volumes of data

Data Engineers who create clustered tables with frequent reads against clustering keys

Q125. A company is planning to use a provisioned Amazon EMR cluster that runs Apache Spark jobs to perform big data analysis. The company requires high reliability. A big data team must follow best practices for running cost-optimized and long-running workloads on Amazon EMR. The team must find a solution that will maintain the company’s current level of performance.
Which combination of resources will meet these requirements MOST cost-effectively? (Choose two.)

Use Hadoop Distributed File System (HDFS) as a persistent data store.

Use Amazon S3 as a persistent data store.

Use x86-based instances for core nodes and task nodes.

Use Graviton instances for core nodes and task nodes.

Use Spot Instances for all primary nodes.

Q126. A Data Engineer is evaluating the performance of a query in a development environment.

Based on the Query Profile what are some performance tuning options the Engineer can use? (Select TWO)

Add a LIMIT to the ORDER BY If possible

Use a multi-cluster virtual warehouse with the scaling policy set to standard

Move the query to a larger virtual warehouse

Create indexes to ensure sorted access to data

Increase the max cluster count

Explanation
The performance tuning options that the Engineer can use based on the Query Profile are:
Add a LIMIT to the ORDER BY If possible: This option will improve performance by reducing the amount of data that needs to be sorted and returned by the query. The ORDER BY clause requires sorting all rows in the input before returning them, which can be expensive and time-consuming. By adding a LIMIT clause, the query can return only a subset of rows that satisfy the order criteria, which can reduce sorting time and network transfer time.
Create indexes to ensure sorted access to data: This option will improve performance by reducing the amount of data that needs to be scanned and filtered by the query. The query contains several predicates on different columns, such as o_orderdate, o_orderpriority, l_shipmode, etc. By creating indexes on these columns, the query can leverage sorted access to data and prune unnecessary micro-partitions or rows that do not match the predicates. This can reduce IO time and processing time.
The other options are not optimal because:
Use a multi-cluster virtual warehouse with the scaling policy set to standard: This option will not improve performance, as the query is already using a multi-cluster virtual warehouse with the scaling policy set to standard. The Query Profile shows that the query is using a 2XL warehouse with 4 clusters and a standard scaling policy, which means that the warehouse can automatically scale up or down based on the load. Changing the warehouse size or the number of clusters will not affect the performance of this query, as it is already using the optimal resources.
Increase the max cluster count: This option will not improve performance, as the query is not limited by the max cluster count. The max cluster count is a parameter that specifies the maximum number of clusters that a multi-cluster virtual warehouse can scale up to. The Query Profile shows that the query is using a 2XL warehouse with 4 clusters and a standard scaling policy, which means that the warehouse can automatically scale up or down based on theload. The default max cluster count for a 2XL warehouse is 10, which means that the warehouse can scale up to 10 clusters if needed. However, the query does not need more than 4 clusters, as it is not CPU-bound or memory-bound. Increasing the max cluster count will not affect the performance of this query, as it will not use more clusters than necessary.

Q127. 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?

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.

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.

Child tables can be recovered using Fail-Safe SQL commands.

Data Engineer needs to first recover the Schema & then Child tables will automatically be recovered irrespective of Retention Inheritance.