Hard Difficulty Questions

Enable default encryption with SSE-KMS, allow both HTTP and HTTPS, and set bucket ACLs to private.

Why it fails: Permitting HTTP fails the in-transit encryption requirement, and private ACLs do not prevent future public grants the way Block Public Access does. It tempts because SSE-KMS satisfies encryption at rest, so this pairing would be correct where only storage encryption and per-object privacy are required.

Use client-side encryption, enforce HTTPS via bucket policy, and enable S3 Block Public Access.

Why it fails: Client-side encryption handles data at rest, but places the burden of key management entirely on the client, which can introduce operational overhead and potential security risks if not implemented perfectly. For sensitive data, server-side encryption (SSE-S3 or SSE-KMS) is typically preferred as it offloads key management to AWS, integrating seamlessly and often simplifying compliance. This option is tempting because client-side encryption does achieve encryption at rest, and the other two actions (HTTPS and Block Public Access) are correct for their respective requirements.

Enable default encryption with SSE-S3, allow HTTP and HTTPS, and use bucket ACLs to block public access.

Why it fails: Allowing HTTP leaves data unencrypted in transit, and bucket ACLs are legacy access controls rather than the account-level Block Public Access setting. It tempts because SSE-S3 and ACLs are familiar S3 tools, and this combination would suit a non-sensitive bucket where transit encryption is not mandated.

Use AWS Schema Conversion Tool (SCT)

Why it fails: AWS SCT converts schema and code between heterogeneous engines, but it is a separate migration-assessment and conversion tool, not a DMS replication feature applied during an ongoing task. It suits pre-migration schema conversion, whereas the stem asks which DMS capability transforms schema during replication.

Use AWS Glue ETL jobs

Why it fails: AWS Glue performs ETL on data after it lands, running outside the DMS replication stream, so it cannot transform schema as rows replicate. Glue suits post-load transformation or analytics pipelines; the stem requires an in-flight DMS transformation mechanism, which is table mappings.

Use AWS Lambda triggers

Why it fails: AWS Lambda triggers operate outside the DMS replication engine and cannot intercept or modify the schema mapping during the ongoing CDC or full-load transfer from Oracle to RDS for MySQL. The temptation arises because Lambda is a general-purpose compute service that can transform data after it lands, but the requirement is for DMS to apply schema transformations *during* replication — a task handled by DMS’s built-in transformation rules, not by post-replication triggers.

Amazon EMR running Spark Structured Streaming

Why it fails: Spark Structured Streaming on EMR maintains stateful operators, but it requires provisioning and managing a cluster, adding operational overhead the managed alternative avoids. It is tempting because Spark handles complex stateful logic well, and would suit teams already running EMR or needing custom libraries beyond the managed service's supported transforms.

Amazon Kinesis Data Firehose with Lambda transformation

Why it fails: Firehose invokes Lambda for transformation, so the 15-minute execution limit and stateless invocation model cannot support state maintained across records. Firehose is tempting because it delivers straight to S3 with minimal administration, and would be correct for simple stateless record-by-record conversion rather than cross-record stateful processing.

AWS Glue streaming ETL job

Why it fails: Glue streaming is based on Spark and has limited stateful support; less mature than Flink.

Amazon FSx for HDFS

Why it fails: FSx for HDFS is not an AWS service; FSx offers Windows File Server, Lustre, NetApp ONTAP and OpenZFS. The tempting idea is a managed HDFS-compatible file system, which would fit lift-and-shift Hadoop workloads, but Redshift Spectrum reads from Amazon S3 or the Glue Data Catalog, not an FSx file system.

Amazon Elastic Block Store (EBS)

Why it fails: EBS volumes attach to a single EC2 instance in one Availability Zone, so neither Amazon EMR clusters nor Redshift Spectrum could concurrently read the HDFS data. EBS suits block storage for a single instance's operating system or database files, not shared multi-service analytics access.

Amazon Elastic File System (EFS)

Why it fails: EFS provides shared POSIX file storage for Linux workloads, but Redshift Spectrum queries data in Amazon S3, not EFS, and EMR cannot present EFS as HDFS-compatible storage for Spectrum. EFS suits shared application file systems across EC2 instances, not analytics engines requiring S3-backed tables.

Cost per GB of storage

Why it fails: Cost per GB of storage is not a decisive differentiator here, because the services diverge on request pricing, throughput capacity and retrieval patterns rather than raw storage rate. It is tempting because storage cost is a standard comparison axis, and it would be relevant when comparing S3 storage classes against each other.

Total data volume

Why it fails: Total data volume alone does not separate the two services, since both scale to very large datasets; the decision turns on access pattern, item size and query flexibility. It is tempting because capacity planning matters generally, and it would be a genuine factor when sizing provisioned DynamoDB throughput or estimating S3 storage growth.

