DEA-C01 practice questions 151–160

1. Question 151 · 313 in bank

   A data engineer is using Amazon Athena to analyze sales data that is in Amazon S3. The data engineer writes a query to retrieve sales amounts for 2023 for several products from a table named sales_data. However, the query does not return results for all of the products that are in the sales_data table. The data engineer needs to troubleshoot the query to resolve the issue. The data engineer's original query is as follows: SELECT product_name, sum(sales_amount) FROM sales_data - WHERE year = 2023 - GROUP BY product_name - How should the data engineer modify the Athena query to meet these requirements?

   • AReplace sum(sales_amount) with count(*) for the aggregation.
   • BChange WHERE year = 2023 to WHERE extract(year FROM sales_data) = 2023.
   • CAdd HAVING sum(sales_amount) > 0 after the GROUP BY clause.
   • DRemove the GROUP BY clause.

   Show answer

   Answer: B

   Explanation

   "SELECT product_name, sum(sales_amount) FROM sales_data WHERE extract(year FROM sales_date) = 2023 GROUP BY product_name;" A. This would change the query to count the number of rows instead of summing sales. C. This would filter out products with zero sales amounts. D. Removing the GROUP BY clause would result in a single sum of all sales amounts without grouping by product_name.

   Source: Adapted from community discussion — GiorgioGss

   Next question

2. Question 152 · 313 in bank

   A data engineer has a one-time task to read data from objects that are in Apache Parquet format in an Amazon S3 bucket. The data engineer needs to query only one column of the data. Which solution will meet these requirements with the LEAST operational overhead?

   • AConfigure an AWS Lambda function to load data from the S3 bucket into a pandas dataframe. Write a SQL SELECT statement on the dataframe to query the required column.
   • BUse S3 Select to write a SQL SELECT statement to retrieve the required column from the S3 objects.
   • CPrepare an AWS Glue DataBrew project to consume the S3 objects and to query the required column.
   • DRun an AWS Glue crawler on the S3 objects. Use a SQL SELECT statement in Amazon Athena to query the required column.

   Show answer

   Answer: B

   Explanation

   https://docs.aws.amazon.com/AmazonS3/latest/userguide/storage-inventory-athena-query.html S3 Select allows you to retrieve a subset of data from an object stored in S3 using simple SQL expressions. It is capable of working directly with objects in Parquet format.

   Source: Adapted from community discussion — rralucard_

   References

   • AWS documentation
   • AWS documentation

   Next question

3. Question 153 · 313 in bank

   A company uses Amazon Redshift for its data warehouse. The company must automate refresh schedules for Amazon Redshift materialized views. Which solution will meet this requirement with the LEAST effort?

   • AUse Apache Airflow to refresh the materialized views.
   • BUse an AWS Lambda user-defined function (UDF) within Amazon Redshift to refresh the materialized views.
   • CUse the query editor v2 in Amazon Redshift to refresh the materialized views.
   • DUse an AWS Glue workflow to refresh the materialized views.

   Show answer

   Answer: C

   Explanation

   Amazon Redshift can automatically refresh materialized views with up-to-date data from its base tables when materialized views are created with or altered to have the autorefresh option. For more details, refer to the documentation here, https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-refresh.html.

   Source: Adapted from community discussion — Christina666

   References

   • AWS documentation
   • AWS documentation
   • AWS documentation

   Next question

4. Question 154 · 313 in bank

   A data engineer must orchestrate a data pipeline that consists of one AWS Lambda function and one AWS Glue job. The solution must integrate with AWS services. Which solution will meet these requirements with the LEAST management overhead?

   • AUse an AWS Step Functions workflow that includes a state machine. Configure the state machine to run the Lambda function and then the AWS Glue job.
   • BUse an Apache Airflow workflow that is deployed on an Amazon EC2 instance. Define a directed acyclic graph (DAG) in which the first task is to call the Lambda function and the second task is to call the AWS Glue job.
   • CUse an AWS Glue workflow to run the Lambda function and then the AWS Glue job.
   • DUse an Apache Airflow workflow that is deployed on Amazon Elastic Kubernetes Service (Amazon EKS). Define a directed acyclic graph (DAG) in which the first task is to call the Lambda function and the second task is to call the AWS Glue job.

   Show answer

   Answer: A

   Explanation

   Step Functions is a managed service for building serverless workflows. You define a state machine that orchestrates the execution sequence. This eliminates the need to manage and maintain your own workflow orchestration server like Airflow.

   Source: Adapted from community discussion — pypelyncar

   Next question

