AWS Certified Data Engineer Associate DEA-C01 (DEA-C01) — Questions 151–225

Why this answer

How to eliminate wrong answers

Option A is wrong because STL_LOAD_ERRORS records errors during COPY or INSERT operations, such as data type mismatches or malformed data, and has no relation to data distribution skew. Option B is wrong because SVV_TABLE_INFO shows overall table size, row count, and compression ratios, but it does not provide per-slice data distribution details needed to identify skew. Option D is wrong because WLM configuration in the parameter group manages query concurrency and memory allocation, not data distribution or skew detection.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because TLS encryption protects data in transit between clients and the Kinesis endpoint, not data at rest within the stream. Option C is wrong because client-side encryption encrypts data before it is sent to Kinesis, but this is a client-managed approach that does not leverage Kinesis's native at-rest encryption and adds complexity; the question asks which configuration should be enabled on the stream itself. Option D is wrong because storing data in CloudWatch Logs does not encrypt the Kinesis stream data at rest and is a different service entirely, not a configuration for Kinesis Data Streams.

Why this answer

How to eliminate wrong answers

Option A is wrong because S3 Transfer Acceleration only speeds up uploads over long distances via edge locations; it does not provide scheduling, incremental data capture, or database connectivity. Option C is wrong because AWS Glue crawlers are designed for schema discovery and metadata cataloging, not for scheduled incremental data transfer from a database to S3; Glue ETL jobs can do this but require custom code, whereas DMS is the managed service for database migration. Option D is wrong because Kinesis Data Firehose ingests streaming data from producers like Kinesis streams or direct PUT, not from a relational database via JDBC; it lacks built-in change data capture for incremental database loads.

Why this answer

How to eliminate wrong answers

Option B is wrong because write throttling is unrelated to read capacity; the question specifies write request throttling, so read capacity settings are irrelevant. Option C is wrong because the question states auto scaling is enabled, so this option describes a scenario that does not match the given condition. Option D is wrong because global tables with conflict resolution handle eventual consistency and replication conflicts, not throughput throttling on write requests.

Why these pairings

Why this answer

How to eliminate wrong answers

Option A (AUTO distribution) is wrong because AUTO lets Redshift choose the distribution style based on table size and usage patterns, but it may not guarantee co-location for frequent joins on customer_id, potentially still causing data movement. Option C (order_date) is wrong because it is not the join column; using it as the distribution key would scatter customer_id values across nodes, forcing redistribution for every join on customer_id. Option D (EVEN distribution) is wrong because it distributes rows round-robin across nodes without considering join keys, which maximizes data movement during joins on customer_id and degrades performance.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because AWS DataSync is a network-based transfer service that still relies on the internet connection; at 100 Mbps, it would take over 48 days to transfer 50 TB, exceeding the 2-week requirement. Option C is wrong because Amazon Kinesis Data Firehose is designed for real-time streaming data ingestion, not for batch transfer of 50 TB of historical data from a Hadoop cluster. Option D is wrong because AWS Glue ETL with a JDBC connection would require pulling data over the slow network, which is impractical for 50 TB, and Glue is optimized for schema discovery and transformation, not bulk data migration.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because S3 Object Lock is a feature for preventing object deletion or overwrites for compliance or retention, not for ensuring consistency. Option B is wrong because while S3 now offers strong consistency for all operations (including overwrites and deletes), this was not always the case; historically S3 offered eventual consistency for overwrites, and the question's phrasing about 'new files' specifically tests the read-after-write consistency model for new PUTS. Option C is wrong because S3 no longer provides eventual consistency for new object PUTS; it guarantees strong read-after-write consistency for new objects since December 2020.

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because changing the prefix to 'logs/archive/' would only apply the lifecycle rules to a different subset of objects, not fix the retention period for the original 'logs/' prefix. Option C is wrong because transitioning to GLACIER on day 365 only changes the storage class for cost optimization; it does not extend the deletion timeline, so objects would still be deleted after 365 days. Option D is wrong because removing the expiration action entirely would mean objects are never automatically deleted, which may lead to indefinite storage and increased costs, not a 5-year retention.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because iterator age exceeding the retention period would cause data to expire and become unavailable, not cause duplicate processing. Option C is wrong because DynamoDB throttling on checkpoint writes would cause checkpoint failures and potential re-processing, but the question states checkpointing is occurring and the issue is duplicate processing, not checkpoint failures. Option D is wrong because the 1 MB API limit applies to the total payload per PutRecords request, not per record, and exceeding it would cause write failures or retries, not duplicate processing of already-successful records.

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because omitting the database name from the JDBC connection string would cause a different error (e.g., 'Unknown database' or connection rejection), not a 'Communications link failure', which indicates a network-level issue. Option B is wrong because AWS Glue automatically includes the correct JDBC driver for MySQL (compatible with Amazon RDS MySQL) when using the Glue connection type 'MySQL'; using the wrong driver would typically produce a class-not-found or driver-incompatibility error, not a communications link failure. Option D is wrong because IAM permissions for Glue jobs use actions like 'glue:GetConnection' and 'rds:DescribeDBInstances' to retrieve connection metadata, but there is no 'rds:Connect' IAM action; database authentication is handled via username/password in the Glue connection, not IAM.

