Amazon DATA-ENGINEER-ASSOCIATE Online Practice Questions and Exam Preparation

Amazon DATA-ENGINEER-ASSOCIATE Online Questions & Answers

• Question 371:

  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?

  A. AWS Step Functions
  B. AWS Glue workflows
  C. AWS Glue Studio
  D. Amazon Managed workflows for Apache Airflow (Amazon MWAA)
  B. AWS Glue workflows

  ---

  Explanation

  AWS Glue workflows are a cost-effective way to orchestrate complex ETL jobs that involve multiple crawlers, jobs, and triggers. AWS Glue workflows allow you to visually monitor the progress and dependencies of your ETL tasks, and automatically handle errors and retries. AWS Glue workflows also integrate with other AWS services, such as Amazon S3, Amazon Redshift, and AWS Lambda, among others, enabling you to leverage these services for your data processing workflows. AWS Glue workflows are serverless, meaning you only pay for the resources you use, and you don't have to manage any infrastructure.

  AWS Step Functions, AWS Glue Studio, and Amazon MWAA are also possible options for orchestrating ETL pipelines, but they have some drawbacks compared to AWS Glue workflows. AWS Step Functions is a serverless function orchestrator that can handle different types of data processing, such as real-time, batch, and stream processing. However, AWS Step Functions requires you to write code to define your state machines, which can be complex and error-prone.

  AWS Step Functions also charges you for every state transition, which can add up quickly for large-scale

  ETL pipelines.

  AWS Glue Studio is a graphical interface that allows you to create and run AWS Glue ETL jobs without writing code. AWS Glue Studio simplifies the process of building, debugging, and monitoring your ETL jobs, and provides a range of pre-built transformations and connectors. However, AWS Glue Studio does not support workflows, meaning you cannot orchestrate multiple ETL jobs or crawlers with dependencies and triggers. AWS Glue Studio also does not support streaming data sources or targets, which limits its use cases for real-time data processing.

  Amazon MWAA is a fully managed service that makes it easy to run open-source versions of Apache Airflow on AWS and build workflows to run your ETL jobs and data pipelines. Amazon MWAA provides a familiar and flexible environment for data engineers who are familiar with Apache Airflow, and integrates with a range of AWS services such as Amazon EMR, AWS Glue, and AWS Step Functions. However, Amazon MWAA is not serverless, meaning you have to provision and pay for the resources you need, regardless of your usage. Amazon MWAA also requires you to write code to define your DAGs, which can be challenging and time-consuming for complex ETL pipelines.

• Question 372:

  A company has a data processing pipeline that runs multiple SQL queries in sequence against an Amazon Redshift cluster. The company merges with a second company. The original company modifies a query that aggregates sales revenue data to join sales tables from both companies. The sales table for the first company is named Table S1. The sales table for the second company is named Table S2. Table S1 contains 10 billion records. Table S2 contains 900 million records.

  The query becomes slow after the modification. A data engineer must improve the query performance.

  Which solutions will meet these requirements? (Choose two.)

  A. Use the KEY distribution style for both sales tables. Select a low cardinality column to use for the join.
  B. Use the KEY distribution style for both sales tables. Select a high cardinality column to use for the join.
  C. Use the EVEN distribution style for Table S1. Use the ALL distribution style for Table S2.
  D. Use the Amazon Redshift query optimizer to review and select optimizations to implement.
  E. Use Amazon Redshift Advisor to review and select optimizations to implement.
  B. Use the KEY distribution style for both sales tables. Select a high cardinality column to use for the join.
  E. Use Amazon Redshift Advisor to review and select optimizations to implement.

  ---

  Explanation

  Choosing KEY distribution on both tables with a high-cardinality join column colocates matching rows across nodes and avoids data skew, improving join performance. Redshift Advisor provides automated, actionable recommendations (e.g., distribution and sort keys, stats) to further optimize the slow query with minimal effort.

