skip to content

Dashboard queries on a shared analytical warehouse jump from 2s to 40s whenever the nightly ETL job runs. How would you isolate the two workloads?

level: seniorimportance: must knowfreq 72%

answer

  1. split the 40 seconds into wait and execution
  2. priority governs who starts, not who is running
  3. storage is shared; compute need not be
  4. the second pool starts cold
  5. sometimes the fix is a schedule change

basics

~20 s

First confirm the slowdown is queue wait or resource contention, not a plan change. Then move ETL onto its own compute pool over the same shared storage — the only fix that removes interference outright. Priorities and memory caps reduce it but never eliminate it.

solid answer

~50 s

Start by decomposing the 40 seconds: how much is queue wait, how much is execution, and did the plan change? If wait grew while execution stayed flat, it is admission contention; if execution grew, the ETL is stealing memory and I/O from running dashboard queries. Then work up a ladder of fixes. **Priority or a reserved lane** for dashboards is cheapest and requires no new compute, but a large ETL query already holding memory still degrades neighbours. **Capping the ETL's grant and concurrency** bounds the damage. **A separate compute pool for ETL, reading and writing the same shared storage**, is the only option that removes interference completely — modern separation of storage and compute makes this a configuration change, not a data copy. Non-infrastructure fixes matter too: shift ETL out of business hours, and serve dashboards from pre-aggregated tables so they never scan the raw facts.

code

text · 7 lines
text
# illustrative pool layout for one warehouse over shared storage
pool: bi_interactive   size: small   concurrency: 20  grant: small   timeout: 5m
pool: etl_batch        size: large   concurrency: 3   grant: large   timeout: 4h
pool: adhoc_analysts   size: medium  concurrency: 8   grant: medium  timeout: 30m

# both pools read and write the same tables in shared storage:
# no data copy, no replication lag, no shared memory or CPU

go deeper

for a junior

Know that heavy batch jobs and interactive dashboards competing for one cluster is a real and common problem, and that giving each its own compute is the standard remedy.

for a middle

Explain the mechanism: shared memory grants and shared admission slots, why the dashboard's wait and execution time both move, and why shared storage lets two compute pools see identical data.

for a senior

Demonstrate the diagnosis first — split elapsed time into wait, compile and execution before touching anything — then walk the ladder from priority to caps to a separate pool, naming the cost of each rung and how you would verify the result.

for a principal

Own the policy: which workloads are entitled to guaranteed isolation, what that entitlement costs per year, and when reshaping the workload through pre-aggregation or scheduling is the better investment than more compute.