5. Question 155 · 313 in bank

   A company needs to set up a data catalog and metadata management for data sources that run in the AWS Cloud. The company will use the data catalog to maintain the metadata of all the objects that are in a set of data stores. The data stores include structured sources such as Amazon RDS and Amazon Redshift. The data stores also include semistructured sources such as JSON files and .xml files that are stored in Amazon S3. The company needs a solution that will update the data catalog on a regular basis. The solution also must detect changes to the source metadata. Which solution will meet these requirements with the LEAST operational overhead?

   • AUse Amazon Aurora as the data catalog. Create AWS Lambda functions that will connect to the data catalog. Configure the Lambda functions to gather the metadata information from multiple sources and to update the Aurora data catalog. Schedule the Lambda functions to run periodically.
   • BUse the AWS Glue Data Catalog as the central metadata repository. Use AWS Glue crawlers to connect to multiple data stores and to update the Data Catalog with metadata changes. Schedule the crawlers to run periodically to update the metadata catalog.
   • CUse Amazon DynamoDB as the data catalog. Create AWS Lambda functions that will connect to the data catalog. Configure the Lambda functions to gather the metadata information from multiple sources and to update the DynamoDB data catalog. Schedule the Lambda functions to run periodically.
   • DUse the AWS Glue Data Catalog as the central metadata repository. Extract the schema for Amazon RDS and Amazon Redshift sources, and build the Data Catalog. Use AWS Glue crawlers for data that is in Amazon S3 to infer the schema and to automatically update the Data Catalog.

   Show answer

   Answer: B

   Explanation

   The AWS Glue Data Catalog is a purpose-built, fully managed service designed to serve as a central metadata repository for your data sources. It provides a unified view of your data across various sources, including structured databases (like Amazon RDS and Amazon Redshift) and semi-structured data formats (like JSON and XML files in Amazon S3).

   Source: Adapted from community discussion — pypelyncar

   Next question

6. Question 156 · 313 in bank

   A company stores data from an application in an Amazon DynamoDB table that operates in provisioned capacity mode. The workloads of the application have predictable throughput load on a regular schedule. Every Monday, there is an immediate increase in activity early in the morning. The application has very low usage during weekends. The company must ensure that the application performs consistently during peak usage times. Which solution will meet these requirements in the MOST cost-effective way?

   • AIncrease the provisioned capacity to the maximum capacity that is currently present during peak load times.
   • BDivide the table into two tables. Provision each table with half of the provisioned capacity of the original table. Spread queries evenly across both tables.
   • CUse AWS Application Auto Scaling to schedule higher provisioned capacity for peak usage times. Schedule lower capacity during off-peak times.
   • DChange the capacity mode from provisioned to on-demand. Configure the table to scale up and scale down based on the load on the table.

   Show answer

   Answer: C

   Explanation

   Option C, using AWS Application Auto Scaling to schedule higher provisioned capacity for peak usage times and lower capacity during off-peak times, is the most cost-effective solution for the described scenario. It allows the company to align their DynamoDB capacity costs with actual usage patterns, scaling up only when needed and scaling down during low-usage periods.

   Source: Adapted from community discussion — rralucard_

   References

   • AWS documentation
   • AWS documentation

   Next question

7. Question 157 · 313 in bank

   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?

   • AUse 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.
   • BConfigure 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.
   • CConfigure 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.
   • DConfigure 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.

   Show answer

   Answer: B

   Explanation

   https://aws.amazon.com/blogs/big-data/migrate-and-deploy-your-apache-hive-metastore-on-amazon-emr/ Option B is likely the most suitable. 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.

   Source: Adapted from community discussion — rralucard_

   References

   • AWS documentation
   • AWS documentation

   Next question

