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

Question 1

A media company stores millions of small JSON files in an Amazon S3 bucket and queries them with Amazon Athena. Analysts report that queries scan far more data than expected, and costs are rising. The data engineer confirms that the files are uncompressed, use no partitioning, and are stored as newline-delimited JSON. Which change will MOST reduce the data scanned per query?

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

Accepted Answer

Convert the files to Apache Parquet, compress with Snappy, and partition the S3 prefix by event date.. Converting to a columnar format, compressing with a splittable codec, and partitioning by a common filter column collectively minimize bytes read. Parquet enables column pruning, Snappy reduces stored size, and date partitioning enables partition pruning so only relevant prefixes are read. This combination produces the largest reduction in data scanned and therefore in Athena query cost.

Answer

Add more small files to increase parallelism across Athena workers.

Answer

Enable S3 Transfer Acceleration on the bucket.

Answer

Increase the Athena workgroup's data usage control limit.

Question 2

A financial services company stores sensitive transaction data in an Amazon S3 bucket. The security team requires that all data be encrypted at rest using AWS Key Management Service (AWS KMS) customer managed keys, and that the encryption keys be automatically rotated every year. A data engineer needs to configure the S3 bucket to meet these requirements with minimal ongoing operational effort. Which solution should the engineer implement?

Accepted Answer

Enable default encryption on the S3 bucket using SSE-KMS with a customer managed key that has automatic key rotation enabled.. To meet the requirements, the engineer should use server-side encryption with AWS KMS customer managed keys (SSE-KMS) and enable automatic key rotation on the key. This ensures data is encrypted at rest with a key the customer controls, and rotation occurs annually without manual intervention. The bucket default encryption setting ensures all new objects are encrypted automatically.

Answer

Enable default encryption on the S3 bucket using SSE-S3 (AES-256).

Answer

Use S3 client-side encryption with a customer-provided key stored in AWS Secrets Manager, and rotate the key annually using a Lambda function.

Answer

Enable default encryption on the S3 bucket using SSE-KMS with an AWS managed key (aws/s3).

Question 3

A financial analytics company stores daily transaction records in Amazon S3 as Apache Parquet files, partitioned by year/month/day. The data engineering team queries these files with Amazon Athena. To reduce query runtime and cost, they want to apply fine-grained access control and column-level filtering without changing the files. Which solution should they use?

Accepted Answer

Define an AWS Lake Formation table with column-level permissions and use Lake Formation to manage access for Athena users.. AWS Lake Formation is designed to provide granular access control for data lakes built on Amazon S3 and the AWS Glue Data Catalog. It allows administrators to grant or revoke permissions at the database, table, column, and row level. Athena respects these permissions, enabling column-level filtering without data duplication or transformation. This meets the requirement to reduce runtime and cost by limiting data access.

Answer

Create an AWS Glue Data Catalog table with partition projection and use Amazon S3 Access Points for authorization.

Answer

Enable Amazon S3 Block Public Access and use IAM policies with condition keys to filter columns.

Answer

Store the data in Amazon Redshift Spectrum and use Redshift database roles to restrict column access.

Question 4

A data engineer is building a data lake on Amazon S3 and needs to catalog metadata for a large number of CSV files stored in a nested folder structure. The engineer wants to automatically discover the schema and update the AWS Glue Data Catalog as new files are added. Which solution should the engineer use?

Accepted Answer

Create an AWS Glue crawler that points to the S3 bucket and schedule it to run periodically.. AWS Glue crawlers are designed to automatically scan data stores, infer schemas, and create or update tables in the Data Catalog. Scheduling a crawler ensures that new files are detected and the catalog stays current. This is the standard, low-effort solution for the described scenario. Other options either require manual intervention or do not provide automatic schema discovery.

Answer

Run an AWS Glue ETL job that reads the CSV files and writes to a new location.

Answer

Use AWS Lake Formation to define a data lake and register the S3 location.

Answer

Manually define tables in the AWS Glue Data Catalog using the AWS Management Console.

Question 5

A data engineer is building a data lake on Amazon S3 and needs to catalog metadata for a large number of CSV files stored in a folder structure. The engineer wants to use AWS Glue crawlers to automatically infer schemas and create tables in the AWS Glue Data Catalog. The crawler should run daily to detect new files and schema changes. Which configuration should the engineer use for the crawler?

Accepted Answer

Set the crawler's data source to the specific S3 folder containing the CSV files, configure it to create a single schema for each S3 path, and set a daily schedule.. The crawler should be pointed to the specific S3 folder to avoid scanning irrelevant data. Configuring it to create a single schema for each S3 path groups files with the same schema into one table, which is ideal for a data lake with partitioned folders. A daily schedule ensures the catalog stays up to date with new files and schema changes.

Answer

Set the crawler's data source to the specific S3 folder and disable the crawler schedule, running it manually when needed.

Answer

Set the crawler's data source to the specific S3 folder and configure it to create a separate table for each file, with a daily schedule.

Answer

Set the crawler's data source to the S3 bucket root and enable 'Update the table definition in the Data Catalog' with a schedule of daily.

Question 6