• Question 373:

  A company wants to run SQL analytics across operational data and historical S3 data without copying all operational rows into the warehouse first. The data engineer is evaluating Amazon Redshift capabilities.

  Which capabilities can help meet this requirement? (Choose two.)

  A. Use Amazon Redshift federated queries to query supported operational databases.
  B. Use Amazon Redshift Spectrum to query data stored in Amazon S3.
  C. Use S3 Transfer Acceleration to rewrite Redshift table blocks.
  D. Use DynamoDB TTL to expose historical S3 partitions to Redshift.
  E. Use Amazon SNS topics as external tables.
  A. Use Amazon Redshift federated queries to query supported operational databases.
  B. Use Amazon Redshift Spectrum to query data stored in Amazon S3.

  ---

  Explanation

  Redshift federated queries can access supported operational databases, and Redshift Spectrum can query external data in Amazon S3. S3 Transfer Acceleration speeds some transfers but does not rewrite Redshift storage or provide query federation. DynamoDB TTL handles item expiration. SNS is a notification service, not an external table source for Redshift analytics.

• Question 374:

  A data engineer needs to build an enterprise data catalog based on the company's Amazon S3 buckets and Amazon RDS databases. The data catalog must include storage format metadata for the data in the catalog.

  Which solution will meet these requirements with the LEAST effort?

  A. Use an AWS Glue crawler to scan the S3 buckets and RDS databases and build a data catalog. Use data stewards to inspect the data and update the data catalog with the data format.
  B. Use an AWS Glue crawler to build a data catalog. Use AWS Glue crawler classifiers to recognize the format of data and store the format in the catalog.
  C. Use Amazon Macie to build a data catalog and to identify sensitive data elements. Collect the data format information from Macie.
  D. Use scripts to scan data elements and to assign data classifications based on the format of the data.
  B. Use an AWS Glue crawler to build a data catalog. Use AWS Glue crawler classifiers to recognize the format of data and store the format in the catalog.

• Question 375:

  A company is building an inventory management system and an inventory reordering system to automatically reorder products. Both systems use Amazon Kinesis Data Streams. The inventorymanagement system uses the Amazon Kinesis Producer Library (KPL) to publish data to a stream. The inventory reordering system uses the Amazon Kinesis Client Library (KCL) to consume data from the stream. The company configures the stream to scale up and down as needed.

  Before the company deploys the systems to production, the company discovers that the inventory reordering system received duplicated data.

  Which factors could have caused the reordering system to receive duplicated data? (Choose Two.)

  A. The producer experienced network-related timeouts.
  B. The stream's value for the IteratorAgeMilliseconds metric was too high.
  C. There was a change in the number of shards, record processors, or both.
  D. The AggregationEnabled configuration property was set to true.
  E. The max_records configuration property was set to a number that was too high.
  A. The producer experienced network-related timeouts.
  C. There was a change in the number of shards, record processors, or both.

  ---

  Explanation

  Problem Analysis:

  The company uses Kinesis Data Streams for both inventory management and reordering.

  The Kinesis Producer Library (KPL) publishes data, and the Kinesis Client Library (KCL) consumes data.

  Duplicate records were observed in the inventory reordering system.

  Key Considerations:

  Kinesis streams are designed for durability but may produce duplicates under certain conditions.

  Factors such as network timeouts shard splits , , or changes in record processors can cause duplication.

  Solution Analysis:

  Option A: Network-Related Timeouts

  If the producer (KPL) experiences network timeouts, it retries data submission, potentially causing duplicates.

  Option B: High IteratorAgeMilliseconds

  High iterator age suggests delays in processing but does not directly cause duplication.

  Option C: Changes in Shards or Processors Changes in the number of shards or record processors can lead to re-processing of records, causing duplication.

  Option D: AggregationEnabled Set to True AggregationEnabled controls the aggregation of multiple records into one, but it does not cause duplication.

  Option E: High max_records Value

  A high max_records value increases batch size but does not lead to duplication.

  Final Recommendation:

  Network-related timeoutsand changes in shards or processors are the most likely causes of duplicate data in this scenario.

  References:

  Amazon Kinesis Data Streams Best Practices Kinesis Producer Library (KPL) Overview Kinesis Client Library (KCL) Overview

• Question 376:

  A company is designing a serverless data processing workflow in AWS Step Functions that involves multiple steps. The processing workflow ingests data from an external API, transforms the data by using multiple AWS Lambda functions, and loads the transformed data into Amazon DynamoDB.

  The company needs the workflow to perform specific steps based on the content of the incoming data.

  Which Step Functions state type should the company use to meet this requirement?

  A. Parallel
  B. Choice
  C. Task
  D. Map
  B. Choice

  ---

  Explanation

  The Choice state in AWS Step Functions is designed to enable conditional branching within a workflow. It allows the workflow to evaluate data and make decisions based on specific conditions. This makes it ideal for cases where certain steps in the workflow need to be executed based on the content of the incoming data.

