Data Warehousing on AWS Training Data Warehousing on AWS: Data Modeling 4

Question 1

In Redshift, what is the effect of choosing DISTSTYLE ALL for a dimension table?

Accepted Answer

A full copy of the table is placed on every compute node

Answer

DISTSTYLE ALL copies the entire table to every compute node, ensuring joins with large fact tables never require cross-node data movement.

Question 2

When loading data into Redshift from S3 using the COPY command, which file format provides the best performance for columnar data?

Accepted Answer

Parquet

Answer

Parquet is a columnar format with built-in compression that Redshift can read in parallel, resulting in faster COPY operations and less I/O.

Question 3

What is the purpose of a factless fact table in dimensional modeling?

Accepted Answer

To record events or conditions that have no numeric measures

Answer

A factless fact table captures the occurrence of events (like student attendance or product promotions) where no quantitative measure naturally exists.

Question 4

A Redshift table has a poor sort key ratio reported by SVV_TABLE_INFO. What maintenance operation should be run?

Accepted Answer

VACUUM SORT ONLY

Answer

VACUUM SORT ONLY re-sorts the unsorted portion of the table, improving the sort key ratio and restoring query performance.

Question 5

Which AWS service is best suited for orchestrating a multi-step data pipeline that loads data from S3, transforms it with Glue, and loads it into Redshift?

Accepted Answer

AWS Step Functions

Answer

AWS Step Functions coordinates multi-step workflows with branching, error handling, and retry logic, making it ideal for complex ETL pipeline orchestration.

Data Warehousing on AWS Training Practice Test

Data Warehousing on AWS Training Practice Test

Data Warehousing on AWS Training Data Warehousing on AWS: Data Modeling 4