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

Question 1

A company uses Amazon Redshift for a data warehouse. They notice that queries are slow due to heavy data skew. Which optimization technique should be applied first?

Accepted Answer

Set an appropriate distribution style. Data skew occurs when rows are distributed unevenly across Redshift slices, causing some nodes to process far more data than others. Setting an appropriate distribution style (e.g., KEY, EVEN, or ALL) redistributes the data to balance the workload, directly addressing the root cause of the slowness. This is the first optimization to apply because skew is a fundamental distribution issue that other tuning steps cannot fix.

Answer

Configure workload management (WLM) queues

Answer

Define sort keys on frequently filtered columns

Answer

Apply compression encodings to columns

Question 2

A company has a DynamoDB table with a partition key of 'user_id' and a sort key of 'timestamp'. They need to query all items for a user within a date range. Which query operation should be used?

Accepted Answer

Query with KeyConditionExpression on partition key and sort key. The Query operation in DynamoDB is designed to retrieve items based on a specific partition key and an optional sort key condition. Since the table has a partition key of 'user_id' and a sort key of 'timestamp', using Query with a KeyConditionExpression that filters on the partition key (user_id) and a range condition on the sort key (timestamp) is the most efficient and correct approach to get all items for a user within a date range.

Answer

BatchGetItem with multiple keys

Answer

GetItem with both partition and sort key

Answer

Scan with FilterExpression

Question 3

A data engineer needs to store streaming data from IoT devices for real-time analytics. The data has a fixed schema and requires low-latency queries. Which AWS service should be used?

Accepted Answer

Amazon Timestream. Amazon Timestream is a time-series database purpose-built for IoT and operational applications that generate large volumes of time-stamped data. It automatically manages data retention and storage tiers (memory and magnetic) to provide fast query performance for recent data and cost-effective storage for historical data, making it ideal for real-time analytics on streaming IoT data with a fixed schema.

Answer

Amazon DynamoDB

Answer

Amazon Redshift

Answer

Amazon S3

Question 4

A data engineer is designing a multi-region disaster recovery solution for Amazon RDS for PostgreSQL. The primary region must have a standby in a different Availability Zone, and the secondary region must have a readable replica that can be promoted in case of failure. Which configuration meets these requirements?

Accepted Answer

Enable Multi-AZ in the primary region and create a cross-region read replica. It meets both requirements: Multi-AZ in the primary region provides a synchronous standby in a different Availability Zone for high availability, and a cross-region read replica in the secondary region provides an asynchronous, readable copy that can be promoted to a standalone primary during a regional failure. This combination ensures both intra-region fault tolerance and inter-region disaster recovery.

Answer

Use a single-AZ primary and enable automatic backups

Answer

Use a single-AZ primary and create a cross-region read replica

Answer

Enable Multi-AZ in both primary and secondary regions

Question 5

A company stores log files in Amazon S3. They want to automatically move logs older than 90 days to S3 Glacier Deep Archive to reduce costs. Which S3 feature should be used?

Accepted Answer

S3 Lifecycle configuration. S3 Lifecycle configuration allows you to define rules that automatically transition objects to colder storage classes, such as S3 Glacier Deep Archive, based on age. By setting a rule to move objects older than 90 days to S3 Glacier Deep Archive, you reduce storage costs without manual intervention. This is the correct feature for automating tier-based data lifecycle management.

Answer

S3 Intelligent-Tiering

Answer

S3 Replication

Answer

S3 Object Lock

Question 6

Which TWO actions can help optimize Amazon S3 storage costs for a data lake? (Choose two.)

Accepted Answer

Use S3 Intelligent-Tiering for unpredictable access patterns. Option B is correct because S3 Intelligent-Tiering automatically moves objects between frequent and infrequent access tiers based on changing access patterns, eliminating retrieval fees and avoiding the cost of manual tiering when access is unpredictable. Option E is correct because S3 Lifecycle policies can transition aging objects to lower-cost storage classes such as S3 Glacier Instant Retrieval, Glacier Flexible Retrieval, or Glacier Deep Archive, directly reducing storage costs for cold data. Option A is not a cost optimization for a data lake because S3 Replication to another region incurs additional storage, request, and inter-region data transfer charges. Option C reduces data scanned and compute cost for querying, but it does not lower S3 storage costs. Option D speeds up uploads over long distances via AWS edge locations but adds a premium charge, so it increases rather than optimizes storage cost.

Answer

Enable S3 Replication to another region

Answer

Use S3 Select to retrieve only needed data

Answer

Enable S3 Transfer Acceleration

Question 7

Which TWO statements about Amazon Redshift data distribution are correct? (Choose two.)

Accepted Answer

KEY distribution places rows with the same distribution key on the same slice. In Amazon Redshift, KEY distribution places all rows with the same distribution key value on the same slice (compute node segment). This ensures that join operations on the distribution key are collocated, reducing data movement across the network and improving query performance.

Answer

DISTSTYLE is a distribution style option

Answer

AUTO distribution always chooses EVEN

Answer

ALL distribution distributes data across all slices

Question 8

Refer to the exhibit. A data engineer ran the CLI command to check the configuration of an RDS instance named 'mydb'. Which statement accurately describes the current configuration?

Accepted Answer

The database is a Single-AZ deployment and is not a read replica. The CLI command output shows 'DBInstanceStatus: available' and 'MultiAZ: False', indicating the database is running as a Single-AZ deployment. Additionally, the absence of a 'SourceDBInstanceIdentifier' field confirms it is not a read replica. The 'ReadReplicaSourceDBInstanceIdentifier' is not present, which would be required if it were a read replica.

Answer

The database is in the 'stopped' state

Answer

The database is a read replica of another instance

Answer