• Question 377:

  A media company wants to use Amazon OpenSearch Service to analyze rea-time data about popular musical artists and songs. The company expects to ingest millions of new data events every day. The new data events will arrive through an Amazon Kinesis data stream. The company must transform the data and then ingest the data into the OpenSearch Service domain.

  Which method should the company use to ingest the data with the LEAST operational overhead?

  A. Use Amazon Kinesis Data Firehose and an AWS Lambda function to transform the data and deliver the transformed data to OpenSearch Service.
  B. Use a Logstash pipeline that has prebuilt filters to transform the data and deliver the transformed data to OpenSearch Service.
  C. Use an AWS Lambda function to call the Amazon Kinesis Agent to transform the data and deliver the transformed data OpenSearch Service.
  D. Use the Kinesis Client Library (KCL) to transform the data and deliver the transformed data to OpenSearch Service.
  A. Use Amazon Kinesis Data Firehose and an AWS Lambda function to transform the data and deliver the transformed data to OpenSearch Service.

• Question 378:

  A company receives a daily file that contains customer data in .xls format. The company stores the file in Amazon S3. The daily file is approximately 2 GB in size.

  A data engineer concatenates the column in the file that contains customer first names and the column that contains customer last names. The data engineer needs to determine the number of distinct customers in the file.

  Which solution will meet this requirement with the LEAST operational effort?

  A. Create and run an Apache Spark job in an AWS Glue notebook. Configure the job to read the S3 file and calculate the number of distinct customers.
  B. Create an AWS Glue crawler to create an AWS Glue Data Catalog of the S3 file. Run SQL queries from Amazon Athena to calculate the number of distinct customers.
  C. Create and run an Apache Spark job in Amazon EMR Serverless to calculate the number of distinct customers.
  D. Use AWS Glue DataBrew to create a recipe that uses the COUNT_DISTINCT aggregate function to calculate the number of distinct customers.
  D. Use AWS Glue DataBrew to create a recipe that uses the COUNT_DISTINCT aggregate function to calculate the number of distinct customers.

  ---

  Explanation

  AWS Glue DataBrew is a visual data preparation tool that allows you to clean, normalize, and transform data without writing code. You can use DataBrew to create recipes that define the steps to apply to your data, such as filtering, renaming, splitting, or aggregating columns. You can also use DataBrew to run jobs that execute the recipes on your data sources, such as Amazon S3, Amazon Redshift, or Amazon Aurora.

  DataBrew integrates with AWS Glue Data Catalog, which is a centralized metadata repository for your data assets.

  The solution that meets the requirement with the least operational effort is to use AWS Glue DataBrew to create a recipe that uses the COUNT_DISTINCT aggregate function to calculate the number of distinct customers. This solution has the following advantages: It does not require you to write any code, as DataBrew provides a graphical user interface that lets you explore, transform, and visualize your data. You can use DataBrew to concatenate the columns that contain customer first names and last names, and then use the COUNT_DISTINCT aggregate function to count the number of unique values in the resulting column.

  It does not require you to provision, manage, or scale any servers, clusters, or notebooks, as DataBrew is a fully managed service that handles all the infrastructure foryou. DataBrew can automatically scale up or down the compute resources based on the size and complexity of your data and recipes.

  It does not require you to create or update any AWS Glue Data Catalog entries, as DataBrew can automatically create and register the data sources and targets in the Data Catalog. DataBrew can also use the existing Data Catalog entries to access the data in S3 or other sources.

  Option A is incorrect because it suggests creating and running an Apache Spark job in an AWS Glue notebook. This solution has the following disadvantages: It requires you to write code, as AWS Glue notebooks are interactive development environments that allow you to write, test, and debug Apache Spark code using Python or Scala. You need to use the Spark SQL or the Spark DataFrame API to read the S3 file and calculate the number of distinct customers.

  It requires you to provision and manage a development endpoint, which is a serverless Apache Spark environment that you can connect to your notebook. You need to specify the type and number of workers for your development endpoint, and monitor its status and metrics.

  It requires you to create or update the AWS Glue Data Catalog entries for the S3 file, either manually or using a crawler. You need to use the Data Catalog as a metadata store for your Spark job, and specify the database and table names in your code.

  Option B is incorrect because it suggests creating an AWS Glue crawler to create an AWS Glue Data Catalog of the S3 file, and running SQL queries from Amazon Athena to calculate the number of distinct customers.

  This solution has the following disadvantages: It requires you to create and run a crawler, which is a program that connects to your data store, progresses through a prioritized list of classifiers to determine the schema for your data, and then creates metadata tables in the Data Catalog.

  You need to specify the data store, the IAM role, the schedule, and the output database for your crawler.

  It requires you to write SQL queries, as Amazon Athena is a serverless interactive query service that allows you to analyze data in S3 using standard SQL. You need to use Athena to concatenate the columns that contain customer first names and last names, and then use the COUNT(DISTINCT) aggregate function to count the number of unique values in the resulting column. Option C is incorrect because it suggests creating and running an Apache Spark job in Amazon EMR Serverless to calculate the number of distinct customers. This solution has the following disadvantages: It requires you to write code, as Amazon EMR Serverless is a service that allows you to run Apache Spark jobs on AWS without provisioning or managing any infrastructure. You need to use the Spark SQL or the Spark DataFrame API to read the S3 file and calculate the number of distinct customers.

  It requires you to create and manage an Amazon EMR Serverless cluster, which is a fully managed and scalable Spark environment that runs on AWS Fargate. You need to specify the cluster name, the IAM role, the VPC, and the subnet for your cluster, and monitor its status and metrics.

  It requires you to create or update the AWS Glue Data Catalog entries for the S3 file, either manually or using a crawler. You need to use the Data Catalog as a metadata store for your Spark job, and specify the database and table names in your code.

  References:

  1: AWS Glue DataBrew - Features

  2: Working with recipes - AWS Glue DataBrew

  3: Working with data sources and data targets - AWS Glue DataBrew [4]: AWS Glue notebooks - AWS Glue [5]: Development endpoints - AWS Glue [6]: Populating the AWS Glue Data Catalog - AWS Glue [7]: Crawlers - AWS Glue [8]: Amazon Athena - Features [9]: Amazon EMR Serverless - Features [10]: Creating an Amazon EMR Serverless cluster - Amazon EMR [11]: Using the AWS Glue Data Catalog with Amazon EMR Serverless - Amazon EMR

