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

Amazon DATA-ENGINEER-ASSOCIATE Online Questions & Answers

• Question 271:

  A company that operates globally must follow regulations that require data from an AWS Region to be accessible only within that Region.

  A data engineer is creating a data pipeline that will create resources in the Region where the data engineer works. The data pipeline should have access to data only from the Region where the data engineer works.

  The pipeline uses Active Directory as an identity and authentication system. The pipeline uses a custom identity broker application to verify that employees are signed in to Active Directory and to obtain temporary credentials by using the AssumeRole API operation.

  Which solution will meet the locality requirements with the LEAST administrative effort?

  A. Create an IAM role that has permissions to create resources. Create a policy for each Region that ensures users can create resources only in that Region. Pass the policy as the session policy when employees obtain the temporary credentials.
  B. Create an IAM role for data engineers in each Region separately. Instruct each data engineer to obtain temporary credentials by assuming the appropriate Region specific IAM role.
  C. Create an IAM group for each Region. Include the required IAM policies for each IAM group. Add users to each IAM group so that when users log in by obtaining the temporary credentials, the users will receive the appropriate access based on the IAM group.
  D. Create individual IAM policies that allow users to create resources in a specific Region. Assign the policies to each data engineer. Allow users to assume the individually assigned role when the users log in to AWS.
  A. Create an IAM role that has permissions to create resources. Create a policy for each Region that ensures users can create resources only in that Region. Pass the policy as the session policy when employees obtain the temporary credentials.

• Question 272:

  A data engineer is writing a query to join two tables in Amazon Athena. The data engineer needs to choose the correct join order for the tables to optimize query performance.

  Which solution will meet these requirements?

  A. Specify the smaller table on the left side of the join and the larger table on the right side of the join.
  B. Specify the larger table on the left side of the join and the smaller table on the right side of the join.
  C. Use AWS Glue to pre-process the tables before performing the join.
  D. Use table statistics to automatically determine the join order.
  A. Specify the smaller table on the left side of the join and the larger table on the right side of the join.

• Question 273:

  A company uses AWS Glue jobs to implement several data pipelines. The pipelines are critical to the company.

  The company needs to implement a monitoring mechanism that will alert stakeholders if the pipelines fail.

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

  A. Create an Amazon EventBridge rule to match AWS Glue job failure events. Configure the rule to target an AWS Lambda function to process events. Configure the function to send notifications to an Amazon Simple Notification Service (Amazon SNS) topic.
  B. Configure an Amazon CloudWatch Logs log group for the AWS Glue jobs. Create an Amazon EventBridge rule to match new log creation events in the log group. Configure the rule to target an AWS Lambda function that reads the logs and sends notifications to an Amazon Simple Notification Service (Amazon SNS) topic if AWS Glue job failure logs are present.
  C. Create an Amazon EventBridge rule to match AWS Glue job failure events. Define an Amazon CloudWatch metric based on the EventBridge rule. Set up a CloudWatch alarm based on the metric to send notifications to an Amazon Simple Notification Service (Amazon SNS) topic.
  D. Configure an Amazon CloudWatch Logs log group for the AWS Glue jobs. Create an Amazon EventBridge rule to match new log creation events in the log group. Configure the rule to send notifications to an Amazon Simple Notification Service (Amazon SNS) topic.
  C. Create an Amazon EventBridge rule to match AWS Glue job failure events. Define an Amazon CloudWatch metric based on the EventBridge rule. Set up a CloudWatch alarm based on the metric to send notifications to an Amazon Simple Notification Service (Amazon SNS) topic.

  ---

  Explanation

  By creating an EventBridge rule that matches Glue job state-change events where the state is FAILED, you can emit a custom CloudWatch metric without writing any code. Then you define a CloudWatch alarm on that metric to notify an SNS topic upon breach. This approach requires no Lambda functions or log parsing (just the rule, the metric, and the alarm) minimizing operational overhead.

