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

Question 1

A data engineer is designing a data warehouse on Amazon Redshift. The workload includes many ad-hoc queries that filter on a high-cardinality column, such as customer_id, and join large dimension tables. The engineer wants to improve query performance by choosing an appropriate distribution style and sort key. Which combination should the engineer use?

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

Accepted Answer

Use KEY distribution on customer_id and set the sort key to customer_id.. For a workload that frequently filters and joins on a high-cardinality column like customer_id, distributing the fact table by that key colocates matching rows and minimizes network traffic during joins. Using the same column as the sort key further speeds up range scans and merge joins. Together, they reduce data movement and I/O, improving ad-hoc query performance.

Answer

Use AUTO distribution and set the sort key to the date column with a compound sort key.

Answer

Use EVEN distribution and set the sort key to the date column.

Answer

Use ALL distribution on the fact table and set the sort key to the join key of the largest dimension.

Question 2

A data engineer is building a data lake on Amazon S3. The engineer needs to catalog metadata for data stored in Parquet format and make it queryable by Amazon Athena. The data is partitioned by year, month, and day in the S3 path. Which AWS service should the engineer use to create and manage the table definitions and partitions?

Accepted Answer

AWS Glue Data Catalog. AWS Glue Data Catalog is the metadata store for Amazon Athena. It allows the data engineer to define table schemas and partitions that Athena can query directly. Using the Glue Data Catalog ensures seamless integration and simplifies data discovery and querying.

Answer

Amazon Athena Data Catalog

Answer

AWS Lake Formation

Answer

Amazon S3 Inventory

Question 3

A data engineer is using AWS Glue to catalog data stored in Amazon S3. The data is in Parquet format and partitioned by year, month, and day. The engineer needs to ensure that AWS Glue crawlers correctly identify the partitions and that Amazon Athena queries can efficiently prune partitions. Which action should the engineer take?

Accepted Answer

Store the data in a directory structure like s3://bucket/year=2023/month=01/day=01/, and run the crawler with the default settings to automatically detect partitions.. The correct action is to store data using Hive-style partition paths (e.g., year=2023/month=01/day=01/) and run the AWS Glue crawler with default settings. The crawler will automatically detect the partitions and update the Data Catalog. Athena can then use partition pruning to optimize queries. Other options either use unnecessary custom classifiers, manual partition management, or irrelevant settings.

Answer

Enable AWS Glue Data Catalog encryption and set the 'classification' property to 'parquet' in the table definition.

Answer

Configure the crawler to use a custom classifier that recognizes the partition structure, and set the table property 'partition_filtering.enabled' to true.

Answer

Manually create the table in the AWS Glue Data Catalog with partition keys, and use AWS Glue ETL jobs to add partitions as new data arrives.

Question 4

A data engineering team stores clickstream events in an Amazon S3 bucket under the prefix s3://analytics/raw/. New objects arrive continuously, and the team wants Amazon Athena queries to scan only the events for the current day without scanning the entire prefix. The events are written as JSON files partitioned by year/month/day, but queries still scan all partitions because the partition metadata is not registered. Which action should the data engineer take to enable partition pruning in Athena?

Accepted Answer

Run MSCK REPAIR TABLE on the Athena table to add the year/month/day partitions to the AWS Glue Data Catalog.. Partition pruning requires the query engine to know which partition values exist and where their data lives. Registering the year/month/day folders as partitions in the Data Catalog lets Athena build a plan that reads only the matching day, cutting scanned bytes and cost. Physical optimizations such as acceleration or columnar formats do not supply that partition metadata.

Answer

Create an Amazon CloudFront distribution in front of the S3 bucket and point Athena at the distribution.

Answer

Enable S3 Transfer Acceleration on the bucket so Athena can retrieve the daily events faster.

Answer

Convert the JSON files to Apache Parquet and rely on columnar storage alone to limit the scan to one day.

Question 5

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 workload includes frequent queries that retrieve all sessions for a user within a specific time range. The engineer notices that some queries are slow and wants to optimize the table design. Which action should the engineer take to improve query performance?

Accepted Answer

Ensure that queries use the partition key and sort key condition to retrieve data efficiently, and consider enabling adaptive capacity.. The correct action is to ensure queries use the partition key and sort key condition to retrieve data efficiently, and to consider enabling adaptive capacity. The table's key schema is already optimized for the query pattern, so leveraging it properly is key. Other options introduce unnecessary complexity, do not address the query pattern, or simply scale capacity without fixing efficiency.

Answer

Increase the provisioned read capacity units (RCUs) for the table to handle the query load.

Answer

Create a global secondary index (GSI) with session_start_time as the partition key and user_id as the sort key.

Answer

