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

Amazon DATA-ENGINEER-ASSOCIATE Online Questions & Answers

• Question 1:

  A company wants to share an Amazon Redshift dataset from a producer workgroup to a consumer workgroup in another AWS account. The company wants consumers to query current data without copying the tables.

  Which steps are part of a Redshift data sharing solution? (Choose two.)

  A. Create a datashare that includes the required database objects in the producer workgroup.
  B. Authorize the consumer account or namespace to access the datashare.
  C. Export all tables to CSV files and send the files by AWS Transfer Family.
  D. Disable encryption on both workgroups before creating the datashare.
  E. Create a DynamoDB global table with the same primary key as the Redshift tables.
  A. Create a datashare that includes the required database objects in the producer workgroup.
  B. Authorize the consumer account or namespace to access the datashare.

  ---

  Explanation

  A producer creates a datashare for selected database objects and authorizes the intended consumer. This lets the consumer query shared live data without copying it. Exporting CSV files creates duplicate data and loses the live-sharing benefit. Disabling encryption is not the required pattern and can violate security requirements. DynamoDB global tables do not provide Redshift data sharing.

• Question 2:

  A healthcare company uses Amazon Kinesis Data Streams to stream real-time health data from wearable devices, hospital equipment, and patient records.

  A data engineer needs to find a solution to process the streaming data. The data engineer needs to store the data in an Amazon Redshift Serverless warehouse. The solution must support near real-time analytics of the streaming data and the previous day's data.

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

  A. Load data into Amazon Kinesis Data Firehose. Load the data into Amazon Redshift.
  B. Use the streaming ingestion feature of Amazon Redshift.
  C. Load the data into Amazon S3. Use the COPY command to load the data into Amazon Redshift.
  D. Use the Amazon Aurora zero-ETL integration with Amazon Redshift.
  B. Use the streaming ingestion feature of Amazon Redshift.

  ---

  Explanation

  The streaming ingestion feature of Amazon Redshift enables you to ingest data from streaming sources, such as Amazon Kinesis Data Streams, into Amazon Redshift tables in near real-time. You can use the streaming ingestion feature to process the streaming data from the wearable devices, hospital equipment, and patient records. The streaming ingestion feature also supports incremental updates, which means you can append new data or update existing data in the Amazon Redshift tables. This way, you can store the data in an Amazon Redshift Serverless warehouse and support near real-time analytics of the streaming data and the previous day' s data. This solution meets the requirements with the least operational overhead, as it does not require any additional services or components to ingest and process the streaming data. The other options are either not feasible or not optimal. Loading data into Amazon Kinesis Data Firehose and then into Amazon Redshift (option A) would introduce additional latency and cost, as well as require additional configuration and management. Loading data into Amazon S3 and then using the COPY command to load the data into Amazon Redshift (option C) would also introduce additional latency and cost, as well as require additional storage space and ETL logic. Using the Amazon Aurora zero-ETL integration with Amazon Redshift (option D) would not work, as it requires the data to be stored in Amazon Aurora first, which is not the case for the streaming data from the healthcare company.

• Question 3:

  A company wants to implement a data mesh with centralized governance and ETL using AWS Glue.

  Which two services should be combined?

  A. Amazon Aurora for storage and Amazon Redshift for analysis
  B. Amazon S3 for storage and Amazon Athena for analysis
  C. AWS Glue DataBrew for governance and access control
  D. AWS Lake Formation for governance and access control
  B. Amazon S3 for storage and Amazon Athena for analysis
  D. AWS Lake Formation for governance and access control

• Question 4:

  A data engineer wants to improve the performance of SQL queries in Amazon Athena that run against a sales data table.

  The data engineer wants to understand the execution plan of a specific SQL statement. The data engineer also wants to see the computational cost of each operation in a SQL query.

  Which statement does the data engineer need to run to meet these requirements?

  A. EXPLAIN SELECT * FROM sales;
  B. EXPLAIN ANALYZE FROM sales;
  C. EXPLAIN ANALYZE SELECT * FROM sales;
  D. EXPLAIN FROM sales;
  C. EXPLAIN ANALYZE SELECT * FROM sales;