• Question 274:

  A company is migrating a legacy application to an Amazon S3 based data lake. A data engineer reviewed data that is associated with the legacy application. The data engineer found that the legacy data contained some duplicate information.

  The data engineer must identify and remove duplicate information from the legacy application data.

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

  A. Write a custom extract, transform, and load (ETL) job in Python. Use the DataFramedrop duplicatesf) function by importing the Pandas library to perform data deduplication.
  B. Write an AWS Glue extract, transform, and load (ETL) job. Use the FindMatches machine learning (ML) transform to transform the data to perform data deduplication.
  C. Write a custom extract, transform, and load (ETL) job in Python. Import the Python dedupe library. Use the dedupe library to perform data deduplication.
  D. Write an AWS Glue extract, transform, and load (ETL) job. Import the Python dedupe library. Use the dedupe library to perform data deduplication.
  B. Write an AWS Glue extract, transform, and load (ETL) job. Use the FindMatches machine learning (ML) transform to transform the data to perform data deduplication.

  ---

  Explanation

  AWS Glue is a fully managed serverless ETL service that can handle data deduplication with minimal operational overhead. AWS Glue provides a built-in ML transform called FindMatches, which can automatically identify and group similar records in a dataset. FindMatches can also generate a primary key for each group of records and remove duplicates. FindMatches does not require any coding or prior ML experience, as it can learn from a sample of labeled data provided by the user. FindMatches can also scale to handle large datasets and optimize the cost and performance of the ETL job.

• Question 275:

  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?

  A. VACUUM FULL Orders
  B. VACUUM DELETE ONLY Orders
  C. VACUUM REINDEX Orders
  D. VACUUM SORT ONLY Orders
  C. VACUUM REINDEX Orders

  ---

  Explanation

  Amazon Redshift is a fully managed, petabyte-scale data warehouse service that enables fast and cost-effective analysis of large volumes of data. Amazon Redshift uses columnar storage, compression, and zone maps to optimize the storage and performance of data. However, over time, as data is inserted, updated, or deleted, the physical storage of data can become fragmented, resulting in wasted disk space and degraded query performance. To address this issue, Amazon Redshift provides the VACUUM command, which reclaims disk space and resorts rows in either a specified table or all tables in the current schema. The VACUUM command has four options: FULL, DELETE ONLY, SORT ONLY, and REINDEX.

  The option that best meets the requirements of the question is VACUUM REINDEX, which re-sorts the rows in a table that has an interleaved sort key and rewrites the table to a new location on disk. An interleaved sort key is a type of sort key that gives equal weight to each column in the sort key, and stores the rows in a way that optimizes the performance of queries that filter by multiple columns in the sort key.

  However, as data is added or changed, the interleaved sort order can become skewed, resulting in suboptimal query performance. The VACUUM REINDEX option restores the optimal interleaved sort order and reclaims disk space by removing deleted rows. This option also analyzes the sort key column and updates the table statistics, which are used by the query optimizer to generate the most efficient query execution plan.

  The other options are not optimal for the following reasons:

  Option A: VACUUM FULL Orders. This option reclaims disk space by removing deleted rows and resorts the entire table. However, this option is not suitable for tables that have an interleaved sort key, as it does not restore the optimal interleaved sort order. Moreover, this option is the most resource-intensive and time-consuming, as it rewrites the entire table to a new location on disk.

  Option B: VACUUM DELETE ONLY Orders. This option reclaims disk space by removing deleted rows, but does not resort the table.

  This option is not suitable for tables that have any sort key, as it does not improve the query performance by restoring the sort order. Moreover, this option does not analyze the sort key column and update the table statistics.

  Option D: VACUUM SORT ONLY Orders. This option resorts the entire table, but does not reclaim disk space by removing deleted rows. This option is not suitable for tables that have an interleaved sort key, as it does not restore the optimal interleaved sort order. Moreover, this option does not analyze the sort key column and update the table statistics.

  References:

  1: Amazon Redshift VACUUM

  2: Amazon Redshift Interleaved Sorting

  3: Amazon Redshift ANALYZE