Why this answer

How to eliminate wrong answers

Option A is wrong because reducing the volume of incoming data is not a scalable solution and may not be feasible; the job should be able to handle the required data volume. Option B is wrong because AWS Glue does not allow direct configuration of memory settings per executor; memory is tied to DPU allocation, and increasing DPUs is the correct way to increase total memory. Option C is wrong because using a smaller file size for input does not address the root cause of memory exhaustion; Glue can process many small files efficiently, but the issue is the total data volume or skewed partitions, not file size.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because reducing the number of partitions would increase the data size per partition, worsening memory pressure and likely causing the same or a more severe memory error. Option C is wrong because switching to a smaller instance type would reduce available memory per executor, directly contradicting the need to resolve a memory error. Option D is wrong because S3 Select can reduce the amount of data read from S3, but it does not increase the memory available to the Glue job's Spark executors; the memory error occurs during processing, not during data ingestion.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because S3 Standard is designed for frequently accessed data and has higher storage costs than Standard-IA, making it less cost-effective for archival data with infrequent access. Option C is wrong because S3 Glacier Deep Archive has the lowest storage cost but retrieval times range from 12 to 48 hours, failing the 'immediately retrievable' requirement. Option D is wrong because S3 Intelligent-Tiering automatically moves data between tiers based on access patterns but incurs a monthly monitoring and automation fee per object, making it less cost-effective than Standard-IA for a predictable infrequent access pattern.

Why this answer

How to eliminate wrong answers

Option A is wrong because a t3.nano EC2 instance requires ongoing management (patching, scaling, monitoring) and incurs fixed hourly costs even at low data volumes, making it less cost-effective and operationally efficient than a serverless solution. Option C is wrong because SQS is a message queue, not a streaming ingestion endpoint; it would require additional components (e.g., an HTTP endpoint to receive POSTs and put to SQS) and the Lambda function adds latency and complexity, whereas Firehose directly accepts HTTP POSTs. Option D is wrong because API Gateway + Lambda introduces per-request costs and cold start latency, and it is over-engineered for simple streaming ingestion; Firehose provides built-in buffering, compression, and direct S3 delivery without custom code.

Why this answer

How to eliminate wrong answers

Option A is wrong because kinesis:PutRecord is used to write data to a Kinesis stream, but the application reads from the input stream as a source, not writes to it; the error is not about writing. Option C is wrong because kinesis:CreateStream is an administrative action to create a new stream, which is irrelevant to an existing stream used as input. Option D is wrong because kinesis:PutRecords is for batch writing to a stream, not for reading or for accessing reference data from S3.

Why this answer

How to eliminate wrong answers

Option A is wrong because 'Inherit schema from table' is not a valid Glue crawler configuration; crawlers do not inherit schemas from existing tables automatically. Option C is wrong because without enabling 'Merge tables', the crawler will create multiple tables for each distinct schema, not a single unified table. Option D is wrong because 'Each file as a separate table' would create a separate table per CSV file, which defeats the goal of having a single table with all columns merged.

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because the Kinesis Client Library (KCL) is used for consuming data from Kinesis Data Streams with checkpointing in DynamoDB, but it does not integrate with Lambda as a trigger; the question specifies using Lambda for transformation, and KCL is a separate consumer model that would require managing EC2 instances or containers. Option B is wrong because increasing timeout and memory only reduces the likelihood of timeouts or out-of-memory errors but does not handle transient errors like network glitches or service throttling; it does not provide a mechanism to retry or capture failed records. Option D is wrong because a dead-letter queue (DLQ) on Lambda captures records that fail after all retries, but it does not automatically write to S3; the engineer would need additional logic to process the DLQ and write to S3, and the question specifically requires writing to S3 with no data loss, which Firehose handles directly.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because the AWS CLI command output shows the object's storage class as STANDARD_IA, which indicates the Lifecycle policy has already transitioned it from STANDARD to STANDARD_IA, proving the filter does not exclude this prefix. Option B is wrong because versioning being enabled does not prevent lifecycle transitions; S3 Lifecycle policies apply to all versions unless explicitly filtered, and the current version's age is based on its own LastModified date, not the oldest version. Option D is wrong because S3 Lifecycle transitions are based on the object's LastModified date, not any metadata timestamp; the LastModified field is the authoritative timestamp for age calculations.

Why this answer

Why this answer

How to eliminate wrong answers