• Question 5:

  A company uses an Amazon QuickSight dashboard to monitor usage of one of the company's applications.

  The company uses AWS Glue jobs to process data for the dashboard. The company stores the data in a single Amazon S3 bucket.

  The company adds new data every day.

  A data engineer discovers that dashboard queries are becoming slower over time. The data engineer determines that the root cause of the slowing queries is long-running AWS Glue jobs.

  Which actions should the data engineer take to improve the performance of the AWS Glue jobs? (Choose two.)

  A. Partition the data that is in the S3 bucket. Organize the data by year, month, and day.
  B. Increase the AWS Glue instance size by scaling up the worker type.
  C. Convert the AWS Glue schema to the DynamicFrame schema class.
  D. Adjust AWS Glue job scheduling frequency so the jobs run half as many times each day.
  E. Modify the IAM role that grants access to AWS glue to grant access to all S3 features.
  A. Partition the data that is in the S3 bucket. Organize the data by year, month, and day.
  B. Increase the AWS Glue instance size by scaling up the worker type.

  ---

  Explanation

  Partitioning the data in the S3 bucket can improve the performance of AWS Glue jobs by reducing the amount of data that needs to be scanned and processed. By organizing the data by year, month, and day, the AWS Glue job can use partition pruning to filter out irrelevant data and only read the data that matches the query criteria. This can speed up the data processing and reduce the cost of running the AWS Glue job. Increasing the AWS Glue instance size by scaling up the worker type can also improve the performance of AWS Glue jobs by providing more memory and CPU resources for the Spark execution engine. This can help the AWS Glue job handle larger data sets and complex transformations more efficiently. The other options are either incorrect or irrelevant, as they do not affect the performance of the AWS Glue jobs. Converting the AWS Glue schema to the DynamicFrame schema class does not improve the performance, but rather provides additional functionality and flexibility for data manipulation. Adjusting the AWS Glue job scheduling frequency does not improve the performance, but rather reduces the frequency of data updates. Modifying the IAM role that grants access to AWS Glue does not improve the performance, but rather affects the security and permissions of the AWS Glue service.

  References:

  Optimising Glue Scripts for Efficient Data Processing: Part 1 (Section: Partitioning Data in S3) Best practices to optimize cost and performance for AWS Glue streaming ETL jobs (Section:Development tools) Monitoring with AWS Glue job run insights (Section: Requirements)

  AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide (Chapter 5, page 133)

• Question 6:

  A company's application needs to search and analyze data in near real time. The application must handle up to 1,000 requests each second with low query latency. The company wants a solution that individual data teams can own and configure to meet each team's cost and performance optimization requirements.

  Which solution will meet these requirements?

  A. Use Amazon S3 buckets to store the data. Use Amazon Athena to query and analyze the data. Assign each data team a separate S3 bucket prefix to optimize queries.
  B. Use streams in Amazon Kinesis Data Streams and Amazon Managed Service for Apache Flink to query and analyze the data. Assign each data team a separate stream to manage and consume.
  C. Use Amazon OpenSearch Service clusters with indexing to query the data. Assign each data team a separate cluster to configure for storage and queries.
  D. Use Amazon Aurora clusters that run on Aurora I/O-Optimized instances. Assign each data team a separate Aurora cluster to configure for storage and queries.
  C. Use Amazon OpenSearch Service clusters with indexing to query the data. Assign each data team a separate cluster to configure for storage and queries.

• Question 7:

  A company stores a 100 MB dataset in an Amazon S3 bucket as an Apache Parquet file. A data engineer needs to profile the data before performing data preparation steps on the data.

  Which solution will meet this requirement in the MOST operationally efficient way?

  A. Create a profile job on the dataset in AWS Glue DataBrew. Review the profile job results.
  B. Stream the data into Amazon Managed Service for Apache Flink for SQL queries. Use the Apache Flink dashboard to profile the data.
  C. Ingest the data into Amazon Redshift Spectrum. Use SQL queries to profile the data.
  D. Load the data into an Amazon QuickSight dataset. Build a topic to profile the data with questions.
  A. Create a profile job on the dataset in AWS Glue DataBrew. Review the profile job results.

