Aws Data Engineer Test 7 - SynergisticIT

Welcome to your AWS Data Engineer Associate – Test-07

Name

Phone

Q1. Our Data Engineering Team is reviewing the architecture for processing and analyzing streaming data from IoT devices. The data is ingested through AWS Kinesis Data Firehose, transformed, and then stored in Amazon S3 as JSON files. Subsequently, the data is analyzed using Amazon Athena. The team noticed a significant cost associated with the Athena queries, which are run multiple times a day.

Which of the following strategies would be the most effective in optimizing costs related to the Athena queries?

Select 1 option(s):

Increase the Kinesis Data Firehose buffer size to batch more records before delivering to Amazon S3, thus reducing the number of files and consequently the cost of Athena queries.

Enable S3 Intelligent-Tiering on the buckets containing the IoT data to reduce storage costs and indirectly reduce the costs associated with Athena queries.

Implement Amazon Redshift to pre-aggregate the data before it is queried by Athena, leveraging Redshift‘s dense compute nodes for cost efficiency.

Convert the data into columnar formats like Parquet or ORC before storing in Amazon S3, which can reduce the amount of data scanned by Athena queries.

Q2. As part of a large-scale data ingestion project, a Data Engineer needs to configure a system to process files efficiently as soon as they are uploaded to an Amazon S3 bucket.

The files are expected to arrive at a very high rate, and there are concerns about the potential for throttling and the need to handle spikes in upload volume without losing any data. The system should also be able to scale automatically and provide a way to retry processing in case of failures.
Given these requirements, which of the following approaches should the Data Engineer implement to ensure efficient, reliable, and scalable processing of the uploaded files?

Select 1 option(s):

Implement an SQS queue to decouple the file upload and processing steps, with the Lambda function triggered by new messages in the queue.

Set up an Amazon CloudWatch Events rule to poll the S3 bucket for new files at regular intervals and trigger the Lambda function.

Use Amazon Kinesis Firehose to buffer the uploaded files in S3 and then trigger the Lambda function to process the files in batches.

Configure an S3 Event Notification to trigger the Lambda function directly for every file upload, ensuring immediate processing.

Q3. A data analytics team is using Amazon Redshift to query a product_table that contains a year column with the format YYYY-MM-DD. They are attempting to retrieve all sales data for the year 2023 using the following SQL query:

SELECT product_name, sum(price)
FROM product_table
WHERE year = 2023
GROUP BY product_name
However, the query does not return any results. What could be the reason for this issue?

Select 1 option(s):

The sum(price) function should be replaced with count(*) to count all product occurrences in 2023.

An ORDER BY clause needs to be added to the query to properly sort and display the results.

The WHERE clause is incorrectly filtering the year column; it should use the EXTRACT(YEAR FROM YEAR) function: WHERE EXTRACT(YEAR FROM YEAR) = 2023.

The query needs a HAVING clause to include products with non-zero prices: HAVING sum(price) > 0.

Q4. A Data Engineering Team is working on a project where they need to run SQL queries on their Amazon Redshift cluster from various applications and microservices.

These applications are diverse, running on different platforms and languages, and the team requires a simple, scalable, and secure way to execute these queries without managing database connections. They also need the ability to retrieve query results asynchronously.
Which Amazon Redshift feature should the team use to meet these requirements most effectively?

Select 1 option(s):

Implement AWS Lambda functions to manage Redshift query executions and handle database connections.

Utilize the Amazon Redshift Data API to execute queries and retrieve results asynchronously without managing database connections.

Use Amazon Redshift Spectrum for querying data across applications without direct cluster access.

Establish direct JDBC/ODBC connections from each application to the Redshift cluster for query execution.

Q5. A Data Engineering Team needs to help the security team to deliver logs from various AWS services in their production AWS account to a centralized logging AWS account for security analysis. They plan to use Amazon Kinesis Data Streams for real-time log processing and delivery.

To ensure secure and efficient log delivery between accounts, where should the Kinesis Data Stream be deployed and what security considerations should be taken into account?

Select 1 option(s):

Deploy the Kinesis Data Stream in the centralized logging account and establish cross-account access for the production account to write logs to the stream.

Implement the Kinesis Data Stream in the production account and use AWS Lambda for cross-account delivery of logs to the centralized logging account.

Set up the Kinesis Data Stream in the production account and configure it to push logs directly to the centralized logging account.

Deploy Kinesis Data Streams in both accounts, using one for collecting logs and the other for receiving and processing these logs.

Q6. A Data Engineer at a retail company needs to extract data from a PostgreSQL RDS database for reporting purposes. The task is to retrieve all orders from the ‘orders‘ table where the cities‘ names start with either “San“ or “El“.

The relevant column for city names in the table is labeled ‘city‘. Which SQL query should the data engineer use to accurately fetch this data in accordance with the specified criteria?

Select 1 option(s):

Q7. A company specializing in market analysis is looking to enhance its data analytics capabilities by incorporating diverse external datasets, such as financial market data, healthcare statistics, and geographical information. They require an efficient, secure, and straightforward method to access and integrate these varied datasets into their AWS environment for analysis.

Which AWS service should the company use to acquire third-party data sets seamlessly and incorporate them into their analytics workflows?

Select 1 option(s):

Deploy AWS Glue to discover and integrate external data sets into their data warehouse.

Explore the AWS Data Market for purchasing and downloading various external data sets.

Utilize AWS Data Exchange to find, subscribe to, and use third-party data sets in the AWS cloud.

Use Amazon Kinesis for streaming and analyzing real-time data from various external sources.

Q8. Your company‘s Cloud Data Engineering Team is using Amazon Kinesis Data Streams to process real-time streaming data from IoT devices. The stream is configured with 4 shards. Lately, the team observes that during peak operational hours, there are intermittent delays in data processing and some records appear to be dropped. The team is using a custom Kinesis consumer application for data processing.

What initial step should the team take to begin troubleshooting these performance issues?

Select 1 option(s):