The user's session has timed out.

Why it fails: A timed-out session produces a connection or authentication failure, not a relation-level permission error; the query would not reach permission checking. It is tempting because session expiry is a common Redshift annoyance, but it applies when reconnecting is needed, not when SELECT has been granted yet denied.

The user does not have CONNECT permission on the database.

Why it fails: Without CONNECT on the database the user cannot log in or run any query at all, so the failure would occur before reaching the table. It is tempting because CONNECT is a prerequisite permission, but the stem shows the query executing and failing specifically on the relation.

The table is in a different schema than expected.

Why it fails: If the table sits in another schema, the unqualified name resolves elsewhere or fails to resolve, and SELECT granted on the intended table does not apply. It is tempting because search_path issues are common, but the error names the relation, indicating the table was found and permission was checked.

Use Amazon ElastiCache for caching

Why it fails: ElastiCache caches query results but does not serve the authoritative dataset; cache misses still reach the primary, and invalidation logic must be written and maintained. It is tempting because it cuts latency for repeated reads, making it the right choice when the same records are requested frequently rather than for broad read offloading.

Enable Multi-AZ deployment

Why it fails: Multi-AZ maintains a synchronous standby that serves no read traffic; it exists for failover availability, so the primary still absorbs every read request. It is tempting because it improves resilience with minimal configuration, making it the right choice when the requirement is high availability rather than offloading read queries.

Increase the instance size of the primary DB

Why it fails: Scaling the primary instance vertically adds CPU and memory but leaves all read traffic terminating on that same primary, so the read bottleneck persists. It is tempting because it is a single-click change requiring no application modification, making it the right choice when the primary is CPU-bound by write processing rather than read volume.

Improves write throughput by batching writes.

Why it fails: DAX does not batch writes.

Supports write-through caching to improve write performance.

Why it fails: DAX is primarily for reads, not writes.

The Deny statement should include a condition on the source IP.

Why it fails: Deny should apply to non-HTTPS regardless of IP.

The Allow statement should include a condition for SecureTransport.

Why it fails: Allow without SecureTransport condition is fine, but Deny is too restrictive.

The Allow statement should specify s3:GetObject instead of s3:GetObject.

Why it fails: Action is correct.

Ability to encrypt data at rest.

Why it fails: Both services support encryption at rest.

Support for multi-region disaster recovery.

Why it fails: Both offer multi-AZ; DynamoDB Global Tables vs RDS cross-region read replicas.

The number of columns in the table.

Why it fails: Column count does not influence Redshift distribution; the planner distributes rows by key values or round-robin, so width is irrelevant to collocation and join data movement. It is tempting because wider tables do cost more to scan, which matters for columnar storage, not for distribution style selection.

The data type of the distribution key column.

Why it fails: Distribution keys must be hashable, and Redshift supports common types, so the key's data type does not determine collocation or reduce join data movement. It is tempting because type mismatches can force casting in join predicates, which is a query-design concern rather than a distribution-style factor.

Remove the Condition block from the Allow statement.

Why it fails: The Deny statement would still block uploads without encryption header.

Change the Condition in the Allow statement to use aws:kms instead of AES256.

Why it fails: That still requires encryption header; Deny still blocks if header is missing.

Set the bucket's default encryption to AES256 and keep the policy unchanged.

Why it fails: The Deny statement still blocks uploads that do not include encryption header, even if bucket has default encryption.

Enable parallel query for the database

Why it fails: Parallel query distributes read-heavy analytical scans across Aurora storage nodes, so it cannot reduce latency caused by many small write transactions. It is tempting because it accelerates reporting queries, and it would be correct for a read-intensive analytics workload, not the write path described here.

Enable Aurora Auto Scaling for read replicas

Why it fails: Auto Scaling adds read replicas, which serve SELECT traffic only; writes still funnel through the single primary writer, so small-transaction commit latency is unchanged. It is tempting because it scales read capacity, and it would be correct for read-heavy workloads, not the write-bound scenario described.

Enable Performance Insights to identify the bottleneck

Why it fails: Performance Insights is a monitoring and diagnostic tool that surfaces wait events and top SQL, but it changes no database configuration, so write latency persists. It is tempting because it pinpoints bottlenecks, and it would be correct as an investigation step before applying an actual tuning change.

Increase the Lambda function memory.

Why it fails: Memory affects CPU, not DynamoDB write throttling.

Increase the DynamoDB read capacity units.

Why it fails: Read capacity does not affect write throughput.

Decrease the Lambda batch size to 1.

Why it fails: Smaller batches may increase per-record overhead and throttling.

Enable Multi-AZ on the primary database.

