AWS Certified Data Engineer Associate DEA-C01 (DEA-C01) — Questions 1–75

AWS DMS supports continuous replication from PostgreSQL source databases using logical replication slots to capture CDC events in near real-time. It can directly stream these changes to Amazon S3 as a target, making it the correct choice for this use case.

The error indicates the DMS replication instance is running out of memory during continuous replication of a 2 TB Oracle database to S3 in Parquet format. Increasing the replication instance size (Option D) directly addresses the memory constraint by providing more RAM and processing capacity, which is necessary for handling large volumes of Change Data Capture (CDC) data and Parquet conversion overhead. This solution requires minimal architectural changes, as it only involves modifying the instance class in the DMS task settings.

The AWS Glue ETL library can be installed locally so developers run the same transformation code against sample data without any managed cluster. This enables genuine unit tests of complex nested-array flattening. The visual editor, interactive sessions, and DataBrew all depend on AWS-hosted execution and therefore cannot satisfy the requirement for local testing before deployment.

Converting CSV to Parquet and partitioning the data by a frequently filtered column are the two most effective actions to reduce Athena query costs and improve performance. Parquet reduces scanned data through columnar compression and predicate pushdown, while partitioning limits the data scanned to relevant partitions. The other actions do not affect query execution or data scanned.

The correct solution uses SSE-KMS with a customer managed key to encrypt data at rest, enables automatic key rotation for that key to meet the annual rotation requirement, and enables AWS CloudTrail to log KMS API calls for auditing key usage. Other options either use incorrect key types (SSE-S3 or AWS managed keys) or do not provide the necessary auditing capabilities.

AWS Glue job bookmarks track the state of data processed by a job, so subsequent runs only process new or changed data. When enabled, bookmarks persist the last processed path or partition, allowing the daily job to skip already-transformed files and reduce runtime and cost. This directly satisfies the requirement to process only new files each day.

AWS DMS with S3 as target is correct because it supports continuous change data capture (CDC) from Oracle, minimizing impact on the source database by reading redo logs instead of querying tables directly. It can handle 500 GB/day over 200 Mbps (which yields ~2.16 TB/day theoretical max) and ensures data integrity via transactional consistency and validation checksums. DMS also automatically partitions large datasets and can resume from failures, making it ideal for daily bulk loads.

Increasing the buffer size to 50 MB and the buffer interval to 300 seconds directly addresses the root cause: the current 5 MB buffer size triggers a flush too frequently, producing many 2 MB files. By raising the buffer size to 50 MB, each flush will contain more data, resulting in larger S3 objects (up to ~50 MB uncompressed), while the 300-second interval ensures latency stays within the 5-minute requirement. This reduces the number of small files without requiring additional services or post-processing.

The correct approach is to use COPY with COMPUPDATE ON to automatically apply compression, and to define an appropriate distribution key and sort key. This optimizes storage, reduces I/O, and improves query performance by ensuring even data distribution and efficient data access patterns. Other options either do not address compression, distribution, or sort keys adequately, or rely on commands that do not achieve the desired optimization.

C is correct because S3 MFA Delete requires multi-factor authentication for permanent deletion of objects or suspension of versioning, adding a critical layer of protection against accidental or malicious deletions. D is correct because S3 Object Versioning preserves every version of an object, allowing recovery from accidental overwrites or deletions by restoring a previous version.

Athena performance and cost are driven by how many bytes a query scans, so converting row-oriented JSON to columnar Parquet, partitioning on common filter columns, and compacting small files directly reduce scanned data. These three changes work together: columnar storage reads only referenced columns, partitions prune entire prefixes, and compaction removes per-object overhead. The result is faster queries at lower cost without altering the source data in S3.

VACUUM in Amazon Redshift reclaims disk space occupied by rows marked for deletion and re-sorts rows to restore sort order. When rows are deleted, they are only logically marked until VACUUM runs, so disk usage keeps growing. Running VACUUM (or VACUUM DELETE ONLY) releases that space back to the system.

Producing partitioned output and registering it in the Glue Data Catalog from a Glue job is achieved by specifying partitionKeys on the write and enabling the job to update the Data Catalog. Together these create the year/month/day folder structure and add the corresponding catalog partitions during the run. Manual API calls or a separate crawler can work but are not the automatic in-job mechanism the scenario requires.

Increasing the Lambda function's memory allocation also increases its CPU allocation, allowing the function to process each log event faster. This reduces the per-invocation processing time, enabling the function to handle more log data per unit time and thus reduce the backlog.

