DEA-C01 practice questions 161–170

1. Question 161 · 313 in bank

   A data engineer needs to maintain a central metadata repository that users access through Amazon EMR and Amazon Athena queries. The repository needs to provide the schema and properties of many tables. Some of the metadata is stored in Apache Hive. The data engineer needs to import the metadata from Hive into the central metadata repository. Which solution will meet these requirements with the LEAST development effort?

   • AUse Amazon EMR and Apache Ranger.
   • BUse a Hive metastore on an EMR cluster.
   • CUse the AWS Glue Data Catalog.
   • DUse a metastore on an Amazon RDS for MySQL DB instance.

   Show answer

   Answer: C

   Explanation

   https://aws.amazon.com/blogs/big-data/metadata-classification-lineage-and-discovery-using-apache-atlas-on-amazon-emr/ Option C, using the AWS Glue Data Catalog, is the best solution to meet the requirements with the least development effort. The AWS Glue Data Catalog is designed to be a central metadata repository that can integrate with various AWS services including EMR and Athena, providing a managed and scalable solution for metadata management with built-in Hive compatibility.

   Source: Adapted from community discussion — rralucard_

   References

   • AWS documentation

   Next question

2. Question 162 · 313 in bank

   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?

   • AUse Amazon S3 for data lake storage. Use S3 access policies to restrict data access by rows and columns. Provide data access through Amazon S3.
   • BUse 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.
   • CUse 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.
   • DUse 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.

   Show answer

   Answer: D

   Explanation

   https://docs.aws.amazon.com/lake-formation/latest/dg/cbac-tutorial.html Option D, using Amazon S3 for data lake storage and AWS Lake Formation for access control, is the most suitable solution. It meets the requirements for row-level and column-level access control and integrates well with Amazon Athena, Amazon Redshift Spectrum, and Apache Hive on EMR, all with lower operational overhead compared to the other options.

   Source: Adapted from community discussion — rralucard_

   References

   • AWS documentation

   Next question

3. Question 163 · 313 in bank

   A company created an extract, transform, and load (ETL) data pipeline in AWS Glue. A data engineer must crawl a table that is in Microsoft SQL Server. The data engineer needs to extract, transform, and load the output of the crawl to an Amazon S3 bucket. The data engineer also must orchestrate the data pipeline. Which AWS service or feature will meet these requirements MOST cost-effectively?

   • AAWS Step Functions
   • BAWS Glue workflows
   • CAWS Glue Studio
   • DAmazon Managed Workflows for Apache Airflow (Amazon MWAA)

   Show answer

   Answer: B

   Explanation

   I asked an AI. Analysis of the answers: A. AWS Step Functions: It is a good option for orchestrating workflows with steps from different AWS services, but requires additional development to connect to Microsoft SQL Server. B. AWS Glue Workflows: This is the best and most profitable option. AWS Glue is designed specifically for ETL on AWS and integrates directly with data sources such as Microsoft SQL Server through connectors. This allows for easier configuration and avoids the need for additional development. C. AWS Glue Studio: It is a visual interface for AWS Glue that makes it easy to create and manage ETL jobs. However, the underlying functionality comes from AWS Glue (B) workflows. D. Amazon Managed Workflows for Apache Airflow (Amazon MWAA): It's a viable option, but it's generally more expensive than native AWS services like AWS Glue Workflows. Additionally, it requires some Airflow experience for setup and maintenance.

   Source: Adapted from community discussion — dev_vicente

   References

   • AWS documentation
   • AWS documentation
   • AWS documentation

   Next question

4. Question 164 · 313 in bank

   Select 2

   An airline company is collecting metrics about flight activities for analytics. The company is conducting a proof of concept (POC) test to show how analytics can provide insights that the company can use to increase on-time departures. The POC test uses objects in Amazon S3 that contain the metrics in .csv format. The POC test uses Amazon Athena to query the data. The data is partitioned in the S3 bucket by date. As the amount of data increases, the company wants to optimize the storage solution to improve query performance. Which combination of solutions will meet these requirements? (Choose two.)

   • AAdd a randomized string to the beginning of the keys in Amazon S3 to get more throughput across partitions.
   • BUse an S3 bucket that is in the same account that uses Athena to query the data.
   • CUse an S3 bucket that is in the same AWS Region where the company runs Athena queries.
   • DPreprocess the .csv data to JSON format by fetching only the document keys that the query requires.
   • EPreprocess the .csv data to Apache Parquet format by fetching only the data blocks that are needed for predicates.

   Show answer

   Answer: CE

   References

   • AWS documentation

   Next question

