DEA-C01 Mock Exam 90 — 25 Questions

Question 1

A company uses Amazon Redshift for analytics. They notice that some queries are slow due to data redistribution. The data engineer wants to minimize data movement across nodes. Which table design strategy should be used? (Choose TWO.)

Accepted Answer

Choose a distribution key that matches the join key for large tables.. Option C is correct because when large tables are joined on their distribution keys, Redshift can perform a collocated join, meaning the matching rows are already on the same node slice, eliminating the need to redistribute data across the network. This directly minimizes data movement and speeds up query execution.

Answer

Set the distribution style to AUTO for all tables.

Answer

Define compound sort keys on frequently filtered columns.

Answer

Use EVEN distribution for all tables.

Question 2

A data engineer is designing a data store for a real-time analytics application that requires sub-millisecond read and write latency. The data is accessed via a REST API. Which AWS services should the engineer consider? (Choose THREE.)

Accepted Answer

Amazon ElastiCache for Redis. Amazon ElastiCache for Redis is correct because it provides an in-memory data store that delivers sub-millisecond read and write latency, ideal for real-time analytics. Redis supports data structures like strings, hashes, and sorted sets, and can be accessed via REST API through a caching layer or directly with Redis commands. This makes it suitable for low-latency, high-throughput workloads where disk-based storage would introduce unacceptable delays.

Answer

Amazon S3

Answer

Amazon RDS for MySQL

Question 3

A company is using Amazon S3 for data lake storage. They need to query the data directly using SQL without loading it into a database. Which AWS service should be used?

Accepted Answer

Amazon Athena. Amazon Athena is the correct choice because it is a serverless, interactive query service that allows you to analyze data directly in Amazon S3 using standard SQL, without needing to load or transform the data into a database. Athena uses Presto under the hood and supports querying structured, semi-structured, and unstructured data formats (e.g., CSV, JSON, Parquet, ORC) stored in S3, making it ideal for ad-hoc SQL queries on a data lake.

Answer

Amazon Redshift Spectrum

Answer

Amazon EMR

Answer

AWS Glue

Question 4

A company uses Amazon DynamoDB to store session data for a web application. The application experiences throttling during peak hours. The data engineer needs to reduce throttling. Which THREE actions should the engineer take?

Accepted Answer

Use DynamoDB Accelerator (DAX) to cache read requests.. DynamoDB Accelerator (DAX) is an in-memory cache that reduces read latency and offloads read requests from the DynamoDB table, directly decreasing the number of read capacity units consumed. By caching frequently accessed session data, DAX mitigates throttling during peak hours without requiring changes to the table's provisioned capacity.

Answer

Increase the provisioned read capacity units.

Answer

Enable auto scaling on the table.

Question 5

A data engineer is migrating a large Oracle data warehouse to Amazon Redshift. The engineer needs to ensure optimal performance. Which TWO practices should the engineer follow?

Accepted Answer

Choose appropriate sort keys based on common query patterns.. Option A is correct because Amazon Redshift uses sort keys to physically order data on disk, which allows the query optimizer to skip large blocks of data during scans via zone maps. Choosing sort keys based on common query patterns (e.g., range filters or frequent GROUP BY columns) dramatically reduces I/O and improves query performance, especially for large tables.

Answer

Design the schema as a normalized star schema with row-based storage.

Answer

Manually define compression encodings for each column.

Answer

Stage data in Amazon S3 before loading into Redshift.

Question 6

A data engineer is setting up a data pipeline to ingest data from an Amazon RDS for MySQL database into Amazon S3 using AWS Glue ETL. The Glue job uses a JDBC connection to read from the MySQL database. The job runs successfully, but the engineer notices that the job is taking longer than expected. The MySQL database is 500 GB in size and the Glue job uses 10 workers of type G.1X. The engineer wants to improve the performance of the extraction phase. The database is actively used by other applications, so the engineer must minimize the impact on the source database. Which approach should the engineer take?

Accepted Answer

Partition the table by a numeric column, such as the primary key, and use the 'hashex' or 'hashpar' partitioning option in the Glue JDBC connection.. Option A is correct because partitioning the table on a key column (e.g., primary key) allows Glue to read in parallel from multiple partitions, reducing the load on the database and improving performance. Option B is wrong because increasing workers without partitioning may cause the database to be overwhelmed with connections. Option C is wrong because using a read replica offloads the read traffic but still requires efficient partitioning; also, setting up a read replica adds cost and complexity. Option D is wrong because full table scans are inefficient; incremental loads are for ongoing changes, not initial extraction.