The database is a Multi-AZ deployment

Question 9

A company is using Amazon S3 to store critical data and needs to ensure that objects are automatically transitioned to S3 Glacier Deep Archive after 180 days to reduce costs. Which S3 lifecycle action should be configured?

Accepted Answer

Transition. The S3 lifecycle 'Transition' action is specifically designed to move objects between storage classes after a specified number of days. To reduce costs by moving objects to S3 Glacier Deep Archive after 180 days, you configure a lifecycle rule with a Transition action that targets the 'DEEP_ARCHIVE' storage class at the 180-day mark.

Answer

Expiration

Answer

AbortIncompleteMultipartUpload

Answer

NoncurrentVersionTransition

Question 10

A company is using Amazon DynamoDB for a gaming application. They want to store player session data that expires after 24 hours. Which DynamoDB feature should be used?

Accepted Answer

Time to Live (TTL). Amazon DynamoDB Time to Live (TTL) allows you to define a per-item timestamp attribute that automatically deletes items after a specified duration. For the gaming session data that must expire after 24 hours, you can set the TTL attribute to the current time plus 24 hours, and DynamoDB will asynchronously delete expired items without any additional cost or write operations.

Answer

DynamoDB Streams

Answer

Global Tables

Answer

Point-in-Time Recovery

Question 11

A company has an Amazon Redshift cluster that stores petabytes of data. Queries are experiencing high disk usage due to large intermediate results. The data engineer needs to improve query performance without adding more nodes. Which action should the engineer take?

Accepted Answer

Set appropriate distribution keys to minimize data movement.. In Amazon Redshift, distribution keys determine how table rows are spread across compute nodes. When joined tables share the same distribution key (or use ALL distribution for small dimension tables), matching rows are co-located on the same node slice, eliminating the broadcast or redistribution steps that spill large intermediate result sets to disk. Since the question specifies high disk usage from large intermediate results and prohibits adding nodes, fixing distribution keys directly reduces the data movement that generates those spills.

Answer

Configure workload management (WLM) queues to limit concurrency.

Answer

Apply column compression encoding to reduce data size.

Answer

Define sort keys on all columns used in WHERE clauses.

Question 12

A company is using Amazon S3 to store sensitive data. They need to ensure that all objects are encrypted at rest. Which combination of actions should be taken? (Choose TWO.)

Accepted Answer

Use a bucket policy to deny PutObject requests that do not include the x-amz-server-side-encryption header.. A bucket policy that denies PutObject requests lacking the `x-amz-server-side-encryption` header enforces encryption at the time of upload, ensuring that any object written without explicit encryption headers is rejected. Option E is correct because enabling default encryption on the S3 bucket automatically applies server-side encryption (SSE-S3 or SSE-KMS) to any object uploaded without specifying encryption headers, providing a fallback that covers all objects. Together, these actions ensure that every object stored in the bucket is encrypted at rest, either by explicit client request or by default bucket settings.

Answer

Enable S3 Versioning on the bucket.

Answer

Enable MFA Delete on the bucket.

Answer

Configure S3 Access Points with network policies.

Question 13

A company uses Amazon Redshift for analytics. They notice that some queries are slow due to data redistribution. The data engineer wants to minimize data movement across nodes. Which table design strategy should be used? (Choose TWO.)

Accepted Answer

Choose a distribution key that matches the join key for large tables.. Option C is correct because choosing a distribution key that matches the join key on large tables colocates matching rows on the same compute node slice, so joins between those tables can be performed locally without broadcasting or redistributing data across nodes. Option E is correct because using distribution style ALL replicates small dimension tables to every node, eliminating the need to redistribute the dimension during joins with large fact tables and thereby minimizing cross-node data movement. Option A is not ideal here because AUTO lets Redshift decide and may still choose EVEN or KEY distribution that results in redistribution for some workloads, rather than guaranteeing join-key alignment. Option B addresses sort keys, which optimize range-restricted scans and merge joins but do not control how rows are distributed across nodes, so they do not directly reduce redistribution. Option D is incorrect because EVEN distribution spreads rows round-robin regardless of join keys, which typically forces data redistribution during joins and can worsen the problem.

Answer

Set the distribution style to AUTO for all tables.

Answer

Define compound sort keys on frequently filtered columns.

Answer

Use EVEN distribution for all tables.

Question 14

A company is using Amazon S3 for data lake storage. They need to query the data directly using SQL without loading it into a database. Which AWS service should be used?

Accepted Answer

Amazon Athena. Amazon Athena is the correct choice because it is a serverless, interactive query service that allows you to analyze data directly in Amazon S3 using standard SQL, without needing to load or transform the data into a database. Athena uses Presto under the hood and supports querying structured, semi-structured, and unstructured data formats (e.g., CSV, JSON, Parquet, ORC) stored in S3, making it ideal for ad-hoc SQL queries on a data lake.

Answer

Amazon Redshift Spectrum

Answer

Amazon EMR

Answer

AWS Glue

Question 15

A data engineer sees this AWS Glue table definition in the Data Catalog. The engineer wants to query this table with Amazon Athena, but the query returns zero rows. What is the MOST likely cause?

Accepted Answer

The data files are not in the specified S3 location.. The most likely cause is that the data files are not in the specified S3 location. When an AWS Glue table is defined in the Data Catalog, Athena reads the table's metadata (including the S3 location) and then attempts to read the underlying data files from that exact path. If the files are missing, misnamed, or in a different prefix, Athena returns zero rows because there is no data to scan. This is a common misconfiguration when the S3 path in the table definition does not match the actual data storage.

Answer

The SerDe library is incorrect for CSV files.

Answer

The table format CSV is not supported by Athena.

Answer

Athena cannot read tables from the Glue Data Catalog.