Data-Engineer-Associate VCE Exam Download

In Amazon Athena, you can use CREATE TABLE AS SELECT with WITH NO DATA to create an empty copy of an existing table’s schema:

“The query CREATE TABLE new_table AS SELECT * FROM old_table WITH NO DATA; creates a new table with the same schema but without copying over the data.”

– Ace the AWS Certified Data Engineer - Associate Certification - version 2 - apple.pdf

This is the most efficient way to create an empty version of the existing table.

Option C is correct because the fastest design combines a columnar storage format with partitioning on the most common query predicate. AWS Athena guidance says that Parquet and ORC are optimized columnar formats and that columns frequently used as filters are good candidates for partitioning. AWS also states that when a query filters on a partition key, the engine reads only matching partitions, which significantly reduces data scanned. Since the company’s main analytical need is insights specific to sensor categories, partitioning by sensor category provides the strongest pruning. Sorting by date then helps organize time-based data within each category partition.

Options B and D use CSV, which is row-based and much slower for analytical scans than Parquet or ORC. Option A uses a good format, but partitioning by date is less optimal than partitioning by sensor category when category is the main filter in the company’s queries. The study guide also identifies Parquet as the preferred analytic storage format for efficient columnar queries.

Amazon DynamoDB is optimized for Query operations, which retrieve items efficiently using the partition key and optionally the sort key. Queries consume significantly fewer RCUs than Scan operations because only matching partitions are read.

By performing a Query using the partition key in the key condition expression, the AWS Glue job reads only the required items directly from DynamoDB. This minimizes the number of read capacity units consumed and avoids unnecessary reads.

Using a Scan operation, even when followed by filtering in a DynamicFrame, consumes RCUs for the entire table or index and is highly inefficient. Using only the sort key in a query is invalid without the partition key and would not meet DynamoDB query requirements. The Glue DynamoDB connector filter option still performs a scan under the hood and does not minimize RCUs.

The queried data can be directly loaded into a DynamicFrame, satisfying the existing processing logic requirement without additional transformations.

Therefore, Option D provides the most efficient and cost-effective solution.