Question 1

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 provisionedcapacity mode, and an Amazon Redshift cluster. A data engineering team must develop a solution that will g 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?

Accepted Answer

The best solution to meet the requirements of giving data scientists the ability to query all data sources by using syntax similar to SQL with the leas overhead is to use 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, and use PartiQL for data that is stored in JSON format.

AWS Glue is a serverless data integration service that makes it easy to prepare, clean, enrich, and move data between data stores1. AWS Glue crawlers ar processes that connect to a data store, progress through a prioritized list of classifiers to determine the schema for your data, and then create metada the Data Catalog2. The Data Catalog is a persistent metadata store that contains table definitions, job definitions, and other control information to he your AWS Glue components3. You can use AWS Glue to crawl the data sources, such as Amazon S3, Amazon RDS for Microsoft SQL Server, and Amazon DynamoDB, and store the metadata in the Data Catalog.

Amazon Athena is a serverless, interactive query service that makes it easy to analyze data directly in Amazon S3 using standard SQL or Python4. Amazon Athena also supports PartiQL, a SQL-compatible query language that lets you query, insert, update, and delete data from semi-structured and nested data, as JSON. You can use Amazon Athena to query the data from the Data Catalog using SQL for structured data sources, such as .csv files and relational data and PartiQL for data that is stored in JSON format. You can also use Athena to query data from other data sources, such as Amazon Redshift, using federa queries.

Using AWS Glue and Amazon Athena to query all data sources by using syntax similar to SQL is the least operational overhead solution, as you do not need provision, manage, or scale any infrastructure, and you pay only for the resources you use. AWS Glue charges you based on the compute time and the data processed by your crawlers and ETL jobs1. Amazon Athena charges you based on the amount of data scanned by your queries. You can also reduce the cost an improve the performance of your queries by using compression, partitioning, and columnar formats for your data in Amazon S3.

Option B is not the best solution, as using AWS Glue to crawl the data sources, store metadata in the AWS Glue Data Catalog, and use Redshift Spectrum t query the data, would incur more costs and complexity than using Amazon Athena. Redshift Spectrum is a feature of Amazon Redshift, a fully managed data warehouse service, that allows you to query and join data across your data warehouse and your data lake using standard SQL. While Redshift Spectrum is powerful and useful for many data warehousing scenarios, it is not necessary or cost-effective for querying all data sources by using syntax similar to

Redshift Spectrum charges you based on the amount of data scanned by your queries, which is similar to Amazon Athena, but it also requires you to have a Amazon Redshift cluster, which charges you based on the node type, the number of nodes, and the duration of the cluster5. These costs can add up quickly especially if you have large volumes of data and complex queries. Moreover, using Redshift Spectrum would introduce additional latency and complexity, a would have to provision and manage the cluster, and create an external schema and database for the data in the Data Catalog, instead of querying it dire Amazon Athena.

Option C is not the best solution, as using AWS Glue to crawl the data sources, store metadata in the AWS Glue Data Catalog, use AWS Glue jobs to transf data that is in JSON format to Apache Parquet or .csv format, store the transformed data in an S3 bucket, and use Amazon Athena to query the original an transformed data from the S3 bucket, would incur more costs and complexity than using Amazon Athena with PartiQL. AWS Glue jobs are ETL scripts that you write in Python or Scala to transform your data and load it to your target data store. Apache Parquet is a columnar storage format that can improve the performance of analytical queries by reducing the amount of data that needs to be scanned and providing efficient compression and encoding schemes6. Whi using AWS Glue jobs and Parquet can improve the performance and reduce the cost of your queries, they would also increase the complexity and the operati overhead of the data pipeline, as you would have to write, run, and monitor the ETL jobs, and store the transformed data in a separate location in Amazo Moreover, using AWS Glue jobs and Parquet would introduce additional latency, as you would have to wait for the ETL jobs to finish before querying the transformed data.