Redshift COPY achieves high throughput by having multiple slices read files in parallel. When hundreds of files sit in one prefix, the leader node may serialize listing and assignment, and some slices may read more files than others, causing skew and resource contention. Distributing files across prefixes or using a manifest gives COPY finer-grained control over parallel reads, balancing work across slices and improving load speed without altering the format or cluster size.

To encrypt data in transit between AWS Glue and Amazon RDS for MySQL, the JDBC connection must be configured to use SSL. Setting sslMode=REQUIRED in the JDBC URL and providing the RDS root certificate ensures that the connection is encrypted and the server certificate is validated. This is the correct approach for meeting the encryption in transit requirement.

Writing to an SSE-KMS encrypted S3 bucket requires the kms:GenerateDataKey permission on the KMS key used for the target bucket. The Glue job role already has read and decrypt permissions on the source, but lacks the necessary permission to encrypt data for the target. Without GenerateDataKey, the S3 PutObject operation fails.

For cross-account S3 access to succeed, both the bucket policy (resource-based policy) and the IAM user policy (identity-based policy) in the other account must grant the necessary permissions. Option B is correct because even if the bucket policy allows access from the other account, the IAM user in that account must have an explicit IAM policy that permits the S3 action (e.g., s3:GetObject) on the bucket. Without this, the request is denied by the other account's own IAM evaluation.

For Athena, the most effective way to reduce cost and improve performance is to use a columnar format like Parquet and partition the data. Parquet allows column pruning, and partitioning enables partition pruning, so queries scan less data. Updating partitions with MSCK REPAIR TABLE ensures the metadata is current, allowing these optimizations to take effect.

AWS CloudTrail with data events enabled captures object-level S3 operations (GetObject, PutObject, DeleteObject) along with the identity of the caller and the source IP address, which is exactly what is needed for a full data access audit. Management events alone do not capture object reads/writes, so data events must be explicitly turned on.

Partition pruning requires the query engine to know which partition values exist and where their data lives. Registering the year/month/day folders as partitions in the Data Catalog lets Athena build a plan that reads only the matching day, cutting scanned bytes and cost. Physical optimizations such as acceleration or columnar formats do not supply that partition metadata.

The Relationalize transform in AWS Glue is specifically designed to convert nested JSON data into a relational format. It flattens nested structures by creating separate tables for arrays and nested objects, which can then be written to Parquet. This is the appropriate feature to handle nested fields when transforming JSON to Parquet.

AWS Glue Python shell jobs are serverless and optimized for small to medium ETL workloads that do not need Spark's distributed processing. For a 500 MB dataset with simple transformations, they provide the lowest operational overhead while integrating with S3 and Redshift. EMR, EC2, and Lambda each introduce either management burden or technical limits that make them less suitable for this scenario.

Enabling error logging on the Firehose delivery stream is crucial for diagnosing why records are not delivered to S3. The logs in CloudWatch will show specific errors such as access denied or invalid records, allowing the engineer to take corrective action. This directly addresses the missing data and high data freshness.

Amazon MSK Connect is a managed Kafka connector service that integrates with Amazon MSK or self-managed Apache Kafka clusters to stream data into Amazon S3 using Kafka Connect. It handles the 10 TB/day volume efficiently with auto-scaling and checkpointing, making it the correct choice for a managed connector setup.

AWS DMS with ongoing replication captures incremental changes from Oracle using its native change data capture (CDC) mechanism, such as Oracle LogMiner or binary logs, and streams them to S3 in near real-time with latency under 1 minute. This approach is cost-effective because DMS charges only for the compute resources used during replication, and S3 storage is inexpensive, making it ideal for a 5 TB dataset with continuous changes.

The `SVV_TABLE_INFO` system view in Amazon Redshift provides metadata about each table, including the `unsorted` column which shows the percentage of unsorted rows. By ordering by `unsorted DESC`, the engineer can quickly identify tables with the highest proportion of unsorted data, which directly impacts query performance due to inefficient zone maps and scan pruning.

DynamoDB Time to Live (TTL) is the correct feature because it allows you to define a per-item timestamp attribute (e.g., `expireAt`) that DynamoDB automatically deletes once that timestamp is reached. This is ideal for expiring session data after 24 hours without requiring custom code or scheduled jobs to scan and delete items, reducing cost and operational overhead.

