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

Question 1

A data engineer is using Amazon Redshift to store sales data. The engineer needs to ensure that the data is encrypted at rest and that encryption keys are managed by AWS. The engineer also wants to minimize administrative overhead. Which Redshift encryption option should the engineer use?

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

Accepted Answer

AWS KMS AWS managed key (aws/redshift). Amazon Redshift supports encryption at rest using AWS KMS. The AWS managed key (aws/redshift) is automatically created and managed by AWS, providing encryption without the need for manual key management. This minimizes administrative overhead while ensuring data is encrypted at rest. Customer managed keys offer more control but require more management, and HSM or client-side encryption add complexity.

Answer

AWS Key Management Service (AWS KMS) customer managed key

Answer

Client-side encryption with a custom key

Answer

Hardware security module (HSM) encryption

Question 2

A data engineer needs to create a table in Amazon Athena that reads JSON data stored in Amazon S3. The JSON records are stored in a single file, one JSON object per line. The engineer wants Athena to automatically discover the schema and create the table without manually defining columns. Which AWS service or feature should the engineer use?

Accepted Answer

AWS Glue crawler. An AWS Glue crawler automatically scans data in Amazon S3, infers the schema, and creates table definitions in the AWS Glue Data Catalog. Athena uses this catalog to query the data without manual schema definition. This is the standard method for automatic schema discovery in a data lake.

Answer

Amazon Athena CREATE TABLE AS SELECT (CTAS) statement

Answer

AWS Glue DataBrew

Answer

Amazon S3 Inventory

Question 3

A data engineer needs to store a large volume of time-series data from IoT sensors. The data will be queried by timestamp and sensor ID, and the engineer wants to use a managed AWS database that can handle high write throughput and provide fast queries on recent data. The engineer also wants to automatically expire old data after 90 days to reduce storage costs. Which AWS service should the engineer use?

Accepted Answer

Amazon Timestream. Amazon Timestream is a fully managed time-series database that handles high write throughput, provides fast queries on recent data, and supports automatic data retention policies. It is designed specifically for time-series data like IoT sensor readings, with built-in functions for time-based queries. Other options either lack native time-series optimization or require manual management of retention and querying.

Answer

Amazon DynamoDB with TTL

Answer

Amazon RDS for PostgreSQL with table partitioning

Answer

Amazon S3 with lifecycle policies

Question 4

A data engineer is using AWS Lake Formation to manage fine-grained access control on an Amazon S3 data lake. The engineer has registered the S3 bucket as a Lake Formation data location and created a table in the AWS Glue Data Catalog. The engineer needs to grant a data analyst permission to query only specific columns (customer_id, order_date) in the sales table using Amazon Athena, while hiding other columns (credit_card_number, address). The analyst uses an IAM role that has no direct S3 permissions. Which action should the engineer take?

Accepted Answer

Grant the analyst's IAM role SELECT permission on the sales table in Lake Formation, and then use Lake Formation column-level security to include only the customer_id and order_date columns.. Lake Formation column-level security is designed to grant SELECT on specific columns while hiding others. By granting SELECT on the table and then specifying included columns, the analyst can query only those columns. Because the analyst's IAM role lacks direct S3 permissions, all data access goes through Lake Formation, which enforces the column restrictions and provides temporary credentials. This meets the requirement without granting broad S3 access.

Answer

Create an IAM policy that allows s3:GetObject on the sales table's S3 prefix, and attach it to the analyst's role. Then use Athena to restrict columns via a view.

Answer

Use AWS Glue DataBrew to create a dataset that includes only the allowed columns, and grant the analyst access to that dataset.

Answer

Grant the analyst's IAM role DESCRIBE and SELECT on the sales table in Lake Formation, and then create an Athena view that selects only the allowed columns.

Question 5

A data engineer is setting up an Amazon Redshift cluster and needs to load data from Amazon S3. The data is in CSV format and contains a large number of rows. The engineer wants to achieve the fastest possible load time. Which method should the engineer use?

Accepted Answer

Use the COPY command to load the data from Amazon S3 into the Redshift cluster.. The COPY command is specifically designed for bulk loading data from Amazon S3 into Redshift. It uses parallel processing to load data quickly and efficiently, supports CSV format, and can handle large volumes. Other methods like DMS or Firehose are not optimized for this use case, and INSERT is too slow.

Answer

Use Amazon Kinesis Data Firehose to stream the data from S3 to Redshift.

Answer

Use AWS Database Migration Service (AWS DMS) to migrate the data from S3 to Redshift.