Inspect the Kinesis Data Streams monitoring metrics in Amazon CloudWatch for incoming record rate and backend processing latencies.

Adjust the retention period of the data in the stream to allow more time for processing.

Change the data record format to a more compact serialization format like Avro or Protocol Buffers.

Increase the shard count in the Kinesis Data Stream to handle higher throughput.

Q9. To enhance the troubleshooting process for a data lake solution, a Cloud Engineering Consultant is tasked with analyzing historical access logs stored in Amazon S3. The logs are partitioned by date and consist of numerous plain text files. The consultant needs to conduct ad-hoc queries on these logs to understand usage patterns and detect potential unauthorized access attempts.

What is the most efficient way to accomplish this without significant data transformation or migration?

Select 1 option(s):

Create an Amazon EMR cluster and use Apache Hive for querying and analyzing the logs stored in S3.

Configure an Amazon Redshift Spectrum to scan the log files directly in S3 and analyze them using SQL queries in Redshift.

Deploy an Amazon OpenSearch Service domain, ingest the logs into OpenSearch, and then use Kibana for querying and visual analysis.

Use AWS Glue to catalog the log files in S3 and then perform the queries using Amazon Athena.

Q10. A medium-sized enterprise with fluctuating data analytics demands is exploring Amazon Redshift options. They are looking for a configuration that minimizes the need for active resource management, especially during unpredictable periods of high query volume. The ideal solution should adapt to changing demands while maintaining operational efficiency.

Which Redshift configuration should they consider to meet these requirements of adaptability and ease of management?

Select 1 option(s):

Select Redshift with On-Demand pricing and set up scaling triggers based on performance metrics to manage varying loads efficiently.

Opt for Redshift with RA3 nodes, which offer scalable storage and compute capabilities, and configure auto-scaling policies to handle variable query loads.

Implement a traditional Redshift cluster with dense compute nodes, using dynamic resizing features for adapting to fluctuating workloads.

Choose Redshift Serverless, as it provides the flexibility to automatically adapt to changing query volumes without manual scaling.

Q11. A Data Engineering Team is managing a large Amazon Redshift data warehouse. Over time, they‘ve noticed a gradual decline in query performance, primarily due to the frequent updates and insertions of data. They need to optimize their table structures to improve query efficiency but want to avoid deleting any data or performing a full table reorganization.

Which variant of the Redshift VACUUM command should they use to enhance their query performance while maintaining their current dataset intact?

Select 1 option(s):

VACUUM REINDEX

VACUUM DELETE ONLY

VACUUM FULL

VACUUM SORT ONLY

Q12. A customer is operating a complex big data environment on-premises, utilizing Apache Pig for batch processing, Presto for interactive querying, and Apache Flink for real-time stream processing. As part of their cloud migration strategy, they want to transition these workloads to AWS with minimal disruption and retain the flexibility offered by these diverse frameworks.

What AWS service should they consider for a comprehensive solution that accommodates their current processing paradigms?

Select 1 option(s):

Migrate to Amazon EMR, which supports diverse big data frameworks including Pig, Presto, and Flink.

Use AWS Glue for serverless data integration and ETL, adapting Pig, Presto, and Flink scripts to AWS Glue Jobs.

Implement AWS Data Pipeline for orchestrating the data processing workflows similar to Pig, Presto, and Flink.

Transition to Amazon Redshift for data warehousing and leverage its compatibility with various data processing frameworks.

Q13. A Data Engineering Team is looking to leverage the power of Apache Spark for complex data processing and analytics within AWS Athena. They have extensive datasets stored in Amazon S3 and want to perform advanced analytics that go beyond standard SQL capabilities.

The team is familiar with Spark‘s data processing framework and wants to integrate this with their existing Athena setup.
What feature in Athena should they use to enable and efficiently work with Apache Spark for their data analytics needs?

Select 1 option(s):

Athena Workgroup

Athena Data Catalog

Athena Query Settings

Athena Query Editor

Q14. A Cloud Data Engineering Consultant is tasked with setting up a centralized logging solution for multiple AWS Lambda functions used by a client‘s serverless application. The application has critical real-time processing components, and the engineering consultant must ensure that logging is set up to capture and respond to error conditions rapidly. The logs must be analyzed for specific error patterns, and notifications must be sent out when these patterns are detected.

Which of the following steps should the Cloud Data Engineering Consultant take to establish a robust logging and notification system using Amazon CloudWatch Logs? (Select TWO)

Select 2 option(s):

Directly modify each Lambda function‘s code to include API calls that send logs to a dedicated CloudWatch Logs group.

Configure each Lambda function to automatically send execution logs to CloudWatch Logs and set up metric filters to match the specific error patterns.

Set up CloudWatch Alarms based on the metric filters, triggering notifications through Amazon Simple Notification Service (SNS) when error patterns are found.

Use Amazon Kinesis Data Firehose to collect logs from Lambda functions and then process them with Amazon CloudWatch for real-time analysis.

Implement a custom logging layer within the Lambda functions to batch and push logs asynchronously to an Amazon DynamoDB table for subsequent analysis and alerting.

Q15. A Cloud Data Engineering team is tasked with creating an interactive dashboard for their retail company‘s management, displaying sales performance metrics across different regions. The data is stored in Amazon Redshift and updates multiple times throughout the day. The team needs to ensure that the dashboard in Amazon QuickSight reflects the most recent data without incurring excessive costs or performance overhead.

Which configuration should they use to best meet these requirements?

Select 1 option(s):

Set up Amazon QuickSight to directly query Amazon Redshift with live data for real-time updates in the dashboard.

Use AWS Lambda to transfer data from Amazon Redshift to Amazon S3 at regular intervals and set up Amazon QuickSight to query from S3 using SPICE.

Import the data from Amazon Redshift into AWS Glue DataBrew, perform necessary transformations, and then visualize it in Amazon QuickSight using direct queries.

