Data Warehousing on AWS Training Data Warehousing on AWS: Data Modeling 3 — Questions and Answers
Question 1: Which Redshift encoding type is best suited for a low-cardinality string column such as a status field with values like 'ACTIVE', 'INACTIVE', 'PENDING'?
- RAW
- ZSTD
- BYTEDICT (Correct answer)
- RUNLENGTH
Correct answer: BYTEDICT
BYTEDICT encoding creates a dictionary of distinct values and stores references, making it highly efficient for low-cardinality string columns.
Question 2: What does the Redshift VACUUM SORT ONLY command specifically address?
- Reclaims disk space from deleted rows
- Re-sorts unsorted rows added after the initial load without reclaiming space (Correct answer)
- Rebuilds all table statistics
- Removes duplicate rows from the table
Correct answer: Re-sorts unsorted rows added after the initial load without reclaiming space
VACUUM SORT ONLY re-sorts the unsorted region of a table without reclaiming space from deleted rows, improving query performance.
Question 3: A retail data warehouse fact table stores daily sales. An analyst frequently runs queries filtering by both product_category and sale_date. What interleaved sort key design should be used?
- INTERLEAVED SORTKEY (product_category, sale_date)
- COMPOUND SORTKEY (sale_date, product_category)
- INTERLEAVED SORTKEY (sale_date, product_category) (Correct answer)
- No sort key; use distribution key instead
Correct answer: INTERLEAVED SORTKEY (sale_date, product_category)
INTERLEAVED sort keys give equal weight to all columns, making INTERLEAVED SORTKEY (sale_date, product_category) efficient when filters alternate between columns.
Question 4: In AWS Glue Data Catalog, what role does it play in a data lake architecture on AWS?
- It serves as the compute engine for ETL transformations
- It provides a centralized metadata repository for tables in S3 and other sources (Correct answer)
- It encrypts data at rest in S3 buckets
- It automatically partitions data in Redshift tables
Correct answer: It provides a centralized metadata repository for tables in S3 and other sources
The AWS Glue Data Catalog is a managed metadata store that maintains table definitions, schemas, and partitions for use by Athena, Redshift Spectrum, and EMR.
Question 5: Which type of Redshift table is automatically refreshed and can be used to pre-aggregate common query patterns to improve performance?
- Staging table
- Materialized view (Correct answer)
- Temporary table
- External table
Correct answer: Materialized view
Materialized views in Redshift store pre-computed results and can be configured to auto-refresh, accelerating repetitive aggregation queries.
Question 6: What problem does column-level encoding (compression) in Redshift primarily solve?
- Reduces network latency between Redshift nodes
- Decreases I/O by reducing the amount of data read from disk per query (Correct answer)
- Improves concurrency by partitioning data across slices
- Eliminates the need for sort keys
Correct answer: Decreases I/O by reducing the amount of data read from disk per query
Column compression in Redshift reduces on-disk data size, so each query reads fewer bytes from storage, dramatically lowering I/O costs.
Question 7: A data engineer is designing a conformed dimension for 'Customer' shared across sales and support fact tables. What is the key requirement for this dimension to be truly conformed?
- It must use identical surrogate keys in both fact tables
- It must have the same attribute definitions and values across both subject areas (Correct answer)
- It must be replicated as separate tables for each fact table
- It must use natural keys instead of surrogate keys
Correct answer: It must have the same attribute definitions and values across both subject areas
A conformed dimension shares identical definitions, labels, and domain values across all fact tables so metrics from different subject areas can be integrated.
Data Warehousing on AWS Training Data Warehousing on AWS: Data Modeling 3
Which Redshift encoding type is best suited for a low-cardinality string column such as a status field with values like 'ACTIVE', 'INACTIVE', 'PENDING'?