Option D is not the best solution, as using AWS Lake Formation to create a data lake, use Lake Formation jobs to transform the data from all data source Apache Parquet format, store the transformed data in an S3 bucket, and use Amazon Athena or RedshiftSpectrum to query the data, would incur more costs a complexity than using Amazon Athena with PartiQL. AWS Lake Formation is a service that helps you centrally govern, secure, and globally share data for a and machine learning7. Lake Formation jobs are ETL jobs that you can create and run using the Lake Formation console or API. While using Lake Formation Parquet can improve the performance and reduce the cost of your queries, they would also increase the complexity and the operational overhead of the dat pipeline, as you would have to create, run, and monitor the Lake Formation jobs, and store the transformed data in a separate location in Amazon S3. Mor using Lake Formation and Parquet would introduce additional latency, as you would have to wait for the Lake Formation jobs to finish before querying the transformed data. Furthermore, using Redshift Spectrum to query the data would also incur the same costs and complexity as mentioned in option B. Refere

- ? What is Amazon Athena?
- ? Data Catalog and crawlers in AWS Glue
- ? AWS Glue Data Catalog
- ? Columnar Storage Formats
- ? AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide
- ? AWS Glue Schema Registry
- ? What is AWS Glue?
- ? Amazon Redshift Serverless
- ? Amazon Redshift provisioned clusters

## Recommend!! Get the Full AWS-Certified-Data-Engineer-Associate dumps in VCE and PDF From SurePassExam

https://www.surepassexam.com/AWS-Certified-Data-Engineer-Associate-exam-dumps.html (80 New Questions)

- ? [Querying external data using Amazon Redshift Spectrum]
- ? [Using stored procedures in Amazon Redshift]
- ? [What is AWS Lambda?]
- ? [PartiQL for Amazon Athena]
- ? [Federated queries in Amazon Athena]
- ? [Amazon Athena pricing]
- ? [Top 10 performance tuning tips for Amazon Athena]
- ? [AWS Glue ETL jobs]
- ? [AWS Lake Formation jobs]

Question 2

A company maintains an Amazon Redshift provisioned cluster that the company uses for extract, transform, and load (ETL) operations to support critical a tasks. A sales team within the company maintains a Redshift cluster that the sales team uses for business intelligence (BI) tasks.

The sales team recently requested access to the data that is in the ETL Redshift cluster so the team can perform weekly summary analysis tasks. The sale needs to join data from the ETL cluster with data that is in the sales team's BI cluster.

The company needs a solution that will share the ETL cluster data with the sales team without interrupting the critical analysis tasks. The solution mus usage of the computing resources of the ETL cluster.

Which solution will meet these requirements?

Accepted Answer

Redshift data sharing is a feature that enables you to share live data across different Redshift clusters without the need to copy or move data. Data s provides secure and governed access to data, while preserving the performance and concurrency benefits of Redshift. By setting up the sales team BI clus consumer of the ETL cluster, the company can share the ETL cluster data with the sales team without interrupting the critical analysis tasks. The soluti minimizes the usage of the computing resources of the ETL cluster, as the data sharing does not consume any storage space or compute resources from the producer cluster. The other options are either not feasible or not efficient. Creating materialized views or database views would require the sales team direct access to the ETL cluster, which could interfere with the critical analysis tasks. Unloading a copy of the data from the ETL cluster to anAmazon every week would introduce additional latency and cost, as well as create data inconsistency issues. References:

? Sharing data across Amazon Redshift clusters

? AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide, Chapter 2: Data Store Management, Section 2.2: Amazon Redshift

Question 3

A company's data engineer needs to optimize the performance of table SQL queries. The company stores data in an Amazon Redshift cluster. The data engine cannot increase the size of the cluster because of budget constraints.

The company stores the data in multiple tables and loads the data by using the EVEN distribution style. Some tables are hundreds of gigabytes in size. O tables are less than 10 MB in size.

Which solution will meet these requirements?

Accepted Answer

This solution meets the requirements of optimizing the performance of table SQL queries without increasing the size of the cluster. By using the ALL di style for rarely updated small tables, you can ensure that the entire table is copied to every node in the cluster, which eliminates the need for data r during joins. This can improve query performance significantly, especially for frequently joined dimension tables. However, using the ALL distribution s increases the storage space and the load time, so it is only suitable for small tables that are not updated frequently orextensively. By specifying prim keys for all tables, you can help the query optimizer to generate better query plans and avoid unnecessary scans or joins. You can also use the AUTO dis style to let Amazon Redshift choose the optimal distribution style based on the table size and the query patterns. References:

- ? Choose the best distribution style
- ? Distribution styles
- ? Working with data distribution styles

Question 4

A company is planning to upgrade its Amazon Elastic Block Store (Amazon EBS) General Purpose SSD storage from gp2 to gp3. The company wants to prevent any interruptions in its Amazon EC2 instances that will cause data loss during the migration to the upgraded storage.

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

Accepted Answer

Changing the volume type of the existing gp2 volumes to gp3 is the easiest and fastest way to migrate to the new storage type without any downtime or d You can use the AWS Management Console, the AWS CLI, or the Amazon EC2 API to modify the volume type, size, IOPS, and throughput of your gp2 volumes. The modification takes effect immediately, and you can monitor the progress of the modification using CloudWatch. The other options are either more comp require additional steps, such as creating snapshots, transferring data, or attaching new volumes, which can increase the operational overhead and the r errors. References:

- ? Migrating Amazon EBS volumes from gp2 to gp3 and save up to 20% on

costs (Section: How to migrate from gp2 to gp3)

- ? Switching from gp2 Volumes to gp3 Volumes to Lower AWS EBS Costs (Section: How to Switch from GP2 Volumes to GP3 Volumes)
- ? Modifying the volume type, IOPS, or size of an EBS volume - Amazon Elastic Compute Cloud (Section: Modifying the volume type)

Question 5

A company uses Amazon RDS for MySQL as the database for a critical application. The database workload is mostly writes, with a small number of reads. A data engineer notices that the CPU utilization of the DB instance is very high. The high CPU utilization is slowing down the application. The data eng reduce the CPU utilization of the DB Instance.

Which actions should the data engineer take to meet this requirement? (Choose two.)

Accepted Answer

Amazon RDS is a fully managed service that provides relational databases in the cloud. Amazon RDS for MySQL is one of the supported database engines th you can use to run your applications. Amazon RDS provides various features and tools to monitor and optimize the performance of your DB instances, such Performance Insights, Enhanced Monitoring, CloudWatch metrics and alarms, etc.

Using the Performance Insights feature of Amazon RDS to identify queries that have high CPU utilization and optimizing the problematic queries will help the CPU utilization of the DB instance. Performance Insights is a feature that allows you to analyze the load on your DB instance and determine what is performance issues. Performance Insights collects, analyzes, and displays database performance data using an interactive dashboard. You can use Performa Insights to identify the top SQL statements, hosts, users, or processes that are consuming the most CPU resources. You can also drill down into the deta each query and see the execution plan, wait events, locks, etc. By using Performance Insights, you can pinpoint the root cause of the high CPU utilizati optimize the queries accordingly. For example, you can rewrite the queries to make them more efficient, add or remove indexes, use prepared statements, Implementing caching to reduce the database query load will also help reduce the CPU utilization of the DB instance. Caching is a technique that allows store frequently accessed data in a fast and scalable storage layer, such as Amazon ElastiCache. By using caching, you can reduce the number of requests hit your database, which in turn reduces the CPU load on your DB instance. Caching also improves the performance and availability of your application, a reduces the latency and increases the throughput of your data access. You can use caching for various scenarios, such as storing session data, user pref application configuration, etc. You can also use caching for read-heavy workloads, such as displaying product details, recommendations, reviews, etc. The other options are not as effective as using Performance Insights and caching. Modifying the database schema to include additional tables and indexes may not improve the CPU utilization, depending on the nature of the workload and the queries. Adding more tables and indexes may increase the complexity overhead of the database, which may negatively affect the performance. Rebooting the RDS DB instance once each week will not reduce the CPU utilization, will not address the underlying cause of the high CPU load. Rebooting may also cause downtime and disruption to your application. Upgrading to a larger size may reduce the CPUutilization, but it will also increase the cost and complexity of your solution. Upgrading may also not be necessary if you can o queries and reduce the database load by using caching. References:

- ? Amazon RDS
- ? Performance Insights
- ? Amazon ElastiCache
- ? [AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide], Chapter 3: Data Storage and Management, Section 3.1: Amazon RDS

Question 6

A data engineer must orchestrate a series of Amazon Athena queries that will run every day. Each query can run for more than 15 minutes. Which combination of steps will meet these requirements MOST cost-effectively? (Choose two.)

Accepted Answer

Option A and B are the correct answers because they meet the requirements most cost-effectively. Using an AWS Lambda function and the Athena Boto3 clie start\_query\_execution API call to invoke the Athena queries programmatically is a simple and scalable way to orchestrate the queries. Creating an AWS St Functions workflow and adding two states to check the query status and invoke the next query is a reliable and efficient way to handle the long-running Option C is incorrect because using an AWS Glue Python shell job to invoke the Athena queries programmatically is more expensive than using a Lambda function, as it requires provisioning and running a Glue job for each query.

Option D is incorrect because using an AWS Glue Python shell script to run a sleep timer that checks every 5 minutes to determine whether the current At

## Recommend!! Get the Full AWS-Certified-Data-Engineer-Associate dumps in VCE and PDF From SurePassExam

https://www.surepassexam.com/AWS-Certified-Data-Engineer-Associate-exam-dumps.html (80 New Questions)

query has finished running successfully is not a cost-effective or reliable way to orchestrate the queries, as it wastes resources and time. Option E is incorrect because using Amazon Managed Workflows for Apache Airflow (Amazon MWAA) to orchestrate the Athena queries in AWS Batch is an overkill solution that introduces unnecessary complexity and cost, as it requires setting up and managing an Airflow environment and an AWS Batch comput environment.

## References:

- ? AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide, Chapter 5: Data Orchestration, Section 5.2: AWS Lambda, Section 5.3: AWS Step Functions, Pages 125-135
- ? Building Batch Data Analytics Solutions on AWS, Module 5: Data Orchestration, Lesson 5.1: AWS Lambda, Lesson 5.2: AWS Step Functions, Pages 1-15 ? AWS Documentation Overview, AWS Lambda Developer Guide, Working with AWS Lambda Functions, Configuring Function Triggers, Using AWS Lambda with Amazon Athena, Pages 1-4
- ? AWS Documentation Overview, AWS Step Functions Developer Guide, Getting Started, Tutorial: Create a Hello World Workflow, Pages 1-8

Question 7

A financial company wants to implement a data mesh. The data mesh must support centralized data governance, data analysis, and data access control. The company has decided to use AWS Glue for data catalogs and extract, transform, and load (ETL) operations.

Which combination of AWS services will implement a data mesh? (Choose two.)

Accepted Answer

A data mesh is an architectural framework that organizes data into domains and treats data as products that are owned and offered for consumption by di teams1. A data mesh requires a centralized layer for data governance and access control, as well as a distributed layer for data storage and analysis. A can provide data catalogs and ETL operations for the data mesh, but it cannot provide data governance and access control by itself2. Therefore, the comp needs to use another AWS service for this purpose. AWS Lake Formation is a service that allows you to create, secure, and manage data lakes on AWS3. It integrates with AWS Glue and other AWS services to provide centralized data governance and access control for the data mesh. Therefore, option E is corr For data storage and analysis, the company can choose from different AWS services depending on their needs and preferences. However, one of the benefits data mesh is that it enables data to be stored and processed in a decoupled and scalable way1. Therefore, using serverless or managed services that can large volumes and varieties of data is preferable. Amazon S3 is a highly scalable, durable, and secure object storage service that can store any type of Amazon Athena is a serverless interactive query service that can analyze data in Amazon S3 using standard SQL. Therefore, option B is a good choice for storage and analysis in a data mesh. Option A, C, and D are not optimal because they either use relational databases that are not suitable for storing d unstructured data, or they require more management and provisioning than serverless services. References:

- ? 1: What is a Data Mesh? - Data Mesh Architecture Explained - AWS
- ? 2: AWS Glue - Developer Guide
- ? 3: AWS Lake Formation - Features
- ? [4]: Design a data mesh architecture using AWS Lake Formation and AWS Glue
- ? [5]: Amazon S3 - Features
- ? [6]: Amazon Athena - Features

