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

Question 1

A data engineer is using AWS Glue Data Catalog to catalog tables that will be queried by Amazon Athena. The source data is stored in Amazon S3 in Parquet format, partitioned by year, month, and day. The engineer notices that Athena queries scan much more data than expected and are slow. The engineer has already run a Glue crawler, but the crawler created a single partition for all data. What should the engineer do to improve query performance and reduce scanned data?

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

Accepted Answer

Run MSCK REPAIR TABLE in Athena to add the missing partitions to the Data Catalog.. Athena relies on partition metadata in the AWS Glue Data Catalog to prune partitions and reduce scanned data. When a crawler registers only one partition for Hive-style S3 prefixes, the table cannot take advantage of year/month/day pruning. Running MSCK REPAIR TABLE adds the missing partition entries based on the S3 path structure. Changing file format or crawler capacity does not fix missing partition metadata.

Answer

Increase the number of DPUs for the Glue crawler and rerun it.

Answer

Create a new table in Athena with a CREATE TABLE AS SELECT statement that includes the partition columns in the SELECT clause.

Answer

Convert the Parquet files to CSV to allow Athena to read them faster.

Question 2

A data engineer is managing an Amazon DynamoDB table that stores IoT sensor data. The table has a partition key of sensor_id and a sort key of timestamp. The workload includes frequent queries for the latest reading of a specific sensor and occasional range queries for a sensor over a time period. The engineer notices that some partitions are hot, causing throttling. Which design change should the engineer implement to improve performance and avoid throttling?

Accepted Answer

Enable DynamoDB adaptive capacity to automatically redistribute throughput.. DynamoDB adaptive capacity automatically redistributes throughput to hot partitions, allowing the table to handle uneven access patterns without manual intervention. It isolates frequently accessed items to prevent throttling, making it the best solution for this scenario without altering the key schema or increasing capacity.

Answer

Enable DynamoDB Accelerator (DAX) to cache frequently accessed items.

Answer

Use a composite partition key by combining sensor_id and a random suffix.

Answer

Increase the provisioned read and write capacity units for the table.

Question 3

A data engineer is designing a data lake on Amazon S3. The engineer needs to optimize query performance for a large dataset that is frequently queried by Amazon Athena. The data is stored in Parquet format and partitioned by date. The engineer notices that queries filtering on a high-cardinality column, such as customer_id, are slow because they scan all partitions. What should the engineer do to improve query performance?

Accepted Answer

Use Amazon Athena's ability to query Parquet with predicate pushdown and consider sorting the data by customer_id within each partition.. For Parquet data in Athena, predicate pushdown can skip irrelevant row groups if the data is sorted or clustered on the filter column. Partitioning by date is already done, but filtering on customer_id requires scanning all partitions. Sorting the data by customer_id within each partition allows Athena to skip more row groups, reducing data scanned and improving query speed. This is a best practice for optimizing Athena queries.

Answer

Convert the data to CSV format to reduce storage size.

Answer

Use AWS Glue to convert the data to ORC format and enable predicate pushdown.

Answer

Partition the data by customer_id in addition to date.

Question 4

A data engineer manages an AWS Glue Data Catalog with a table backed by Parquet files in Amazon S3. The engineer must improve Athena query performance and reduce scan costs for queries that frequently filter on a single integer column named event_id. The Parquet files are currently written without any column statistics. Which action should the engineer take to meet these requirements with the LEAST operational effort?

Accepted Answer

Configure AWS Glue crawlers to compute and store column-level statistics in the Data Catalog.. AWS Glue crawlers can populate column-level statistics in the Data Catalog for Parquet data, enabling Athena to use predicate pushdown and skip irrelevant row groups when filtering on event_id. This directly reduces bytes scanned and query cost with minimal operational overhead. Other actions either do not collect statistics, introduce excessive partitions, or degrade the data format.

Answer

Create a new AWS Glue table with the same location and partition on event_id.

Answer

Run MSCK REPAIR TABLE on the table to refresh partition metadata.

Answer

Convert the Parquet files to CSV format and register them in the Data Catalog.

Question 5

A data engineer manages an Amazon RDS for PostgreSQL database that contains a large table with historical order data. To reduce storage costs, the engineer wants to move data older than one year to an Amazon S3 bucket, while keeping the most recent data in RDS for fast access. The engineer needs a solution that allows querying the archived data directly from S3 using standard SQL, without loading it back into RDS. Which approach should the engineer take?

Accepted Answer

Use Amazon RDS to export the historical data to Amazon S3 in Parquet format via a manual pg_dump and upload, then query the data with Amazon Athena.. The engineer needs to offload historical data to S3 and query it with SQL without reimporting. Exporting to Parquet and using Athena provides a cost-effective, serverless query solution. This avoids additional database infrastructure and keeps only recent data in RDS, reducing storage costs while maintaining query access to archived data.

Answer