Why it fails: Multi-AZ maintains a synchronous standby within one Region for availability, not cross-Region replication throughput, so it leaves the replica lag unchanged. It is the right choice when the requirement is automatic failover inside a single Region, not disaster recovery across Regions.

Increase the instance size of the primary database.

Why it fails: Enlarging the primary increases its write capacity but does not address the cross-Region network transfer or replica apply throughput that causes lag. It suits a primary bottlenecked on CPU or memory, not one whose cross-Region replica cannot keep pace.

Decrease the instance size of the replica to reduce cost.

Why it fails: Shrinking the replica reduces its capacity to apply incoming WAL changes, which increases lag rather than reducing it. Smaller instances suit cost reduction for lightly queried replicas, not a disaster-recovery replica already struggling to keep pace with the primary.

Set an S3 Lifecycle policy to delete files smaller than 1 MB.

Why it fails: Lifecycle policies delete objects asynchronously on a schedule and cannot distinguish a zero-byte file still being written from a finished one, so the ETL job may still read incomplete data. Lifecycle rules are correct for cost-driven expiry or storage-class transitions, not write-completion signalling.

Use S3 Copy to move files to a 'processed' folder after the ETL job reads them.

Why it fails: Copying after reading does not prevent the ETL job from encountering a zero-byte file mid-write; the failure occurs before the move. Post-processing archiving is appropriate for retaining or relocating already-consumed objects, not for gating reads on write completion.

Configure S3 Select to query the files and only return rows if the file is complete.

Why it fails: S3 Select filters rows within a single object's content; it cannot detect that an object is still being written, so a zero-byte or partial file simply returns no rows rather than blocking the ETL. It is designed for retrieving subsets of columns from complete objects, which would be correct for reducing data transfer, not for coordinating write completion.

To enforce server-side encryption with KMS.

Why it fails: The condition does not reference aws:ServerSideEncryption or a KMS key ARN, so it cannot mandate SSE-KMS on uploads. It tempts administrators hardening cross-account writes, since encryption enforcement is a common S3 control, but that requires a dedicated encryption condition key rather than the one present here.

To limit the size of objects that can be uploaded.

Why it fails: The condition evaluates a request attribute tied to identity or encryption context, not object byte size; S3 upload size limits are enforced through bucket policy or presigned URL constraints. Size restriction is tempting because upload conditions often govern payloads, yet this statement's key targets a different request element.

To restrict uploads to only a specific AWS account.

Why it fails: Account restriction is expressed through the Principal element or aws:SourceAccount, not this condition key. It tempts because cross-account uploads are inherently scoped to a trusted account, yet the statement's condition inspects a different request attribute, leaving the principal as the actual account-limiting mechanism.

The Deny statement blocks all decrypt requests

Why it fails: A blanket Deny on decrypt requests would be visible in the attached policy, and the stem gives no such statement. It is tempting because an explicit Deny always overrides Allow in IAM evaluation, making it the correct diagnosis when a policy genuinely contains a Deny for kms:Decrypt on that key.

The Lambda function does not have permission to call kms:GenerateDataKey

Why it fails: GenerateDataKey is called during encryption to produce a data key, not during decryption of an existing object, so its absence cannot cause this error. It is tempting because GenerateDataKey and Decrypt are both KMS cryptographic actions, and it is correct when the failure occurs while encrypting rather than decrypting.

The IAM policy does not include kms:Decrypt permission

Why it fails: The stem states the execution role already carries a policy, and decryption failures from a missing kms:Decrypt statement would be visible in that policy's contents. It is tempting because kms:Decrypt is genuinely required to decrypt data keys, so it is the right answer when the attached policy omits that action.

Use Amazon Kinesis Data Streams to capture DynamoDB Streams changes.

Why it fails: This is for streaming, not for bulk export; also may not guarantee exactly-once for large backfills.

Use Amazon S3 Object Lambda to transform data on the fly.

Why it fails: S3 Object Lambda is for user-defined transformations on GET requests, not for ingestion pipelines.

Enable S3 multipart upload for the Glue job.

Why it fails: Multipart upload only splits a single object into parts during transfer; it does not coalesce the many small objects Spark writes per partition. It is tempting because it speeds large uploads and improves resilience, but the stem's problem is file count, not transfer throughput.

Increase the 'spark.sql.shuffle.partitions' to 500.

Why it fails: Raising shuffle partitions to 500 increases task count, producing even more, smaller output files per partition — the opposite of the goal. It is tempting because more partitions can relieve skew and improve parallelism on large datasets, but here it worsens the small-file problem.

Reduce the 'spark.sql.shuffle.partitions' to 50.

Why it fails: Fewer shuffle partitions means fewer concurrent tasks, so each task writes a larger file, but with only 50 tasks the job loses parallelism and risks skew and memory pressure, violating the requirement to maintain performance. Reducing partitions is right only when the cluster is small.