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

Amazon DATA-ENGINEER-ASSOCIATE Online Questions & Answers

• Question 121:

  A data engineer needs to deploy a complex pipeline. The stages of the pipeline must be able to run a script. The data engineer must use only fully managed and serverless services in the pipeline.

  Which solution will meet these requirements?

  A. Deploy AWS Glue jobs and workflows. UseAWS Glue to run the jobs and workflows on a schedule.
  B. Use Amazon Managed Workflows for Apache Airflow (Amazon MWAA) to build and schedule the pipeline.
  C. Deploy the script to Amazon EC2 instances. Use Amazon EventBridge to run the script on a schedule.
  D. Use Aws Glue DataBrew to build the pipeline. Use Amazon EventBridge to run the pipeline on a schedule.
  B. Use Amazon Managed Workflows for Apache Airflow (Amazon MWAA) to build and schedule the pipeline.

• Question 122:

  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?

  A. Use Amazon S3 for data lake storage. Use S3 access policies to restrict data access by rows and columns. Provide data access through Amazon S3.
  B. Use 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.
  C. Use 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.
  D. Use 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.
  D. Use 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.

  ---

  Explanation

  Option D is the best solution to meet the requirements with the least operational overhead because AWS Lake Formation is a fully managed service that simplifies the process of building, securing, and managing data lakes. AWS Lake Formation allows you to define granular data access policies at the row and column level for different users and groups. AWS Lake Formation also integrates with Amazon Athena, Amazon Redshift Spectrum, and Apache Hive on Amazon EMR, enabling these services to access the data in the data lake through AWS Lake Formation.

  Option A is not a good solution because S3 access policies cannot restrict data access by rows and columns. S3 access policies are based on the identity and permissions of the requester, the bucket and object ownership, and the object prefix and tags. S3access policies cannot enforce fine-grained data access control at the row and column level.

  Option B is not a good solution because it involves using Apache Ranger and Apache Pig, which are not fully managed services and require additional configuration and maintenance. Apache Ranger is a framework that provides centralized security administration for data stored in Hadoop clusters, such as Amazon EMR. Apache Ranger can enforce row-level and column-level access policies for Apache Hive tables. However, Apache Ranger is not a native AWS service and requires manual installation and configuration on Amazon EMR clusters. Apache Pig is a platform that allows you to analyze large data sets using a high-level scripting language called Pig Latin. Apache Pig can access data stored in Amazon S3 and process it using Apache Hive. However, Apache Pig is not a native AWS service and requires manual installation and configuration on Amazon EMR clusters.

  Option C is not a good solution because Amazon Redshift is not a suitable service for data lake storage. Amazon Redshift is a fully managed data warehouse service that allows you to run complex analytical queries using standard SQL. Amazon Redshift can enforce row-level and column-level access policies for different users and groups. However, Amazon Redshift is not designed to store and process large volumes of unstructured or semi-structured data, which are typical characteristics of data lakes. Amazon Redshift is also more expensive and less scalable than Amazon S3 for data lake storage.

  References:

  AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide

  What Is AWS Lake Formation? - AWS Lake Formation Using AWS Lake Formation with Amazon Athena - AWS Lake Formation Using AWS Lake Formation with Amazon Redshift Spectrum - AWS Lake Formation Using AWS Lake Formation with Apache Hive on Amazon EMR - AWS Lake Formation Using Bucket Policies and User Policies - Amazon Simple Storage Service Apache Ranger Apache Pig What Is Amazon Redshift? - Amazon Redshift

• Question 123:

  The company stores a large volume of customer records in Amazon S3. To comply with regulations, the company must be able to access new customer records immediately for the first 30 days after the records are created. The company accesses records that are older than 30 days infrequently.

  The company needs to cost-optimize its Amazon S3 storage.

  Which solution will meet these requirements MOST cost-effectively?

  A. Apply a lifecycle policy to transition records to S3 Standard Infrequent-Access (S3 Standard-IA) storage after 30 days.
  B. Use S3 Intelligent-Tiering storage.
  C. Transition records to S3 Glacier Deep Archive storage after 30 days.
  D. Use S3 Standard-Infrequent Access (S3 Standard-IA) storage for all customer records.
  A. Apply a lifecycle policy to transition records to S3 Standard Infrequent-Access (S3 Standard-IA) storage after 30 days.

• Question 124:

  A data engineer needs to provide analysts with SQL access to data in Amazon S3 without loading the data into a database. The team already has table metadata in AWS Glue Data Catalog.

  Which service should the engineer use for serverless querying?

  A. Amazon Athena
  B. AWS Backup
  C. Amazon MemoryDB for Redis
  D. AWS Application Migration Service
  A. Amazon Athena

  ---

  Explanation

  Athena provides serverless SQL querying for data in Amazon S3 and can use AWS Glue Data Catalog metadata. AWS Backup protects supported resources. MemoryDB is an in-memory database for low-latency workloads. Application Migration Service is for lift-and-shift server migrations.