Configure Amazon QuickSight to use SPICE (Super-fast, Parallel, In-memory Calculation Engine) to cache the data, and schedule regular data refreshes every hour.

Q16. A Data Engineering Team is developing an automated ETL pipeline using AWS Glue. The pipeline includes running Glue crawlers to classify and register schema in the Glue Data Catalog, executing Glue ETL jobs for data transformation, and storing the processed data in Amazon S3.

The team seeks a solution that offers seamless integration with their pipeline, ensuring that these stages are orchestrated efficiently with dependencies and error handling
Which AWS service should they use to orchestrate this ETL workflow with the LEAST operational overhead?

Select 1 option(s):

Use AWS Glue Workflows to create a managed ETL orchestration service.

Configure AWS Lambda functions to manually orchestrate each step in the ETL process,

Deploy Amazon Managed Workflows for Apache Airflow (MWAA) for orchestration.

Implement AWS Step Functions to orchestrate the ETL pipeline,

Q17. A Data Engineering team is working on setting up a secure and well-governed data lake using AWS Lake Formation. They need to design a schema that enables different groups within the organization to have varying levels of access to specific datasets. The datasets are primarily used for diverse analytics and machine learning tasks and contain sensitive information that requires fine-grained access control.

Which approach should the team take to meet these security and governance requirements while designing the schema in Lake Formation?

Select 1 option(s):

Create a single, comprehensive database and rely exclusively on Amazon S3 bucket policies to manage access to different datasets.

Utilize Lake Formation‘s granular permissions to create multiple databases corresponding to different access levels and datasets, assigning appropriate permissions to each user group.

Implement all datasets within a single database in Lake Formation and manage access through a combination of AWS IAM roles and Redshift Spectrum external schemas.

Design the schema based on a single-table design model in DynamoDB, using Lake Formation for managing only the metadata of the datasets.

Q18. A Data Engineering Team at a technology company is looking to query data from various databases, including Oracle, SQL Server, and PostgreSQL, which are hosted in Amazon RDS. They need a solution that allows them to efficiently query and analyze data across these different RDS databases, leveraging the power and flexibility of SQL.

Which AWS service and feature should they use to meet these requirements?

Select 1 option(s):

Employ AWS Lambda to execute SQL queries on each database and store the results in Amazon S3, where they can be analyzed further.

Utilize Amazon RDS Proxy to manage connections and run queries across different RDS databases, improving performance and efficiency.

Use Amazon QuickSight for direct SQL querying and analysis of data across various RDS databases, leveraging its built-in data visualization tools.

Leverage AWS Glue to catalog data from different RDS instances and use Amazon Athena Federated Queries for running queries across these cataloged datasets.

Q19. A financial analytics team is extensively using Amazon Athena for querying data in Amazon S3. As the company grows, they face the challenge of managing Athena query costs more effectively across different departments, each with distinct budgetary constraints. The team needs a method to ensure that query executions stay within the allocated budget for each department.

What is the best approach to manage and monitor Athena query costs for different departments while maintaining effective cost control?

Select 1 option(s):

Configure Athena Data Usage Plans, assigning different data usage limits and cost controls to each department.

Set up Athena Workgroups for each department, with tailored cost controls and query usage limits specific to each department‘s budget.

Implement Athena Saved Queries to track and limit the number and complexity of queries run by each department.

Utilize AWS Cost Explorer to monitor and manage Athena query costs on a departmental basis, setting up budget alerts for each department.

Q20. A Cloud Data Engineering Consultant has been tasked with optimizing the data transfer process between Amazon S3 and Amazon Redshift for a client. The client‘s data warehousing solution requires regular loading of large datasets into Redshift for complex querying, as well as periodic unloading of query results back into S3 for long - term storage and further processing. The consultant needs to ensure that these load and unload operations are performed efficiently and securely.

Which combination of methods should the consultant recommend to perform these operations effectively? (Select TWO)

Select 2 option(s):

Configure Redshift to automatically unload query results to S3 using the UNLOAD command, with encryption enabled for data security.

Utilize the COPY command in Amazon Redshift to parallelize data loads from Amazon S3, making use of Redshift‘s MPP (Massively Parallel Processing) architecture.

Set up AWS Data Pipeline with a custom script for moving data between S3 and Redshift, allowing for complex data transformation during the transfer.

Implement Redshift Spectrum to directly query data on Amazon S3 without loading it into Redshift, reducing data movement.

Q21. A Data Engineering Team has deployed an EC2 instance within a VPC to host a data processing application, accessible via a web interface on port 80. The security group for the instance is configured as follows:

Inbound Rules:
HTTP (Port 80) from source 0.0.0.0/0
SSH (Port 22) from source 10.0.1.0/24
Outbound Rules:
All traffic (All Ports) to destination 0.0.0.0/0
However, external data analysts outside the company‘s internal network are unable to access the application‘s web interface. What could be causing this access issue?

Select 1 option(s):

The inbound rule for HTTP is incorrectly set to allow traffic only from the internal network.

An inbound rule for HTTPS (Port 443) is required for external access.

The outbound rule does not permit returning HTTP traffic, impacting users outside the internal network.

The network ACLs associated with the EC2 instance‘s subnet might be blocking inbound HTTP traffic from external sources.

Q22. A company has an operational RDS PostgreSQL database, and a process which transfers the previous day‘s data to a Redshift cluster every night. They have an analytics application, which requires the previous 6 months of data with one day‘s delay. Some analysts also need to be able to produce infrequent ad hoc queries that combine data from the current day and the past 2 years.

Which combination of steps would meet their requirements with the LEAST operational overhead and costs (SELECT TWO)?

Select 2 option(s):

Use life-cycle rules to move data older than 6 months from Redshift to S3 and query the data using Redshift Spectrum.

Unload the data older than 6 months from Redshift to S3 and query the data using Redshift Spectrum.

Use Redshift federated queries to combine data from operational RDS database with Redshift data.

Use Redshift Spectrum to combine data from operational RDS database with Redshift data.

