Data Warehousing on AWS Training Data Warehousing on AWS: Architecture Design 5 — Questions and Answers
Question 1: A company wants to apply column-level access controls in Redshift so analysts can query a table but cannot see the SSN column. How should this be implemented?
- Delete the SSN column before loading data
- Use Redshift column-level security with GRANT/REVOKE on specific columns (Correct answer)
- Create a separate table without the SSN column for analysts
- Encrypt the SSN column with a customer-managed KMS key
Correct answer: Use Redshift column-level security with GRANT/REVOKE on specific columns
Redshift supports column-level access control, allowing administrators to REVOKE SELECT on specific columns from certain roles or users.
Question 2: What is the purpose of running VACUUM in Amazon Redshift, and when should it be scheduled?
- To compress existing table data using new encoding algorithms
- To reclaim disk space and resort rows after DELETE and INSERT operations (Correct answer)
- To refresh materialized views with the latest base table data
- To update table statistics used by the query optimizer
Correct answer: To reclaim disk space and resort rows after DELETE and INSERT operations
VACUUM reclaims space from deleted rows and re-sorts rows that were inserted out of sort key order, maintaining query performance.
Question 3: In a Redshift multi-AZ deployment, what is the primary benefit for data warehouse architects?
- Lower query latency due to geographic distribution of compute nodes
- Automatic failover to a standby instance in another availability zone (Correct answer)
- Ability to run write workloads on both AZs simultaneously
- Reduced cost by sharing reserved instance pricing across zones
Correct answer: Automatic failover to a standby instance in another availability zone
Redshift Multi-AZ maintains a standby instance in a second AZ and automatically fails over to it if the primary AZ becomes unavailable.
Question 4: Which approach is recommended for loading data from multiple small S3 files into Redshift for optimal performance?
- Load files one at a time using INSERT INTO statements
- Combine files into one large file before running COPY
- Split data into multiple files matching the number of slices and use COPY (Correct answer)
- Use AWS DMS to stream records row by row into Redshift
Correct answer: Split data into multiple files matching the number of slices and use COPY
COPY loads data in parallel when there are multiple files; having one file per slice allows each slice to load simultaneously for maximum throughput.
Question 5: A company is designing their star schema and must decide the grain of their fact table. What does 'grain' refer to?
- The number of dimension tables connected to the fact table
- The level of detail or atomic unit represented by a single fact row (Correct answer)
- The compression algorithm applied to numeric measure columns
- The sort key chosen for the fact table distribution
Correct answer: The level of detail or atomic unit represented by a single fact row
The grain defines what one row in the fact table represents, such as one transaction line item or one daily sales summary.
Question 6: Which AWS service provides serverless, on-demand query capabilities for data stored in S3, complementing Redshift in a lake house architecture?
- Amazon EMR
- Amazon Athena (Correct answer)
- AWS Glue Studio
- Amazon QuickSight
Correct answer: Amazon Athena
Amazon Athena is a serverless interactive query service that queries data directly in S3 using standard SQL without infrastructure management.
Question 7: When designing a Redshift architecture for a global company, what is the recommended strategy for making data available across multiple AWS regions?
- Enable Redshift cross-region data sharing for live access
- Configure automated cross-region snapshots and restore in target regions as needed (Correct answer)
- Run separate COPY jobs from the same S3 bucket in each region
- Use Route 53 latency routing to point users to the nearest cluster
Correct answer: Configure automated cross-region snapshots and restore in target regions as needed
Automated cross-region snapshot copying allows a cluster in another region to be restored quickly for disaster recovery or regional analytics needs.
Data Warehousing on AWS Training Data Warehousing on AWS: Architecture Design 5
A company wants to apply column-level access controls in Redshift so analysts can query a table but cannot see the SSN column.
How should this be implemented?