Answer

Use the INSERT INTO command to load the data row by row from S3.

Question 6

A data engineer is designing a real-time analytics solution using Amazon DynamoDB. The workload requires capturing all changes to a DynamoDB table and processing them in near-real-time to update a materialized view in Amazon Redshift. Which approach should the engineer use to capture and process the changes?

Accepted Answer

Enable DynamoDB Streams and use AWS Lambda to process the stream and write to Redshift.. DynamoDB Streams captures item-level changes in near-real-time, and AWS Lambda can process these changes and write to Amazon Redshift. This serverless architecture is scalable, cost-effective, and requires minimal operational overhead. It is the standard pattern for change data capture from DynamoDB to other data stores.

Answer

Enable DynamoDB global tables and use Amazon Kinesis Data Streams to replicate changes to Redshift.

Answer

Use AWS Database Migration Service (AWS DMS) with ongoing replication from DynamoDB to Redshift.

Answer

Use DynamoDB Accelerator (DAX) to cache changes and periodically export to Redshift.

Question 7

A company stores application logs in Amazon S3 in JSON format. The logs are partitioned by year/month/day. A data engineer needs to create a table in the AWS Glue Data Catalog so that Amazon Athena can query the logs efficiently. The engineer wants to minimize query costs and ensure that new partitions are automatically recognized. Which combination of actions should the engineer take?

Accepted Answer

Create a table with partition projection enabled for year/month/day and set the storage location to the S3 prefix.. Partition projection in Athena allows the table to compute partition locations dynamically based on a defined pattern, so new partitions are recognized immediately without manual intervention or crawlers. This reduces metadata operations and enables partition pruning, which lowers query costs. Manual partition management, non-partitioned tables, or scheduled crawlers either add overhead or fail to provide immediate partition recognition.

Answer

Create a table with partitions and configure an AWS Glue crawler to run on a schedule to discover new partitions.

Answer

Create a table with partitions defined manually and run ALTER TABLE ADD PARTITION for each new day.

Answer

Create a table without partitions and rely on Athena to scan the entire S3 prefix.

Question 8

A data engineer is using Amazon Redshift to store sales data. The engineer needs to ensure that the data is encrypted at rest and that encryption keys are managed by AWS. The engineer also wants to minimize administrative overhead. Which Redshift encryption option should the engineer use?

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

Accepted Answer

DISTSTYLE ALL. For a small dimension table that is frequently joined with a large fact table, DISTSTYLE ALL replicates the dimension table to all nodes. This ensures that each node has a local copy, eliminating the need to broadcast or redistribute the dimension data during joins. As a result, data movement is minimized, and join performance is significantly improved.

Answer

DISTSTYLE AUTO

Answer

DISTSTYLE KEY

Answer

DISTSTYLE EVEN

Question 9

A data engineer is optimizing an Amazon S3 data lake that stores large volumes of JSON logs. The engineer wants to reduce storage costs and improve query performance in Amazon Athena. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Convert the JSON logs to Apache Parquet format.. Converting JSON logs to Parquet reduces storage size and improves Athena query performance by enabling columnar reads and better compression. Partitioning the data by commonly filtered columns like date allows Athena to skip irrelevant data, reducing the amount scanned and lowering costs. These two actions together address both storage cost reduction and query performance improvement.

Answer

Enable S3 Transfer Acceleration for the bucket.

Answer

Use S3 Standard-Infrequent Access (S3 Standard-IA) storage class for all log data.

Answer

Enable S3 server access logging on the bucket.

Question 10

A data engineer is using Amazon Redshift to store sales data. The engineer needs to ensure that the data is encrypted at rest and that encryption keys are managed by AWS. The engineer also wants to minimize administrative overhead. Which Redshift encryption option should the engineer use?

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

Accepted Answer

ALL. ALL distribution replicates the entire dimension table to every node, eliminating the need to redistribute data during joins with the fact table. This minimizes network traffic and speeds up join operations. It is best suited for small dimension tables that are frequently joined with larger tables.

Answer

AUTO

Answer

EVEN

Answer

KEY

Question 11

A data engineer is configuring an Amazon S3 bucket that will receive raw clickstream files from a mobile application. The engineer must ensure that the objects are protected against accidental overwrites and deletions for a defined retention period, and that the protection cannot be removed or shortened by any user, including the account root user. Which S3 feature should the engineer use?

Accepted Answer