Use Athena federated query to combine data from operational RDS database with Redshift data.

Q23. A mobile application both reads from and writes to a DynamoDB table. The table is provisioned with 400 Write Capacity Units (WCUs), which are shared across 4 partitions. However, you notice that one partition consistently receives 250 WCUs per second, while the others receive significantly fewer. As a consequence, you encounter the error ProvisionedThroughputExceededException.

Which of the following options is the most likely reason for this error?

Select 1 option(s):

CloudTrail monitoring is lagging

The IAM policy is misconfigured

There is a hot partition

WCUs are applied across all DynamoDB tables and must be reconfigured

Q24. You are designing the data lake architecture at your organization for storing unstructured data on S3 that needs to be queried using Amazon Athena.

Which of the following data optimization techniques are according to best practices to enhance the query performance when using Athena (SELECT TWO)?

Select 2 option(s):

Use bucketing for lookups on keys with high cardinality.

Use MSCK REPAIR TABLE for partition maintenance.

Always use STRING as the type for partition keys.

Use bucketing for lookups on keys with low cardinality.

Use simple file formats like CSV that organize data by column instead of by row.

Q25. A Data Engineering Team is presented with a table in their AWS Glue ETL job that contains sales data. The table has rows representing sales figures for different products across various regions. The team needs to transform this data so that each product’s sales figures appear as separate columns, one for each region. This transformation is required to support a specific analytical use case.

What AWS Glue ETL transformation should the team use to convert the rows representing regional sales figures into columns for each product?

Select 1 option(s):

Use the Pivot transformation in AWS Glue ETL to restructure the data, transforming rows into columns based on regional sales figures for each product.

Implement a Map transformation in AWS Glue ETL to manually rearrange the data rows into the required columnar format.

Utilize the Join transformation in AWS Glue ETL to merge the rows into the desired columnar structure based on product and region.

Apply the Unpivot transformation in AWS Glue ETL to reorganize the table‘s rows into columns, aligning sales figures by product and region.

Q26. A team of data analysts frequently conducts identical queries on large datasets stored in Amazon S3, using Athena for analysis. They‘ve noticed that their query results typically remain relevant and unchanged for about 2 hours after execution. To optimize query efficiency and reduce costs, the team seeks a method to avoid redundant data scans for this 2-hour period.

What approach should they adopt to meet this requirement without the need for significant changes to their query infrastructure?

Select 1 option(s):

Enable Athena‘s query result reuse feature and set the maximum age for reusing query results to 120 minutes.

Configure Athena to automatically reuse query results, relying on the default maximum age setting, which is 120 minutes.

Utilize Amazon S3 lifecycle policies to retain and reuse query results for 2 hours.

Implement a custom AWS Lambda function to store and fetch query results for a period of 2 hours.

Q27. A Data Engineering Team is using Amazon Redshift for complex querying and data analysis tasks. They often need to run ad-hoc SQL queries and retrieve results for downstream processing in various analytics tools.

The team is looking for a solution that enables them to execute these queries programmatically while minimizing the overhead of result set management, such as dealing with large result sets or paginating through results.
What approach should the team take to improve their query execution and result set management process?

Select 1 option(s):

Utilize the Amazon Redshift Data API, which offers simplified query execution and automated result set pagination for large queries.

Set up AWS Glue with Amazon Redshift to handle query executions and manage large result sets through ETL jobs.

Use Amazon Redshift Spectrum to externally store and manage large query results, enabling efficient querying and retrieval.

Implement scheduled query executions using Amazon Redshift Scheduled Actions, storing results in designated S3 buckets for later retrieval.

Q28. A company stores customer data in Amazon S3, which includes Personally Identifiable Information (PII). They need a solution to provide access to this data for various users and applications while dynamically redacting PII for certain user groups based on their access level.

Which AWS service should the company use to enable PII redaction in response to S3 read requests from different users?

Select 1 option(s):

Configure Amazon S3 Access Points to control user access and apply PII redaction rules to the data served to different user groups.

Implement Amazon S3 Object Lambda to apply PII redaction logic dynamically to data retrieved from S3 based on user access levels.

Employ AWS Identity and Access Management (IAM) policies to restrict access to specific S3 objects and manually manage redacted and non-redacted data versions.

Use AWS Lambda functions triggered by Amazon S3 events to create redacted copies of the data in separate S3 buckets based on user access levels.

Q29. A retail company is planning to launch a digital marketing campaign on social media using Instagram Ads for their upcoming product. They want to track on an hourly basis key metrics from their campaign including clicks, conversion rate, and impressions and visualize them on a dashboard for the marketing team.

Which of the following steps allows you to move the required key metrics data to S3 for further processing and analysis with the LEAST development time?

Select 1 option(s):

Use Kinesis Data Firehose with an hourly buffer to stream data from Instagram Ads to S3.

Use Amazon AppFlow with a recurring trigger to ingest data from Instagram Ads to S3.

Use AWS Glue with a time-based trigger to ingest data from Instagram Ads to S3.

Use AWS DataSync scheduled transfer to copy data from Instagram Ads to S3.

Q30. A data engineer working for a financial services company has been tasked to create an ETL process for an application. The application should be triggered by the arrival of a file to a given S3 bucket, and the processing should be done using Apache Spark. The processing times may vary, but they can be up-to 30 minutes.

Which combination of steps would satisfy the engineer‘s requirements with the LEAST operational overhead (SELECT TWO)?

Select 2 option(s):

Create an AWS EMR job, which will be the target of the event.

Create an Eventbridge Event Bus, and set up the S3 Event Notifications to send Put Object notifications to the event bus. Connect the event bus to trigger the ETL process.

Create an AWS Lambda job, which will be the target of the event.

Create an AWS Glue job, which will be the target of the event.

Setup S3 Event Notifications for Put Object events for the given bucket. Connect the S3 Event Notifications to trigger the ETL process.