8. Question 158 · 313 in bank

   A company uses an Amazon Redshift provisioned cluster as its database. The Redshift cluster has five reserved ra3.4xlarge nodes and uses key distribution. A data engineer notices that one of the nodes frequently has a CPU load over 90%. SQL Queries that run on the node are queued. The other four nodes usually have a CPU load under 15% during daily operations. The data engineer wants to maintain the current number of compute nodes. The data engineer also wants to balance the load more evenly across all five compute nodes. Which solution will meet these requirements?

   • AChange the sort key to be the data column that is most often used in a WHERE clause of the SQL SELECT statement.
   • BChange the distribution key to the table column that has the largest dimension.
   • CUpgrade the reserved node from ra3.4xlarge to ra3.16xlarge.
   • DChange the primary key to be the data column that is most often used in a WHERE clause of the SQL SELECT statement.

   Show answer

   Answer: B

   Explanation

   https://docs.aws.amazon.com/redshift/latest/dg/t_Distributing_data.html Option B, changing the distribution key, is the most effective solution to balance the load more evenly across all five compute nodes. Selecting an appropriate distribution key that aligns with the query patterns and data characteristics can result in a more uniform distribution of data and workloads, thus reducing the likelihood of one node being overutilized while others are underutilized.

   Source: Adapted from community discussion — rralucard_

   References

   • AWS documentation
   • AWS documentation

   Next question

9. Question 159 · 313 in bank

   A security company stores IoT data that is in JSON format in an Amazon S3 bucket. The data structure can change when the company upgrades the IoT devices. The company wants to create a data catalog that includes the IoT data. The company's analytics department will use the data catalog to index the data. Which solution will meet these requirements MOST cost-effectively?

   • ACreate an AWS Glue Data Catalog. Configure an AWS Glue Schema Registry. Create a new AWS Glue workload to orchestrate the ingestion of the data that the analytics department will use into Amazon Redshift Serverless.
   • BCreate an Amazon Redshift provisioned cluster. Create an Amazon Redshift Spectrum database for the analytics department to explore the data that is in Amazon S3. Create Redshift stored procedures to load the data into Amazon Redshift.
   • CCreate an Amazon Athena workgroup. Explore the data that is in Amazon S3 by using Apache Spark through Athena. Provide the Athena workgroup schema and tables to the analytics department.
   • DCreate an AWS Glue Data Catalog. Configure an AWS Glue Schema Registry. Create AWS Lambda user defined functions (UDFs) by using the Amazon Redshift Data API. Create an AWS Step Functions job to orchestrate the ingestion of the data that the analytics department will use into Amazon Redshift Serverless.

   Show answer

   Answer: A

   Explanation

   Option A, creating an AWS Glue Data Catalog with Glue Schema Registry and orchestrating data ingestion into Amazon Redshift Serverless using AWS Glue, appears to be the most cost-effective and suitable solution. It offers a serverless approach to manage the evolving data schema of the IoT data and efficiently supports data analytics needs without the overhead of managing a provisioned database cluster or complex orchestration setups.

   Source: Adapted from community discussion — rralucard_

   References

   • AWS documentation

   Next question

10. Question 160 · 313 in bank

    A company stores details about transactions in an Amazon S3 bucket. The company wants to log all writes to the S3 bucket into another S3 bucket that is in the same AWS Region. Which solution will meet this requirement with the LEAST operational effort?

    • AConfigure an S3 Event Notifications rule for all activities on the transactions S3 bucket to invoke an AWS Lambda function. Program the Lambda function to write the event to Amazon Kinesis Data Firehose. Configure Kinesis Data Firehose to write the event to the logs S3 bucket.
    • BCreate a trail of management events in AWS CloudTraiL. Configure the trail to receive data from the transactions S3 bucket. Specify an empty prefix and write-only events. Specify the logs S3 bucket as the destination bucket.
    • CConfigure an S3 Event Notifications rule for all activities on the transactions S3 bucket to invoke an AWS Lambda function. Program the Lambda function to write the events to the logs S3 bucket.
    • DCreate a trail of data events in AWS CloudTraiL. Configure the trail to receive data from the transactions S3 bucket. Specify an empty prefix and write-only events. Specify the logs S3 bucket as the destination bucket.

    Show answer

    Answer: D

    Explanation

    https://docs.aws.amazon.com/AmazonS3/latest/userguide/logging-with-S3.html Option D, creating a trail of data events in AWS CloudTrail, is the best solution to meet the requirement with the least operational effort. It directly logs the desired activities to another S3 bucket and does not involve the development and maintenance of additional resources like Lambda functions or Kinesis Data Firehose streams.

    Source: Adapted from community discussion — rralucard_

    References

    • AWS documentation
    • AWS documentation

    Next question

Keyboard:J Kmove between questionsA–Dselect an answerRshow or hide the answer← →change page

DEA-C01 practice, question 151. 313 questions available.