Enable DynamoDB Streams to capture changes and use AWS Lambda to maintain a separate queryable table.

Question 6

A logistics company stores shipment tracking events in an Amazon DynamoDB table. The table uses a partition key of shipment_id and a sort key of event_timestamp. Analysts frequently run queries that filter by shipment_id and a range of event_timestamp values. The data engineer must ensure these queries are efficient and consume minimal read capacity. What should the data engineer do?

Accepted Answer

Use the base table with a Query operation specifying shipment_id as the partition key and a condition on event_timestamp.. The composite primary key already matches the access pattern, so a Query with shipment_id as the partition key and a condition on event_timestamp reads only the relevant item collection. This is the most efficient and lowest-cost approach and requires no additional index or infrastructure.

Answer

Create a global secondary index on event_timestamp alone.

Answer

Run a Scan operation with a FilterExpression on shipment_id and event_timestamp.

Answer

Enable DynamoDB Streams on the table and query the stream for matching events.

Question 7

A company stores application logs in Amazon S3 and uses AWS Glue crawlers to populate the AWS Glue Data Catalog. A data engineer needs to query the logs with Amazon Athena. The logs are partitioned by year/month/day in S3, but Athena queries are scanning all partitions and returning errors about missing partitions. What should the engineer do to enable partition pruning?

Accepted Answer

Run the AWS Glue crawler with the option to update the table's partition metadata and ensure the crawler has permission to list the partition folders.. For Athena to use partition pruning, the AWS Glue Data Catalog must contain accurate partition metadata. The crawler needs permission to list the S3 partition folders and must be configured to update partitions. Once partitions are registered, Athena can prune and avoid scanning irrelevant data, resolving the errors and improving performance.

Answer

Convert the logs to Parquet format and re-crawl the data.

Answer

Increase the Athena query timeout and retry the queries.

Answer

Move the logs to a single prefix without date partitions and update the crawler.

Question 8

A data engineer manages an Amazon S3 data lake with millions of small JSON files. To improve query performance with Amazon Athena, the engineer wants to compact these files into larger Parquet files. The engineer must also minimize ongoing storage costs. Which solution should the engineer implement?

Accepted Answer

Use AWS Glue ETL jobs to read the JSON files, transform them to Parquet, and write the output to a new S3 prefix. Then, configure an S3 Lifecycle rule to expire the original JSON objects after a retention period.. Converting JSON to Parquet with AWS Glue ETL compacts files and enables columnar storage, which improves Athena query performance. Writing to a new prefix and using an S3 Lifecycle rule to expire original JSON files after a retention period reduces storage costs while preserving data integrity. This combination addresses both performance and cost requirements.

Answer

Create an AWS Lambda function that triggers on each S3 PUT event to merge JSON files into larger files.

Answer

Use Amazon Kinesis Data Firehose to stream the JSON files into Parquet format in real time.

Answer

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

Question 9

A data engineer is building an Amazon DynamoDB table that will store IoT sensor readings. Each reading has a deviceId (partition key) and a timestamp (sort key). The team wants to retrieve all readings for a device within the last 24 hours, and they also want to minimize the number of read capacity units consumed. Which access pattern should the engineer implement?

Accepted Answer

Issue a Query operation with the partition key equal to the deviceId and a key condition expression on the sort key using a BETWEEN clause for the timestamp range.. The table already has deviceId as the partition key and timestamp as the sort key, which is the ideal design for retrieving a device's readings over a time range. Querying that partition with a BETWEEN condition on the sort key reads only the matching items, minimizing consumed read capacity. Scan, parallel Scan, and a GSI on timestamp all read or return excessive data.

Answer

Create a global secondary index on timestamp and Query it with a key condition on the timestamp range.

Answer

Use a parallel Scan with multiple segments and a FilterExpression on deviceId.

Answer

Issue a Scan operation on the table with a FilterExpression on deviceId and timestamp.

Question 10

A data engineer is building a data lake on Amazon S3. The engineer needs to store structured data that will be queried by Amazon Athena. The data is currently in CSV format and is partitioned by date. The engineer wants to improve query performance and reduce the amount of data scanned. Which action should the engineer take?

Accepted Answer

Convert the data to Apache Parquet format and use AWS Glue to update the table metadata in the AWS Glue Data Catalog.. Converting data to a columnar format like Parquet significantly reduces the amount of data scanned by Athena because only the required columns are read. Updating the AWS Glue Data Catalog ensures the table metadata reflects the new format. This combination improves query performance and lowers cost.

Answer

Use Amazon Redshift Spectrum to query the CSV data directly from S3.

Answer

Compress the CSV files using gzip and update the table metadata in the AWS Glue Data Catalog.

Answer

Increase the number of partitions by adding a partition for each hour of the day.

