DEA-C01 Practice Test 49 — 15 Questions

Question 1

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?

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

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 2

A data engineer is using AWS Lake Formation to manage access to a data lake in Amazon S3. The company wants to grant a data analyst read-only access to specific columns in a table stored in the AWS Glue Data Catalog. The analyst should not be able to see other columns or any rows that contain sensitive data. The engineer sets up Lake Formation permissions on the table, granting SELECT on specific columns. However, when the analyst queries the table using Amazon Athena, they can see all columns. What is the most likely reason?

Accepted Answer

The analyst has IAM permissions that allow direct access to the S3 bucket, bypassing Lake Formation.. Lake Formation enforces fine-grained access control only when users do not have direct IAM permissions to the underlying data. If the analyst has IAM permissions to read the S3 bucket, they can bypass Lake Formation and access all columns and rows. To enforce column-level security, the analyst's IAM policy must not allow direct S3 access; all data access must be mediated through Lake Formation. The other options are either incorrect or would produce different errors.

Answer

The Glue Data Catalog table definition does not include the column-level metadata required for Lake Formation.

Answer

Lake Formation column-level permissions are not supported for Athena queries.

Answer

The analyst's IAM role lacks the lakeformation:GetDataAccess permission.

Question 3

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?

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 4

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 5

A data engineer is configuring an Amazon Redshift cluster and needs to optimize query performance for complex analytical queries that involve large joins. The engineer wants to reduce the amount of data movement during query execution. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Define sort keys on columns frequently used in join and filter conditions.. To reduce data movement during joins in Amazon Redshift, the engineer should choose a distribution style that colocates joined tables on the same node slices and define sort keys on columns used in joins and filters. These actions minimize network traffic and enable efficient merge joins. Increasing nodes, enabling concurrency scaling, or using columnar storage do not directly address data movement within a query.

Answer

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

Answer

Enable concurrency scaling to handle concurrent queries.

Answer

Use columnar storage for all tables.

Question 6

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.

Question 7

A data engineer is configuring an AWS Glue ETL job to read from an Amazon S3 bucket that contains Apache Parquet files partitioned by year, month, and day. The engineer wants the job to only process data for the year 2023 and month 10, and to minimize the amount of data scanned. The Glue job uses the Glue Data Catalog table `sales_data` with the correct partition structure. What is the MOST efficient way to configure the job to read only the required partitions?

Accepted Answer

Use the Glue `create_dynamic_frame.from_catalog` with `push_down_predicate` set to `year=='2023' and month=='10'`.. The correct approach is to use `push_down_predicate` with `create_dynamic_frame.from_catalog`. This pushes the filter down to the Glue Data Catalog, so only the relevant partitions are read from Amazon S3. It minimizes data scanned, reduces cost, and improves job performance. Other methods either read all data first or bypass the catalog, which is less efficient and harder to maintain.

Answer

Read the entire `sales_data` table into a DynamicFrame and then apply a `Filter` transform with the condition `year=='2023' and month=='10'`.

Answer

Use the Glue `create_dynamic_frame.from_options` with the S3 path `s3://bucket/sales_data/year=2023/month=10/` and format `parquet`.

Answer

Create a new Glue Data Catalog table that points only to the `year=2023/month=10` prefix, and then read from that table.

Question 8

A data engineering team is troubleshooting a slow AWS Glue ETL job that reads from an Amazon DynamoDB table and writes to Amazon S3 in Parquet format. The job processes 50 GB of data. Which action would most effectively improve job performance?

Accepted Answer

Increase the number of DPUs. Increasing the number of DPUs (Data Processing Units) for the AWS Glue job directly allocates more distributed computing resources (CPU, memory, and network bandwidth) to parallelize the read from DynamoDB and the write to S3. Since the job processes 50 GB of data, the bottleneck is likely the throughput of the Glue Spark cluster, and adding DPUs increases parallelism, reducing overall execution time.

Answer

Use S3 Select to push down filters

Answer

Reduce the batch size in the DynamoDB connector

Answer

Change output to JSON format to reduce overhead

Question 9

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 10

A company uses AWS Glue ETL to transform data from Amazon RDS for MySQL to Amazon S3. The Glue job reads from a JDBC connection. The job runs once daily and processes all records, but the data volume is growing. Which change would improve performance and reduce costs?

Accepted Answer

Enable Glue job bookmarking and set the job to process only new data. Enabling Glue job bookmarking allows the job to process only new or changed data since the last run, rather than reprocessing the entire dataset. This reduces both the data volume read from the JDBC source and the transformation time, directly improving performance and lowering costs by minimizing DPU usage.