• Question 125:

  A company stores logs in an Amazon S3 bucket. When a data engineer attempts to access several log files, the data engineer discovers that some files have been unintentionally deleted.

  The data engineer needs a solution that will prevent unintentional file deletion in the future.

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

  A. Manually back up the S3 bucket on a regular basis.
  B. Enable S3 Versioning for the S3 bucket.
  C. Configure replication for the S3 bucket.
  D. Use an Amazon S3 Glacier storage class to archive the data that is in the S3 bucket.
  B. Enable S3 Versioning for the S3 bucket.

  ---

  Explanation

  To prevent unintentional file deletions and meet the requirement with minimal operational overhead, enabling S3 Versioning is the best solution.

  S3 Versioning:

  S3 Versioning allows multiple versions of an object to be stored in the same S3 bucket. When a file is deleted or overwritten, S3 preserves the previous versions, which means you canrecover from accidental deletions or modifications.

  Enabling versioning requires minimal overhead, as it is a bucket-level setting and does not require additional backup processes or data replication.

  Users can recover specific versions of files that were unintentionally deleted, meeting the needs of the data engineer to avoid accidental data loss.

• Question 126:

  A company is building an analytics solution. The solution uses Amazon S3 for data lake storage and Amazon Redshift for a data warehouse. The company wants to use Amazon Redshift Spectrum to query the data that is in Amazon S3.

  Which actions will provide the FASTEST queries? (Choose two.)

  A. Use gzip compression to compress individual files to sizes that are between 1 GB and 5 GB.
  B. Use a columnar storage file format.
  C. Partition the data based on the most common query predicates.
  D. Split the data into files that are less than 10 KB.
  E. Use file formats that are not splittable.
  B. Use a columnar storage file format.
  C. Partition the data based on the most common query predicates.

  ---

  Explanation

  Amazon Redshift Spectrum is a feature that allows you to run SQL queries directly against data in Amazon

  S3, without loading or transforming the data. Redshift Spectrum can query 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, using a columnar storage file format, such as Parquet, will provide faster queries, as it allows Redshift Spectrum to scan only the relevant columns and skip the rest, reducing the amount of data read from S3. Additionally, partitioning the data based on the most common query predicates, such as date, time, region, etc., will provide faster queries, as it allows Redshift Spectrum to prune the partitions that do not match the query criteria, reducing the amount of data scanned from S3. Partitioning also improves the performance of joins and aggregations, as it reduces data skew and shuffling.

  The other options are not as effective as using a columnar storage file format and partitioning the data.

  Using gzip compression to compress individual files to sizes that are between 1 GB and 5 GB will reduce the data size, but it will not improve the query performance significantly, as gzip is not a splittable compression algorithm and requires decompression before reading. Splitting the data into files that are less than 10 KB will increase the number of files and the metadata overhead, which will degrade the query performance. Using file formats that are not supported by Redshift Spectrum, such as XML, will not work, as Redshift Spectrum will not be able to read or parse the data.

• Question 127:

  A company is creating near real-time dashboards to visualize time series data. The company ingests data into Amazon Managed Streaming for Apache Kafka (Amazon MSK). A customized data pipeline consumes the data. The pipeline then writes data to Amazon Keyspaces (for Apache Cassandra), Amazon OpenSearch Service, and Apache Avro objects in Amazon S3.

  Which solution will make the data available for the data visualizations with the LEAST latency?

  A. Create OpenSearch Dashboards by using the data from OpenSearch Service.
  B. Use Amazon Athena with an Apache Hive metastore to query the Avro objects in Amazon S3. Use Amazon Managed Grafana to connect to Athena and to create the dashboards.
  C. Use Amazon Athena to query the data from the Avro objects in Amazon S3. configure Amazon Keyspaces as the data catalog. Connect Amazon QuickSight to Athena to create the dashboards.
  D. Use AWS Glue to catalog the data. Use S3 Select to query the Avro objects in Amazon S3. Connect Amazon QuickSight to the S3 bucket to create the dashboards.
  A. Create OpenSearch Dashboards by using the data from OpenSearch Service.

