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

Question 1

A company uses Amazon Redshift for its data warehouse. The data engineer notices that the most frequently accessed table is sorted by date, but queries often filter by customer_id. The table has 500 million rows and uses AUTO distribution style. What change would MOST improve query performance?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Change the sort key to include customer_id as a compound sort key.. Since queries frequently filter by customer_id but the table is sorted only by date, Redshift must scan all rows that match the date range and then filter by customer_id. By adding customer_id as a compound sort key (date, customer_id), Redshift can use zone maps to skip blocks that don't contain the requested customer_id within the date range, dramatically reducing the number of rows scanned and improving query performance.

Answer

Change distribution style to KEY on customer_id.

Answer

Change distribution style to EVEN.

Answer

Change the sort key to an interleaved sort key on date and customer_id.

Question 2

A data engineer needs to migrate an on-premises Apache Hadoop cluster to AWS. The cluster stores data in HDFS and runs MapReduce jobs. The company wants to minimize operational overhead and leverage serverless technologies where possible. Which AWS service should the data engineer use to replace HDFS storage?

Accepted Answer

Amazon S3. Amazon S3 is the correct replacement for HDFS because it provides highly durable, scalable, and serverless object storage that can be used as the primary storage layer for Amazon EMR. Unlike HDFS, S3 decouples storage from compute, eliminating the need to manage cluster storage and allowing jobs to run on ephemeral clusters, which minimizes operational overhead. S3 integrates with EMR via the EMR File System (EMRFS), enabling MapReduce jobs to read/write data directly from S3 as if it were HDFS.

Answer

Amazon EBS

Answer

Amazon EMR

Answer

Amazon Redshift

Question 3

A company uses Amazon DynamoDB to store session data for a web application. The application experiences occasional spikes in traffic, causing throttling on the table. The data engineer needs to implement a solution that handles traffic spikes without manual intervention and minimizes cost. What should the data engineer do?

Accepted Answer

Enable DynamoDB Auto Scaling for the table.. DynamoDB Auto Scaling (option D) automatically adjusts the provisioned read and write capacity based on actual traffic patterns, using CloudWatch alarms and the Application Auto Scaling service. This handles traffic spikes without manual intervention and minimizes cost by scaling down during low traffic periods, making it the ideal solution for variable workloads like session data.

Answer

Switch to provisioned capacity with a high fixed read/write capacity.

Answer

Implement DynamoDB Accelerator (DAX) to cache read requests.

Answer

Purchase DynamoDB reserved capacity.

Question 4

A data engineer is designing a data lake on AWS using Amazon S3. The data consists of CSV files generated by IoT devices. The data is accessed by multiple analytics jobs, and the engineer needs to ensure that new files are immediately visible to all consumers after writing. What S3 consistency model applies?

Accepted Answer

Strong consistency for all operations.. Amazon S3 now provides strong consistency for all operations. After a successful write of a new object (or overwrite of an existing object), any subsequent read request immediately receives the latest version of the object, and list operations are also strongly consistent. Therefore, for new CSV files written to S3, the applicable model is strong consistency for all operations.

Answer

Consistent reads require S3 Object Lock.

Answer

Eventual consistency for all operations.

Answer

Read-after-write consistency for new object PUTS.

Question 5

A company has an Amazon RDS for PostgreSQL DB instance with a large table that is frequently updated. The data engineer needs to reduce storage costs by archiving old records that are no longer accessed. The archived records must be retained for 7 years due to compliance requirements. Which solution is MOST cost-effective?

Accepted Answer

Export old records using pg_dump and store in S3 Glacier Deep Archive.. 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.

Answer

Use RDS native backup and restore to keep a separate backup.

Answer

Enable storage autoscaling on the RDS instance.

Answer

Move old records to a separate table in the same RDS instance.

Question 6

