Data Warehousing on AWS Training Data Warehousing on AWS: Data Modeling 2 — Questions and Answers
Question 1: Which Redshift distribution style minimizes data movement for large fact tables joined with large dimension tables?
- EVEN
- ALL
- KEY (Correct answer)
- AUTO
Correct answer: KEY
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?
- version_number and is_active
- effective_start_date, effective_end_date, and is_current (Correct answer)
- created_at and updated_at
- surrogate_key and natural_key only
Correct answer: effective_start_date, effective_end_date, and is_current
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?
- Faster query performance due to fewer joins
- Reduced storage through normalized dimension tables (Correct answer)
- Simpler ETL pipeline design
- Better compatibility with Redshift Spectrum
Correct answer: Reduced storage through normalized dimension tables
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?
- INTERLEAVED sort key on sensor_id and timestamp
- COMPOUND sort key with timestamp as the leading column (Correct answer)
- No sort key to maximize write throughput
- EVEN distribution with no sort key
Correct answer: COMPOUND sort key with timestamp as the leading column
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?
- Store arrays in a single dimension column
- Create a bridge table between the fact table and the dimension (Correct answer)
- Duplicate fact table rows for each dimension member
- Use a snowflake sub-dimension exclusively
Correct answer: Create a bridge table between the fact table and the dimension
A bridge (or junction) table resolves many-to-many relationships without duplicating fact rows or denormalizing dimension data.
Question 6: Which Redshift feature allows you to query data in Amazon S3 without loading it into Redshift tables?
- Redshift Materialized Views
- Redshift Federated Query
- Redshift Spectrum (Correct answer)
- Redshift Data Sharing
Correct answer: Redshift Spectrum
Redshift Spectrum enables SQL queries directly against data stored in S3 using external tables, without ingesting the data.
Question 7: In dimensional modeling, what distinguishes a degenerate dimension from a regular dimension?
- It exists only in the dimension table with no attributes
- It is stored in the fact table with no corresponding dimension table (Correct answer)
- It spans multiple fact tables
- It contains only slowly changing attributes
Correct answer: It is stored in the fact table with no corresponding dimension table
A degenerate dimension is a dimension key (like an order number) that lives in the fact table because it has no descriptive attributes warranting a separate dimension table.
Data Warehousing on AWS Training Data Warehousing on AWS: Data Modeling 2
Which Redshift distribution style minimizes data movement for large fact tables joined with large dimension tables?