5. Question 165 · 313 in bank

   Select 2

   A company uses Amazon RDS for MySQL as the database for a critical application. The database workload is mostly writes, with a small number of reads. A data engineer notices that the CPU utilization of the DB instance is very high. The high CPU utilization is slowing down the application. The data engineer must reduce the CPU utilization of the DB Instance. Which actions should the data engineer take to meet this requirement? (Choose two.)

   • AUse the Performance Insights feature of Amazon RDS to identify queries that have high CPU utilization. Optimize the problematic queries.
   • BModify the database schema to include additional tables and indexes.
   • CReboot the RDS DB instance once each week.
   • DUpgrade to a larger instance size.
   • EImplement caching to reduce the database query load.

   Show answer

   Answer: AD

   Explanation

   Ans. AE A) Use Amazon RDS Performance Insights to identify the query that's responsible for the database load. Check the SQL tab that corresponds to a particular timeframe. E) If there's a query that's repeatedly running, use prepared statements to lower the pressure on your CPU. Repeated running of prepared statements caches the query plan. Because the plan is already in cache for further runs, the time for planning is much less. https://repost.aws/knowledge-center/rds-aurora-postgresql-high-cpu

   Source: Adapted from community discussion — damaldon

   References

   • repost.aws documentation
   • repost.aws documentation

   Next question

6. Question 166 · 313 in bank

   A company has used an Amazon Redshift table that is named Orders for 6 months. The company performs weekly updates and deletes on the table. The table has an interleaved sort key on a column that contains AWS Regions. The company wants to reclaim disk space so that the company will not run out of storage space. The company also wants to analyze the sort key column. Which Amazon Redshift command will meet these requirements?

   • AVACUUM FULL Orders
   • BVACUUM DELETE ONLY Orders
   • CVACUUM REINDEX Orders
   • DVACUUM SORT ONLY Orders

   Show answer

   Answer: C

   Explanation

   https://docs.aws.amazon.com/redshift/latest/dg/r_VACUUM_command.html "A full vacuum doesn't perform a reindex for interleaved tables. To reindex interleaved tables followed by a full vacuum, use the VACUUM REINDEX option." A - "A full vacuum doesn't perform a reindex for interleaved tables."- from the docs above B- "A DELETE ONLY vacuum operation doesn't sort table data." - from the docs above D - "without reclaiming space freed by deleted rows. " - from the docs above

   Source: Adapted from community discussion — GiorgioGss

   References

   • AWS documentation

   Next question

7. Question 167 · 313 in bank

   A manufacturing company wants to collect data from sensors. A data engineer needs to implement a solution that ingests sensor data in near real time. The solution must store the data to a persistent data store. The solution must store the data in nested JSON format. The company must have the ability to query from the data store with a latency of less than 10 milliseconds. Which solution will meet these requirements with the LEAST operational overhead?

   • AUse a self-hosted Apache Kafka cluster to capture the sensor data. Store the data in Amazon S3 for querying.
   • BUse AWS Lambda to process the sensor data. Store the data in Amazon S3 for querying.
   • CUse Amazon Kinesis Data Streams to capture the sensor data. Store the data in Amazon DynamoDB for querying.
   • DUse Amazon Simple Queue Service (Amazon SQS) to buffer incoming sensor data. Use AWS Glue to store the data in Amazon RDS for querying.

   Show answer

   Answer: C

   Explanation

   Option C is the best solution to meet the requirements with the least operational overhead: Use Amazon Kinesis Data Streams to ingest real-time sensor data Store the nested JSON data in Amazon DynamoDB for low latency queries The key advantages of Option C are: Kinesis Data Streams fully manages real-time data ingestion with auto-scaling and persistence DynamoDB provides single digit millisecond latency for queries DynamoDB natively supports nested JSON data models Fully managed services minimize operational overhead In contrast: Option A requires managing Kafka clusters Option B uses Lambda which can't provide persistent storage Option D requires integrating SQS, Glue, and RDS leading to complexity

   Source: Adapted from community discussion — Felix_G

   Next question

