Question 1

A data engineer is using Amazon Athena for querying customer engagement data stored in Amazon S3. The analyst needs to extract information about customer interactions for the year 2023 from a table called interaction_data. The initial query designed to fetch the total interactions per customer for 2023 is not yielding results for some customers known to have interactions in that year. The query needs adjustment to correctly reflect the entire dataset.

The original query is:

SELECT customer_id, sum(interactions)

FROM interaction_data

WHERE year = 2023

GROUP BY customer_id

How should the data engineer modify the Athena query to ensure it includes all relevant customer data for 2023?

Options :

A : Replace WHERE year = 2023 with a more inclusive condition, such as WHERE year >= 2023, to capture late-recorded data.

B : Introduce a CASE statement within the SUM function to handle any null values in the interactions column.

C : Include a subquery to first select distinct customer IDs from the interaction data, then sum interactions for those customers.

D : Add a LEFT JOIN with another table that lists all customers, ensuring inclusion of customers with zero interactions.

Answer: D

Question 2

A growing fintech company is leveraging an Amazon Redshift cluster equipped with dense compute (DC2) nodes. To accommodate their expanding user base, the company needs to dynamically adjust both read and write capacities based on varying workloads. The data engineer has been tasked to configure the Redshift cluster to automatically add additional query processing power.

What action should the data engineer take to enable this functionality?

Options :

A : Enable elasticity at the Redshift cluster by configuring Elastic IP addresses for each node.

B : Configure the Redshift cluster to utilize Elastic Resize for adjusting node count based on demand.

C : Implement automatic WLM on the Redshift cluster to dynamically manage query queues.

D : Activate concurrency scaling in the Redshift cluster-s workload management (WLM) configuration.

Answer: D

Question 3

A company uses Amazon Redshift as its data warehouse. Data encoding is applied to the existing tables of the

data warehouse. A data engineer discovers that the compression encoding applied to some of the tables is not

the best fit for the data.

The data engineer needs to improve the data encoding for the tables that have sub-optimal encoding.

Which solution will meet this requirement?

Options :

A :

Run the ANALYZE command against the identified tables. Manually update the compression encoding of columns based on the output of the command.

B :

Run the ANALYZE COMPRESSION command against the identified tables. Manually update the compression encoding of columns based on the output of the command.

C : Run the VACUUM REINDEX command against the identified tables.

D : Run the VACUUM RECLUSTER command against the identified tables.

Answer: B

Question 4

A data engineer at a healthcare company needs to implement a solution to ensure that sensitive patient data in an Amazon S3 bucket is encrypted at rest. The company's security policy mandates the use of customer-managed keys for encryption to meet compliance requirements. The data engineer must also ensure that key usage can be audited.

Which of the following would be the MOST suitable solution?

Options :

A : Use server-side encryption with AWS KMS managed keys (SSE-KMS), create a customer managed key, and enable logging and auditing via AWS CloudTrail.

B : Encrypt data client-side using a customer-managed encryption library before uploading to S3, and utilize AWS CloudWatch for monitoring access.

C : Implement server-side encryption with AWS KMS managed keys (SSE-KMS), using an AWS managed key (default key), and track key usage through AWS Config.

D : Enable server-side encryption on the S3 bucket with Amazon S3 managed keys (SSE-S3) and use AWS CloudTrail to audit key usage.

Answer: A

Question 5

A company is deploying a stateful application on Amazon EC2 that requires temporary storage for processing large datasets with maximum I/O. The application must also maintain a smaller subset of essential state data that needs to persist beyond the life of the instance. The company needs a storage solution that ensures the essential data is not lost if the EC2 instance is stopped or terminated.

Which combination of storage solutions should the company choose?

Options :

A : Attach Amazon EBS volumes for essential state data and use Amazon DynamoDB for temporary data.

B : Attach additional Amazon EBS volumes for temporary data processing and rely on the root device volume for essential state data.

C : Attach Amazon EC2 instance store volumes for temporary data processing and Amazon Elastic Block Store (EBS) for essential state data.

D : Implement a combination of Amazon EC2 instance store volumes for rapid I/O and EBS snapshots for daily backups of essential data.

Answer: C