A company is using an Amazon RDS for PostgreSQL database to store application data. The data engineering team needs to run complex analytical queries that join multiple large tables. These queries are causing performance degradation on the production database. The team wants to offload the analytical workload to a separate system that can handle large-scale data processing. Which AWS service should the team use?

Accepted Answer

Amazon Redshift. Amazon Redshift is purpose-built for analytical workloads, offering columnar storage, massively parallel processing, and advanced query optimization. By moving the analytical queries to Redshift, the team can achieve faster performance without impacting the production RDS database. This separation of transactional and analytical workloads is a common best practice.

Answer

Amazon ElastiCache for Redis

Answer

Amazon RDS for MySQL

Answer

Amazon DynamoDB

Question 7

A data engineer is building an Amazon Redshift data warehouse. The cluster will ingest data from Amazon S3 using the COPY command. The engineer needs to ensure that the data is loaded in a way that maximizes query performance for future complex analytical queries. The data is currently stored as uncompressed CSV files in S3. Which action should the engineer take to optimize the load and subsequent query performance?

Accepted Answer

Use the COPY command with the COMPUPDATE ON option to automatically apply compression, and define an appropriate distribution key and sort key on the target table.. The correct approach is to use COPY with COMPUPDATE ON to automatically apply compression, and to define an appropriate distribution key and sort key. This optimizes storage, reduces I/O, and improves query performance by ensuring even data distribution and efficient data access patterns. Other options either do not address compression, distribution, or sort keys adequately, or rely on commands that do not achieve the desired optimization.

Answer

Use the COPY command with the GZIP option to load compressed files, which automatically distributes the data evenly across all nodes.

Answer

Load the data into a table with no compression and then run a VACUUM command to reorganize and compress the data.

Answer

Load the data into a single large staging table and then use CREATE TABLE AS (CTAS) to redistribute it into multiple tables.

Question 8

A data engineer manages an Amazon DynamoDB table used for a high-traffic gaming leaderboard. The table uses on-demand capacity mode and has a partition key of UserId (string) with no sort key. The leaderboard must retrieve the top 100 scores across all users. Currently, the engineer scans the entire table and sorts the results in application code, which takes several seconds and consumes large amounts of read capacity. What should the engineer do to improve the performance of retrieving the top scores?

Accepted Answer

Create a global secondary index (GSI) with a constant partition key and Score as the sort key, then query the index in descending order with a limit of 100.. The leaderboard requires a global, sorted view of scores. A GSI with a constant partition key and Score as sort key allows efficient Query operations that return the highest scores first using ScanIndexForward=false and a Limit. This design avoids full table scans and scales well.

Answer

Increase the table's read capacity by switching to provisioned mode with a high RCU value.

Answer

Add a local secondary index (LSI) on the Score attribute and query it with a limit of 100.

Answer

Enable DynamoDB Streams and use AWS Lambda to maintain a separate sorted list in Amazon ElastiCache for Redis.

Question 9

A media company ingests thousands of small JSON files per hour into an Amazon S3 bucket. A data engineer needs to convert these files into a compact, columnar format for efficient querying with Amazon Athena. The engineer wants to minimize storage costs and improve query performance. Which approach should the engineer take?

Accepted Answer

Use AWS Glue ETL to read the JSON files, convert them to Apache Parquet, and write the output to a new S3 prefix partitioned by date.. AWS Glue ETL is a fully managed extract, transform, and load service that can efficiently process large volumes of data. It can read JSON from S3, convert to Parquet, and write partitioned output. This reduces storage costs due to Parquet's compression and columnar format, and improves Athena query performance by reducing data scanned. Partitioning by date further optimizes queries.

Answer

Use Amazon Athena to create a new table as Parquet using CREATE TABLE AS SELECT (CTAS) from the JSON table.

Answer

Use Amazon Kinesis Data Firehose to convert JSON to Parquet and deliver to S3.

Answer

Use AWS Lambda to read each JSON file, convert to Parquet, and write back to S3.

Question 10

A data engineer maintains an Amazon S3 data lake with millions of small JSON objects. The engineer needs to improve query performance by reducing the number of objects and compressing them into a columnar format that Amazon Athena can query efficiently. The data must remain partitioned by date. Which solution should the engineer use?

Accepted Answer

Use AWS Glue ETL to read the JSON objects, repartition by date, write them as Parquet with Snappy compression, and register the resulting tables in the AWS Glue Data Catalog.. Converting small JSON files to Parquet with Snappy compression and repartitioning by date reduces storage footprint and the amount of data scanned by Athena. AWS Glue ETL can perform this transformation and update the Data Catalog so Athena queries the new tables. This is the standard pattern for optimizing S3 data lakes for analytics.

Answer

Enable S3 Transfer Acceleration on the bucket and run Amazon Athena queries with the existing JSON objects.

Answer

Use Amazon S3 Lifecycle policies to transition the JSON objects to S3 Glacier Instant Retrieval and query them with Athena.

Answer

Create an Amazon EMR cluster and run a MapReduce job that merges the JSON files into larger JSON files without changing the format.

Question 11

