Task 2.4: Design data models and schema evolution

• AUse EVEN distribution for both tables

  Incorrect

  EVEN spreads rows round-robin regardless of values. It suits tables that do not take part in joins.

• BUse KEY distribution on order_id for both tables

  Correct

  Distributing a pair of tables on their joining column puts matching rows on the same slice, so the join needs no redistribution.

• CUse ALL distribution for both tables

  Incorrect

  ALL copies the whole table to every node, multiplying storage and load time. It is meant for relatively slow-moving tables, not two large ones.

• DUse KEY distribution on each table's own primary key column

  Incorrect

  order_items would be distributed on its own key, not order_id, so rows with the same order_id would still sit on different slices.

Collocation is the point of KEY distribution: when both sides of a frequent join are distributed on the join column, each slice can join its own rows.

• AMake event_time the leading column of the sort key

  Correct

  Redshift keeps the minimum and maximum value of each 1 MB block. With data sorted by event_time, a range filter skips most blocks; AWS recommends a timestamp as the leading sort key column for this.

• BMake event_time the distribution key of the table

  Incorrect

  The distribution key decides which slice stores each row. It does not let a range filter skip blocks.

• CUse ALL distribution so every node holds the full table

  Incorrect

  ALL copies the table to every node. Each node would read even more data for the same filter.

• DSet the encoding of the event_time column to RAW

  Incorrect

  RAW means the column is stored without compression. An encoding does not order the rows, so a range filter would still read blocks from all 5 years.

Sort keys and zone maps work together: when rows are stored in event_time order, the block metadata tells Redshift which blocks cannot match.

• AAdd a global secondary index keyed on warehouse_id and order_date

  Correct

  A global secondary index can have a key schema different from the table's, and it can be queried directly; ScanIndexForward set to false returns the newest items first.

• BAdd a local secondary index that uses warehouse_id as its sort key

  Incorrect

  A local secondary index keeps the table's partition key, so it only sorts within one customer, and it can only be created with the table.

• CScan the table with a filter expression on the warehouse_id attribute

  Incorrect

  A Scan reads every item before the filter applies, which gets slower and more expensive as the table grows.

• DRaise the table's read capacity and read it with a parallel scan

  Incorrect

  A parallel scan still reads the whole table. More capacity makes it faster but not efficient.

A new access pattern on an existing table is what global secondary indexes are for: a different partition key, added without recreating the table.

• ADMS Schema Conversion in an AWS DMS migration project

  Correct

  DMS Schema Conversion reads the source metadata, runs an assessment of what converts automatically and what needs manual work, and converts the schema in a migration project.

• BAn AWS DMS full-load task with the target table preparation mode set to drop and create

  Incorrect

  A migration task moves data. DMS Schema Conversion is the feature that converts schema code such as procedures and views.

• CAn AWS Glue crawler that reads the Oracle schema and creates the tables in Aurora

  Incorrect

  Crawlers populate the AWS Glue Data Catalog with table definitions. They do not create objects in a target database.

• DAWS DataSync, which copies the Oracle data files to the Aurora cluster

  Incorrect

  DataSync transfers files and objects between storage systems. It does not convert database schemas.

Heterogeneous migrations have two parts: converting the schema and code, then moving the data. DMS Schema Conversion handles the first part inside AWS DMS.

• ARaise the maximum number of results that each retrieval returns

  Incorrect

  numberOfResults sets the maximum number of chunks returned. It does not restrict which documents they come from.

• BSwitch the retrieval search type from semantic search to hybrid search

  Incorrect

  Hybrid search combines embeddings with a search of the raw text. It does not limit results to one department.

• CAdd a department attribute in metadata files and filter on it

  Correct

  A fileName.metadata.json file in the S3 data source adds attributes to a document, and queries can filter on those attributes.

• DChange the chunking strategy to hierarchical chunking for all documents

  Incorrect

  Chunking controls how documents are split before embedding. It does not decide which department's chunks a query sees.

Metadata attributes stored beside the vectors let one index serve many audiences; filters narrow retrieval before the model sees any text.

• AALL

  Incorrect

  ALL keeps a copy of the entire table on every node. The style does not change as the table grows large.

• BEVEN

  Incorrect

  EVEN spreads rows round-robin regardless of values. It is a fixed choice, not one that adapts to the table's size.

• CAUTO

  Correct

  With AUTO, Redshift assigns a style based on table size, for example ALL while the table is small, and changes it to KEY or EVEN as it grows.

• DKEY on a column chosen now

  Incorrect

  KEY places matching values together for a chosen column, but the join patterns that would guide that choice are not yet known.

When size and access patterns are uncertain, AUTO lets Redshift move from ALL to KEY or EVEN as data grows; explicit styles fit known workloads.

• APARTITIONED BY (bucket(16, event_ts))

  Incorrect

  bucket(N, col) partitions by a hashed value modulo N buckets, which spreads each day across buckets instead of grouping it.

• BPARTITIONED BY (truncate(10, event_ts))

  Incorrect

  truncate(L, col) works on int, long, decimal, and string columns, not on timestamps.

• CPARTITIONED BY (hour(event_ts))

  Incorrect

  hour(ts) partitions by hour, which is finer than the daily partitions the table needs.

• DPARTITIONED BY (day(event_ts))

  Correct

  day(ts) partitions a date or timestamp column by day, and Athena supports Iceberg's hidden partitioning, so analysts keep filtering on event_ts.

Iceberg partition transforms derive partitions from a column, so queries on the source column still prune partitions without an extra date column.

• ACompress each PDF with GZIP and store it in a Binary attribute of the item

  Incorrect

  Compression can help values fit, but a 5 MB file is more than 12 times the 400 KB item limit.

• BStore each PDF in Amazon S3 and keep its object key in the claim item

  Correct

  DynamoDB items are limited to 400 KB. Storing the large object in S3 and its identifier in the item keeps the claim within that limit.

• CSwitch the table from provisioned to on-demand capacity mode

  Incorrect

  The capacity mode changes how reads and writes are billed and scaled. The 400 KB item size limit still applies.

• DWrite the claims with BatchWriteItem instead of PutItem calls

  Incorrect

  BatchWriteItem fails when any item in the batch exceeds 400 KB, so the limit is unchanged.

Large blobs belong in S3 with a pointer in DynamoDB; the table keeps the small, queryable attributes.

• ARename the column in the table and keep the default access by name

  Incorrect

  Renaming columns is listed as safe for Parquet only when it is read by index; by name, old files would not match the new column name.

• BSet parquet.column.index.access to true, then rename the column

  Correct

  Parquet is read by name by default. Reading by index uses the column's ordinal number, and renaming columns is supported for Parquet read by index.

• CAdd customer_id at the end of the table and keep cust_id as well

  Incorrect

  Adding a column at the end is safe, but the values would sit in two columns instead of one.

• DSet parquet.column.index.access to false, then rename the column

  Incorrect

  False sets the access method to column name, which is the default that does not support renaming.

Parquet and ORC differ in default column access, and the access method decides which schema changes are safe: by name for adding and removing, by index for renaming.