Amazon API Gateway with S3 integration (Option D) allows you to create a REST API that directly writes incoming webhook payloads to an S3 bucket without any custom code, using a proxy integration with an AWS service. This minimizes custom code because the integration handles the mapping and storage automatically.

The root cause is that the job reads all objects under the prefix, including files that do not match the schema. Restricting the S3 source path with a glob pattern or limiting to valid partitions ensures only conforming files are read. This is a configuration change that directly prevents the failures without adding compute or custom classification logic.

AWS Glue job bookmarks track processed data by recording the state of previously processed files and partitions, enabling incremental processing of new or changed data in subsequent runs. This is essential for efficiently handling incremental updates without reprocessing the entire dataset.

Amazon RDS Read Replicas allow you to offload read traffic from the primary DB instance to one or more read-only copies, which can be placed in different AWS Regions to reduce read latency for a global user base. Unlike Multi-AZ, which is designed for high availability, Read Replicas directly address read performance and latency by distributing read queries closer to users. Cross-Region Replication for RDS MySQL is achieved through Read Replicas, making option C the correct choice for reducing read latency globally.

Creating a read replica of the RDS for PostgreSQL instance allows the analytical query to run on a separate database engine without affecting the primary workload. Read replicas in Amazon RDS use asynchronous replication from the source instance, so the replica can handle heavy I/O and long-running queries independently. This ensures the primary instance remains available for the production workload without performance degradation.

S3 Object Lock in compliance mode provides write-once-read-many protection that no principal, including the root user, can override before the retention date. A default retention rule applies the period automatically to newly created object versions. Since the lock applies to object versions, the bucket itself can still be deleted after retention lapses and versions are removed.

Lake Formation column-level permissions let you grant SELECT on a subset of columns in a Data Catalog table. Analysts can then query the permitted columns through Athena, and any attempt to select the Social Security number column is denied. Duplicating tables or using broad IAM denies does not achieve the same precise, least-privilege control.

DynamoDB global tables provide active-active multi-region replication with sub-second latency, ensuring that data written in one region is automatically replicated to other regions within seconds. This meets the RPO of less than 1 minute and RTO of less than 5 minutes because the table is already available in the secondary region for immediate reads and writes, with no manual restore or failover steps required.

The Relationalize transform is specifically designed to flatten nested JSON structures (arrays and objects) into a set of related tables, making it ideal for this use case. It automatically handles complex nesting by creating separate DataFrames for each nested level and linking them via foreign keys, which is exactly what is needed when ingesting JSON with nested arrays and objects into a relational format.

Even if the IAM role has `kms:Decrypt` permission, the KMS key policy must also allow the Glue job's role to use the key. KMS key policies are resource-based policies that control access to the key, and if they do not explicitly grant permission to the Glue job's role, the decrypt operation will fail. This is a common oversight when using SSE-KMS with services like AWS Glue.

Exporting old records via pg_dump and storing them in S3 Glacier Deep Archive provides the lowest-cost storage for data that must be retained for 7 years but is never accessed. S3 Glacier Deep Archive offers retrieval times of 12–48 hours at a storage cost of approximately $0.00099/GB/month, far cheaper than any RDS storage tier. This approach removes the archived data from the RDS instance, reducing provisioned storage costs while meeting compliance requirements.

To determine whether an S3 object is eligible for lifecycle transition to S3 Glacier based on age, you need the object's creation date (LastModified) and the current date to compute its age. The exhibit presumably shows the object's LastModified timestamp, so the missing piece is the current date to calculate how many days old the object is relative to the lifecycle rule's transition threshold.

Job bookmarks reduce the data read on subsequent runs by tracking processed files, and partitioning with predicate pushdown reduces the data scanned per run. Together they lower runtime and DPU consumption, improving performance while reducing cost. Increasing DPUs or converting to CSV would increase cost or hurt performance, and disabling auto-scaling removes a cost-saving feature.

The AWS Glue Data Catalog with crawlers keeps table and partition metadata current as schemas evolve, and both Athena and Redshift Spectrum read from it. Using open columnar formats such as Parquet or ORC with partitioned prefixes enables column projection and partition pruning, which cut scanned bytes and cost. Together, these two actions support evolving schemas and efficient querying across both engines.

AWS Glue (and Spark generally) performs poorly with millions of tiny files because each file requires a separate S3 GET request, metadata operation, and task scheduling overhead. S3 file grouping (via 'groupFiles' and 'groupSize' job parameters) coalesces multiple small files into a single read per Spark partition, drastically reducing the number of S3 operations and task overhead. This directly addresses the root cause of the slowness.