Answer

Use an incremental extraction strategy with a watermark column to reduce the amount of data read each time.

Answer

Create a read replica of the MySQL database and configure the Glue job to read from the replica.

Answer

Increase the number of Glue workers to 20 to increase parallelism.

Question 7

An e-commerce company uses Amazon DynamoDB as the primary data store for its product catalog. The table has a simple primary key (ProductID) and handles 10,000 writes per second during peak hours. Recently, the engineering team noticed increased write latency and throttled requests during peak times. The table's provisioned write capacity is set to 12,000 WCU. What is the most likely cause of the throttling?

Accepted Answer

Write traffic is unevenly distributed across partitions. Option C is correct because DynamoDB partitions data by the primary key's hash value. If write traffic is unevenly distributed across partitions (e.g., a few ProductIDs receive most writes), those hot partitions can exceed their individual throughput limits (3,000 WCU per partition for provisioned tables), causing throttling even when the table's total provisioned WCU of 12,000 is not fully utilized.

Answer

The table has reached the maximum number of partitions

Answer

DynamoDB Accelerator (DAX) is not configured

Answer

A global secondary index is consuming write capacity

Question 8

A data engineer reviewed the S3 lifecycle policy shown in the exhibit. The engineer notices that objects under the 'logs/' prefix are being deleted after 365 days. The business requirement is to retain logs for at least 5 years. What should the engineer change in the lifecycle policy?

Accepted Answer

Set the expiration days to 1825. The business requirement is to retain logs for at least 5 years, which is 1,825 days (5 × 365). The current lifecycle policy sets expiration to 365 days, causing premature deletion. By setting the expiration days to 1,825, the S3 lifecycle policy will delete objects under the 'logs/' prefix only after 5 years, meeting the retention requirement.

Answer

Change the prefix to 'logs/archive/'

Answer

Change the transition to GLACIER on day 365

Answer

Remove the expiration action

Question 9

A gaming company collects player event data from mobile devices. The data is sent to an Amazon API Gateway endpoint, which triggers an AWS Lambda function that writes the data to an Amazon DynamoDB table. The company wants to also store the data in Amazon S3 for historical analysis. The data volume is about 100 GB per day. The data engineer needs to design a solution to copy data from DynamoDB to S3 with minimal impact on the DynamoDB table. What should the data engineer do?

Accepted Answer

Enable DynamoDB Streams on the table and configure a Lambda function to write changes to S3.. Option A is correct. Using DynamoDB Streams with a Lambda function that writes to S3 is a common pattern for real-time replication with minimal impact. Option B is wrong because Export to S3 is a one-time or scheduled export, not continuous. Option C is wrong because using Scan would consume read capacity and impact performance. Option D is wrong because adding a secondary index does not help with exporting data.

Answer

Create a global secondary index on the table and export the index to S3.

Answer

Use AWS Glue to scan the DynamoDB table and write results to S3 every hour.

Answer

Use the DynamoDB Export to S3 feature to export the entire table daily.

Question 10

A healthcare company processes patient records in near-real-time using Amazon Kinesis Data Streams. Each record contains sensitive personal health information (PHI). The data must be encrypted at rest and in transit. The company also needs to audit access to the data. The data engineer is designing the ingestion pipeline. Which combination of services and configurations meets these requirements?

Accepted Answer

Use Kinesis Data Streams with SSE-KMS and TLS, and enable CloudTrail for data events.. Option C is correct. Kinesis Data Streams supports server-side encryption (SSE) using AWS KMS for at-rest encryption, and TLS for in-transit. CloudTrail can log Kinesis API calls for auditing. Option A lacks encryption at rest. Option B lacks auditing. Option D is wrong because S3 does not replace Kinesis for streaming.

Answer

Use Kinesis Data Firehose to deliver data to S3 with SSE-S3, and enable CloudTrail for S3.

Answer

Use Kinesis Data Streams with TLS and enable CloudTrail for auditing. Do not enable SSE.

Answer

Use Kinesis Data Streams with SSE-KMS and TLS. Do not enable any auditing.

Question 11