• Question 8:

  A company stores sensitive data in an Amazon Redshift table. The company needs to give specific users the ability to access the sensitive data. The company must not create duplication in the data.

  Customer support users must be able to see the last four characters of the sensitive data. Audit users must be able to see the full value of the sensitive data. No other users can have the ability to access the sensitive information.

  Which solution will meet these requirements?

  A. Create a dynamic data masking policy to allow access based on each user role. Create IAM roles that have specific access permissions. Attach the masking policy to the column that contains sensitive data.
  B. Enable metadata security on the Redshift cluster. Create IAM users and IAM roles for the customer support users and the audit users. Grant the IAM users and IAM roles permissions to view the metadata in the Redshift cluster.
  C. Create a row-level security policy to allow access based on each user role. Create IAM roles that have specific access permissions. Attach the security policy to the table.
  D. Create an AWS Glue job to redact the sensitive data and to load the data into a new Redshift table.
  A. Create a dynamic data masking policy to allow access based on each user role. Create IAM roles that have specific access permissions. Attach the masking policy to the column that contains sensitive data.

  ---

  Explanation

  Amazon Redshift's dynamic data masking lets you define masking policies on sensitive columns without duplicating data. You attach a masking policy that exposes only the last four characters to customer support IAM roles, while audit IAM roles see the full values. This meets the requirement for role-based column-level access with no data duplication.

