DEA-C01 Data Store Management Practice Test 5 — 15 Questions

Question 1

A company wants to store data from thousands of IoT devices with varying data rates. The data must be stored in a schema-on-read fashion and support SQL queries. Which AWS service should be used?

Accepted Answer

Amazon S3 with Amazon Athena. Amazon S3 stores data in its native format (e.g., JSON, Parquet) without requiring a predefined schema, enabling schema-on-read. Amazon Athena uses Presto-based SQL to query data directly from S3, making it ideal for IoT data with varying rates and ad-hoc SQL analysis without provisioning servers.

Answer

Amazon RDS for MySQL

Answer

Amazon DynamoDB

Answer

Amazon Redshift

Question 2

A company runs a data warehouse on Amazon Redshift. Queries are slow, and the team suspects data distribution is skewed. Which approach would best help identify distribution skew?

Accepted Answer

Query the SVV_DISKUSAGE table to examine data distribution across slices. The SVV_DISKUSAGE table provides per-slice data distribution information, allowing you to identify skew by comparing the number of blocks allocated to each slice for a given table. In Amazon Redshift, data is distributed across slices based on the distribution key, and significant variation in block counts across slices indicates distribution skew, which can cause query performance degradation due to uneven workload distribution.

Answer

Check the STL_LOAD_ERRORS table for load failures

Answer

Query the SVV_TABLE_INFO table to see table size

Answer

Review the WLM configuration in the parameter group

Question 3

A company needs to store JSON documents that are frequently read and written by a web application. The data must be highly available and durable across multiple Availability Zones. Which AWS database service meets these requirements?

Accepted Answer

Amazon DynamoDB. Amazon DynamoDB is a fully managed NoSQL key-value and document database that provides single-digit millisecond performance at any scale. It stores JSON documents natively, supports frequent reads and writes, and offers built-in high availability and durability by automatically replicating data across multiple Availability Zones (AZs) in an AWS Region. This makes it the ideal choice for the described web application workload.

Answer

Amazon RDS for PostgreSQL

Answer

Amazon S3

Answer

Amazon ElastiCache for Redis

Question 4

A company is migrating an on-premises MongoDB database to Amazon DocumentDB. The migration must have minimal downtime. Which service should be used to perform the migration?

Accepted Answer

AWS Database Migration Service (DMS). 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.

Answer

AWS Glue

Answer

AWS DataSync

Answer

Amazon S3 Transfer Acceleration

Question 5

A company uses Amazon Redshift for analytics. The data engineering team wants to improve query performance for frequently used aggregate queries. Which TWO actions would help achieve this?

Accepted Answer

Use distribution keys to collocate data on the same node slices. Distribution keys determine how data is distributed across node slices in Amazon Redshift. By choosing distribution keys that align with the join and aggregation columns, the database can collocate related data on the same slice, minimizing data movement during query execution. This directly improves performance for aggregate queries by reducing network traffic and enabling local computation.

Answer

Increase the number of WLM query queues

Answer

Run the VACUUM command to reclaim space from deleted rows

Answer

Increase the number of nodes in the cluster

Question 6

A company stores sensitive data in Amazon S3. The security team requires encryption at rest and that the encryption keys are managed by the company using AWS KMS. The data is frequently accessed by multiple AWS services. Which THREE steps should be taken to meet these requirements?

Accepted Answer

Configure the KMS key policy to allow the necessary AWS services to use the key for decryption. The security team requires that encryption keys be managed by the company using AWS KMS, and that multiple AWS services can access the data. To allow those services to decrypt objects encrypted with a customer-managed KMS key, the KMS key policy must explicitly grant the necessary AWS services (e.g., AWS Lambda, Amazon Athena) permission to use the key for decryption (kms:Decrypt). Without this policy, even if the bucket is configured for SSE-KMS, the services will fail to read the encrypted objects.

Answer

Use client-side encryption with the KMS key before uploading to S3

Answer

Enable default encryption on the S3 bucket using SSE-S3

Question 7

A company is designing a data lake on Amazon S3. The data includes CSV files, Parquet files, and images. The data engineering team needs to catalog the metadata and enable SQL queries. Which TWO AWS services should be used together?

Accepted Answer

Amazon Athena. Amazon Athena is correct because it is a serverless interactive query service that can directly query data stored in Amazon S3 using standard SQL, without needing to load or transform data. AWS Glue is correct because it provides a fully managed data catalog (AWS Glue Data Catalog) that stores metadata about the data lake's schema, partitions, and locations, which Athena can use to discover and query the data efficiently.

Answer

Amazon EMR

Answer

Amazon Redshift Spectrum

Answer

Amazon QuickSight

Question 8

A company uses Amazon DynamoDB for a gaming application. The application experiences throttling during peak hours. The table's read and write capacity is provisioned. Which TWO actions can reduce throttling?

Accepted Answer

Enable DynamoDB auto scaling for the table. DynamoDB auto scaling (Option B) automatically adjusts the provisioned read and write capacity based on actual traffic patterns, preventing throttling during peak hours without manual intervention. This is the correct action because it dynamically increases capacity when demand spikes and reduces it during low traffic, directly addressing the throttling issue.

Answer

Enable TTL (time to live) on the table to automatically delete old items

Answer

Increase the provisioned read capacity units (RCUs)

Answer

Add a DynamoDB Global Table for the table

Question 9

A company is migrating a large Oracle database to Amazon Aurora PostgreSQL. The migration must have minimal downtime and preserve data consistency. Which THREE AWS services or features should be used?

Accepted Answer

AWS Schema Conversion Tool (SCT) for schema conversion. The AWS Schema Conversion Tool (SCT) is required to convert the source Oracle database schema (including stored procedures, functions, and data types) to a format compatible with Amazon Aurora PostgreSQL. Without SCT, the heterogeneous migration would fail due to incompatible SQL dialects and database objects.