A data engineer is designing a solution to ingest streaming data from Amazon Kinesis Data Streams into an Amazon Redshift cluster for near-real-time analytics. The engineer needs to ensure that data is loaded efficiently and that the Redshift cluster can handle the ingestion load without impacting query performance. Which approach should the engineer use?

Accepted Answer

Use the Amazon Redshift Streaming Ingestion feature to directly ingest from Kinesis Data Streams into Redshift materialized views.. Amazon Redshift Streaming Ingestion is designed to ingest data directly from Kinesis Data Streams into Redshift materialized views with low latency. It eliminates the need for intermediate storage and complex ETL, and it offloads ingestion from the cluster's query processing. This meets the near-real-time analytics requirement while minimizing impact on query performance.

Answer

Use Amazon Kinesis Client Library (KCL) on an Amazon EC2 instance to read from Kinesis and insert data into Redshift using INSERT statements.

Answer

Use Amazon Kinesis Data Firehose to deliver the stream to an Amazon S3 bucket, then use a COPY command to load data into Redshift.

Answer

Use AWS Glue streaming ETL to read from Kinesis and write to Redshift using JDBC connections.

Question 12

A data engineer is using AWS Glue to catalog data stored in Amazon S3. The data is in Apache Parquet format and partitioned by year, month, and day. The engineer notices that AWS Glue crawlers are taking a long time to run and are not correctly identifying new partitions. The engineer needs to improve the crawler performance and ensure new partitions are added automatically. Which action should the engineer take?

Accepted Answer

Use AWS Glue partition projection instead of crawling for partitions.. Partition projection in AWS Glue allows you to specify the partitioning scheme directly in the table properties, so the crawler does not need to discover partitions. This reduces crawler runtime and ensures that new partitions are immediately available for query engines like Athena. It is the most efficient solution for regularly partitioned data.

Answer

Enable the crawler option to update the Data Catalog with new partitions only, and set a partition index.

Answer

Increase the number of data processing units (DPUs) for the crawler.

Answer

Configure the crawler to use a custom classifier for Parquet.

Question 13

A company stores sensitive financial data in an Amazon S3 bucket. The data engineering team must ensure that all data is encrypted at rest using AWS Key Management Service (AWS KMS) customer managed keys, and that the encryption keys are rotated annually. The team also needs to audit key usage. Which solution meets these requirements?

Accepted Answer

Enable default encryption on the S3 bucket using SSE-KMS with a customer managed key, enable automatic key rotation for the KMS key, and enable AWS CloudTrail to log KMS API calls.. The correct solution uses SSE-KMS with a customer managed key to encrypt data at rest, enables automatic key rotation for that key to meet the annual rotation requirement, and enables AWS CloudTrail to log KMS API calls for auditing key usage. Other options either use incorrect key types (SSE-S3 or AWS managed keys) or do not provide the necessary auditing capabilities.

Answer

Enable default encryption on the S3 bucket using SSE-S3, and configure S3 server access logging to track key usage.

Answer

Use client-side encryption with a customer provided key stored in AWS Secrets Manager, and enable AWS CloudTrail to log S3 API calls.

Answer

Enable default encryption on the S3 bucket using SSE-KMS with an AWS managed key, and configure S3 Inventory to report on encryption status.

Question 14

A data engineer is configuring an Amazon S3 bucket to store sensitive financial data. The company requires that all data be encrypted at rest using AWS Key Management Service (AWS KMS) customer managed keys, and that the encryption key be automatically rotated every year. The engineer creates a KMS customer managed key and enables automatic rotation. When uploading objects using the AWS CLI, the engineer uses the --sse aws:kms parameter but does not specify a key ID. What is the result of this configuration?

Accepted Answer

The objects are encrypted with the AWS managed key for S3 (aws/s3), not the customer managed key.. To use a specific KMS customer managed key for S3 default encryption or per-object encryption, you must explicitly provide the key ARN or alias. Omitting the key ID causes S3 to fall back to the AWS managed key (aws/s3), which does not meet the requirement of using a customer managed key with annual rotation.

Answer

The objects are encrypted with the customer managed key because it is the only KMS key in the account.

Answer

The objects are encrypted with the customer managed key, and automatic rotation applies because the key is the default KMS key for the account.

Answer

The upload fails because a KMS key ID is required when using --sse aws:kms.

Question 15

A retail company uses Amazon DynamoDB to store product catalog data. The table has a partition key of ProductID and a sort key of Category. The company needs to retrieve all products in a specific category, sorted by ProductID. Which operation should be used?

Accepted Answer

Create a global secondary index (GSI) with Category as the partition key and ProductID as the sort key, then query the GSI.. To efficiently retrieve all products in a specific category sorted by ProductID, a global secondary index (GSI) is needed. The GSI should use Category as the partition key and ProductID as the sort key. This allows Query operations on the GSI to return the desired items in sorted order, leveraging DynamoDB's indexing capabilities for performance and cost efficiency.

Answer

Scan the table with a filter expression on Category.

Answer

Query the table using the partition key and sort key condition.

Answer

Use the BatchGetItem operation with Category as a key.