Use AWS Glue to crawl the RDS database, extract data older than one year, write it to Amazon S3 in Parquet, and then use Amazon Redshift Spectrum to query the S3 data.

Answer

Export the historical data from RDS to Amazon S3 using the RDS native export to S3 feature, then query the exported data directly with Amazon Athena.

Answer

Use AWS Database Migration Service (AWS DMS) to continuously replicate the entire RDS database to Amazon S3 in Parquet format, then query the S3 data with Amazon Athena.

Question 6

A company uses Amazon DynamoDB to store user session data. The table has a partition key of UserID and no sort key. During peak hours, the application experiences throttling on write operations. The data engineer needs to reduce throttling without changing the application's write pattern. The table currently uses on-demand capacity mode. Which action should the engineer take?

Accepted Answer

Add a sort key to the table to distribute writes more evenly across partitions.. In DynamoDB, throttling often occurs when a single partition receives too much traffic, creating a hot partition. With a partition key of UserID and no sort key, all writes for a user go to the same partition. Adding a sort key creates a composite key, allowing multiple items per user to be distributed across partitions based on the sort key value, thus spreading the write load and reducing throttling.

Answer

Enable DynamoDB Accelerator (DAX) for the table.

Answer

Switch the table to provisioned capacity mode with auto scaling.

Answer

Increase the write capacity units (WCUs) manually by switching to provisioned mode.

Question 7

A data engineer is using AWS Glue to catalog data stored in Amazon S3. The data is partitioned by year, month, and day. The engineer notices that Athena queries are slow because they scan all partitions. The engineer wants to improve query performance by ensuring that Athena only scans the relevant partitions. Which action should the engineer take?

Accepted Answer

Add partition keys to the AWS Glue Data Catalog table.. To enable partition pruning in Athena, the table in the AWS Glue Data Catalog must have partition keys defined. Once partitions are registered, Athena can use the partition metadata to scan only the relevant S3 prefixes. Converting to Parquet, increasing DPUs, or enabling Transfer Acceleration do not address the root cause of scanning all partitions.

Answer

Convert the data to Parquet format.

Answer

Enable Amazon S3 Transfer Acceleration.

Answer

Increase the number of DPUs for the AWS Glue job.

Question 8

A data engineer is configuring an Amazon Redshift cluster to handle a mixed workload of complex analytical queries and frequent small insert operations from a dashboard. The engineer wants to improve query performance and reduce contention. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Enable concurrency scaling to automatically add transient clusters for bursts of read queries.. For a mixed workload with complex analytical queries and frequent small inserts, reducing contention requires isolating workloads and scaling read capacity. Implementing WLM with separate queues prevents resource competition by allocating memory and concurrency per workload type. Enabling concurrency scaling adds transient clusters for read bursts, allowing analytical queries to run without impacting the main cluster. Together, these actions improve performance and reduce contention.

Answer

Use Elastic Resize to add more nodes during peak hours and remove them during off-peak.

Answer

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

Answer

Increase the number of leader nodes in the cluster to distribute query parsing.

Question 9

A company uses Amazon Redshift for its data warehouse. The cluster is experiencing performance degradation during peak hours due to heavy write operations from an ETL process that loads data into a large fact table. The ETL process uses COPY commands to load data from Amazon S3. The fact table has a distribution key on `customer_id` and a sort key on `order_date`. The engineer needs to improve the load performance without affecting query performance. Which action should the engineer take?

Accepted Answer

Use the `COMPUPDATE OFF` option in the COPY command to skip compression analysis during loads.. The `COMPUPDATE OFF` option in the COPY command disables automatic compression analysis, which is a resource-intensive step during loads. Since the table already has compression encodings, skipping this step speeds up the load process without compromising query performance. Other options either affect query performance negatively or do not address load speed. This is a targeted optimization for ETL loads.

Answer

Use the `MAXERROR` option to allow a certain number of errors during loads.

Answer

Use the `STATUPDATE OFF` option in the COPY command to skip statistics update during loads.

Answer

Change the distribution key to `order_date` to improve load parallelism.

Question 10

A data engineer is using AWS Glue to catalog data stored in Amazon S3. The engineer needs to ensure that the AWS Glue Data Catalog automatically updates when new partitions are added to the S3 bucket. Which method should the engineer use to achieve this with minimal operational overhead?

Accepted Answer

Use AWS Glue partition projection to automatically infer partitions based on a predefined pattern.. AWS Glue partition projection automatically computes partitions based on a defined pattern, so the Data Catalog does not need to be updated when new partitions are added. This eliminates the need for crawlers or manual partition management, reducing operational overhead and ensuring immediate queryability. It is the most efficient method for predictable partition structures.

Answer

Enable the AWS Glue Data Catalog partition index and use AWS Glue crawlers with incremental crawling.

Answer

Use AWS Lambda to trigger an AWS Glue crawler whenever an S3 event notification is received.

Answer

Run an AWS Glue crawler on a schedule to detect new partitions.

