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

Question 1

A data engineer is building a data lake on Amazon S3 and must choose the optimal file format for a dataset that is queried by Amazon Athena. The queries typically select a few columns from wide tables containing hundreds of columns, and the data volume is in terabytes. The engineer wants to minimize query scan costs and improve performance. Which file format should the engineer use?

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

Accepted Answer

Apache Parquet. Apache Parquet is a columnar storage format that allows Amazon Athena to read only the columns needed for a query, significantly reducing the amount of data scanned. For wide tables where queries access a small subset of columns, this column pruning leads to lower costs and faster performance. Parquet also offers efficient compression, further reducing storage and scan volume, making it the best choice for this scenario.

Answer

Apache Avro

Answer

CSV

Answer

JSON

Question 2

A data engineer is managing an Amazon Redshift cluster that experiences performance degradation during peak query loads. The cluster uses a dc2.large node type with 2 nodes. Analysis shows that the workload involves frequent large table scans and complex joins. The engineer wants to improve query performance without changing the overall data volume. Which action should the engineer take?

Accepted Answer

Change the distribution style of large tables to KEY on the join columns and enable sort keys on frequently filtered columns.. Proper distribution and sort keys are fundamental Redshift tuning techniques. Distributing large tables on join keys collocates data, minimizing network traffic during joins. Sort keys on filter columns allow the query engine to skip blocks. These changes directly address the performance bottlenecks without adding hardware, making them the most effective and cost-efficient solution.

Answer

Increase the number of nodes in the cluster by resizing to a larger node type with more memory.

Answer

Enable concurrency scaling to automatically add transient clusters during peak loads.

Answer

Enable short query acceleration (SQA) to prioritize short-running queries.

Question 3

A company uses Amazon RDS for PostgreSQL to store customer data. The data engineer needs to ensure that the database can be restored to any point in time within the last 35 days. The engineer also wants to minimize the impact on the production database during backups. What should the engineer do?

Accepted Answer

Enable automated backups and set the backup retention period to 35 days.. Amazon RDS automated backups provide point-in-time recovery to any second within the retention period, which can be set up to 35 days. They are managed by RDS, require no manual intervention, and have minimal performance impact. Manual snapshots, Multi-AZ, and read replicas do not offer the same continuous PITR capability.

Answer

Create a manual snapshot every day and retain them for 35 days.

Answer

Enable Multi-AZ deployment and rely on the standby replica for backups.

Answer

Configure a read replica and take snapshots from the read replica.

Question 4

A data engineer manages an Amazon Redshift provisioned cluster that serves a nightly ELT workload. The cluster's largest fact table is loaded with new rows each night, and queries frequently filter on a date column and join to a customer dimension. The engineer wants to improve query performance and reduce the time spent vacuuming. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Set the customer join column as the distribution key so matching rows of the fact and dimension tables co-locate on the same slice.. Performance and vacuum efficiency in Redshift are driven primarily by table design. Sorting on the frequently filtered date column enables zone-map pruning and makes VACUUM's work easier, while distributing the fact table and customer dimension on the join column enables collocated joins that avoid network-heavy shuffles. Together they reduce scanned blocks and join cost without adding hardware.

Answer

Enable automatic compression on all columns and disable the sort key to reduce storage footprint.

Answer

Convert the fact table to a view over the raw staging table to avoid storing duplicate data.

Answer

Increase the cluster's number of nodes without changing the table design or sort keys.

Question 5

A data engineer is using Amazon Redshift and needs to improve the performance of complex queries that join large tables. The engineer has already set the distribution style to KEY on the join columns. What additional step should the engineer take to optimize the join performance?

Accepted Answer

Set the sort key on the columns used in the join and in the WHERE clause.. Sort keys on join and filter columns allow Redshift to perform merge joins and skip unnecessary data blocks, reducing I/O and data movement. This optimizes join performance without adding resources. The other options either scale resources or change distribution in ways that are not optimal for large tables.

Answer

Change the distribution style to ALL for all large tables.

Answer

Enable concurrency scaling to handle concurrent queries.

Answer

Increase the number of nodes in the Redshift cluster to add more compute resources.

Question 6

A data engineer is designing a data lake on Amazon S3 and needs to ensure that data is encrypted at rest. The company requires that encryption keys be managed by AWS and automatically rotated annually. The engineer also wants to audit key usage. Which S3 encryption option should the engineer choose?

