DEA-C01 practice questions 181–190

1. Question 181 · 313 in bank

   A company stores datasets in JSON format and .csv format in an Amazon S3 bucket. The company has Amazon RDS for Microsoft SQL Server databases, Amazon DynamoDB tables that are in provisioned capacity mode, and an Amazon Redshift cluster. A data engineering team must develop a solution that will give data scientists the ability to query all data sources by using syntax similar to SQL. Which solution will meet these requirements with the LEAST operational overhead?

   • AUse AWS Glue to crawl the data sources. Store metadata in the AWS Glue Data Catalog. Use Amazon Athena to query the data. Use SQL for structured data sources. Use PartiQL for data that is stored in JSON format.
   • BUse AWS Glue to crawl the data sources. Store metadata in the AWS Glue Data Catalog. Use Redshift Spectrum to query the data. Use SQL for structured data sources. Use PartiQL for data that is stored in JSON format.
   • CUse AWS Glue to crawl the data sources. Store metadata in the AWS Glue Data Catalog. Use AWS Glue jobs to transform data that is in JSON format to Apache Parquet or .csv format. Store the transformed data in an S3 bucket. Use Amazon Athena to query the original and transformed data from the S3 bucket.
   • DUse AWS Lake Formation to create a data lake. Use Lake Formation jobs to transform the data from all data sources to Apache Parquet format. Store the transformed data in an S3 bucket. Use Amazon Athena or Redshift Spectrum to query the data.

   Show answer

   Answer: A

   Explanation

   A. Unified Querying with Athena: Athena provides a SQL-like interface for querying various data sources, including JSON and CSV in S3, as well as traditional databases. PartiQL Support: Athena's PartiQL extension allows querying semi-structured JSON data directly, eliminating the need for a separate query engine. Serverless and Managed: Both AWS Glue and Athena are serverless, minimizing infrastructure management for the data engineers. No Unnecessary Transformations: Avoiding transformations for JSON data simplifies the pipeline and reduces operational overhead. B. Redshift Spectrum: While Spectrum can query external data, it's primarily intended for Redshift data warehouse extensions. It adds complexity for the RDS and DynamoDB data sources.

   Source: Adapted from community discussion — Christina666

   References

   • AWS documentation

   Next question

2. Question 182 · 313 in bank

   A data engineer is configuring Amazon SageMaker Studio to use AWS Glue interactive sessions to prepare data for machine learning (ML) models. The data engineer receives an access denied error when the data engineer tries to prepare the data by using SageMaker Studio. Which change should the engineer make to gain access to SageMaker Studio?

   • AAdd the AWSGlueServiceRole managed policy to the data engineer's IAM user.
   • BAdd a policy to the data engineer's IAM user that includes the sts:AssumeRole action for the AWS Glue and SageMaker service principals in the trust policy.
   • CAdd the AmazonSageMakerFullAccess managed policy to the data engineer's IAM user.
   • DAdd a policy to the data engineer's IAM user that allows the sts:AddAssociation action for the AWS Glue and SageMaker service principals in the trust policy.

   Show answer

   Answer: B

   Explanation

   B. Add a policy to the data engineer’s IAM user that includes the sts:AssumeRole action for the AWS Glue and SageMaker service principals in the trust policy. • This is the most appropriate solution. The sts:AssumeRole action allows the data engineer’s IAM user to assume a role that has the necessary permissions for both AWS Glue and SageMaker. This is a common approach for granting cross-service access in AWS.

   Source: Adapted from community discussion — atu1789

   References

   • AWS documentation
   • repost.aws documentation
   • AWS documentation

   Next question

3. Question 183 · 313 in bank

   A company extracts approximately 1 TB of data every day from data sources such as SAP HANA, Microsoft SQL Server, MongoDB, Apache Kafka, and Amazon DynamoDB. Some of the data sources have undefined data schemas or data schemas that change. A data engineer must implement a solution that can detect the schema for these data sources. The solution must extract, transform, and load the data to an Amazon S3 bucket. The company has a service level agreement (SLA) to load the data into the S3 bucket within 15 minutes of data creation. Which solution will meet these requirements with the LEAST operational overhead?

   • AUse Amazon EMR to detect the schema and to extract, transform, and load the data into the S3 bucket. Create a pipeline in Apache Spark.
   • BUse AWS Glue to detect the schema and to extract, transform, and load the data into the S3 bucket. Create a pipeline in Apache Spark.
   • CCreate a PySpark program in AWS Lambda to extract, transform, and load the data into the S3 bucket.
   • DCreate a stored procedure in Amazon Redshift to detect the schema and to extract, transform, and load the data into a Redshift Spectrum table. Access the table from Amazon S3.

   Show answer

   Answer: B

   Explanation

   B. Use AWS Glue to detect the schema and to extract, transform, and load the data into the S3 bucket. Create a pipeline in Apache Spark.

   Source: Adapted from community discussion — rralucard_

   Next question