Answer

Amazon RDS for Oracle as the target

Answer

AWS DataSync for initial load

Question 10

A company needs to store log files from multiple applications in a centralized location. The logs are written once and accessed rarely after 30 days. The company must retain logs for 5 years. Which TWO actions meet these requirements cost-effectively?

Accepted Answer

Configure a lifecycle policy to transition objects to S3 Glacier Deep Archive after 30 days. S3 Glacier Deep Archive is the lowest-cost storage class for data that is accessed rarely, with retrieval times of 12 hours or more, making it ideal for logs that are rarely accessed after 30 days. A lifecycle policy transitions objects from a higher-cost class (e.g., S3 Standard) to S3 Glacier Deep Archive after 30 days, meeting the 5-year retention requirement cost-effectively.

Answer

Configure a lifecycle policy to transition objects to S3 Glacier Flexible Retrieval after 30 days

Answer

Use S3 One Zone-IA for the first 30 days, then delete

Answer

Store all logs in S3 Standard

Question 11

A company is using Amazon RDS for MySQL with Multi-AZ deployment. They notice that during a recent failover test, the application experienced a brief write outage. The application uses a connection string that points to the RDS instance endpoint. What is the MOST likely cause of the write outage?

Accepted Answer

The application is using the RDS instance endpoint instead of the cluster endpoint, so it does not automatically route to the standby after failover.. In a Multi-AZ RDS deployment, the instance endpoint always points to the current primary instance. During a failover, the DNS record for the instance endpoint is updated to point to the new primary, but existing connections to the old primary are dropped, and the DNS change can take time to propagate. The application's connection string using the instance endpoint means it does not automatically route to the standby during the failover transition, causing a brief write outage until the DNS update completes and the application reconnects. In contrast, using a cluster endpoint (available for Aurora, not standard RDS) or implementing retry logic in the application would mitigate this.

Answer

The application is using a read replica endpoint, which does not support write operations.

Answer

The application is connecting through a Network Load Balancer, which is not configured for cross-zone failover.

Answer

The application connection pool is exhausted because the failover caused all existing connections to drop simultaneously.

Question 12

A data engineer needs to store semi-structured JSON files that are accessed infrequently but must be retrievable within minutes. The data is immutable and must be stored cost-effectively. Which AWS service should the engineer use?

Accepted Answer

Amazon S3 with S3 Standard-IA storage class. Amazon S3 Standard-IA (Infrequent Access) is designed for data that is accessed less frequently but requires rapid retrieval when needed, with retrieval times in milliseconds. It offers lower storage costs than S3 Standard while maintaining high durability and availability, making it ideal for storing immutable semi-structured JSON files that must be retrievable within minutes. The service is cost-effective for infrequently accessed data because it charges a retrieval fee per GB, but the storage price is significantly lower than standard tiers.

Answer

Amazon DynamoDB with on-demand capacity

Answer

Amazon EBS with gp3 volume

Answer

Amazon RDS for PostgreSQL with JSONB data type

Question 13

A company runs an Apache Spark job on Amazon EMR that writes output to an S3 bucket. The job fails with the error 'S3AccessDeniedException' when writing the final output, but earlier stages succeed. The EMR cluster uses a service role and an instance profile. The S3 bucket policy allows access from the VPC only. What is the MOST likely cause?

Accepted Answer

The EMR cluster is not using a VPC endpoint for S3, so requests are denied by the bucket policy's VPC condition.. The bucket policy restricts access to requests originating from the VPC, typically using a condition like `aws:SourceVpc`. If the EMR cluster does not use a VPC endpoint for S3 (either Gateway or Interface endpoint), traffic from the cluster to S3 traverses the public internet and does not match the VPC condition, causing the `S3AccessDeniedException`. Earlier stages may succeed if they use cached data or different paths, but the final write fails because it hits the bucket policy check.

Answer

The S3 bucket uses SSE-C encryption, and the EMR cluster does not have the encryption key.

Answer

The EMR service role does not have permissions to write to the S3 bucket.

Answer

The S3 bucket is configured with 'Bucket owner enforced' setting for ACLs, and the EMR cluster's account is not the bucket owner.

Question 14

A media company stores large video files in Amazon S3 and uses Amazon CloudFront for content delivery. Users in different regions report slow download speeds for popular content. The data engineer needs to improve performance while minimizing cost. Which solution should the engineer implement?

Accepted Answer

Enable CloudFront Origin Shield. CloudFront Origin Shield acts as a centralized caching layer in front of the S3 origin, reducing the number of requests that reach the origin and improving cache hit ratios. This minimizes latency for users in different regions by serving popular content from the edge or shield cache, while also reducing origin load and data transfer costs.

Answer

Change the S3 storage class to S3 Standard-IA

Answer

Enable S3 Transfer Acceleration on the bucket

Answer

Create multiple S3 buckets in different regions and configure CloudFront with multiple origins

Question 15

A company is running a production Amazon Aurora PostgreSQL database. The database experiences high write latency during peak hours. The data engineer suspects that the issue is due to a large number of small transactions. Which action would MOST effectively reduce write latency?

Accepted Answer

Increase the instance size and use Provisioned IOPS storage. Increasing the instance size and using Provisioned IOPS storage directly addresses high write latency by providing more CPU and memory resources to handle transaction processing, while Provisioned IOPS ensures consistent, low-latency I/O for write operations. This is the most effective action because small transactions create high I/O demand, and scaling up the instance with dedicated IOPS reduces contention and write queue depth.

Answer

Enable parallel query for the database

Answer

Enable Aurora Auto Scaling for read replicas

Answer

Enable Performance Insights to identify the bottleneck