• Question 276:

  A company is setting up a data pipeline in AWS. The pipeline extracts client data from Amazon S3 buckets, performs quality checks, and transforms the data. The pipeline stores the processed data in a relational database. The company will use the processed data for future queries.

  Which solution will meet these requirements MOST cost-effectively?

  A. Use AWS Glue ETL to extract the data from the S3 buckets and perform the transformations. Use AWS Glue Data Quality to enforce suggested quality rules. Load the data and the quality check results into an Amazon RDS for MySQL instance.
  B. Use AWS Glue Studio to extract the data from the S3 buckets. Use AWS Glue DataBrew to perform the transformations and quality checks. Load the processed data into an Amazon RDS for MySQL instance. Load the quality check results into a new S3 bucket.
  C. Use AWS Glue ETL to extract the data from the S3 buckets and perform the transformations. Use AWS Glue DataBrew to perform quality checks. Load the processed data and the quality check results into a new S3 bucket.
  D. Use AWS Glue Studio to extract the data from the S3 buckets. Use AWS Glue DataBrew to perform the transformations and quality checks. Load the processed data and quality check results into an Amazon RDS for MySQL instance.
  A. Use AWS Glue ETL to extract the data from the S3 buckets and perform the transformations. Use AWS Glue Data Quality to enforce suggested quality rules. Load the data and the quality check results into an Amazon RDS for MySQL instance.

  ---

  Explanation

  Using a single AWS Glue ETL job to both transform the data and invoke Glue Data Quality checks lets you declaratively enforce recommended rules without spinning up separate tools. You can then write the cleansed data and, if desired, the quality metrics, directly into your Amazon RDS for MySQL instance. This serverless, end-to-end approach minimizes service sprawl and only incurs Glue and RDS costs, making it the most cost-effective with the least operational overhead.

• Question 277:

  A data engineer is using an AWS Glue ETL job to remove outdated customer records from a table that contains customer account information. The data engineer is using the following SQL command to remove customers that exist in a table named monthly_accounts_update table from the customer accounts table:

  MERGE INTO accounts t USING monthly_accounts_update s ON t.customer = s.customer WHEN MATCHED THEN DELETE

  What will happen when the data engineer runs the SQL command?

  A. All customer records that exist in both the customer accounts table and the monthly_accounts_update table will be deleted from the accounts table.
  B. Only customer records that are present in both tables will be retained in the customer accounts table.
  C. The monthly_accounts_update table will be deleted.
  D. No records will be deleted because the command syntax is not valid in AWS Glue.
  A. All customer records that exist in both the customer accounts table and the monthly_accounts_update table will be deleted from the accounts table.

  ---

  Explanation

  That MERGE statement deletes every row in accounts where the customer value matches a row in monthly_accounts_update. All overlapping customer records will be removed from the accounts table.

• Question 278:

  A company needs to partition the Amazon S3 storage that the company uses for a data lake. The partitioning will use a path of the S3 object keys in the following format: s3://bucket/prefix/year=2023/ month=01/day=01.

  A data engineer must ensure that the AWS Glue Data Catalog synchronizes with the S3 storage when the company adds new partitions to the bucket.

  Which solution will meet these requirements with the LEAST latency?

  A. Schedule an AWS Glue crawler to run every morning.
  B. Manually run the AWS Glue CreatePartition API twice each day.
  C. Use code that writes data to Amazon S3 to invoke the Boto3 AWS Glue create partition API call.
  D. Run the MSCK REPAIR TABLE command from the AWS Glue console.
  C. Use code that writes data to Amazon S3 to invoke the Boto3 AWS Glue create partition API call.

