skip to content

How would you keep daytime BI responsive on a Redshift cluster that also runs continuous ETL?

level: principalimportance: should knowfreq 38%

answer

  1. nothing can be governed until it is distinguishable
  2. express the business ordering, don't hand-carve memory
  3. buy elasticity only where latency is worth money
  4. measure the split again after every change
  5. the escalation is separate compute, not a cleverer queue

basics

~20 s

Route the two workloads to separate WLM queues by user group, run automatic WLM with BI at a higher priority than ETL, enable concurrency scaling on the BI queue and monitoring rules on the ad-hoc one. When contention persists, move BI to its own compute via data sharing.

solid answer

~50 s

Start by separating the traffic: distinct database users or `query_group` labels so BI and ETL land in different WLM queues rather than both falling into the default one. Run **automatic WLM** and express the business ordering as priority — BI at `high` or `normal`, ETL at `low` — so ETL yields rather than being killed. Enable **concurrency scaling** on the BI queue so the 9am refresh burst goes to transient clusters, and leave it off for ETL, where waiting is free. Add **query monitoring rules** on the ad-hoc queue to abort runaway nested loops and cap external-table scan size. Then measure the queue-time-versus-execution-time split per queue and iterate. The escalation, when a single cluster genuinely cannot hold both, is physical separation: **data sharing** from an RA3 producer cluster to a consumer cluster or Serverless workgroup that BI queries, so the workloads no longer compete for the same compute at all.

code

json · 12 lines
json
[
  { "user_group": ["bi_readers"], "auto_wlm": true, "priority": "high",
    "concurrency_scaling": "auto" },
  { "user_group": ["analysts"],   "auto_wlm": true, "priority": "normal",
    "rules": [ { "rule_name": "adhoc_runaway",
                 "predicate": [
                   { "metric_name": "query_execution_time", "operator": ">", "value": 1800 } ],
                 "action": "abort" } ] },
  { "user_group": ["etl"],        "auto_wlm": true, "priority": "low" },
  { "auto_wlm": true, "priority": "normal" },
  { "short_query_queue": true }
]

go deeper

for a junior

Know that BI and ETL on one Redshift cluster compete for the same memory and slots, and that workload management queues are how you keep them apart.

for a middle

Explain the routing mechanism and the priority lever: separate users or query_group labels map to separate queues, and automatic WLM priority decides who yields when both are busy.

for a senior

Demonstrate the operating loop — measure queue versus execution time per queue, change one thing, re-measure — and know that monitoring rules belong on the ad-hoc queue rather than on scheduled loads.

for a principal

Own the escalation and its economics: when continuous contention means the answer is separate compute through data sharing rather than a better queue configuration, and how each option maps to cost and chargeback.