Question 8

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 Ama bucket. The size of each S3 object is less than 100 MB.

Which solution will meet these requirements MOST cost-effectively?

Accepted Answer

AWS Glue is a fully managed serverless ETL service that can handle various data sources and formats, including .csv files in Amazon S3. AWS Glue provid two types of jobs: PySpark and Python shell. PySpark jobs use Apache Spark to process large-scale data in parallel, while Python shell jobs use Python s process small-scale data in a single execution environment. For this requirement, a Python shell job is more suitable and cost-effective, as the size of object is less than 100 MB, which does not require distributed processing. A Python shell job can use pandas, a popular Python library fordata analysis, transform the .csv data as needed. The other solutions are not optimal or relevant for this requirement. Writing a custom Python application and hosting Amazon EKS cluster would require more effort and resources to set up and manage the Kubernetes environment, as well as to handle the data ingestion and transformation logic. Writing a PySpark ETL script and hosting it on an Amazon EMR cluster would also incur more costs and complexity to provision and c the EMR cluster, as well as to use Apache Spark for processing small data files. Writing an AWS Glue PySpark job would also be less efficient and econom than a Python shell job, as it would involve unnecessary overhead and charges for using Apache Spark for small data files. References:

- ? AWS Glue
- ? Working with Python Shell Jobs
- ? pandas
- ? [AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide]

Question 9

A company is planning to migrate on-premises Apache Hadoop clusters to Amazon EMR. The company also needs to migrate a data catalog into a persistent

storage solution.

The company currently stores the data catalog in an on-premises Apache Hive metastore on the Hadoop clusters. The company requires a serverless solution migrate the data catalog.

Which solution will meet these requirements MOST cost-effectively?

Accepted Answer

AWS Database Migration Service (AWS DMS) is a service that helps you migrate databases to AWS quickly and securely. You can use AWS DMS to migrate the Hive metastore from the on-premises Hadoop clusters into Amazon S3, which is a highlyscalable, durable, and cost-effective object storage service. AWS G Data Catalog is a serverless, managed service that acts as a central metadata repository for your data assets. You can use AWS Glue Data Catalog to scan Amazon S3 bucket that contains the migrated Hive metastore and create a data catalog that is compatible with Apache Hive and other AWS services. This so meets the requirements of migrating the data catalog into a persistent storage solution and using a serverless solution. This solution is also the most as it does not incur any additional charges for running Amazon EMR or Amazon Aurora MySQL clusters. The other options are either not feasible or not opt Configuring a Hive metastore in Amazon EMR (option B) or an external Hive metastore in Amazon EMR (option C) would require running and maintaining Amazo EMR clusters, which would incur additional costs and complexity. Using Amazon Aurora MySQL to store the company's data catalog (option C) would also inc additional costs and complexity, as well as introduce compatibility issues with Apache Hive. Configuring a new Hive metastore in Amazon EMR (option D) w not migrate the existing data catalog, but create a new one, which would result in data loss and inconsistency. References:

- ? Using AWS Database Migration Service
- ? Populating the AWS Glue Data Catalog
- ? AWS Certified Data Engineer - Associate DEA-C01 Complete Study Guide, Chapter 4: Data Analysis and Visualization, Section 4.2: AWS Glue Data Catalog

Question 10

A company stores data from an application in an Amazon DynamoDB table that operates in provisioned capacity mode. The workloads of the application have predictable throughput load on a regular schedule. Every Monday, there is an immediate increase in activity early in the morning. The application has ve usage during weekends.

The company must ensure that the application performs consistently during peak usage times

Which solution will meet these requirements in the MOST cost-effective way?

Accepted Answer

Amazon DynamoDB is a fully managed NoSQL database service that provides fast and predictable performance with seamless scalability. DynamoDB offers two capacity modes for throughput capacity: provisioned and on-demand. In provisioned capacity mode, you specify the number of read and write capacity units second that you expect your application to require. DynamoDB reserves the resources to meet your throughput needs with consistent performance. In on-dem capacity mode, you pay per request and DynamoDB scales the resources up and down automatically based on the actual workload. On-demand capacity mode is suitable for unpredictable workloads that can vary significantly over time1.

