Data Warehousing on AWS Training Data Warehousing on AWS: Data Modeling 5 — Questions and Answers
Question 1: Which Redshift system view would a DBA query to identify tables with significant amounts of deleted (ghost) rows that have not yet been reclaimed?
- SVV_TABLE_INFO (Correct answer)
- STL_LOAD_ERRORS
- SVL_QUERY_SUMMARY
- STV_BLOCKLIST
Correct answer: SVV_TABLE_INFO
SVV_TABLE_INFO exposes the 'tbl_rows', 'skew_rows', and 'pct_used' metrics including deleted row counts that indicate the need for a VACUUM DELETE operation.
Question 2: A data modeler is designing an aggregate fact table for monthly sales summaries. Which grain should this aggregate table capture?
- Individual transaction level
- Daily customer-product level
- Monthly product-region level (Correct answer)
- Weekly promotion-channel level
Correct answer: Monthly product-region level
The aggregate grain should match the most common summary query pattern; monthly product-region aligns with typical monthly sales reporting needs.
Question 3: In a Redshift data warehouse, what is the key benefit of defining primary key and foreign key constraints even though Redshift does not enforce them?
- They automatically create indexes for faster lookups
- The query optimizer uses them as hints to generate more efficient query plans (Correct answer)
- They prevent duplicate rows from being loaded
- They enable automatic referential integrity checks during COPY
Correct answer: The query optimizer uses them as hints to generate more efficient query plans
Redshift's query planner uses declared PK/FK constraints to eliminate redundant joins and optimize execution plans, even without enforcement.
Question 4: When should you use an INTERLEAVED sort key instead of a COMPOUND sort key in Redshift?
- When queries always filter on the leading sort key column
- When the table is loaded in strict timestamp order
- When multiple columns are queried with roughly equal frequency as filter predicates (Correct answer)
- When the table has fewer than one million rows
Correct answer: When multiple columns are queried with roughly equal frequency as filter predicates
INTERLEAVED sort keys distribute equal weight across all key columns, benefiting workloads where different queries filter on different column combinations.
Question 5: Which data modeling pattern best supports incremental ETL loads into a Redshift fact table while avoiding full table scans for deduplication?
- Truncate and reload the entire fact table nightly
- Use a staging table with UPSERT (DELETE + INSERT) keyed on a natural business key (Correct answer)
- Append all records and deduplicate with a nightly VACUUM
- Use Redshift Spectrum to query raw S3 files for each report
Correct answer: Use a staging table with UPSERT (DELETE + INSERT) keyed on a natural business key
Loading into a staging table and then performing a targeted DELETE on matching keys followed by INSERT allows efficient incremental updates without full table scans.
Question 6: A sales analysis query joins a 500-million-row fact table (DISTSTYLE KEY on customer_id) with a customer dimension table. The join is slow. The customer dimension has 2 million rows. What change most improves performance?
- Change the fact table to DISTSTYLE EVEN
- Change the customer dimension to DISTSTYLE ALL (Correct answer)
- Add an INTERLEAVED sort key to the fact table
- Increase the number of Redshift nodes
Correct answer: Change the customer dimension to DISTSTYLE ALL
Setting the dimension table to DISTSTYLE ALL places a copy on every node, eliminating cross-node data movement when joining with the distributed fact table.
Question 7: What is the purpose of a Role-Playing Dimension in dimensional modeling?
- A single dimension table referenced multiple times in the same fact table under different aliases (Correct answer)
- A dimension that changes its schema based on user role permissions
- A bridge table linking two fact tables through a shared dimension
- A conformed dimension shared between two different data marts
Correct answer: A single dimension table referenced multiple times in the same fact table under different aliases
A role-playing dimension is one physical dimension table that appears multiple times in a fact table with different foreign keys, such as a Date dimension used for order_date, ship_date, and return_date.
Data Warehousing on AWS Training Data Warehousing on AWS: Data Modeling 5
Which Redshift system view would a DBA query to identify tables with significant amounts of deleted (ghost) rows that have not yet been reclaimed?