Data Store Management practice questions

Change DISTSTYLE to EVEN to distribute rows evenly across slices.

Why it fails: DISTSTYLE ALL already replicates the entire table to every node slice, so EVEN would not reduce per-slice data volume for this fact table. EVEN suits large dimension or staging tables joined less frequently, where even slice distribution matters.

Increase the number of nodes in the Redshift cluster.

Why it fails: Adding nodes does not fix the underlying cause: DISTSTYLE ALL copies the full table to every slice, so more nodes means more redundant copies and longer loads, not faster scans. Scaling suits clusters genuinely short of compute or storage capacity.

Change the table to use a SORTKEY on the most frequently filtered column.

Why it fails: A SORTKEY reduces the blocks scanned only when queries filter on that column, but the stem never states which columns queries filter on. SORTKEY selection requires identifying the most frequently filtered column first, so this is speculative rather than the likely fix.

Enable S3 Transfer Acceleration on the bucket.

Why it fails: Transfer Acceleration speeds uploads over long distances using edge locations; it does not encrypt stored objects. Encryption at rest requires SSE-S3, SSE-KMS, or SSE-C, or default bucket encryption. Transfer Acceleration would be correct for accelerating geographically distant uploads, not for meeting encryption requirements.

Use client-side encryption before uploading objects to S3.

Why it fails: Client-side encryption protects objects before they reach S3, but the question asks for server-side controls applied within the bucket. It is tempting because it genuinely encrypts data at rest and gives the customer full key custody, which suits scenarios demanding end-to-end control where AWS must never hold plaintext or keys.

Attach a bucket policy that denies unencrypted PUT requests.

Why it fails: A deny policy on unencrypted PUTs enforces encryption in transit and rejects non-compliant uploads, but it does not itself encrypt stored objects. It is tempting because such policies are the standard guardrail for preventing plaintext writes, and would be correct when the goal is enforcing encryption rather than performing it.

S3 Transfer Acceleration

Why it fails: Transfer Acceleration speeds uploads over long distances via edge locations, but does not provide immutability or streaming ingestion. It would suit geographically dispersed uploads, whereas S3 Object Lock and event notifications address the stated requirements.

S3 Cross-Region Replication (CRR)

Why it fails: CRR copies objects to another Region for disaster recovery or latency reduction; it neither enforces immutability nor accelerates ingestion. The stem demands immutability and high-throughput streaming, which S3 Object Lock and Kinesis Data Firehose address. CRR would be correct for cross-Region durability or compliance-driven geographic redundancy.

Enable DynamoDB Accelerator (DAX) for caching.

Why it fails: DAX adds cost and may not eliminate scans fully.

Set a TTL attribute to automatically expire old data.

Why it fails: TTL deletes items after expiry, so the daily scan returns fewer rows and the reporting output changes, violating the requirement to maintain identical output. It is tempting because TTL genuinely lowers storage and scan costs when old data is no longer needed by the report.

Reduce the read capacity units (RCU) in the table.

Why it fails: On-demand tables have no provisioned RCUs to reduce; capacity is billed per request, so this setting does not exist for the table and cannot lower the scan cost. It is tempting because lowering RCUs genuinely cuts spend on provisioned tables with over-allocated capacity.

The bucket has versioning enabled.

Why it fails: Versioning does not prevent lifecycle transitions. Objects of any version can still transition based on the policy.

Deep Archive is not supported in the bucket's region.

Why it fails: S3 Deep Archive is supported in all commercial AWS regions, so this is not the cause.

The transition to Deep Archive requires a minimum of 30 days after the previous transition.

Why it fails: AWS requires a minimum of 30 days between transitions. The policy transitions at 30 and 60 days, exactly 30 days apart, so this requirement is satisfied and is not the cause of failure.

No, because the object is encrypted and the user does not have decrypt permission.

Why it fails: No, because the object is encrypted and the user does not have decrypt permission. — Actually, the user does have GetObject permission, and SSE-S3 decryption is automatic. The user does not need a separate decrypt permission for SSE-S3.

No, because the request does not include the required encryption header.

Why it fails: No, because the request does not include the required encryption header. — This is incorrect because SSE-S3 does not require encryption headers on GET requests.

Yes, because the object is encrypted with SSE-S3, which uses AES256.