A marketing analytics team needs to ingest customer transaction data from an on-premises PostgreSQL database into Amazon S3 for analysis. The data volume is about 10 GB daily, and the team wants to perform full refresh daily (truncate and load) into S3 as Parquet files. The company has a Direct Connect connection to AWS. The team needs a simple, managed solution that minimizes operational overhead. What should the team use?

Accepted Answer

Use an AWS Glue ETL job with a JDBC connection to the PostgreSQL database, extract data, and write to S3 in Parquet format.. Option A is correct: AWS Glue can connect to on-premises PostgreSQL via JDBC using a connection, and perform full extract and write to S3 as Parquet. Option B (DMS) is more suited for ongoing replication, not full refresh. Option C (Data Pipeline) requires more configuration. Option D (EMR) is overkill.

Answer

Set up AWS Database Migration Service (DMS) to continuously replicate data to S3 in Parquet format.

Answer

Use Amazon EMR with a Spark job that reads from PostgreSQL and writes to S3.

Answer

Use AWS Data Pipeline with a SQLActivity to extract data and copy to S3.

Question 12

A data engineer runs this CLI command. Which query is MOST efficient against this table?

Accepted Answer

Query the CustomerIndex GSI by CustomerID and OrderDate.. The CLI command likely created a global secondary index (GSI) named CustomerIndex on CustomerID and OrderDate. Querying this GSI directly is the most efficient because it uses the index's sort key to retrieve only the relevant items without scanning the entire table, minimizing read capacity consumption.

Answer

Scan the table to find all orders for a CustomerID.

Answer

Create a local secondary index on CustomerID.

Answer

Query by OrderID and filter by OrderDate.

Question 13

A company is using Amazon DynamoDB for an e-commerce application. The application experiences sudden spikes in traffic, causing throttling errors. The data engineer needs to handle the spikes cost-effectively. Which solution should be used?

Accepted Answer

Use DynamoDB auto scaling with a target utilization of 70%.. Option C is correct because DynamoDB auto scaling with a target utilization of 70% allows the table to dynamically adjust provisioned read/write capacity based on actual traffic patterns, handling sudden spikes without manual intervention while avoiding over-provisioning. This balances performance and cost by scaling up during spikes and scaling down during low traffic, preventing throttling errors cost-effectively.

Answer

Implement DynamoDB Accelerator (DAX) to cache reads.

Answer

Switch to DynamoDB on-demand capacity mode.

Answer

Provision high read and write capacity units to handle peak traffic.

Question 14

A data engineer ran the command shown in the exhibit on the bucket 'my-data-lake'. The engineer then tries to delete an object version but receives an 'AccessDenied' error. The engineer has full S3 permissions via IAM. What is the most likely reason for the error?

Accepted Answer

MFA Delete is enabled, requiring multi-factor authentication. The command shown in the exhibit (likely `aws s3api put-bucket-versioning --bucket my-data-lake --versioning-configuration Status=Enabled,MFADelete=Enabled`) enables both versioning and MFA Delete on the bucket. When MFA Delete is enabled, any operation that permanently deletes an object version or changes the versioning state requires the request to include a multi-factor authentication token. Even though the engineer has full S3 permissions via IAM, the missing MFA token causes the 'AccessDenied' error. This is a bucket-level setting that overrides IAM permissions for these specific operations.

Answer

Versioning is suspended on the bucket

Answer

The bucket policy denies s3:DeleteObjectVersion

Answer

An S3 Object Lock retention policy is in effect

Question 15

A data engineer notices that a nightly AWS Glue ETL job has been failing for the past three days with the error 'Unable to locate credentials'. The job uses an IAM role for execution. What is the most likely cause of this error?

Accepted Answer

The IAM role's trust policy does not include glue.amazonaws.com as a trusted entity.. The error 'Unable to locate credentials' indicates that the AWS Glue job cannot obtain AWS credentials to authenticate API calls. Since the job uses an IAM role for execution, the most likely cause is that the trust policy of that IAM role does not include 'glue.amazonaws.com' as a trusted entity. Without this trust relationship, AWS Glue cannot assume the role and thus has no credentials to sign requests.

Answer

The IAM role does not have an access key attached.

Answer

The S3 bucket name in the job parameters is misspelled.

Answer

The JDBC connection string contains an incorrect password.

Question 16

