Data Warehousing on AWS Training Data Warehousing on AWS: Workload Management 4 — Questions and Answers
Question 1: How does enabling 'Enable short query acceleration' affect the WLM default queue in Redshift?
- It merges the default queue with the first user-defined queue
- SQA creates a separate fast lane outside of standard WLM queues for eligible short queries (Correct answer)
- It reduces the default queue's memory allocation by 50%
- It disables the default queue entirely
Correct answer: SQA creates a separate fast lane outside of standard WLM queues for eligible short queries
SQA operates independently of configured WLM queues by routing predicted short-running queries to a dedicated acceleration lane, leaving standard queues for longer workloads.
Question 2: Which parameter group setting must be modified to apply WLM configuration changes to an Amazon Redshift cluster?
- Cluster security group
- Cluster parameter group (Correct answer)
- Cluster subnet group
- IAM instance profile
Correct answer: Cluster parameter group
WLM configuration is stored in the cluster parameter group; changes to the parameter group are applied to associated clusters (requiring a reboot for static parameters).
Question 3: What is the recommended approach when you need a dedicated queue for ETL jobs that run during off-peak hours in Redshift?
- Create a separate Redshift cluster exclusively for ETL
- Configure a WLM queue with user group routing mapped to the ETL database users (Correct answer)
- Use Amazon EMR instead of Redshift for ETL workloads
- Schedule ETL queries using Amazon EventBridge with no WLM changes
Correct answer: Configure a WLM queue with user group routing mapped to the ETL database users
Creating a dedicated WLM queue with user group routing for ETL database users ensures ETL jobs are isolated from interactive query workloads in the same cluster.
Question 4: Which Redshift system view shows currently queued and executing queries along with their WLM state?
- STV_WLM_SERVICE_CLASS_STATE (Correct answer)
- STV_RECENTS
- SVL_QLOG
- STL_QUERY
Correct answer: STV_WLM_SERVICE_CLASS_STATE
STV_WLM_SERVICE_CLASS_STATE provides a real-time snapshot of each WLM service class (queue) including current concurrency, queued queries, and resource usage.
Question 5: In Redshift WLM, what is a 'service class'?
- An IAM role that grants access to specific Redshift tables
- The internal representation of a WLM queue including its resource allocations (Correct answer)
- A tiered pricing model for Redshift Reserved Nodes
- A CloudWatch alarm category for Redshift performance metrics
Correct answer: The internal representation of a WLM queue including its resource allocations
In Redshift internals, each WLM queue is referred to as a service class, identified by a service class ID that appears in system tables like STV_WLM_SERVICE_CLASS_STATE.
Question 6: When applying a new WLM configuration that changes only dynamic properties in Redshift, what is required?
- A full cluster reboot
- No reboot is needed; changes take effect immediately (Correct answer)
- A manual vacuum and analyze operation
- Terminating all active sessions before applying
Correct answer: No reboot is needed; changes take effect immediately
Dynamic WLM properties such as concurrency level and memory allocation can be applied without rebooting the cluster, taking effect for new queries immediately.
Question 7: What is the default behavior for queries submitted by superusers in Redshift WLM queue routing?
- Superuser queries are always routed to the queue with the most free slots
- Superuser queries are automatically routed to the dedicated superuser queue (Correct answer)
- Superuser queries bypass WLM entirely and run at full cluster capacity
- Superuser queries are subject to the same routing rules as regular users
Correct answer: Superuser queries are automatically routed to the dedicated superuser queue
Redshift maintains a reserved superuser queue that superuser queries are automatically routed to, ensuring administrative queries are never blocked by regular workload queues.
Data Warehousing on AWS Training Data Warehousing on AWS: Workload Management 4
How does enabling 'Enable short query acceleration' affect the WLM default queue in Redshift?