• Question 128:

  A company ingests data from multiple data sources and stores the data in an Amazon S3 bucket. An AWS Glue extract, transform, and load (ETL) job transforms the data and writes the transformed data to an Amazon S3 based data lake.

  The company uses Amazon Athena to query the data that is in the data lake.

  The company needs to identify matching records even when the records do not have a common unique identifier.

  Which solution will meet this requirement?

  A. Use Amazon Made pattern matching as part of the ETL job.
  B. Train and use the AWS Glue PySpark Filter class in the ETL job.
  C. Partition tables and use the ETL job to partition the data on a unique identifier.
  D. Train and use the AWS Lake Formation FindMatches transform in the ETL job.
  D. Train and use the AWS Lake Formation FindMatches transform in the ETL job.

  ---

  Explanation

  The problem described requires identifying matching records even when there is no unique identifier. AWS Lake Formation FindMatches is designed for this purpose. It uses machine learning (ML) to deduplicate and find matching records in datasets that do not share a common identifier.

  D. Train and use the AWS Lake Formation FindMatches transform in the ETL job: FindMatchesis a transform available in AWS Lake Formation that uses ML to discover duplicate records or related records that might not have a common unique identifier. It can be integrated into an AWS Glue ETL job to perform deduplication or matching tasks. FindMatches is highly effective in scenarios where records do not share a key, such as customer records from different sources that need to be merged or reconciled.

• Question 129:

  A data engineer uses Amazon Managed Workflows for Apache Airflow (Amazon MWAA) to run data pipelines in an AWS account.

  A workflow recently failed to run. The data engineer needs to use Apache Airflow logs to diagnose the failure of the workflow.

  Which log type should the data engineer use to diagnose the cause of the failure?

  A. YourEnvironmentName-WebServer
  B. YourEnvironmentName-Scheduler
  C. YourEnvironmentName-DAGProcessing
  D. YourEnvironmentName-Task
  D. YourEnvironmentName-Task

  ---

  Explanation

  In Amazon Managed Workflows for Apache Airflow (MWAA), the type of log that is most useful for diagnosing workflow (DAG) failures is the Task logs . These logs provide detailed information on the execution of each task within the DAG, including error messages, exceptions, and other critical details necessary for diagnosing failures.

  Option D: YourEnvironmentName-TaskTask logs capture the output from the execution of each task within a workflow (DAG), which is crucial for understanding what went wrong when a DAG fails. These logs contain detailed execution information, including errors and stack traces, making them the best source for debugging.

  Other options(WebServer, Scheduler, and DAGProcessing logs) provide general environment-level logs or logs related to scheduling and DAG parsing, but they do not provide the granular task-level execution details needed for diagnosing workflow failures.

• Question 130:

  A retail company stores data from a product lifecycle management (PLM) application in an on-premises MySQL database. The PLM application frequently updates the database when transactions occur.

  The company wants to gather insights from the PLM application in near real time. The company wants to integrate the insights with other business datasets and to analyze the combined dataset by using an Amazon Redshift data warehouse.

  The company has already established an AWS Direct Connect connection between the on-premises infrastructure and AWS.

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

  A. Run a scheduled AWS Glue extract, transform, and load (ETL) job to get the MySQL database updates by using a Java Database Connectivity (JDBC) connection. Set Amazon Redshift as the destination for the ETL job.
  B. Run a full load plus CDC task in AWS Database Migration Service (AWS DMS) to continuously replicate the MySQL database changes. Set Amazon Redshift as the destination for the task.
  C. Use the Amazon AppFlow SDK to build a custom connector for the MySQL database to continuously replicate the database changes. Set Amazon Redshift as the destination for the connector.
  D. Run scheduled AWS DataSync tasks to synchronize data from the MySQL database. Set Amazon Redshift as the destination for the tasks.
  B. Run a full load plus CDC task in AWS Database Migration Service (AWS DMS) to continuously replicate the MySQL database changes. Set Amazon Redshift as the destination for the task.

  ---

  Explanation

  Problem Analysis:

  The company needs near real-time replication of MySQL updates to Amazon Redshift.

  Minimal development effort is required for this solution.

  Key Considerations:

  AWS DMS provides a full load + CDC (Change Data Capture) mode for continuous replication of database changes.

  DMS integrates natively with both MySQL and Redshift, simplifying setup.

  Solution Analysis:

  Option A: AWS Glue Job Glue is batch-oriented and does not support near real-time replication.

  Option B: DMS with Full Load + CDC Efficiently handles initial database load and continuous updates.

  Requires minimal setup and operational overhead.

  Option C: AppFlow SDK

  AppFlow is not designed for database replication. Custom connectors increase development effort.

  Option D: DataSync

  DataSync is for file synchronization and not suitable for database updates.

  Final Recommendation:

  Use AWS DMS full load + CDC in mode for continuous replication.

  References:

  AWS Database Migration Service Documentation Setting Up DMS with Redshift