The solution that meets the requirements in the most cost-effective way is to use AWS Application Auto Scaling to schedule higher provisioned capacity f usage times and lower capacity during off-peak times. This solution has the following advantages:

- ? It allows you to optimize the cost and performance of your DynamoDB table by adjusting the provisioned capacity according to your predictable workload patterns. You can use scheduled scaling to specify the date and time for the scaling actions, and the new minimum and maximum capacity limits. For examp you can schedule higher capacity for every Monday morning and lower capacity for weekends2.

? It enables you to take advantage of the lower cost per unit of provisioned capacity mode compared to on-demand capacity mode. Provisioned capacity mod charges a flat hourly rate for the capacity you reserve, regardless of how much you use. On-demand capacity mode charges for each read and write request consume, with nominimum capacity required. For predictable workloads, provisioned capacity mode can be more cost-effective than on-demand capacity mode1

? It ensures that your application performs consistently during peak usage times by having enough capacity to handle the increased load. You can also us scaling to automatically adjust the provisioned capacity based on the actual utilization of your table, and set a target utilization percentage for your secondary index. This way, you can avoid under-provisioning or over- provisioning your table2.

Option A is incorrect because it suggests increasing the provisioned capacity to the maximum capacity that is currently present during peak load times. solution has the following disadvantages:

- ? It wastes money by paying for unused capacity during off-peak times. If you provision the same high capacity for all times, regardless of the actual w are over-provisioning your table and paying for resources that you don't need1.
- ? It does not account for possible changes in the workload patterns over time. If your peak load times increase or decrease in the future, you may need adjust the provisioned capacity to match the new demand. This adds operational overhead and complexity to your application2.

Option B is incorrect because it suggests dividing the table into two tables and provisioning each table with half of the provisioned capacity of the or This solution has the following disadvantages:

- ? It complicates the data model and the application logic by splitting the data into two
- separate tables. You need to ensure that the queries are evenly distributed across both tables, and that the data is consistent and synchronized between This adds extra development and maintenance effort to your application3.

? It does not solve the problem of adjusting the provisioned capacity according to the workload patterns. You still need to manually or automatically sc capacity of each table based on the actual utilization and demand. This may result in under- provisioning or over-provisioning your tables2.

## Recommend!! Get the Full AWS-Certified-Data-Engineer-Associate dumps in VCE and PDF From SurePassExam

https://www.surepassexam.com/AWS-Certified-Data-Engineer-Associate-exam-dumps.html (80 New Questions)

## Recommend!! Get the Full AWS-Certified-Data-Engineer-Associate dumps in VCE and PDF From SurePassExam

https://www.surepassexam.com/AWS-Certified-Data-Engineer-Associate-exam-dumps.html (80 New Questions)

Option D is incorrect because it suggests changing the capacity mode from provisioned to on-demand. This solution has the following disadvantages: ? It may incur higher costs than provisioned capacity mode for predictable workloads. On-demand capacity mode charges for each read and write request yo consume, with no minimum capacity required. For predictable workloads, provisioned capacity mode can be more cost-effective than on-demand capacity mode as you can reserve the capacity you need at a lower rate1.

? It may not provide consistent performance during peak usage times, as on-demand capacity mode may take some time to scale up the resources to meet the sudden increase in demand. On-demand capacity mode uses adaptive capacity to handle bursts of traffic, but it may not be able to handle very large spike sustained high throughput. In such cases, you may experience throttling or increased latency.

## References:

- ? 1: Choosing the right DynamoDB capacity mode - Amazon DynamoDB
- ? 2: Managing throughput capacity automatically with DynamoDB auto scaling - Amazon DynamoDB
- ? 3: Best practices for designing and using partition keys effectively - Amazon DynamoDB
- ? [4]: On-demand mode guidelines - Amazon DynamoDB
- ? [5]: How to optimize Amazon DynamoDB costs - AWS Database Blog
- ? [6]: DynamoDB adaptive capacity: How it works and how it helps - AWS Database Blog
- ? [7]: Amazon DynamoDB pricing - Amazon Web Services (AWS)