S3 Object Lock in compliance mode with a retention period matching the required window.. S3 Object Lock provides write-once-read-many protection by binding a retention period to object versions. In compliance mode, no principal, including the account root user, can shorten or remove that retention, which is the only option that satisfies an absolute immutability requirement. Governance mode, by contrast, allows privileged users to bypass retention.

Answer

S3 Versioning with a lifecycle rule that transitions objects to S3 Standard-IA after 30 days.

Answer

A bucket policy that denies s3:DeleteObject and s3:PutObject to all principals except the ingestion role.

Answer

S3 default encryption with AWS KMS customer managed keys and a restrictive key policy.

Question 12

A company stores application logs in Amazon S3 and wants to analyze them using Amazon Athena. The logs are in JSON format and are compressed with gzip. The data engineer needs to create an Athena table that can query these logs efficiently. The logs are stored in an S3 bucket with the prefix logs/year=2023/month=10/day=15/. The engineer wants to minimize query costs and improve performance. Which action should the engineer take?

Accepted Answer

Create an external table with the JSON SerDe, specifying the S3 location as s3://bucket/logs/ and defining partitions for year, month, and day.. To query JSON logs efficiently in Athena, the table must use the JSON SerDe to parse the data correctly. Defining partitions on year, month, and day allows Athena to prune partitions based on query filters, reducing the amount of data scanned and lowering costs. The table location should point to the root prefix so that all partitions are included. This combination provides accurate parsing and cost-effective queries.

Answer

Create an external table with the JSON SerDe, specifying the S3 location as s3://bucket/logs/ and using the OpenCSVSerDe for better performance.

Answer

Create an external table with the CSV SerDe, specifying the S3 location as s3://bucket/logs/ and defining partitions for year, month, and day.

Answer

Create an external table with the JSON SerDe, specifying the S3 location as s3://bucket/logs/year=2023/month=10/day=15/ and no partitions.

Question 13

A data engineer is managing an Amazon S3 data lake that contains raw JSON data. The engineer needs to optimize the data lake for query performance and cost when using Amazon Athena. The data is currently stored in a single S3 prefix without partitioning, and queries often filter on `event_type` and `event_date`. The engineer wants to implement best practices for Athena. Which TWO actions should the engineer take? (Choose two.)

Accepted Answer

Convert the JSON data to Apache Parquet format.. The two most effective actions are converting JSON to Parquet and partitioning by `event_type` and `event_date`. Parquet's columnar format reduces data scanned, and partitioning enables partition pruning. Together, they minimize query cost and improve performance. Other options either do not affect Athena queries or are less effective. These are core best practices for optimizing Athena on S3 data lakes.

Answer

Enable S3 Transfer Acceleration on the bucket.

Answer

Compress the JSON files using gzip.

Answer

Use Amazon S3 Select to filter data before querying with Athena.

Question 14

A data engineer is designing a data lake on Amazon S3 for a financial analytics workload. The raw data arrives as JSON files from an on-premises system. Analysts need to query the data using Amazon Athena with fast performance and minimal cost for queries that filter on a specific transaction date and customer ID. The engineer wants to convert the data to a columnar format that supports predicate pushdown and compression. Which storage format should the engineer choose?

Accepted Answer

Apache Parquet. Apache Parquet is a columnar storage format that allows Athena to read only the columns referenced in a query and to skip row groups using predicate pushdown. This reduces the amount of data scanned, lowering cost and improving performance for filters on transaction date and customer ID. Parquet also compresses efficiently, further reducing storage and scan volume.

Answer

Apache Avro

Answer

JSON

Answer

CSV

Question 15

A data engineer is using Amazon Redshift to store sales data. The engineer needs to ensure that the data is encrypted at rest and that encryption keys are managed by AWS. The engineer also wants to minimize administrative overhead. Which Redshift encryption option should the engineer use?

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

Accepted Answer

Use the COPY command with the FROM 's3://bucket/prefix/date=2023-10-01/' option to load only that partition.. The COPY command can load data directly from a specific S3 prefix, so specifying the exact partition prefix loads only that partition efficiently. This avoids unnecessary data transfer and cost. Using a WHERE clause with COPY is invalid, and alternatives like AWS Glue or Redshift Spectrum add complexity or do not load data into Redshift tables as required.

Answer

Use AWS Glue to read the Parquet data, filter by the latest date, and write to Redshift.

Answer

Use Amazon Redshift Spectrum to create an external table and query the latest partition.

Answer

Use the COPY command with the FROM 's3://bucket/prefix' option and specify the partition column in the WHERE clause.