Q31. A marketing team at a mid-sized company is looking to streamline its workflow by integrating data from various SaaS applications like Salesforce, Slack, and Google Analytics into their AWS environment for comprehensive analysis. They need a solution that is easy to set up, manage, and capable of securely transferring data between these applications and AWS services without extensive coding.

Which AWS service is most suitable for achieving seamless integration and data flow between their SaaS applications and AWS with LEAST operational overhead?

Select 1 option(s):

Employ Amazon Kinesis to stream data from SaaS applications into the AWS environment.

Configure AWS Lambda functions to handle data integration and transfer between SaaS applications and AWS.

Use Amazon AppFlow to create secure and scalable data flows between SaaS applications and AWS services.

Implement AWS Data Pipeline to configure data transfers between each SaaS application and AWS services.

Q32. A Data Engineering Team requires a solution to perform real-time, federated analytical queries across multiple data sources, including a SQL database in Amazon RDS, a dataset in Amazon Redshift, a NoSQL database in Amazon DynamoDB, and data stored in Amazon S3. The solution must allow seamless querying across these data stores using a single service without the need for significant data movement or transformation processes.

What approach should they use to efficiently and directly execute these types of queries?

Select 1 option(s):

Utilize Athena Federated Queries, employing SQL for querying RDS, Redshift, and S3, and PartiQL for DynamoDB.

Implement Amazon Redshift Spectrum to query data across RDS, Redshift, DynamoDB, and S3 using SQL and PartiQL.

Configure Amazon EMR for complex data processing across RDS, Redshift, DynamoDB, and S3, followed by analysis.

Use AWS Data Pipeline to orchestrate data movement from RDS, Redshift, DynamoDB, and S3 into a central repository for querying.

Q33. An analytics team uses Amazon Redshift for complex, multi-join queries on their daily transaction data. They have noticed that some of these queries, particularly those involving aggregations and historical comparisons, are taking increasingly longer to execute, impacting their reporting efficiency. The team is looking for a way to speed up these specific types of queries without making significant architecture changes.

Which Redshift feature should they consider implementing to address this issue?

Select 1 option(s):

Use Amazon Redshift‘s concurrency scaling feature to add more query processing power during high-demand periods.

Enable Amazon Redshift Spectrum to extend query capabilities to external data in Amazon S3, thereby offloading some of the query loads.

Configure Redshift Enhanced VPC Routing to improve the performance of data load operations from Amazon S3.

Implement Amazon Redshift materialized views to precompute and store the results of expensive aggregation and join operations.

Q34. A financial services company is using Amazon RDS for PostgreSQL to manage sensitive customer data. The database is accessed by various applications deployed in an AWS environment. The company‘s security policy mandates that the data must be encrypted at rest. The database administrator needs to enable encryption for the existing unencrypted database with minimal downtime.

Which of the following approaches should the administrator take?

Select 1 option(s):

Take a snapshot of the existing RDS instance, copy the snapshot with encryption enabled, and then restore the database from the encrypted snapshot.

Enable encryption on the existing RDS instance directly via the AWS Management Console.

Use AWS Key Management Service (KMS) to encrypt the existing database file at the storage level.

Create a new RDS instance with encryption enabled and migrate the data from the existing unencrypted database using AWS Database Migration Service (DMS).

Q35. A Data Engineering Consultant is tasked with ensuring the data quality of a marketing analytics pipeline. The pipeline ingests data from various sources like social media, ad campaigns, and website traffic, and stores it in a central data lake on Amazon S3. To maintain the high quality of data and ensure the reliability of analytics, the consultant plans to use AWS Glue DataBrew.

Which approach should they take to establish effective data quality monitoring and enforcement using DataBrew?

Select 1 option(s):

Use AWS Glue DataBrew to directly modify the source data on ingestion, enforcing data consistency and correcting anomalies before data is stored in S3.

Establish DataBrew jobs with custom data quality rules that validate the integrity, accuracy, and completeness of the data as it‘s processed, alerting the team in case of rule violations.

Implement an AWS Glue workflow that triggers DataBrew for data transformation and a separate Amazon Redshift cluster for running advanced data quality analytics.

Configure AWS Glue DataBrew to apply transformations on the ingested data and store the cleaned data in a new S3 bucket, using Amazon QuickSight for visualizing data quality metrics.

Q36. An organization using Amazon Athena has multiple teams, each with different query execution requirements. The organization decides to enforce workgroup-wide settings in Athena to standardize query execution parameters and control costs.

What is an important consideration when enforcing workgroup-wide settings in Athena?

Select 1 option(s):

Workgroup-wide settings in Athena are only applicable for cost controls and do not affect other query execution parameters.

Enforcing workgroup-wide settings means all queries in the workgroup will use these settings, overriding any individual client-side settings.

Workgroup-wide settings in Athena can only be applied to new queries, and existing queries will continue to use their original client-side settings.

Users can temporarily override workgroup-wide settings for individual queries if they have the necessary permissions.

Q37. A Cloud Data Engineering team at an e-commerce company is working to improve the system‘s resilience during high-traffic events, such as seasonal sales. They need a robust monitoring solution to track application performance and send automatic alerts to the team when performance metrics hit critical thresholds.

Which combination of AWS services should you implement to ensure that you can dynamically track application metrics and receive immediate alerts when specific performance thresholds are breached? (Select TWO)

Select 2 option(s):

Implement Amazon CloudWatch to keep an eye on application performance metrics and configure alarms that trigger when those metrics exceed the set thresholds.

Incorporate AWS Config to monitor the application environment‘s compliance status and alert the Cloud Data Engineering team about any undesirable configuration changes.

Use Amazon Simple Email Service (SES) to automatically send out email notifications when the CloudWatch alarms go off.

Set up Amazon Simple Notification Service (SNS) topics to distribute alerts through email and SMS to the Cloud Data Engineering team when CloudWatch alarms are triggered.

Deploy Amazon Kinesis Data Streams for comprehensive collection and analysis of application metrics, looking out for notable fluctuations.