8. Question 168 · 313 in bank

   A company stores data in a data lake that is in Amazon S3. Some data that the company stores in the data lake contains personally identifiable information (PII). Multiple user groups need to access the raw data. The company must ensure that user groups can access only the PII that they require. Which solution will meet these requirements with the LEAST effort?

   • AUse Amazon Athena to query the data. Set up AWS Lake Formation and create data filters to establish levels of access for the company's IAM roles. Assign each user to the IAM role that matches the user's PII access requirements.
   • BUse Amazon QuickSight to access the data. Use column-level security features in QuickSight to limit the PII that users can retrieve from Amazon S3 by using Amazon Athena. Define QuickSight access levels based on the PII access requirements of the users.
   • CBuild a custom query builder UI that will run Athena queries in the background to access the data. Create user groups in Amazon Cognito. Assign access levels to the user groups based on the PII access requirements of the users.
   • DCreate IAM roles that have different levels of granular access. Assign the IAM roles to IAM user groups. Use an identity-based policy to assign access levels to user groups at the column level.

   Show answer

   Answer: A

   Explanation

   Option A, using Amazon Athena with AWS Lake Formation, is the most suitable solution. Lake Formation is designed to provide fine-grained access control to data lakes stored in S3 and integrates well with Athena, thereby meeting the requirements with the least effort. https://aws.amazon.com/blogs/big-data/anonymize-and-manage-data-in-your-data-lake-with-amazon-athena-and-aws-lake-formation/

   Source: Adapted from community discussion — rralucard_

   References

   • AWS documentation

   Next question

9. Question 169 · 313 in bank

   Select 2

   A data engineer must build an extract, transform, and load (ETL) pipeline to process and load data from 10 source systems into 10 tables that are in an Amazon Redshift database. All the source systems generate .csv, JSON, or Apache Parquet files every 15 minutes. The source systems all deliver files into one Amazon S3 bucket. The file sizes range from 10 MB to 20 GB. The ETL pipeline must function correctly despite changes to the data schema. Which data pipeline solutions will meet these requirements? (Choose two.)

   • AUse an Amazon EventBridge rule to run an AWS Glue job every 15 minutes. Configure the AWS Glue job to process and load the data into the Amazon Redshift tables.
   • BUse an Amazon EventBridge rule to invoke an AWS Glue workflow job every 15 minutes. Configure the AWS Glue workflow to have an on-demand trigger that runs an AWS Glue crawler and then runs an AWS Glue job when the crawler finishes running successfully. Configure the AWS Glue job to process and load the data into the Amazon Redshift tables.
   • CConfigure an AWS Lambda function to invoke an AWS Glue crawler when a file is loaded into the S3 bucket. Configure an AWS Glue job to process and load the data into the Amazon Redshift tables. Create a second Lambda function to run the AWS Glue job. Create an Amazon EventBridge rule to invoke the second Lambda function when the AWS Glue crawler finishes running successfully.
   • DConfigure an AWS Lambda function to invoke an AWS Glue workflow when a file is loaded into the S3 bucket. Configure the AWS Glue workflow to have an on-demand trigger that runs an AWS Glue crawler and then runs an AWS Glue job when the crawler finishes running successfully. Configure the AWS Glue job to process and load the data into the Amazon Redshift tables.
   • EConfigure an AWS Lambda function to invoke an AWS Glue job when a file is loaded into the S3 bucket. Configure the AWS Glue job to read the files from the S3 bucket into an Apache Spark DataFrame. Configure the AWS Glue job to also put smaller partitions of the DataFrame into an Amazon Kinesis Data Firehose delivery stream. Configure the delivery stream to load data into the Amazon Redshift tables.

   Show answer

   Answer: BD

   Explanation

   Option B: Amazon EventBridge Rule with AWS Glue Workflow Job Every 15 Minutes - for its streamlined process, automated scheduling, and ability to handle schema changes. Option D: AWS Lambda to Invoke AWS Glue Workflow When a File is Loaded - for its responsiveness to file arrival and adaptability to schema changes, though it is slightly more complex than option B.

   Source: Adapted from community discussion — rralucard_

   Next question

10. Question 170 · 313 in bank

    A financial company wants to use Amazon Athena to run on-demand SQL queries on a petabyte-scale dataset to support a business intelligence (BI) application. An AWS Glue job that runs during non-business hours updates the dataset once every day. The BI application has a standard data refresh frequency of 1 hour to comply with company policies. A data engineer wants to cost optimize the company's use of Amazon Athena without adding any additional infrastructure costs. Which solution will meet these requirements with the LEAST operational overhead?

    • AConfigure an Amazon S3 Lifecycle policy to move data to the S3 Glacier Deep Archive storage class after 1 day.
    • BUse the query result reuse feature of Amazon Athena for the SQL queries.
    • CAdd an Amazon ElastiCache cluster between the BI application and Athena.
    • DChange the format of the files that are in the dataset to Apache Parquet.

    Show answer

    Answer: B

    Explanation

    https://docs.aws.amazon.com/athena/latest/ug/performance-tuning.html Use the Query Result Reuse Feature of Amazon Athena. This leverages Athena's built-in feature to reduce redundant data scans and thus lowers query costs.

    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 161. 313 questions available.