A data engineer is designing a multi-region disaster recovery plan for an Amazon DynamoDB table. The table stores critical user profile data and must have a Recovery Point Objective (RPO) of less than 1 minute and a Recovery Time Objective (RTO) of less than 5 minutes. Which solution meets these requirements?

Accepted Answer

Use DynamoDB global tables to replicate data to another region.. 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.

Answer

Configure DynamoDB Streams and a Lambda function to replicate data to another region.

Answer

Use DynamoDB on-demand backup and restore to another region.

Answer

Enable point-in-time recovery (PITR) and restore to another region.

Question 7

A company uses Amazon S3 to store customer documents. The data engineer needs to ensure that all objects uploaded to a specific S3 bucket are automatically encrypted with a customer-managed AWS KMS key. What should the data engineer do?

Accepted Answer

Set default encryption on the bucket to use SSE-KMS with the customer-managed key.. Setting default encryption on the S3 bucket to SSE-KMS with the customer-managed key ensures that all objects uploaded without explicit encryption headers are automatically encrypted using that KMS key. This satisfies the requirement without relying on client-side behavior, as S3 applies the encryption server-side at the time of write.

Answer

Use pre-signed URLs for all uploads that include encryption parameters.

Answer

Create a bucket policy that denies uploads without encryption.

Answer

Enable S3 Versioning on the bucket.

Question 8

A data engineer is designing a data pipeline that ingests streaming data from IoT devices into Amazon S3 using Amazon Kinesis Data Firehose. The data must be transformed from JSON to Parquet format before storage. Which TWO actions should the data engineer take to achieve this?

Accepted Answer

Create a Glue Data Catalog table defining the schema and configure Firehose to use the table for Parquet conversion.. Amazon Kinesis Data Firehose can directly convert incoming JSON data to Parquet format by referencing a table schema defined in the AWS Glue Data Catalog. This allows Firehose to perform the schema-aware conversion without custom code, leveraging the Glue table's column definitions and SerDe for Parquet serialization.

Answer

Enable Firehose's built-in Parquet conversion without any additional configuration.

Answer

Use Amazon Kinesis Data Analytics to convert the data format.

Answer

Configure Firehose to convert the data to Apache Avro format.

Question 9

A company stores sensitive financial data in an Amazon Redshift cluster. The data engineer must ensure that all queries are logged for audit purposes and that the logs are stored in Amazon S3 with server-side encryption. Which THREE steps should the data engineer take to meet these requirements?

Accepted Answer

Configure audit logs to be stored in an Amazon S3 bucket.. Amazon Redshift audit logs can be configured to be stored directly in an Amazon S3 bucket, which is a native feature for exporting connection logs, user logs, and query logs. This satisfies the requirement to log all queries for audit purposes without relying on external services.

Answer

Enable encryption on the Redshift cluster.

Answer

Enable AWS CloudTrail to log Redshift queries.

Question 10

A data engineer is optimizing an Amazon RDS for MySQL database that experiences high write throughput. The engineer wants to improve write performance and reduce latency. Which TWO database-level configuration changes can help achieve this?

Accepted Answer

Use Provisioned IOPS (io1 or io2) storage.. Provisioned IOPS (io1 or io2) storage delivers consistent and predictable I/O performance by guaranteeing a specified number of I/O operations per second, which directly reduces latency and improves write throughput for high-write workloads. This is the most effective storage-level change for write-intensive RDS for MySQL databases.

Answer

Reduce the backup retention period to 1 day.

Answer

Create a Read Replica to offload writes.

Answer

Enable Multi-AZ for high availability.

Question 11

A company is using Amazon RDS for MySQL with Multi-AZ deployment. The primary DB instance experiences a hardware failure, causing automatic failover to the standby. After the failover, the application reports that the database endpoint is unreachable for about 60 seconds. What is the MOST likely cause?

Accepted Answer