4. Question 184 · 313 in bank

   A company has multiple applications that use datasets that are stored in an Amazon S3 bucket. The company has an ecommerce application that generates a dataset that contains personally identifiable information (PII). The company has an internal analytics application that does not require access to the PII. To comply with regulations, the company must not share PII unnecessarily. A data engineer needs to implement a solution that with redact PII dynamically, based on the needs of each application that accesses the dataset. Which solution will meet the requirements with the LEAST operational overhead?

   • ACreate an S3 bucket policy to limit the access each application has. Create multiple copies of the dataset. Give each dataset copy the appropriate level of redaction for the needs of the application that accesses the copy.
   • BCreate an S3 Object Lambda endpoint. Use the S3 Object Lambda endpoint to read data from the S3 bucket. Implement redaction logic within an S3 Object Lambda function to dynamically redact PII based on the needs of each application that accesses the data.
   • CUse AWS Glue to transform the data for each application. Create multiple copies of the dataset. Give each dataset copy the appropriate level of redaction for the needs of the application that accesses the copy.
   • DCreate an API Gateway endpoint that has custom authorizers. Use the API Gateway endpoint to read data from the S3 bucket. Initiate a REST API call to dynamically redact PII based on the needs of each application that accesses the data.

   Show answer

   Answer: B

   Explanation

   Ans. B You can use an Amazon S3 Object Lambda Access Point to control access to documents with personally identifiable information (PII). https://docs.aws.amazon.com/comprehend/latest/dg/using-access-points.html

   Source: Adapted from community discussion — damaldon

   References

   • AWS documentation
   • AWS documentation

   Next question

5. Question 185 · 313 in bank

   A company uses Amazon Athena for one-time queries against data that is in Amazon S3. The company has several use cases. The company must implement permission controls to separate query processes and access to query history among users, teams, and applications that are in the same AWS account. Which solution will meet these requirements?

   • ACreate an S3 bucket for each use case. Create an S3 bucket policy that grants permissions to appropriate individual IAM users. Apply the S3 bucket policy to the S3 bucket.
   • BCreate an Athena workgroup for each use case. Apply tags to the workgroup. Create an IAM policy that uses the tags to apply appropriate permissions to the workgroup.
   • CCreate an IAM role for each use case. Assign appropriate permissions to the role for each use case. Associate the role with Athena.
   • DCreate an AWS Glue Data Catalog resource policy that grants permissions to appropriate individual IAM users for each use case. Apply the resource policy to the specific tables that Athena uses.

   Show answer

   Answer: B

   Explanation

   B. Create an Athena workgroup for each use case. Apply tags to the workgroup. Create an IAM policy that uses the tags to apply appropriate permissions to the workgroup. Explanation: Athena workgroups allow you to isolate and manage different workloads, users, and permissions. By creating a separate workgroup for each use case, you can control access to query history, manage permissions, and enforce resource usage limits independently for each workload. Applying tags to workgroups allows you to categorize and organize them based on the use case, which simplifies policy management.

   Source: Adapted from community discussion — TonyStark0122

   References

   • AWS documentation

   Next question

6. Question 186 · 313 in bank

   A data engineer needs to build an extract, transform, and load (ETL) job. The ETL job will process daily incoming .csv files that users upload to an Amazon S3 bucket. The size of each S3 object is less than 100 MB. Which solution will meet these requirements MOST cost-effectively?

   • AWrite a custom Python application. Host the application on an Amazon Elastic Kubernetes Service (Amazon EKS) cluster.
   • BWrite a PySpark ETL script. Host the script on an Amazon EMR cluster.
   • CWrite an AWS Glue PySpark job. Use Apache Spark to transform the data.
   • DWrite an AWS Glue Python shell job. Use pandas to transform the data.

   Show answer

   Answer: D

   Explanation

   Option D: Write an AWS Glue Python shell job and use pandas to transform the data, is the most cost-effective solution for the described scenario. AWS Glue’s Python shell jobs are a good fit for smaller-scale ETL tasks, especially when dealing with .csv files that are less than 100 MB each. The use of pandas, a powerful and efficient data manipulation library in Python, makes it an ideal tool for processing and transforming these types of files. This approach avoids the overhead and additional costs associated with more complex solutions like Amazon EKS or EMR, which are generally more suited for larger-scale, more complex data processing tasks. Given the requirements – processing daily incoming small-sized .csv files – this solution provides the necessary functionality with minimal resources, aligning well with the goal of cost-effectiveness.

   Source: Adapted from community discussion — atu1789

   References

   • AWS documentation

   Next question

