Data Warehousing on AWS Training Data Warehousing on AWS: Data Modeling 5

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?

Accepted Answer

SVV_TABLE_INFO

Answer

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?

Accepted Answer

Monthly product-region level

Answer

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?

Accepted Answer

The query optimizer uses them as hints to generate more efficient query plans

Answer

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?

Accepted Answer

When multiple columns are queried with roughly equal frequency as filter predicates

Answer

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?

Accepted Answer

Use a staging table with UPSERT (DELETE + INSERT) keyed on a natural business key

Answer

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.

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 5