Accepted Answer

Server-side encryption with AWS KMS managed keys (SSE-KMS).. SSE-KMS uses AWS KMS to manage encryption keys, supports automatic annual key rotation, and logs key usage in AWS CloudTrail. This provides both the required encryption and auditability. Other options either do not support AWS-managed rotation or lack auditing capabilities.

Answer

Server-side encryption with Amazon S3 managed keys (SSE-S3).

Answer

Server-side encryption with customer-provided keys (SSE-C).

Answer

Client-side encryption with a custom key management solution.

Question 7

A data engineer needs to store large volumes of semi-structured JSON data in Amazon S3 and query it using Amazon Athena. The data is generated continuously and appended to S3 in small files. The engineer wants to optimize query performance and reduce costs. Which action should the engineer take?

Accepted Answer

Convert the JSON data to Apache Parquet and partition it by date.. Converting JSON to Parquet and partitioning by date leverages columnar storage and partition pruning, which are key optimizations for Athena. Parquet reduces the amount of data scanned by reading only required columns, while partitioning allows Athena to skip irrelevant partitions based on query filters. Together, they minimize cost and improve performance for large-scale semi-structured data.

Answer

Enable S3 Transfer Acceleration for faster data uploads.

Answer

Use Amazon S3 Select to query the JSON data directly.

Answer

Store the JSON data in a single large file per day.

Question 8

A data engineer is managing an Amazon DynamoDB table that stores user session data. The table has a partition key of user_id and a sort key of session_start_time. The engineer needs to retrieve all sessions for a specific user that started within the last 30 days. Which operation should the engineer use to achieve the lowest latency?

Accepted Answer

Use the Query operation with the partition key set to the user_id and a condition on the sort key to filter sessions from the last 30 days.. The Query operation is designed to retrieve multiple items with the same partition key and can filter on the sort key. Since the table's schema matches the access pattern, querying the base table with a sort key condition yields the lowest latency and consumes minimal read capacity. Other operations either scan the entire table or are not suited for range retrievals.

Answer

Use the Scan operation with a filter expression on user_id and session_start_time.

Answer

Use the GetItem operation with the user_id and a session_start_time range.

Answer

Create a global secondary index (GSI) on user_id and session_start_time, then query the index.

Question 9

A data engineer is using Amazon Redshift and needs to load data from Amazon S3 into a Redshift table. The data is in Parquet format and is partitioned by date in S3. The engineer wants to load only the data for the last 7 days to reduce load time and cost. Which Redshift command should the engineer use?

Accepted Answer

COPY command with a manifest file that lists only the files for the last 7 days.. The COPY command with a manifest file allows precise selection of files to load, enabling the engineer to load only the Parquet files for the last 7 days. This minimizes data transfer and load time. Other options either do not exist (PARTITION clause in COPY) or are for unloading data (UNLOAD) or are less efficient for bulk loading (INSERT from Spectrum).

Answer

UNLOAD command with the PARTITION BY clause.

Answer

INSERT INTO ... SELECT FROM spectrum ... WHERE date >= ...

Answer

COPY command with the PARTITION clause.

Question 10

A data engineer is designing a data store for a real-time analytics application that requires low-latency reads and writes at scale. The data model includes time-series data with high ingest rates and queries that aggregate data over sliding time windows. The engineer needs a fully managed AWS service that supports automatic scaling and can handle millions of writes per second. Which service should the engineer choose?

Accepted Answer

Amazon DynamoDB. Amazon DynamoDB is a fully managed NoSQL database that provides low-latency performance at any scale, with automatic scaling and support for high-throughput workloads. It can handle millions of writes per second and is ideal for time-series data due to features like TTL and on-demand capacity. Its ability to scale horizontally without downtime makes it the best choice for real-time analytics applications requiring low-latency reads and writes.

Answer

Amazon S3

Answer

Amazon Redshift

Answer

Amazon RDS for PostgreSQL

Question 11

A data engineer manages an Amazon S3 data lake with millions of small JSON files ingested continuously. Amazon Athena queries over this data are slow and expensive because each query scans many small objects. The engineer wants to improve query performance and reduce cost without changing the data content. Which solution should the engineer implement?

Accepted Answer