A company runs a real-time analytics platform that ingests data from thousands of sensors via Amazon Kinesis Data Streams. Each sensor sends a JSON payload every second. The data is consumed by a fleet of EC2 instances running a custom consumer application. Recently, the consumer has been falling behind, with the iterator age exceeding 10 minutes. The company has already increased the number of shards to 100, but the problem persists. The consumer application is single-threaded per shard and uses the Kinesis Client Library (KCL). The CPU utilization on the EC2 instances is below 30%. What should the data engineer do to reduce the iterator age?

Accepted Answer

Modify the consumer to use multiple worker threads per shard. Option B is correct because the consumer is not processing data fast enough due to being single-threaded per shard; using multiple worker threads per shard can increase processing throughput. Option A (more shards) has already been tried without success. Option C (larger instance) may not help if CPU is low. Option D (Lambda) may not handle the volume efficiently and adds complexity.

Answer

Increase the number of shards to 200

Answer

Use larger EC2 instances with more vCPUs

Answer

Replace the EC2 consumer with AWS Lambda functions

Question 17

A data engineer reviews this IAM policy attached to an S3 bucket. What is the effect of this policy?

Accepted Answer

Allows PutObject only when the object is NOT encrypted with SSE-KMS.. Option D is correct because the IAM policy uses a `Deny` effect with a `StringNotEquals` condition on `s3:x-amz-server-side-encryption` for `aws:kms`. This means that if the encryption header is NOT `aws:kms` (i.e., SSE-KMS), the request is denied. The net effect is that `PutObject` is allowed only when the object is encrypted with SSE-KMS, as any other encryption or no encryption triggers the deny.

Answer

Denies PutObject when encryption is not SSE-KMS.

Answer

Denies all PutObject requests.

Answer

Allows PutObject only when the object is encrypted with SSE-KMS.

Question 18

A social media company ingests user activity data from multiple sources using Amazon Kinesis Data Firehose. The data is delivered to Amazon S3 in near-real-time. The company wants to transform the data by adding a timestamp and masking email addresses before storing it in S3. The transformation should be applied to all records. What is the most cost-effective way to implement this transformation?

Accepted Answer

Configure the Firehose delivery stream to invoke a Lambda function for data transformation.. Option A is correct. Kinesis Data Firehose can invoke a Lambda function to transform data on the fly. This is cost-effective because it runs only when data is flowing. Option B is wrong because Glue jobs are batch-oriented and add latency. Option C is wrong because S3 Events with Lambda adds complexity and cost. Option D is wrong because Athena is for querying, not transforming.

Answer

Use Amazon Athena to run a CTAS query that transforms the data and writes to a new location.

Answer

Use AWS Glue to schedule a batch job every 5 minutes to transform the data.

Answer

Use Amazon S3 Events to trigger a Lambda function whenever a new object is created.

Question 19

A data engineer sees this AWS Glue table definition in the Data Catalog. The engineer wants to query this table with Amazon Athena, but the query returns zero rows. What is the MOST likely cause?

Accepted Answer

The data files are not in the specified S3 location.. The most likely cause is that the data files are not in the specified S3 location. When an AWS Glue table is defined in the Data Catalog, Athena reads the table's metadata (including the S3 location) and then attempts to read the underlying data files from that exact path. If the files are missing, misnamed, or in a different prefix, Athena returns zero rows because there is no data to scan. This is a common misconfiguration when the S3 path in the table definition does not match the actual data storage.

Answer

The SerDe library is incorrect for CSV files.

Answer

The table format CSV is not supported by Athena.

Answer

Athena cannot read tables from the Glue Data Catalog.

Question 20

A CloudFormation template includes this IAM policy for a cross-account S3 upload use case. What is the purpose of the condition?

Accepted Answer

To ensure that uploaded objects grant full control to the bucket owner.. The condition in the IAM policy uses the `s3:x-amz-acl` key with a value of `bucket-owner-full-control`. This ensures that any object uploaded to the S3 bucket explicitly grants the bucket owner full control over the object, overriding the default behavior where the uploading account retains ownership. This is critical in cross-account uploads to prevent the uploading account from retaining exclusive access to the objects.

Answer

To enforce server-side encryption with KMS.

Answer

To limit the size of objects that can be uploaded.

Answer

To restrict uploads to only a specific AWS account.