Option B is wrong because using an AWS Lambda function to transform records to Parquet before sending to Firehose introduces unnecessary complexity, additional cost, and operational overhead (e.g., managing Lambda concurrency, packaging Parquet libraries, handling record size limits), whereas Firehose's built-in conversion handles this natively. Option C is wrong because Amazon Kinesis Data Analytics is designed for real-time analytics and stream processing using SQL or Flink, not for simple format conversion; it adds latency, complexity, and cost without benefit for a straightforward schema conversion. Option D is wrong because provisioning an Amazon EMR cluster to convert data in micro-batches is a heavy, over-engineered solution that requires cluster management, scaling, and job orchestration, far exceeding the operational overhead needed for a simple format conversion that Firehose can perform automatically.

Why this answer

How to eliminate wrong answers

Option B is wrong because a dead letter queue (DLQ) is used to capture failed events for asynchronous invocations, not to prevent logs from being written; it does not affect CloudWatch Logs permissions. Option C is wrong because if the Lambda function had not been invoked, the log group would not exist at all; the presence of the log group with `storedBytes` of 0 indicates the function was invoked but failed to write logs. Option D is wrong because if the log group were encrypted with a KMS key and the Lambda function lacked decrypt permission, the function would fail with an access denied error when trying to write logs, but the log group would still show `storedBytes` as 0; however, the question states the log group exists and `storedBytes` is 0, which is consistent with missing write permissions, not KMS decryption issues (KMS errors would typically produce a different error message in CloudWatch).

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because AWS Backup is an additional service that adds complexity and cost; RDS native automated backups already support retention up to 35 days without needing AWS Backup. Option C is wrong because manual snapshots require custom scripting to create and delete daily, which is not the simplest approach and does not provide automated point-in-time recovery. Option D is wrong because automatic export of transaction logs to S3 is not a native RDS feature for PostgreSQL; RDS handles transaction logs internally for point-in-time recovery, and using S3 lifecycle policies would not replace the need for automated backups.

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because Lambda cold starts are a potential issue only if the consuming application uses Lambda as a consumer, but the question specifies a KCL worker, not a Lambda function. Option B is wrong because the incoming data rate is 1 MB/second, and a single Kinesis shard supports up to 1 MB/second write capacity, so 10 shards provide 10 MB/second—far more than needed. Option D is wrong because the shard count of 10 is more than sufficient to handle the 1 MB/second data rate; the bottleneck is on the consumer side, not the stream's capacity.

Why this answer

How to eliminate wrong answers

Option B (Amazon Simple Queue Service) is wrong because SQS is a message queue for decoupling application components, not a streaming buffer designed for high-throughput data delivery to S3; it lacks native integration to automatically write data to S3 with configurable latency. Option C (Amazon Kinesis Data Streams) is wrong because it is a real-time data streaming service that requires custom consumers (e.g., Lambda or Kinesis Client Library) to read and write data to S3, adding complexity and latency beyond the 60-second requirement; it does not natively buffer and deliver to S3. Option D (AWS Lambda) is wrong because Lambda is a serverless compute service for running code in response to events, not a buffer or delivery mechanism; it cannot handle sustained 500 MB/s ingestion without additional services and would require custom orchestration to meet latency goals.

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because an in-place major version upgrade for RDS PostgreSQL requires a database restart and can take significant time (often 10-30 minutes or more) depending on instance size and data volume, leading to unacceptable downtime. Option B is wrong because modifying the DB instance class to a larger size does not perform a version upgrade; it only changes compute and memory resources, leaving the PostgreSQL version unchanged. Option D is wrong because using pg_dump and pg_restore involves exporting the entire database to a file and then importing it into a new instance, which can take hours for large datasets and requires the source database to be unavailable or read-only during the process, resulting in extended downtime.

Why this answer

Why this answer

Why this answer

Why this answer

Why this answer

How to eliminate wrong answers

Option A is wrong because increasing the buffer size to 10 MB would actually increase the time needed to fill the buffer, worsening the latency issue during peak hours when records are small and the buffer interval is the primary trigger. Option C is wrong because enabling GZIP compression reduces storage size and cost but does not affect the delivery frequency or buffer flush timing, so it has no impact on latency. Option D is wrong because converting JSON to Parquet via Lambda adds processing overhead and introduces additional latency from the transformation invocation, which would increase rather than reduce delivery delay.

Why this answer

How to eliminate wrong answers

Option B is wrong because defining DISTKEY and SORTKEY improves query read performance by optimizing data distribution and sort order, but does not directly address the write performance issue caused by many small inserts. Option C is wrong because increasing the number of nodes adds compute and storage capacity, but does not solve the fundamental problem of per-insert overhead; small inserts will still be slow on a larger cluster. Option D is wrong because configuring workload management (WLM) queues manages concurrency and prioritizes queries, but does not reduce the overhead of individual small INSERT statements.