Data Warehousing on AWS Training Data Warehousing on AWS: Data Modeling 4 — Questions and Answers
Question 1: In Redshift, what is the effect of choosing DISTSTYLE ALL for a dimension table?
- Each row is distributed to a different node using round-robin
- A full copy of the table is placed on every compute node (Correct answer)
- Rows are distributed based on a hash of the distribution key
- The table is stored only on the leader node
Correct answer: A full copy of the table is placed on every compute node
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?
- CSV
- JSON
- Parquet (Correct answer)
- XML
Correct answer: Parquet
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?
- To store facts with null measures temporarily during ETL
- To record events or conditions that have no numeric measures (Correct answer)
- To serve as a bridge between two conformed dimensions
- To hold pre-aggregated summary metrics
Correct answer: To record events or conditions that have no numeric measures
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?
- ANALYZE
- VACUUM SORT ONLY (Correct answer)
- ALTER TABLE REINDEX
- CLUSTER BY
Correct answer: VACUUM SORT ONLY
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?
- AWS Lambda
- Amazon EventBridge
- AWS Step Functions (Correct answer)
- Amazon Kinesis Data Streams
Correct answer: AWS Step Functions
AWS Step Functions coordinates multi-step workflows with branching, error handling, and retry logic, making it ideal for complex ETL pipeline orchestration.
Question 6: In a Redshift data model, what is the recommended approach for handling late-arriving facts?
- Reject late records and log them to an error table
- Insert with a NULL foreign key until the dimension record arrives
- Use a generic 'Unknown' or 'Not Yet Assigned' dimension member as a placeholder (Correct answer)
- Store late facts in S3 and reload the entire fact table nightly
Correct answer: Use a generic 'Unknown' or 'Not Yet Assigned' dimension member as a placeholder
Using a placeholder 'Unknown' dimension member allows late facts to be loaded immediately and updated later when the correct dimension record arrives.
Question 7: What does Redshift Federated Query allow that Redshift Spectrum does not?
- Querying data in S3 using external tables
- Querying live operational data in RDS or Aurora PostgreSQL/MySQL without ETL (Correct answer)
- Joining Redshift tables with Glue Data Catalog tables
- Automatic compression of query results
Correct answer: Querying live operational data in RDS or Aurora PostgreSQL/MySQL without ETL
Federated Query connects directly to live Amazon RDS and Aurora databases to query operational data in real time, whereas Spectrum only queries S3-based data.
Data Warehousing on AWS Training Data Warehousing on AWS: Data Modeling 4
In Redshift, what is the effect of choosing DISTSTYLE ALL for a dimension table?