Q38. In an effort to improve security and manageability, a Cloud Data Engineering Team is re-evaluating the strategy used to manage environment-specific configurations and non-sensitive parameters, like API endpoints and service URLs, across multiple AWS Lambda functions. Their primary requirements are low-latency access, no need for secret rotation, and simple integration with existing AWS Lambda and CloudFormation templates. They also prefer a solution that does not incur additional costs.

Considering these requirements, which service should the team use for storing and managing these configurations and parameters?

Select 1 option(s):

Store the configurations and parameters in an Amazon DynamoDB table and access them from Lambda functions as needed.

Configure environment variables directly in the Lambda function settings for each set of configurations and parameters.

Utilize AWS Systems Manager Parameter Store for storing configurations and parameters, allowing Lambda functions to retrieve them with minimal latency.

Use AWS Secrets Manager to store all configurations and parameters, retrieving them dynamically within Lambda functions.

Q39. In an AWS Redshift environment, a data engineer needs to analyze queries where the query optimizer has identified potential performance issues such as inefficient joins or excessive data scanning.

The engineer is looking for a system view that not only highlights these issues but also offers recommendations for optimization.
Which Redshift system view should be used for this purpose?

Select 1 option(s):

STL_PLAN_INFO

STL_ALERT_EVENT_LOG

STL_USAGE_CONTROL

STL_QUERY_METRICS

Q40. A Data Engineering Team is designing a system to manage a series of dependent data processing and transformation jobs. These jobs involve various AWS services, including AWS Lambda for data manipulation, Amazon S3 for data storage, and AWS Glue for ETL operations. The team needs a robust solution to orchestrate these jobs, ensuring that they execute in a specific sequence and with conditional error handling.

Which AWS service should the team use for the most effective orchestration of these data processing workflows?

Select 1 option(s):

Implement Amazon Managed Workflows for Apache Airflow (MWAA) for managing job sequences and dependencies.

Configure Amazon EventBridge to trigger and manage the sequence of data processing jobs efficiently.

Use AWS Step Functions to coordinate the various data processing and transformation tasks across different AWS services in a cost-effective manner.

Utilize AWS Glue Workflows for orchestrating the data processing jobs, focusing on ETL operations.

Q41. As a Data Engineer, you are optimizing query performance for a client‘s data warehouse solution. The client is dealing with large datasets and is particularly focused on enhancing the performance of join queries while minimizing data shuffling between nodes.

Which distribution style within their data warehouse solution, specifically Amazon Redshift, would be best suited for this purpose?

Select 1 option(s):

Implementing the AUTO distribution style in Amazon Redshift, allowing the service to automatically manage the distribution of data across nodes based on the table size and query patterns, potentially improving join query performance.

Utilizing the KEY distribution style in Amazon Redshift by assigning it to a frequently joined column, thereby ensuring that data corresponding to the key is colocated on the same node, which can significantly reduce the amount of data shuffling required for join queries.

Adopting the EVEN distribution style in Amazon Redshift, which distributes the data uniformly across all slices and nodes.

Selecting the ALL distribution style in Amazon Redshift, which duplicates the entire table across every node.

Q42. A Data Engineering Team is looking to integrate a Python script into their AWS infrastructure to process HTTP requests. They want to use Amazon API Gateway to handle the incoming requests and trigger the Python script accordingly.

What is the simplest and most effective way to couple their Python script with API Gateway for request processing?

Select 1 option(s):

Couple the Python script with an AWS Lambda function and set API Gateway to trigger the Lambda function.

Store the Python script in an Amazon S3 bucket and configure API Gateway to retrieve and execute the script upon receiving requests.

Directly host the Python script on API Gateway and configure API methods to execute the script.

Use AWS Elastic Beanstalk to deploy the Python script and configure API Gateway to forward requests to the Elastic Beanstalk environment.

Q43. A Cloud Data Engineering Consultant is advising a client on setting up a secure data processing workflow using AWS services. The workflow includes a CloudFormation template to deploy an AWS Lambda function and an Amazon API Gateway. The Lambda function needs to invoke AWS CLI commands to manage AWS resources as part of its execution.

To enable this securely, which of the following approaches should the consultant recommend for setting up IAM roles and permissions?

Select 1 option(s):

Assign an IAM role to the Lambda function with necessary permissions for the AWS CLI commands and ensure that the Lambda execution role has the AWSLambdaBasicExecutionRole policy attached.

Create an IAM role with the necessary permissions for the AWS CLI commands and assign it to the CloudFormation stack during deployment.

Embed access and secret keys directly in the Lambda function‘s code to provide it with the necessary permissions to execute AWS CLI commands.

Grant the Lambda function the necessary permissions by adding the AdministratorAccess policy to its execution role to cover all possible AWS CLI command requirements.

Q44. A startup focusing on real-time analytics for financial data is building their infrastructure on AWS. They have a continuous stream of data coming from various financial markets and require a scalable solution to ingest, process, and analyze this data in real-time. Their system must also accommodate the storage of large datasets efficiently for ad-hoc querying.

Which of the following AWS service combinations would best fulfill these requirements?

Select 1 option(s):

Implement Amazon MSK for ingestion, process data using Amazon EMR running Apache Spark, store in Amazon Redshift, and query using Amazon QuickSight.

Leverage AWS Kinesis Data Firehose for data ingestion, process with Amazon SageMaker Data Wrangler, store in Amazon S3, and utilize AWS Glue DataBrew for data preparation before querying with Amazon Athena.

Utilize Amazon Managed Streaming for Apache Kafka (MSK) for data ingestion, process using AWS Glue, store in Amazon S3, and query using Amazon Redshift with Redshift Spectrum.

Use AWS Kinesis Data Streams for real-time data ingestion, process with AWS Lambda, store in Amazon S3, and analyze using Amazon Athena.

Q45. A company is using AWS Athena to query data stored in Amazon S3. They recently added new partitions to their S3 data, but these partitions are not yet available for querying in Athena.

What command should they use to update the Athena table and make the new partitions queryable?