7. Question 187 · 313 in bank

   A data engineer creates an AWS Glue Data Catalog table by using an AWS Glue crawler that is named Orders. The data engineer wants to add the following new partitions: s3://transactions/orders/order_date=2023-01-01 s3://transactions/orders/order_date=2023-01-02 The data engineer must edit the metadata to include the new partitions in the table without scanning all the folders and files in the location of the table. Which data definition language (DDL) statement should the data engineer use in Amazon Athena?

   • AALTER TABLE Orders ADD PARTITION(order_date=’2023-01-01’) LOCATION ‘s3://transactions/orders/order_date=2023-01-01’; ALTER TABLE Orders ADD PARTITION(order_date=’2023-01-02’) LOCATION ‘s3://transactions/orders/order_date=2023-01-02’;
   • BMSCK REPAIR TABLE Orders;
   • CREPAIR TABLE Orders;
   • DALTER TABLE Orders MODIFY PARTITION(order_date=’2023-01-01’) LOCATION ‘s3://transactions/orders/2023-01-01’; ALTER TABLE Orders MODIFY PARTITION(order_date=’2023-01-02’) LOCATION ‘s3://transactions/orders/2023-01-02’;

   Show answer

   Answer: A

   Explanation

   Why the Other Options Are Incorrect: Option B: MSCK REPAIR TABLE Orders: This command is used to repair the partitions of a table by scanning all the files in the specified location. This is not efficient if you know the specific partitions you want to add, as it will scan the entire table location. Option C: REPAIR TABLE Orders: This is not a valid Athena DDL command. Option D: ALTER TABLE Orders MODIFY PARTITION: This command is used to modify the location of existing partitions, not to add new partitions. It would not work for adding new partitions.

   Source: Adapted from community discussion — Ja13

   References

   • AWS documentation

   Next question

8. Question 188 · 313 in bank

   A company stores 10 to 15 TB of uncompressed .csv files in Amazon S3. The company is evaluating Amazon Athena as a one-time query engine. The company wants to transform the data to optimize query runtime and storage costs. Which file format and compression solution will meet these requirements for Athena queries?

   • A.csv format compressed with zip
   • BJSON format compressed with bzip2
   • CApache Parquet format compressed with Snappy
   • DApache Avro format compressed with LZO

   Show answer

   Answer: C

   Explanation

   Parquet provides efficient columnar storage, enabling Athena to read only the necessary data for queries, which reduces scan times and speeds up query performance. Snappy compression offers a good balance between compression speed and efficiency, reducing storage costs without significantly impacting query times.

   Source: Adapted from community discussion — tgv

   Next question

9. Question 189 · 313 in bank

   A company uses Apache Airflow to orchestrate the company's current on-premises data pipelines. The company runs SQL data quality check tasks as part of the pipelines. The company wants to migrate the pipelines to AWS and to use AWS managed services. Which solution will meet these requirements with the LEAST amount of refactoring?

   • ASetup AWS Outposts in the AWS Region that is nearest to the location where the company uses Airflow. Migrate the servers into Outposts hosted Amazon EC2 instances. Update the pipelines to interact with the Outposts hosted EC2 instances instead of the on-premises pipelines.
   • BCreate a custom Amazon Machine Image (AMI) that contains the Airflow application and the code that the company needs to migrate. Use the custom AMI to deploy Amazon EC2 instances. Update the network connections to interact with the newly deployed EC2 instances.
   • CMigrate the existing Airflow orchestration configuration into Amazon Managed Workflows for Apache Airflow (Amazon MWAA). Create the data quality checks during the ingestion to validate the data quality by using SQL tasks in Airflow.
   • DConvert the pipelines to AWS Step Functions workflows. Recreate the data quality checks in SQL as Python based AWS Lambda functions.

   Show answer

   Answer: C

   Explanation

   The solution that will meet these requirements with the least amount of refactoring is Option C: Migrate the existing Airflow orchestration configuration into Amazon Managed Workflows for Apache Airflow (Amazon MWAA). Create the data quality checks during the ingestion to validate the data quality by using SQL tasks in Airflow. Amazon Managed Workflows for Apache Airflow (MWAA) is a fully managed service that makes it easy to run open-source versions of Apache Airflow on AWS. It allows you to build workflows to design and visualize pipelines, automate complex tasks, and monitor executions. Since the company is already using Apache Airflow for orchestration, migrating to Amazon MWAA would require minimal refactoring.

   Source: Adapted from community discussion — bakarys

   Next question

10. Question 190 · 313 in bank

    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?

    • AAmazon EventBridge
    • BAmazon Managed Workflows for Apache Airflow (Amazon MWAA)
    • CAWS Step Functions
    • DAWS Glue Workflows

    Show answer

    Answer: C

    Explanation

    B is not cost effective, D is only to orchestrate Glue Jobs and Crawlers within AWS Glue itself. Hence C is correct, Step functions is cost effective and can link together your different AWS services.

    Source: Adapted from community discussion — chrispchrisp

    Next question

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

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