Why it fails: Yes, because the object is encrypted with SSE-S3, which uses AES256. — This is partially correct but not the direct reason; the key reason is that the policy allows GetObject, and SSE-S3 decryption is transparent.

Create a global secondary index with the same key schema to distribute reads

Why it fails: A GSI with an identical key schema still partitions by game_id, so a hot game's top-10 query hits one partition and throttles on that partition's throughput. It is tempting because GSIs genuinely spread read load when their partition key differs from the base table's.

Remove the sort key and use a global secondary index

Why it fails: Removing the sort key destroys the ordering that lets a Query return the top 10 scores efficiently, forcing full partition reads and changing the access pattern. It is tempting because GSIs genuinely enable alternate query patterns, but not by discarding the range key that serves this one.

Increase the provisioned RCUs to 2000

Why it fails: Throttling here is per-partition, not table-wide: average consumption of 800 RCUs leaves headroom, so raising provisioned RCUs to 2000 does not relieve the hot game_id partition. It is tempting because raising capacity genuinely fixes throttling when the whole table is under-provisioned.

The Lifecycle policy has a filter that excludes this object's prefix

Why it fails: A prefix filter would prevent transition entirely, yet the object already sits in STANDARD_IA, proving the policy matched it. Prefix filters are the right mechanism when only a subset of a bucket, such as a logs/ prefix, should transition.

Versioning is enabled and the current version is not the oldest

Why it fails: VersionId is null, so versioning is not enabled.

The metadata timestamp is used for lifecycle transitions instead of LastModified

Why it fails: Lifecycle policies use the object's LastModified date, not custom metadata.

Use S3 server-side encryption with AWS KMS and an AWS managed key

Why it fails: AWS managed KMS keys cannot be backed by the customer's own HSM, and AWS controls their rotation schedule, so the 90-day customer-managed requirement fails. It is tempting because KMS integration is the standard S3 encryption route, and would be correct where AWS-managed rotation and key custody are acceptable.

Use client-side encryption with keys stored in AWS Secrets Manager

Why it fails: Client-side encryption with keys in AWS Secrets Manager fails because Secrets Manager is a managed AWS service, not the company’s own HSM; the requirement mandates that the company’s own HSM manage and rotate keys every 90 days, which Secrets Manager cannot fulfil. It is tempting because Secrets Manager can store and rotate encryption keys automatically, making it a correct choice when the key management authority is delegated to AWS rather than retained in a customer-controlled HSM.

Use S3 server-side encryption with S3 managed keys (SSE-S3)

Why it fails: SSE-S3 keys are owned and rotated by AWS with no customer HSM involvement and no configurable rotation interval, so it cannot satisfy either requirement. It is tempting because SSE-S3 is the zero-configuration default that encrypts every object automatically, and would be correct where no key custody or rotation control is mandated.

Configure S3 Intelligent-Tiering on both prefixes with automatic archiving to Glacier Deep Archive after 90 days in raw and deletion after 365 days in curated.

Why it fails: Intelligent-Tiering may not enforce exact days and can incur monitoring costs.

Create separate lifecycle policies for each prefix: one to transition raw to S3 Glacier Deep Archive after 90 days, another to delete curated after 365 days.

Why it fails: Separate policies are unnecessary; a single policy can handle both.

Create a lifecycle policy that transitions raw zone data to S3 Standard-IA after 90 days and deletes curated zone data after 365 days.

Why it fails: Standard-IA is not the cheapest for deep archival; Glacier Deep Archive is cheaper.

Use ALL distribution for the 'sales' fact table to replicate data to every node and avoid data movement.

Why it fails: ALL distribution is for small dimension tables; replicating a large fact table wastes storage.

Use a compound sort key with the most frequently filtered column first.

Why it fails: Compound sort keys are less effective when queries filter on columns other than the first.

Choose AUTO distribution style for all tables and let Amazon Redshift automatically assign distribution.

Why it fails: AUTO may not always be optimal; manual selection can be better based on query patterns.

Modify the table's primary key to include 'OrderDate' as an additional sort key.

Why it fails: DynamoDB primary keys are immutable after table creation, so 'OrderDate' cannot be appended to the existing key schema. It is tempting because altering the key would directly serve the query, and would be correct only when designing a new table before any data is written.

Use a Scan operation with a filter expression on 'CustomerID' and 'OrderDate' to retrieve the data.