• Question 9:

  A gaming company uses Amazon Kinesis Data Streams to collect clickstream data. The company uses Amazon Kinesis Data Firehose delivery streams to store the data in JSON format in Amazon S3. Data scientists at the company use Amazon Athena to query the most recent data to obtain business insights.

  The company wants to reduce Athena costs but does not want to recreate the data pipeline.

  Which solution will meet these requirements with the LEAST management effort?

  A. Change the Firehose output format to Apache Parquet. Provide a custom S3 object YYYYMMDD prefix expression and specify a large buffer size. For the existing data, create an AWS Glue extract, transform, and load (ETL) job. Configure the ETL job to combine small JSON files, convert the JSON files to large Parquet files, and add the YYYYMMDD prefix. Use the ALTER TABLE ADD PARTITION statement to reflect the partition on the existing Athena table.
  B. Create an Apache Spark job that combines JSON files and converts the JSON files to Apache Parquet files. Launch an Amazon EMR ephemeral cluster every day to run the Spark job to create new Parquet files in a different S3 location. Use the ALTER TABLE SET LOCATION statement to reflect the new S3 location on the existing Athena table.
  C. Create a Kinesis data stream as a delivery destination for Firehose. Use Amazon Managed Service for Apache Flink (previously known as Amazon Kinesis Data Analytics) to run Apache Flink on the Kinesis data stream. Use Flink to aggregate the data and save the data to Amazon S3 in Apache Parquet format with a custom S3 object YYYYMMDD prefix. Use the ALTER TABLE ADD PARTITION statement to reflect the partition on the existing Athena table.
  D. Integrate an AWS Lambda function with Firehose to convert source records to Apache Parquet and write them to Amazon S3. In parallel, run an AWS Glue extract, transform, and load (ETL) job to combine the JSON files and convert the JSON files to large Parquet files. Create a custom S3 object YYYYMMDD prefix. Use the ALTER TABLE ADD PARTITION statement to reflect the partition on the existing Athena table.
  A. Change the Firehose output format to Apache Parquet. Provide a custom S3 object YYYYMMDD prefix expression and specify a large buffer size. For the existing data, create an AWS Glue extract, transform, and load (ETL) job. Configure the ETL job to combine small JSON files, convert the JSON files to large Parquet files, and add the YYYYMMDD prefix. Use the ALTER TABLE ADD PARTITION statement to reflect the partition on the existing Athena table.

  ---

  Explanation

  Step 1: Understanding the Problem

  The company collects clickstream data via Amazon Kinesis Data Streams and stores it in JSON format in Amazon S3 using Kinesis Data Firehose. They use Amazon Athena to query the data, but they want to reduce Athena costs while maintaining the same data pipeline.

  Since Athena charges based on the amount of data scanned during queries, reducing the data size (by converting JSON to a more efficient format like Apache Parquet ) is a key solution to lowering costs.

  Step 2: Why Option A is Correct Option Aprovides a straightforward way to reduce costs with minimal management overhead : Changing the Firehose output format to Parquet: Parquet is a columnar data format, which is more compact and efficient than JSON for Athena queries. It significantly reduces the amount of data scanned, which in turn reduces Athena query costs.

  Custom S3 Object Prefix (YYYYMMDD): Adding a date-based prefix helps in partitioning the data, which further improves query efficiency in Athena by limiting the data scanned to only relevant partitions.

  AWS Glue ETL Job for Existing Data: To handle existing data stored in JSON format, a one-time AWS Glue ETL job can combine small JSON files, convert them to Parquet, and apply the YYYYMMDD prefix. This ensures consistency in the S3 bucket structure and allows Athena to efficiently query historical data.

  ALTER TABLE ADD PARTITION: This command updates Athena's table metadata to reflect the new partitions, ensuring that future queries target only the required data.

  Step 3: Why Other Options Are Not Ideal Option B (Apache Spark on EMR)introduces higher management effort by requiring the setup of Apache Spark jobs and an Amazon EMR cluster . While it achieves the goal of converting JSON to Parquet, it involves running and maintaining an EMR cluster, which adds operational complexity.

  Option C (Kinesis and Apache Flink)is a more complex solution involving Apache Flink , which adds a real-time streaming layer to aggregate data. Although Flink is a powerful tool for stream processing, it adds unnecessary overhead in this scenario since the company already uses Kinesis Data Firehose for batch delivery to S3.

  Option D (AWS Lambda with Firehose)suggests using AWS Lambda to convert records in real time. While Lambda can work in some cases, it's generally not the best tool for handling large-scale data transformations like JSON-to-Parquet conversion due to potential scaling and invocation limitations.

  Additionally, running parallel Glue jobs further complicates the setup.

  Step 4: How Option A Minimizes Costs

  By using Apache Parquet , Athena queries become more efficient, as Athena will scan significantly less data, directly reducing query costs. Firehosenatively supports Parquet as an output format, so enabling this conversion in Firehose requires minimal effort. Once set, new data will automatically be stored in Parquet format in S3, without requiring any custom coding or ongoing management. The AWS Glue ETL job for historical data ensures that existing JSON files are also converted to Parquet format, ensuring consistency across the data stored in S3.

  Conclusion:

  Option A meets the requirement to reduce Athena costs without recreating the data pipeline, using Firehose's native support for Apache Parquet and a simple one-time AWS Glue ETL job for existing data.

  This approach involves minimal management effort compared to the other solutions.

• Question 10:

  Files from multiple data sources arrive in an Amazon S3 bucket on a regular basis. A data engineer wants to ingest new les into Amazon Redshift in near real time when the new les arrive in the S3 bucket.

  Which solution will meet these requirements?

  A. Use the query editor v2 to schedule a COPY command to load new les into Amazon Redshift.
  B. Use the zero-ETL integration between Amazon Aurora and Amazon Redshift to load new les into Amazon Redshift.
  C. Use AWS Glue job bookmarks to extract, transform, and load (ETL) load new les into Amazon Redshift.
  D. Use S3 Event notifications to invoke an AWS Lambda function that loads new les into Amazon Redshift.
  D. Use S3 Event notifications to invoke an AWS Lambda function that loads new les into Amazon Redshift.