Question 11

A data engineer maintains an Amazon S3 data lake with millions of small JSON objects written by a streaming ingestion job. Athena queries against these objects are slow, and the team wants to improve query performance and lower cost without changing the source data's content. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Convert the small JSON objects to larger Apache Parquet files partitioned by commonly filtered columns.. Athena cost and latency scale with bytes scanned and the number of objects read. Converting small JSON objects to fewer, larger Parquet files makes reads columnar and cuts per-object overhead, while partitioning on frequently filtered columns enables prefix pruning. An AWS Glue job can perform that compaction and format conversion at scale, and combining both actions yields the largest improvement without changing the data's content.

Answer

Move the objects into an Amazon S3 Glacier Instant Retrieval storage class.

Answer

Enable Amazon S3 Versioning on the data lake bucket.

Answer

Configure the Athena workgroup to enforce a per-query data usage control limit.

Question 12

A data engineer is designing a data lake on Amazon S3. The data includes raw logs in JSON format and processed data in Parquet format. The engineer needs to ensure that the processed data is stored efficiently and can be queried by Amazon Athena with minimal cost. The raw logs are infrequently accessed after 30 days. Which combination of S3 lifecycle policies and storage classes should the engineer use to optimize cost and performance?

Accepted Answer

Store raw logs in S3 Standard and transition to S3 Glacier Instant Retrieval after 30 days. Store processed data in S3 Standard.. S3 Glacier Instant Retrieval offers low-cost storage for infrequently accessed data with immediate retrieval, making it suitable for raw logs. Processed data in S3 Standard ensures fast access for Athena queries, balancing cost and performance. This combination avoids retrieval delays and excessive costs.

Answer

Store raw logs in S3 Standard and transition to S3 One Zone-IA after 30 days. Store processed data in S3 Standard.

Answer

Store raw logs in S3 Standard and transition to S3 Glacier Deep Archive after 30 days. Store processed data in S3 Standard-Infrequent Access (S3 Standard-IA).

Answer

Store raw logs in S3 Intelligent-Tiering and store processed data in S3 Glacier Flexible Retrieval.

Question 13

A data engineer is managing an Amazon S3 data lake that stores raw data in Parquet format. The engineer needs to optimize query performance and reduce storage costs for data that is accessed frequently in the first 30 days and rarely thereafter. The engineer plans to use S3 Lifecycle policies and Amazon Athena for querying. Which TWO actions should the engineer take to meet these requirements? (Choose two.)

Accepted Answer

Convert the raw data to a columnar format like Parquet if it is not already, and partition the data by commonly filtered columns.. To optimize query performance and reduce storage costs, the engineer should ensure data is in a columnar format and partitioned for efficient Athena queries. Additionally, a lifecycle policy to transition data to a lower-cost storage class like S3 Glacier Instant Retrieval after 30 days addresses the cost reduction for rarely accessed data. These two actions together meet the performance and cost requirements.

Answer

Enable S3 Intelligent-Tiering to automatically move data to the most cost-effective access tier based on access patterns.

Answer

Use Amazon S3 Select to query the data directly from S3 without loading it into Athena.

Answer

Enable versioning on the S3 bucket to keep multiple versions of objects and reduce storage costs.

Question 14

A financial services company stores transactional data in Amazon S3 in Parquet format partitioned by date. Analysts frequently query only the last 7 days of data using Amazon Athena. To improve query performance and reduce data scanned, a data engineer needs to optimize the table definition in the AWS Glue Data Catalog. Which action should the engineer take?

Accepted Answer

Ensure that queries include a filter on the partition key (date).. Partition pruning is a key optimization for Athena queries on partitioned data. By ensuring that queries filter on the partition key, only the relevant partitions are scanned, which reduces the amount of data read and improves performance. This directly addresses the need to query only recent data efficiently.

Answer

Increase the number of partitions by adding a secondary partition key.

Answer

Add a partition projection configuration to the table properties.

Answer

Convert the data to ORC format to reduce storage size.

Question 15

A data engineer manages an Amazon S3 data lake where a daily batch job appends new objects to a prefix. The engineer must configure lifecycle rules to minimize storage cost for objects older than 90 days that are rarely accessed but must remain immediately retrievable for at least 7 years. Which storage class transition should be configured?

Accepted Answer

Transition objects to S3 Glacier Instant Retrieval after 90 days.. The requirement is for rarely accessed data that must remain immediately retrievable for 7 years. S3 Glacier Instant Retrieval provides millisecond access with lower storage cost than S3 Standard-IA, and its 90-day minimum duration aligns with the 90-day transition. Other classes either have retrieval delays or higher costs for this access pattern.

Answer

Transition objects to S3 Standard-Infrequent Access (S3 Standard-IA) after 30 days.

Answer

Transition objects to S3 One Zone-IA after 30 days.

Answer

Transition objects to S3 Glacier Flexible Retrieval after 90 days.