Why it fails: Scan reads every item in the table before the filter is applied, consuming provisioned capacity proportional to table size rather than matching items. It is tempting because it needs no schema change, and would be correct for small tables or ad hoc analysis where efficiency is not the priority.

Create a Local Secondary Index (LSI) with 'CustomerID' as partition key and 'OrderDate' as sort key.

Why it fails: An LSI must share the table's partition key, so 'CustomerID' cannot serve as its partition key; LSIs also require creation at table creation time. It is tempting because LSIs provide alternate sort keys, which would be correct if the query kept 'OrderID' as partition key and sorted by 'OrderDate'.

Create the destination bucket first, then enable versioning on source and destination, then add a replication rule, then assign an IAM role.

Why it fails: This is incorrect because versioning must be enabled before creating the replication rule, but creating the destination bucket before enabling versioning is permissible; however, the order of versioning and bucket creation could be swapped, but versioning must be enabled before rule creation. In this sequence, versioning is after bucket creation, which is allowed but not optimal. The key error is that versioning must be on before rule creation, which is satisfied, but the bucket creation before versioning is technically allowed but not required. However, this order is not the standard recommended sequence; still, it could work. To make this clearly wrong, consider that the question expects a specific order. We'll mark it incorrect.

Enable versioning on source and destination, then add a replication rule, then create the destination bucket, then assign an IAM role.

Why it fails: This is incorrect because the destination bucket must exist before adding a replication rule. Adding a rule before creating the bucket will cause an error.

Enable versioning on source and destination, then create the destination bucket, then assign an IAM role, then add a replication rule.

Why it fails: This is incorrect because the IAM role must be assigned as part of the replication rule, not separately before adding the rule. The rule creation step includes specifying the role.

Deny s3:PutObject unless s3:x-amz-server-side-encryption is present

Why it fails: This only enforces that some form of encryption is used, not necessarily KMS with the specific key.

Allow s3:PutObject only if s3:x-amz-server-side-encryption is present

Why it fails: This allows but does not enforce KMS; also, it does not require the specific key.

Deny s3:PutObject unless s3:x-amz-server-side-encryption equals AES256

Why it fails: This enforces SSE-S3, not KMS.

Enable Multi-AZ deployment

Why it fails: Multi-AZ provides high availability but does not improve write throughput.

Increase the provisioned IOPS on the existing RDS instance

Why it fails: This increases cost and may not be efficient if the bottleneck is elsewhere.

Add a read replica to offload read traffic

Why it fails: Read replicas handle only read queries, not writes.

Transition to Glacier Flexible Retrieval after 30 days, then delete after 120 days

Why it fails: Glacier has retrieval delays; data still needs to be accessed rarely, not archived.

Transition to One Zone-IA after 30 days, then to Glacier Deep Archive after 120 days

Why it fails: One Zone-IA is less durable and not suitable if data loss is unacceptable.

Transition to Glacier Deep Archive after 30 days, then delete after 120 days

Why it fails: Immediate retrieval is needed for 30 days; Glacier Deep Archive has long retrieval times.

Amazon RDS for MySQL with InnoDB

Why it fails: RDS is relational and not optimized for high-volume small writes; may require scaling.

Amazon S3 with standard storage class

Why it fails: S3 is not designed for high-frequency small writes; can lead to high costs and latency.

Amazon Elastic Block Store (EBS) volumes

Why it fails: EBS is block storage for EC2, not a managed data store for IoT ingestion.

Use the COPY command to reload data

Why it fails: COPY is for loading data, not reclaiming space.

Run VACUUM FULL on all tables

Why it fails: VACUUM FULL locks tables and may interrupt queries.

Resize the cluster to a larger instance type

Why it fails: Resizing is disruptive and does not reclain space; it adds capacity.

Amazon RDS

Why it fails: RDS is a relational database, not an S3 ingestion service.

Amazon EMR

Why it fails: EMR processes data but is not a direct ingestion service.

Amazon DynamoDB

Why it fails: DynamoDB is a NoSQL database, not an S3 ingestion service.

S3 supports POSIX file system semantics

Why it fails: S3 is an object store, not POSIX-compliant.

HDFS can be directly mounted as an S3 bucket

Why it fails: HDFS and S3 are different filesystems; can't mount directly.