Use AWS Glue ETL to compact the small files into larger Parquet files partitioned by common query filters.. The scenario describes slow and costly Athena queries caused by many small JSON files. Compacting into larger Parquet files and partitioning by common filters reduces the number of objects scanned and leverages columnar storage to scan less data. This directly improves performance and lowers cost without changing data content, making it the correct solution.

Answer

Increase the Athena query result reuse cache TTL to 7 days.

Answer

Convert the S3 bucket to S3 Intelligent-Tiering to improve read throughput.

Answer

Enable S3 Transfer Acceleration on the bucket to speed up Athena query reads.

Question 12

A data engineer is designing a data lake on Amazon S3 for a retail company. The company ingests point-of-sale data as small JSON files every few minutes, totaling about 5 GB per day. Analysts query the data with Amazon Athena, and costs are rising due to many small files and full scans. The engineer wants to reduce Athena query costs and improve performance while keeping the data in S3. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Run an AWS Glue ETL job or AWS Glue compaction to merge small files into larger files of 128 MB or more.. Athena cost and performance depend on bytes scanned and the number of files read. Converting JSON to partitioned Parquet enables column pruning and partition pruning, while compacting small files into larger ones reduces request overhead and metadata processing. These two actions together cut scanned bytes and improve query speed without leaving S3.

Answer

Increase the Athena workgroup data usage control limit to allow larger scans.

Answer

Enable Amazon S3 Intelligent-Tiering on the bucket to automatically move data to lower-cost storage classes.

Answer

Enable S3 Transfer Acceleration on the bucket to speed up Athena queries.

Question 13

A company uses Amazon DynamoDB to store user session data. The table has a partition key of UserID and a sort key of SessionStartTime. The application frequently queries for all sessions of a specific user within a date range. The table is provisioned with 1000 RCUs and 1000 WCUs. During peak hours, the application experiences throttling on read requests. Which action should a data engineer take to resolve the throttling with minimal changes?

Accepted Answer

Increase the provisioned read capacity units (RCUs) for the table.. Read throttling occurs when the consumed read capacity exceeds the provisioned RCUs. Increasing RCUs is the most direct and minimal solution, as it provides more read capacity without requiring application changes or architectural modifications. It addresses the root cause of the throttling.

Answer

Create a global secondary index (GSI) on the SessionStartTime attribute.

Answer

Enable DynamoDB Accelerator (DAX) for the table.

Answer

Change the table's capacity mode to on-demand.

Question 14

A data engineer is deploying an Amazon Redshift cluster that must be accessible only from within a private VPC and must not have a public IP address. The cluster will be queried by an Amazon EMR cluster in the same VPC and by on-premises BI tools over a VPN connection. Which configuration should the engineer choose?

Accepted Answer

Launch the Redshift cluster with the publicly accessible setting disabled and attach it to a private subnet group.. To ensure a Redshift cluster has no public IP and is reachable only privately, the engineer must disable the publicly accessible setting and use a private subnet group. This prevents internet exposure while allowing access from within the VPC and over VPN. The other options either enable public access or do not explicitly disable it, failing the requirement.

Answer

Launch the Redshift cluster in a private subnet group and configure an AWS Site-to-Site VPN connection to the VPC.

Answer

Launch the Redshift cluster with the publicly accessible setting enabled and attach it to a public subnet group.

Answer

Launch the Redshift cluster with the publicly accessible setting disabled and attach it to a public subnet group.

Question 15

A data engineer is designing an Amazon DynamoDB table for an order-processing application. The table uses a partition key of order_id and a sort key of order_date. The application needs to retrieve all orders for a specific customer within a date range, and the queries must be efficient at scale. The engineer must choose a design that supports these access patterns without full table scans. What should the engineer do?

Accepted Answer

Create a global secondary index with customer_id as the partition key and order_date as the sort key.. The application needs to query orders by customer and date range efficiently. A global secondary index can use a different partition key and sort key from the base table, so customer_id as the partition key and order_date as the sort key provides the required access path. Local secondary indexes must share the base table partition key, scans are inefficient, and stream-based copies do not replace a queryable index.

Answer

Enable DynamoDB Streams and use a Lambda function to write customer-based query results to a separate table.

Answer

Create a local secondary index with customer_id as the partition key and order_date as the sort key.

Answer

Use a Scan operation with a FilterExpression on customer_id and order_date.