## Diagnose before you isolate The symptom — same query, same data, ten to twenty times slower during a known window — is almost certainly interference, but confirm which kind, because the fixes differ. Break the elapsed time into **queue wait**, **compile/plan time** and **execution time**, all of which a warehouse's query-history views expose. - **Wait grew, execution flat.** The dashboard query is not being admitted. This is an admission-control problem: the ETL is occupying the slots. - **Execution grew, wait flat.** The dashboard query is running but starved: smaller memory grant, spilling, or competing for the same scan and network bandwidth as the ETL's shuffles. - **Plan changed.** Occasionally the ETL's writes shift statistics or invalidate caches, and the dashboard picks a different, worse plan. That is not an isolation problem at all, and isolating will not fix it. Also check whether the dashboard's benefit from **cached results or warm local caches** simply disappeared: an ETL that rewrites the underlying table invalidates result caches and evicts warm data from local SSD caches, and the resulting cold read can account for a large slice of the regression on its own. ## The isolation ladder Work up it, because each rung costs more. **1. Priority and reserved lanes.** Put dashboards in their own admission class with reserved concurrency slots and give it precedence. Cost: near zero, no new compute. Limit: a query that has *already* been admitted keeps its memory and its share of I/O until it finishes. Priority controls who starts, not who is running, so a large in-flight ETL query still hurts. Preemption is rare and expensive in analytical engines, since killing a half-finished shuffle throws away real work. **2. Bound the offender.** Cap the ETL class's concurrency and per-query memory grant so it can never take more than a fixed share, and add a runtime limit that flags a job that has gone rogue. This makes the bad case bounded and predictable rather than unbounded, but the dashboards still pay a tax. **3. A separate compute pool.** Give ETL its own cluster reading and writing the same storage. Because analytical platforms separate storage from compute, this is a routing change, not a data copy: both pools see the same tables, and there is no replication lag to reason about. Interference disappears because there is no shared memory, no shared CPU and no shared admission queue. This is the answer interviewers are usually looking for, and it is the only one that removes the problem rather than reducing it. Its costs are real and you should name them: you now pay for two pools; the second pool starts with **cold caches**, so the first queries after it spins up are slower; and you have two things to size, monitor and budget rather than one. Idle-suspend policies keep the bill close to usage for bursty pools. **4. Change the workload, not the plumbing.** Two moves often beat any amount of isolation: - **Reschedule.** If ETL runs at 09:00 because someone set it there years ago, moving it outside the dashboard's active hours costs nothing. - **Pre-aggregate.** Dashboards that scan raw fact tables are expensive by construction. Materialising the aggregate the dashboard actually needs cuts its cost by orders of magnitude, which makes it far more tolerant of a busy cluster — and reduces the total work the platform does. ## Choosing between them The decision hinges on how strict the dashboard SLA is and how bursty the ETL is. - Predictable ETL, tolerant dashboards: priority plus caps is enough, and cheapest. - Strict interactive SLA (an executive dashboard, an embedded customer-facing report): separate pool. Do not try to win a strict latency SLA with priority settings on a shared cluster; you will be tuning it forever. - Very spiky ETL: separate pool that suspends when idle, so isolation costs little more than the compute the job actually uses. ## Verify with a measurement, not a feeling Whatever you change, prove it: compare the dashboard's p95 **queue wait and execution time inside the ETL window against outside it**. Successful isolation makes those two distributions converge. If they still diverge after splitting the pools, the remaining difference is cache coldness or a plan change — and you have learned that the original diagnosis was incomplete.

  • Why doesn't giving dashboards the highest priority on the shared cluster fully solve this?
    Priority governs admission, not eviction. An ETL query that is already running holds its memory grant, its CPU threads and its share of disk and network bandwidth until it completes, and analytical engines rarely preempt because discarding a half-finished shuffle wastes substantial work. So a high-priority dashboard query starts promptly but then executes in a starved, contended environment. Priority reduces queue wait; it does not restore execution time.
  • What do you lose by moving ETL to a separate compute pool?
    You pay for a second pool, and its caches start cold, so the first queries after it resumes read from remote storage and run slower. You also gain a second thing to size, monitor and budget. What you do not lose is data consistency: both pools read and write the same shared storage, so there is no copy and no replication lag. Idle auto-suspend keeps the cost of a bursty pool close to its actual usage.
  • How would you prove the isolation worked?
    Compare the dashboard's p95 queue wait and p95 execution time inside the ETL window against the same metrics outside it, before and after the change. Successful isolation makes the two distributions converge. If execution time still rises inside the window after the pools are split, the residual cause is not contention — most likely cold caches after the ETL rewrote the table, or a plan change driven by refreshed statistics.

Two teams sharing one kitchen can agree on priority rules, but the caterer prepping a banquet still occupies every hob. Giving the caterer a second kitchen with access to the same pantry is the only change that lets both cook at full speed.

saying these in an interview costs you the question

  • Jumps to adding nodes without decomposing wait versus execution
  • Believes query priority also throttles queries already running
  • Thinks a second compute pool requires copying or replicating the data
  • Ignores cheaper fixes like rescheduling ETL or pre-aggregating
  • Assumes the dashboard slowdown must be contention, never a plan change

context

resolving links…