DynamoDB auto scaling allows the table to automatically adjust its provisioned write capacity based on actual traffic patterns, preventing throttling during sudden spikes without manual intervention. Since the partition keys are already well-distributed, throttling is likely due to insufficient write capacity units, which auto scaling can dynamically increase.

The only mechanism that blocks an unencrypted PUT before the object is stored is a bucket policy using a Deny effect with the s3:x-amz-server-side-encryption condition key. Default encryption and console warnings are passive safeguards that cannot stop an explicit unencrypted request from an SDK or CLI. Block Public Access and lifecycle rules address different concerns entirely.

The most likely cause is that the analyst lacks Lake Formation permissions on the table. Even with IAM permissions to run Athena, Lake Formation enforces fine-grained access control at the table and column level. If the analyst has not been granted SELECT permission on the table via Lake Formation, Athena queries will fail with an access denied error, even though the table appears in the Glue Data Catalog.

ProvisionedThroughputExceededException is a throttling error that occurs when request rate exceeds the table's provisioned read/write capacity, and AWS's documented best practice is to implement exponential backoff and retry logic so the client retries with increasing delays instead of hammering the table. This is the correct fix for intermittent throttling because it lets the Glue job ride out short bursts of capacity contention without failing. It also works whether the table is in provisioned or on-demand mode, and it is the least disruptive, most cost-effective change.

CloudTrail data events provide the necessary audit trail for object-level access, identifying IAM principals. SSE-KMS with customer managed keys satisfies the encryption at rest requirement with customer-managed keys. Together, they meet the auditing and encryption needs.

To use a custom JDBC driver with AWS Glue, you must upload the driver jar to Amazon S3 and specify its path in the job's 'Dependent jars path' parameter. Additionally, the IAM role associated with the job must have s3:GetObject permission for that jar. This ensures the driver is available in the classpath at runtime, resolving the 'Unable to find a suitable JDBC driver' error.

The Redshift COPY command is the native bulk loader and supports Parquet directly via the PARQUET format option, which reads columnar data efficiently from S3. For Parquet files with no required transformation, COPY is faster and simpler than a Glue job, and the other options either move data the wrong direction or add streaming overhead.

Amazon Athena is fully serverless and executes SQL directly against S3 data. A CREATE TABLE AS SELECT statement reads the source, applies projections and filters, and writes the result to S3 in a chosen format like Parquet. This satisfies the SQL and no-infrastructure requirements without cluster or job management.

AWS Database Migration Service (DMS) is the correct choice because it supports continuous replication from MongoDB to Amazon DocumentDB using change data capture (CDC), enabling near-zero downtime migrations. DMS can perform a full load of existing data and then apply ongoing changes from the source MongoDB oplog, keeping the target DocumentDB synchronized until the cutover.

Lake Formation tag-based access control centralizes permission decisions on tags instead of individual tables. Defining an LF-Tag named DataClass and granting access only for matching tag values means any table lacking the required tag value has no applicable grant and is therefore not queryable, satisfying the governance rule with minimal administrative effort.

Amazon Kinesis Data Firehose supports record format conversion natively, allowing a delivery stream to convert incoming JSON records to Parquet or ORC using a schema stored in the AWS Glue Data Catalog. This is the most cost-effective approach because the conversion happens within Firehose without provisioning or paying for separate compute such as Lambda or Glue ETL jobs.

S3 Lifecycle policies allow you to define rules that automatically transition objects between storage classes based on age or other criteria. In this scenario, a lifecycle rule can be configured to transition objects from S3 Standard to a lower-cost class like S3 Glacier Deep Archive after 30 days, directly meeting the requirement for automated cost optimization.

The consumer is falling behind because the total throughput of the stream (1 MB/s or 1,000 records/s per shard for writes, and 2 MB/s per shard for reads) is insufficient for the incoming data volume. Increasing the number of shards scales both the write and read capacity linearly, allowing the consumer to process records faster and catch up. Changing the partition key or retention period does not increase throughput, and switching to Firehose changes the delivery model but does not inherently solve the consumer lag.

The Lambda function is throttled by DynamoDB because the write capacity is insufficient for the incoming Kinesis stream rate. Increasing the DynamoDB write capacity resolves the throttling, and configuring a DLQ on the Lambda function ensures that any records that fail after retries are captured for later processing, preventing data loss. This combination directly addresses the root cause while providing a safety net.