Select 1 option(s):

ALTER TABLE ADD PARTITION

MSCK REPAIR TABLE

CREATE TABLE AS SELECT (CTAS):

Q46. A Data Engineering Team is tasked with selecting an appropriate compute service for a new application that processes streaming data. The application‘s load will be highly variable, often experiencing unpredictable spikes. The team must decide between using Amazon EC2 and AWS Lambda.

What are the key considerations they should evaluate in making this decision?

Select 1 option(s):

Go with AWS Lambda for its ease of management and pay-as-you-go pricing model, which can be beneficial for applications with variable and unpredictable loads.

Choose Amazon EC2 because it provides the flexibility to customize the compute environment for specialized requirements, which could be critical for applications with streaming data.

Opt for AWS Lambda due to its ability to automatically scale with the application‘s load, offering a cost-effective solution for unpredictable, sporadic traffic patterns.

Select Amazon EC2 for its capacity to support more extended computing operations, which is typically needed for in-depth analysis and processing of streaming data.

Q47. A Data Engineer is developing a serverless workflow using AWS Step Functions for processing data that varies significantly in structure and content. The workflow requires different processing paths based on specific attributes of the input data. To efficiently handle this variability, the architect needs to incorporate a state that allows for conditional routing within the state machine, directing the workflow to different processing tasks based on the characteristics of the data.

Which type of state should be used in the AWS Step Functions state machine to achieve this conditional routing?

Select 1 option(s):

Implement the ‘Choice‘ state to direct the workflow to different paths based on the input data attributes.

Utilize the ‘Pass‘ state with conditional logic to modify and assess input data, thereby directing it to specific processing paths.

Apply the ‘Map‘ state, tailored to iterate over the data and conditionally direct each item to the appropriate processing task.

Use the ‘Parallel‘ state, configuring it to evaluate input data and concurrently execute different branches based on data conditions.

Q48. A company has completed a full database load to their S3 data lake and is now using AWS Database Migration Service (DMS) to stream ongoing change data capture (CDC) updates. They need an effective method to apply these CDC updates, including deletes and upserts, to their structured data in S3.

What is the most efficient and transactionally consistent approach for applying these updates?

Select 1 option(s):

Implement Amazon Redshift Spectrum to manage and apply CDC updates to the structured data in the S3 data lake.

Trigger AWS Lambda functions for each CDC update to apply changes to the S3 data.

Structure the data in Apache Iceberg format on S3 and use AWS Glue to apply CDC logic.

Use AWS Glue with custom ETL scripts to process and apply the CDC changes to the data in S3.

Q49. A Data Engineering Team is setting up a new job in AWS Glue ETL. They have configured their data sources and targets, defined the necessary transformations, but when they attempt to run the job, it fails with an authorization error. The team needs to ensure that the AWS Glue job has the necessary permissions to access the required AWS resources for execution.

What should the team do to resolve this issue and successfully run the Glue ETL job?

Select 1 option(s):

Assign an AWS Glue Service Role to the ETL job, ensuring it has the required permissions to access necessary resources.

Attach an IAM policy directly to the AWS Glue ETL job with the necessary permissions.

Enable AWS Glue Data Catalog encryption, which automatically grants necessary permissions to the ETL job.

Create an IAM group with the appropriate permissions and add the AWS Glue service account to this group.

Q50. A Data Engineering Team needs to perform a one-time, ad-hoc analysis on a large dataset stored in Amazon S3 as CSV files. To ensure this analysis is as efficient and cost-effective as possible, they require a method to transform these CSV files into a more optimized format like Parquet.

What is the approach with the LEAST operational overhead?

Select 1 option(s):

Using Athena‘s Create Table As Select (CTAS) feature to create a new table in Parquet format based on the existing CSV data.

Setting up an AWS Glue ETL job to convert the CSV data into Parquet format.

Writing a Lambda function to read the CSV files and write them back in Parquet format.

Altering the table properties in Athena to change the data format from CSV to Parquet.

Q51. During a routine security review, your team, consisting of Cloud Data Engineers, noticed that several Amazon RDS instances used for transactional data processing have security groups that are outdated and overly permissive. These RDS instances are located in a VPC and are accessed by different application servers within the same VPC. You‘re tasked with updating the security groups to enforce the principle of least privilege.

How should you proceed to ensure the RDS instances remain accessible only by the designated application servers while enhancing the overall security posture?

Select 1 option(s):

Restrict the RDS security group to accept inbound connections only on the database port (e.g., 5432 for PostgreSQL) from the security group associated with the application servers.

Change the RDS instances to use Publicly Accessible settings and limit access using network Access Control Lists (ACLs) to only include IP addresses of the application servers.

Leave the security group as is, but implement AWS Shield Advanced for enhanced protection against data breaches and other external threats.

Update the security group to deny all inbound traffic, relying on RDS‘s built-in database authentication and encryption features for security.

Q52. A data analytics team is working with time-sensitive financial data. Their primary dataset resides in an Amazon Redshift cluster, but crucial, frequently updated transactional data is stored in a PostgreSQL database on Amazon RDS. They need a solution to analyze combined data sets in real-time, ensuring that the most current transactional information is included in their analysis without the overhead of continually transferring data between the two systems.

What approach should they consider to efficiently perform these real-time, integrated queries?

Select 1 option(s):

Implement Redshift Spectrum for querying external data, creating external tables that reflect the RDS PostgreSQL data.

Regularly export transactional data from RDS PostgreSQL to S3 and use Redshift Spectrum for querying alongside Redshift data.

Utilize Athena Federated Queries to perform real-time querying across both the Redshift data and the RDS PostgreSQL database.

Use Redshift Federated Query to directly integrate and query data from the Redshift cluster and the RDS PostgreSQL database in real-time.

Q53. A company has set up an Amazon S3 VPC endpoint to allow private connections between their VPC and Amazon S3. However, they find that despite the presence of the endpoint, certain VPC resources are unexpectedly denied access to S3 buckets.