• Question 279:

  A company uploads .csv files to an Amazon S3 bucket. The company's data platform team has set up an AWS Glue crawler to perform data discovery and to create the tables and schemas.

  An AWS Glue job writes processed data from the tables to an Amazon Redshift database. The AWS Glue job handles column mapping and creates the Amazon Redshift tables in the Redshift database appropriately.

  If the company reruns the AWS Glue job for any reason, duplicate records are introduced into the Amazon Redshift tables. The company needs a solution that will update the Redshift tables without duplicates.

  Which solution will meet these requirements?

  A. Modify the AWS Glue job to copy the rows into a staging Redshift table. Add SQL commands to update the existing rows with new values from the staging Redshift table.
  B. Modify the AWS Glue job to load the previously inserted data into a MySQL database. Perform an upsert operation in the MySQL database. Copy the results to the Amazon Redshift tables.
  C. Use Apache Spark's DataFrame dropDuplicates() API to eliminate duplicates. Write the data to the Redshift tables.
  D. Use the AWS Glue ResolveChoice built-in transform to select the value of the column from the most recent record.
  A. Modify the AWS Glue job to copy the rows into a staging Redshift table. Add SQL commands to update the existing rows with new values from the staging Redshift table.

  ---

  Explanation

  To avoid duplicate records in Amazon Redshift, the most effective solution is to perform the ETL in a way that first loads the data into a staging table and then uses SQL commands like MERGE or UPDATE to insert new records and update existing records without introducing duplicates.

  Using Staging Tables in Redshift: The AWS Glue job can write data to a staging table in Redshift. Once the data is loaded, SQL commands can be executed to compare the staging data with the target table andupdate or insert records appropriately. This ensures no duplicates are introduced during re-runs of the Glue job.

• Question 280:

  A data engineer needs Amazon Athena queries to finish faster. The data engineer notices that all the files the Athena queries use are currently stored in uncompressed .csv format. The data engineer also notices that users perform most queries by selecting a specific column.

  Which solution will MOST speed up the Athena query performance?

  A. Change the data format from .csvto JSON format. Apply Snappy compression.
  B. Compress the .csv files by using Snappy compression.
  C. Change the data format from .csvto Apache Parquet. Apply Snappy compression.
  D. Compress the .csv files by using gzjg compression.
  C. Change the data format from .csvto Apache Parquet. Apply Snappy compression.

  ---

  Explanation

  Amazon Athena is a serverless interactive query service that allows you to analyze data in Amazon S3 using standard SQL. Athena supports various data formats, such as CSV, JSON, ORC, Avro, and Parquet.

  However, not all data formats are equally efficient for querying. Some data formats, such as CSV and JSON, are row-oriented, meaning that they store data as a sequence of records, each with the same fields.

  Row-oriented formats are suitable for loading and exporting data, but they are not optimal for analytical queries that often access only a subset of columns. Row-oriented formats also do not support compression or encoding techniques that can reduce the data size and improve the query performance.

  On the other hand, some data formats, such as ORC and Parquet, are column-oriented, meaning that they store data as a collection of columns, each with a specific data type. Column-oriented formats are ideal for analytical queries that often filter, aggregate, or join data by columns. Column-oriented formats also support compression and encoding techniques that can reduce the data size and improve the query performance. For example, Parquet supports dictionary encoding, which replaces repeated values with numeric codes, and run-length encoding, which replaces consecutive identical values with a single value and a count. Parquet also supports various compression algorithms, such as Snappy, GZIP, and ZSTD, that can further reduce the data size and improve the query performance.

  Therefore, changing the data format from CSV to Parquet and applying Snappy compression will most speed up the Athena query performance. Parquet is a column-oriented format that allows Athena to scan only the relevant columns and skip the rest, reducing the amount of data read from S3. Snappy is a compression algorithm that reduces the data size without compromising the query speed, as it is splittable and does not require decompression before reading. This solution will also reduce the cost of Athena queries, as Athena charges based on the amount of data scanned from S3.

  The other options are not as effective as changing the data format to Parquet and applying Snappy compression. Changing the data format from CSV to JSON and applying Snappy compression will not improve the query performance significantly, as JSON is also a row-oriented format that does not support columnar access or encoding techniques. Compressing the CSV files by using Snappy compression will reduce the data size, but it will not improve the query performance significantly, as CSV is still a row-oriented format that does not support columnar access or encoding techniques. Compressing the CSV files by using gzjg compression will reduce the data size, but it will degrade the query performance, as gzjg is not a splittable compression algorithm and requires decompression before reading.