The DNS record for the DB instance endpoint needed to update to point to the new primary.. After an automatic failover in Amazon RDS Multi-AZ, the DNS record for the DB instance endpoint is updated to point to the new primary. This DNS change can take up to 60 seconds to propagate, during which the application may receive 'unreachable' errors if it caches the old DNS resolution. The 60-second outage aligns with the typical TTL (Time To Live) of 30 seconds for RDS DNS records plus propagation delays.

Answer

The standby instance took longer than expected to promote to primary.

Answer

The standby instance was not in a synchronized state and required a manual promotion.

Answer

The application was using the wrong endpoint and needed to be reconfigured.

Question 12

A data engineer is designing a data lake on Amazon S3. The data is ingested from multiple sources in Parquet format, partitioned by date. The engineer needs to ensure that queries using Amazon Athena are cost-effective and perform well. Which approach should the engineer take?

Accepted Answer

Use Parquet format with Snappy compression and partition by year, month, day.. Parquet is a columnar storage format that reduces the amount of data scanned by Athena, and Snappy compression provides a good balance between compression ratio and decompression speed. Partitioning by year, month, and day allows Athena to use partition pruning to skip irrelevant data, minimizing scanned bytes and reducing query cost.

Answer

Store data in uncompressed CSV format and partition by year, month, day, hour.

Answer

Use JSON format with Snappy compression and partition by date only.

Answer

Use Gzip-compressed CSV files with no partitioning.

Question 13

A company stores critical financial data in Amazon DynamoDB. To meet compliance requirements, the data must be encrypted at rest with a customer-managed key. Which solution should the data engineer implement?

Accepted Answer

Configure the DynamoDB table to use a customer managed key from AWS KMS.. AWS DynamoDB integrates with AWS KMS to support encryption at rest using customer-managed keys (CMKs). By selecting a customer-managed key from KMS during table creation or via an update, the company can meet compliance requirements for controlling the encryption key lifecycle, including rotation and access policies. This approach ensures that the data is encrypted using AES-256 encryption, with the key material managed by the customer rather than AWS.

Answer

Use AWS CloudHSM to generate a key and import it into DynamoDB.

Answer

Enable default encryption on the DynamoDB table using S3-managed keys.

Answer

Use AWS Certificate Manager to issue a certificate and configure TLS.

Question 14

A data engineer is migrating an on-premises PostgreSQL database to Amazon RDS for PostgreSQL. The database is 2 TB in size and has a tight migration window. Which migration approach minimizes downtime?

Accepted Answer

Use AWS DMS with ongoing replication to capture changes during migration.. AWS DMS with ongoing replication (change data capture, CDC) allows you to perform a full load of the existing data and then continuously replicate incremental changes from the source PostgreSQL database to the target RDS for PostgreSQL. This minimizes downtime because you can keep the source database fully operational during the migration and only switch applications over after the target is fully synchronized, resulting in a very short cutover window.

Answer

Use AWS Database Migration Service (DMS) with full load only.

Answer

Use pg_dump to export the database and pg_restore to import into RDS.

Answer

Create a read replica in RDS and promote it when ready.

Question 15

A company uses Amazon Redshift for analytics. The data engineer notices that queries are slow and the system is experiencing high disk usage. The engineer suspects that the distribution style is suboptimal. Which action should the engineer take to improve query performance?

Accepted Answer

Use the DISTSTYLE AUTO setting and analyze query patterns to let Redshift choose.. DISTSTYLE AUTO allows Amazon Redshift to automatically assign distribution styles (KEY, EVEN, or ALL) based on query patterns and table size, optimizing data distribution for improved query performance. This is particularly effective when the engineer suspects suboptimal distribution but lacks detailed knowledge of the ideal key, as Redshift analyzes workload patterns to reduce data movement and disk usage.

Answer

Convert all tables to use SORTKEY on the most frequently filtered column.

Answer

Increase the number of nodes in the cluster to distribute data across more slices.

Answer

Set all tables to DISTSTYLE EVEN to distribute data evenly.