Data Warehousing on AWS Training Data Warehousing on AWS: Data Modeling 2

Question 1

Which Redshift distribution style minimizes data movement for large fact tables joined with large dimension tables?

Accepted Answer

KEY

Answer

KEY distribution places matching join key values on the same node slice, eliminating cross-node data shuffling during joins.

Question 2

In a slowly changing dimension (SCD) Type 2 implementation in Redshift, which columns are typically added to track history?

Accepted Answer

effective_start_date, effective_end_date, and is_current

Answer

SCD Type 2 tracks history by adding effective_start_date, effective_end_date, and an is_current flag to mark the active record.

Question 3

What is the primary advantage of using a snowflake schema over a star schema in a Redshift data warehouse?

Accepted Answer

Reduced storage through normalized dimension tables

Answer

Snowflake schemas normalize dimension tables into sub-dimensions, reducing redundant data and storage consumption.

Question 4

When designing a Redshift table for time-series IoT sensor data with billions of rows, which sort key strategy is most effective?

Accepted Answer

COMPOUND sort key with timestamp as the leading column

Answer

A COMPOUND sort key with timestamp leading allows Redshift to skip large blocks of data during time-range queries, greatly reducing scan cost.

Question 5

A data modeler needs to represent many-to-many relationships in a Redshift star schema. What is the recommended approach?

Accepted Answer

Create a bridge table between the fact table and the dimension

Answer

A bridge (or junction) table resolves many-to-many relationships without duplicating fact rows or denormalizing dimension data.

Data Warehousing on AWS Training Practice Test