Question 11

A data engineer is configuring an Amazon Redshift cluster for a workload that runs large nightly ELT jobs loading data from Amazon S3 and then executes complex analytical queries. The team wants to improve query performance and reduce the time the cluster spends on data loading. Which TWO configuration choices should the engineer make? (Choose two.)

Accepted Answer

Use the COPY command with a manifest file and parallelism to load data from multiple S3 objects.. Bulk loading with COPY and a manifest, combined with parallelism, uses Redshift's MPP architecture to shorten load time. Choosing an appropriate distribution key on large fact tables reduces inter-slice data movement during complex joins, improving query performance. Row-by-row inserts, restricting maintenance to the load window, and applying ALL distribution to every table all degrade performance or increase load time.

Answer

Enable automatic vacuum and analyze only during the nightly load window.

Answer

Load data using single-row INSERT statements in a loop from the application.

Answer

Set the cluster's distribution style to ALL for every table to avoid joins.

Question 12

A data engineer is designing an Amazon S3 data lake and needs to enforce schema-on-read for a dataset that is queried by Amazon Athena. The data is stored as Parquet files partitioned by year, month, and day. The engineer wants to minimize the amount of data scanned by queries that filter on a specific date range. Which approach should the engineer take?

Accepted Answer

Create an AWS Glue Data Catalog table with partition keys year, month, and day, and run MSCK REPAIR TABLE or use partition projection before querying in Athena.. Athena uses the AWS Glue Data Catalog to resolve table schemas and partition locations. When the year, month, and day columns are declared as partition keys and the partitions are either registered or projected, date filters cause Athena to read only the matching S3 prefixes. Storing everything in one prefix, converting to CSV, or moving to Redshift Spectrum does not achieve the same partition-pruning benefit.

Answer

Store all Parquet files in a single prefix and rely on Parquet column pruning to skip dates.

Answer

Convert the dataset to CSV and add a WHERE clause on the date column in every query.

Answer

Create an Amazon Redshift Spectrum external table and query it from Redshift instead of Athena.

Question 13

A data engineer manages an Amazon Redshift cluster that stores sales data in a table with a sort key on the sale_date column. The table is growing rapidly, and queries that filter by sale_date are becoming slower. The engineer notices that the table has a high percentage of unsorted rows. What should the engineer do to improve query performance with the least effort?

Accepted Answer

Run the VACUUM SORT ONLY command on the table.. VACUUM SORT ONLY sorts the rows in a table according to the defined sort key, which directly improves the performance of queries that filter on that key. It is the least intrusive vacuum operation that addresses unsorted rows. ANALYZE only updates statistics, while VACUUM FULL and VACUUM REINDEX are more resource-intensive and not required for the described issue.

Answer

Run the VACUUM REINDEX command on the table.

Answer

Run the ANALYZE command on the table.

Answer

Run the VACUUM FULL command on the table.

Question 14

A media company stores millions of thumbnail images in an Amazon S3 bucket. Analysts run ad hoc queries against the image metadata, which is kept as JSON objects in the same bucket. Query latency is unpredictable and costs are rising because Athena scans large volumes of JSON for every query. The team wants faster queries and lower scan cost while keeping the data in S3 and queryable with SQL. Which change should the data engineer make?

Accepted Answer

Use AWS Glue to crawl the metadata, convert it to Apache Parquet partitioned by date, and register the table in the Data Catalog for Athena queries.. The root cause is that Athena must read entire JSON objects and every partition for each query. Converting metadata to columnar Parquet and partitioning by date lets the engine read only needed columns and folders, cutting scanned bytes and latency. Catalog registration keeps the data in S3 and preserves SQL access, which matches all stated constraints.

Answer

Enable S3 Intelligent-Tiering on the bucket to automatically move infrequently accessed metadata objects to cheaper storage.

Answer

Move the metadata into an Amazon DynamoDB table and have analysts query it with PartiQL.

Answer

Increase the Athena workgroup data usage control limit so queries can scan more data without failing.

Question 15

A company stores application logs in Amazon S3 and needs to query them using standard SQL. The logs are in JSON format and are updated daily. The data engineering team wants a serverless solution that requires minimal management and can automatically discover the schema. Which AWS service should they use?

Accepted Answer

Amazon Athena with an AWS Glue Data Catalog table.. Amazon Athena is a serverless query service that integrates with the AWS Glue Data Catalog to automatically discover schemas and query data directly in S3. It requires no infrastructure management and supports standard SQL, making it ideal for querying JSON logs with minimal operational overhead.

Answer

Amazon EMR with Apache Hive.

Answer

Amazon RDS for PostgreSQL with the aws_s3 extension.

Answer

Amazon Redshift Spectrum.