Answer

Increase the number of DPUs for the Glue job

Answer

Switch to a Glue Python shell job

Answer

Use a higher JDBC fetch size

Question 11

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 12

A data engineer is designing a data lake on Amazon S3 and needs to catalog data using the AWS Glue Data Catalog. The data is stored in Parquet format, partitioned by year/month/day. The engineer wants to query the data using Amazon Athena and ensure that partition pruning occurs to minimize query costs. Which action should the engineer take?

Accepted Answer

Configure the table in AWS Glue Data Catalog with partition projection using the appropriate storage descriptor and table properties.. Partition projection in AWS Glue Data Catalog enables Athena to dynamically compute partition locations, avoiding the need to manage partitions manually. It supports efficient partition pruning, reducing the amount of data scanned and lowering query costs. This is the recommended approach for highly partitioned datasets with a predictable structure.

Answer

Use AWS Glue crawlers to automatically discover the schema and partitions, then run MSCK REPAIR TABLE in Athena.

Answer

Use AWS Glue ETL jobs to write data to S3 in a non-partitioned layout and rely on Athena's predicate pushdown.

Answer

Manually define the table in AWS Glue Data Catalog with partition keys and use Athena's ALTER TABLE ADD PARTITION for each partition.

Question 13

A data engineer manages an Amazon S3 data lake that holds sensitive customer transaction logs. Compliance requires that all objects be encrypted at rest with keys that the company rotates every 90 days and fully controls, including the ability to immediately revoke access and audit key usage separately from other AWS accounts. The engineer must choose an encryption method that meets these requirements with minimal operational overhead. Which solution should the engineer implement?

Accepted Answer

Use server-side encryption with AWS KMS customer managed keys (SSE-KMS). The requirement for customer-controlled keys, custom 90-day rotation, immediate revocation, and separate key usage audit points directly to AWS KMS customer managed keys with SSE-KMS. S3 manages the encryption process, so operational overhead remains low, while the key policy and CloudTrail integration provide the necessary control and visibility. Other encryption options either lack customer control or shift too much operational responsibility to the application.

Answer

Use server-side encryption with Amazon S3 managed keys (SSE-S3)

Answer

Use client-side encryption with a customer-provided key stored in AWS Secrets Manager

Answer

Use server-side encryption with customer-provided keys (SSE-C)

Question 14

A data engineer manages an Amazon DynamoDB table that stores IoT sensor readings. The table uses a partition key of deviceId and a sort key of timestamp, with a provisioned read capacity of 100 RCUs. During a sudden spike in traffic, the engineer observes throttling on read operations even though the consumed read capacity is well below the provisioned limit. What is the MOST likely cause of the throttling?

Accepted Answer

The table's partition key is a low-cardinality attribute, causing a hot partition.. DynamoDB throttling can occur even when total consumed capacity is below the provisioned level if reads are concentrated on a few partitions. Each partition has a maximum throughput of 3,000 RCUs. A low-cardinality partition key creates hot partitions, causing localized throttling. The solution is to use a higher-cardinality partition key or add a write sharding strategy.

Answer

The table uses eventually consistent reads, which are throttled more aggressively than strongly consistent reads.

Answer

The provisioned read capacity is set too low for the table's data volume.

Answer

The table lacks a global secondary index (GSI) on the timestamp attribute.

Question 15

A data engineer stores Apache Parquet files in an Amazon S3 data lake partitioned by dt=YYYY-MM-DD. Analysts query the data with Amazon Athena, and monthly reports that scan one month of data are slow and expensive. The engineer confirms that queries filter on the dt column. Which action will MOST effectively reduce the amount of data scanned by these reports?

Accepted Answer

Run MSCK REPAIR TABLE on the table, or enable partition projection for the dt partition column.. Athena reduces scan size through partition pruning, which requires the table's partition metadata to reflect the actual S3 prefixes. When files are written as dt=YYYY-MM-DD without catalog registration, filters on dt cannot eliminate prefixes, so monthly reports scan the entire table. Registering partitions with MSCK REPAIR TABLE or defining partition projection restores pruning, cutting both bytes scanned and cost.

Answer

Convert the Parquet files to CSV so that Athena can read them faster.

Answer

Enable Amazon S3 Transfer Acceleration on the data lake bucket.

Answer

Increase the Athena per-query data limit in the workgroup settings.