## Frame the problem before configuring anything A mixed BI-plus-ETL cluster is a scheduling problem with a business ordering attached: an executive's dashboard must return in seconds during business hours; a continuous load can be minutes late without anyone noticing. Every mechanism below is a way of expressing that ordering to Amazon Redshift. Start by writing down what you are actually protecting — a latency target for interactive queries, a freshness target for the loaded data — because without them there is no way to tell whether a configuration change helped. ## Step 1: make the workloads distinguishable WLM routes on the submitting user's group and on the session's `query_group` label. If ETL and BI both connect as the same user with no label, they cannot be separated no matter how many queues you create — and the most common finding on a struggling cluster is exactly that: everything is in the default queue. So the first change is organisational: separate database users (or groups) for the loader, for the BI tool's service account, and for human ad-hoc analysts, with a queue per class and the default queue as the catch-all for stragglers. Three classes is usually the right granularity: - **ETL/load** — high volume, latency-insensitive, memory hungry. - **BI/dashboard** — high concurrency, short queries, latency-critical. - **Ad-hoc/human** — unpredictable, occasionally pathological, must not damage the other two. ## Step 2: express the ordering with automatic WLM priority Under automatic WLM you no longer hand-partition memory; you set a `priority` per queue. Give BI a higher priority than ETL. The important property is that priority is *soft* — a low-priority ETL job is admitted later and gets a smaller share while running, but it still completes. That is exactly right for a load job: delaying it costs freshness, killing it costs a rerun. A manual WLM configuration can express something similar with fixed memory percentages, and there is one case where it is still the better answer: when the load path must never spill regardless of what BI is doing, a reserved memory percentage is a guarantee that priority is not. That is the honest tradeoff to name — manual buys a floor at the cost of continuous maintenance. ## Step 3: absorb the burst rather than sizing for it Dashboard traffic is spiky. Enable concurrency scaling on the BI queue so the morning refresh backlog is served by transient clusters, and cap the blast radius with `max_concurrency_scaling_clusters` plus a usage limit. Leave it off on the ETL queue: queueing there is harmless, and paying for transient compute to speed up a batch job that has hours of slack is waste. Also enable short query acceleration so a two-second dashboard query is not stuck behind a ten-minute report in the same queue. ## Step 4: contain the pathological case Human ad-hoc SQL will eventually contain a cross join. Query monitoring rules on the ad-hoc queue — a predicate combining `query_execution_time` with `nested_loop_join_row_count`, action `abort`, plus a `spectrum_scan_size_mb` cap if external tables are in play — turn a cluster-wide outage into one analyst getting an error. Roll them out with the log action first and calibrate the thresholds against `SVL_QUERY_METRICS_SUMMARY` rather than guessing. ## Step 5: measure, don't assume After every change, re-measure the queue-time-versus-execution-time split per queue from `STL_WLM_QUERY` or `SYS_QUERY_HISTORY`. Two outcomes matter. If BI wait time drops and ETL completes within its window, you are done. If BI *execution* time is the problem, none of this helps and the work is in the tables — distribution and sort keys, statistics, spill. It is common for a team to spend a month tuning WLM for a problem that was a missing `ANALYZE`. ## Step 6: know when configuration has run out WLM divides one cluster's resources; it cannot create capacity. The signals that you have reached the end are: the BI queue queues all day rather than in bursts, concurrency scaling runs continuously (you are now paying for a second cluster while pretending you have one), and ETL misses its window even at high priority. At that point the answer is physical separation. Redshift **data sharing** lets an RA3 producer cluster expose its data live to a consumer cluster or a Serverless workgroup with no copy and no ETL; BI queries the consumer, loads run on the producer, and the two workloads stop competing for compute entirely. Each side is then sized and billed for its own workload, which is also easier to charge back. ## What a principal-level answer sounds like The strong version is not a list of settings — it is an ordering with stated tradeoffs: separate the traffic so it can be governed at all, express the business priority softly so nothing is killed, buy elasticity only where latency is worth money, contain the pathological case, measure the split before and after, and hold the line that when contention is continuous rather than bursty the correct answer is separate compute, not a cleverer queue configuration.

  • Why prefer automatic WLM with priorities over manual queues with fixed memory percentages here?
    Priorities express the business ordering directly and softly — ETL yields instead of dying — while Redshift sizes memory per query so a heavy load statement is not forced into a slot someone guessed at last year. Manual percentages go stale as data and query shapes change, and each stale percentage is a spill waiting to happen. The exception is a load path that must never spill, where a reserved percentage is a guarantee priority cannot give.
  • What tells you a single cluster can no longer host both workloads?
    Continuous rather than bursty symptoms: the BI queue queues all day, concurrency scaling runs almost constantly so you are effectively paying for a second cluster already, and ETL misses its window even at elevated priority. That is the point to separate compute — a data-sharing consumer cluster or Serverless workgroup for BI — rather than to keep re-tuning queues.
  • How does data sharing between RA3 clusters change the isolation story?
    It gives each workload its own compute reading the same live data with no copy and no extra ETL: loads run on the producer, BI runs on a consumer cluster or Serverless workgroup. Contention for memory and slots disappears because the workloads are no longer on the same cluster, and each side is sized and billed for what it actually does, which also makes chargeback straightforward.
  • If BI is slow but its queue time is near zero, what does that mean for this whole design?
    That WLM is not the problem and none of the queue work will help. The time is going into execution, so the investigation moves to the tables and plans: disk spill, stale statistics, a distribution key that forces redistribution of a large fact table, a sort key that does not support the dashboard's filters. Teams regularly spend weeks tuning queues for what was a missing ANALYZE.

saying these in an interview costs you the question

  • Creating queues while all traffic still runs as one database user
  • Killing ETL to protect BI instead of demoting it
  • Enabling concurrency scaling everywhere without a cap
  • Treating WLM as a fix for slow-executing queries
  • Never revisiting the split after making changes

context

resolving links…