• Question 379:

  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?

  A. Configure 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.
  B. Create 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.
  C. Configure 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.
  D. Create 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.
  D. Create 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.

  ---

  Explanation

  This solution meets the requirement of logging all writes to the S3 bucket into another S3 bucket with the least operational effort. AWS CloudTrail is a service that records the API calls made to AWS services, including Amazon S3. By creating a trail of data events, you can capture the details of the requests that are made to the transactions S3 bucket, such as the requester, the time, the IP address, and the response elements. By specifying an empty prefix and write-only events, you can filter the data events to only include the ones that write to the bucket. By specifying the logs S3 bucket as the destination bucket, you can store the CloudTrail logs in another S3 bucket that is in the same AWS Region. This solution does not require any additional coding or configuration, and it is more scalable and reliable than using S3 Event Notifications and Lambda functions.

  References:

  Logging Amazon S3 API calls using AWS CloudTrail Creating a trail for data events Enabling Amazon S3 server access logging

• Question 380:

  A company has a business intelligence platform on AWS. The company uses an AWS Storage Gateway Amazon S3 File Gateway to transfer les from the company's on-premises environment to an Amazon S3 bucket.

  A data engineer needs to setup a process that will automatically launch an AWS Glue workflow to run a series of AWS Glue jobs when each file transfer finishes successfully.

  Which solution will meet these requirements with the LEAST operational overhead?

  A. Determine when the file transfers usually finish based on previous successful file transfers. Set up an Amazon EventBridge scheduled event to initiate the AWS Glue jobs at that time of day.
  B. Set up an Amazon EventBridge event that initiates the AWS Glue workflow after every successful S3 File Gateway file transfer event.
  C. Set up an on-demand AWS Glue workflow so that the data engineer can start the AWS Glue workflow when each file transfer is complete.
  D. Set up an AWS Lambda function that will invoke the AWS Glue Workflow. Set up an event for the creation of an S3 object as a trigger for the Lambda function.
  B. Set up an Amazon EventBridge event that initiates the AWS Glue workflow after every successful S3 File Gateway file transfer event.