Assuming that the VPC endpoint and network configurations are correctly set up, what is a likely reason for this access issue?

Select 1 option(s):

Network Access Control Lists (NACLs) are configured to block traffic between the VPC and the S3 endpoint.

Security Groups associated with the VPC resources incorrectly restrict outbound traffic to the S3 endpoint.

The S3 bucket policies explicitly deny access from the VPC endpoint‘s ID.

The S3 buckets are encrypted with a key that the VPC resources do not have permission to use.

Q54. A company uses Amazon DynamoDB for their application, which experiences predictable variations in workload. On certain days, the traffic is significantly lower, while on others, it follows a common pattern of high demand. The company seeks to optimize costs and minimize operational overhead by adjusting DynamoDB capacity in line with these predictable traffic patterns.

What is the most effective strategy for scaling their DynamoDB capacity to align with their workload patterns, while ensuring the least operational overhead?

Select 1 option(s):

Manually adjust DynamoDB capacity at the start of each day based on expected traffic.

Provision DynamoDB capacity to handle the maximum peak traffic at all times.

Use DynamoDB‘s built-in auto-scaling feature to dynamically adjust capacity based on real-time traffic.

Implement Application Auto Scaling to adjust DynamoDB capacity based on pre-defined schedules that align with the known high and low traffic patterns.

Q55. An analyst is using Amazon Athena and needs to create a new table in the Parquet format with SNAPPY compression from an existing table named old_table. The task requires the correct syntax for a Create Table As Select (CTAS) statement in Athena. Which of the following SQL statements correctly achieves this objective?

Select 1 option(s):

CREATE TABLE new_table AS
SELECT *
FROM old_table
USING (‘format‘ = ‘Parquet‘, ‘compression‘ = ‘SNAPPY‘);

CREATE TABLE new_table
WITH (
format = ‘Parquet‘,
write_compression = ‘SNAPPY‘)
AS SELECT *
FROM old_table;

CREATE NEW TABLE new_table
FROM old_table
WITH FORMAT AS ‘Parquet‘ AND COMPRESSION AS ‘SNAPPY‘;

CREATE TABLE new_table
SET FORMAT = ‘Parquet‘, COMPRESSION = ‘SNAPPY‘
AS SELECT *
FROM old_table;

Q56. A data engineering team is configuring their ETL jobs in AWS Glue, using version 3.0 for Spark-based processes. Their jobs are not time-sensitive and can tolerate varying start and completion times.

Given these requirements, what is the most cost-effective AWS Glue setting the team should choose to optimize their job configuration?

Select 1 option(s):

Enable Job Bookmarking

Increase the number of DPUs

Set Job ExecutionClass to STANDARD

Set Job ExecutionClass to FLEX

Q57. A system administrator is managing an EC2 instance attached to an EBS volume currently configured as a General Purpose SSD (gp2). Due to changing performance requirements, they need to switch the EBS volume type to Provisioned IOPS SSD (io1) to achieve higher IOPS.

What approach should the administrator take to change the EBS volume type without incurring downtime?

Select 1 option(s):

Use AWS DataSync to transfer data from the gp2 volume to a newly created io1 volume, then switch the EC2 instance to use the new volume.

Directly modify the EBS volume type from gp2 to io1 using the Elastic Volumes feature while the volume is in use.

Create an AMI from the EC2 instance, launch a new instance with an io1 volume, and then switch over to the new instance.

Detach the gp2 volume, create a snapshot, and then create and attach a new io1 volume from the snapshot.

Q58. A Data Engineering Team is planning to deploy an Amazon EMR (Elastic MapReduce) cluster for processing large datasets with Apache Spark. The team wants to choose the most cost-effective and efficient instance types for their EMR cluster, considering both performance and cost.

Which of the following instance types would be the optimal choice for their EMR workloads?

Select 1 option(s):

Traditional x86-based instances, known for their compatibility and broad support across various big data applications.

Memory-optimized instances for handling large datasets and in-memory computations.

GPU-based instances for enhanced performance in data processing and analytics.

AWS Graviton-based instances, which offer great price-performance ratio for EMR workloads.

Q59. A Data Engineering Consultant is working on optimizing the CI/CD pipeline for a data processing application on AWS. The application‘s CI/CD pipeline utilizes AWS CodeBuild for its build and test processes. One of the requirements is to reduce build times and resource consumption. The application has conditional build scenarios, where certain builds require more compute resources than others based on the complexity and size of the data being processed.

Which feature of AWS CodeBuild should the consultant configure to optimize the build process while considering varying resource needs for different builds?

Select 1 option(s):

Use CodeBuilds buildspec file to define different compute types for each build scenario, dynamically adjusting the environment based on the source code‘s requirements.

Implement build batching in CodeBuild to group source versions and run them as a single build, ensuring efficient use of resources for varying build scenarios.

Configure CodeBuild to utilize On-Demand instances for regular builds and switch to Spot Instances for resource-intensive builds, optimizing costs and compute resource usage.

Leverage the CodeBuild cache feature to store frequently used build artifacts and dependencies, reducing build times for similar build scenarios.

Q60. A healthcare organization is looking to store highly confidential patient records in an Amazon S3 bucket. The storage solution must comply with regulatory standards that require not only encryption of data at rest but also stringent control and auditing over who can access the encryption keys. Since their IT team lacks the resources to manage encryption processes manually, they need an encryption method that is both secure and manageable within their AWS environment.

What encryption method should they use for their S3 bucket to ensure compliance with these standards?

Select 1 option(s):

Implement Server-Side Encryption with AWS Key Management Service (SSE-KMS) for encryption at rest and to maintain control over encryption keys.

Use Amazon S3 default encryption with an Amazon-managed key for automatic encryption of all stored data.

Enable Amazon S3 managed keys (SSE-S3) for server-side encryption to ensure data is encrypted at rest with Amazon-managed keys.

Opt for client-side encryption with a client-managed key for encrypting data before uploading it to the S3 bucket.