Why this matters
Analytics Engineers keep BI dashboards fast during peak hours while protecting pipelines and controlling cost. You will often be asked to:
- Make morning dashboards load under 5–10 seconds, even with hundreds of concurrent users.
- Prevent ELT/ETL jobs from slowing BI, and vice versa.
- Right-size compute and auto-scaling to handle bursts without overspending.
- Design aggregates and caching so common queries barely touch raw, huge tables.
Concept explained simply
Concurrency is how many queries your warehouse can run at the same time. BI workloads are bursty: a stand-up meeting, a product launch, or month-end reporting can create spikes. When incoming queries exceed capacity, they queue and users wait.
Three levers determine experience:
- Capacity: parallelism (clusters/slots), compute size, and concurrency limits.
- Efficiency: query design, pruning, aggregation, and caching.
- Scheduling: staggering extracts/refreshes and isolating workloads.
Mental model
Think of your warehouse as a highway:
- Lanes = parallelism (clusters/slots).
- Car size = query weight (scan size/complexity).
- Speed limit = SLO for dashboard latency (for example, p95 < 5s).
- On-ramp lights = concurrency controls and queues.
You can add lanes (scale out), make cars smaller (aggregates, pruning), or meter the on-ramp (limits and schedules).
Core techniques that actually work
1) Separate workloads (strong isolation)
- Use separate compute pools/virtual warehouses/queues for BI vs ELT/ML.
- Give BI a predictable lane so pipelines cannot starve dashboards.
- Set clear SLOs: for example, p95 dashboard query < 5s during 8–11 AM.
2) Scale smartly for bursts
- Enable auto-scaling or multi-cluster: min low (cost), max high (burst tolerance).
- Use queue thresholds to trigger scale-up; scale down when idle.
- Increase cluster count for many small queries; increase size for a few heavy queries.
3) Concurrency controls and limits
- Cap concurrent queries per user/role to prevent thundering herds.
- Limit very expensive queries (timeouts, scan caps, query governor rules).
- Prefer short-running BI queries to keep the highway flowing.
4) Caching and precomputation
- Result/metadata caching for repeated queries that hit unchanged data.
- Materialized views / persistent derived tables for common, expensive joins.
- BI tool caching: tune TTLs to balance freshness vs speed.
5) Aggregate tables (semantic accelerators)
- Create daily/weekly aggregates at the grain your dashboards need.
- Route dashboard queries to aggregates first; fall back to detail only when required.
- Refresh incrementally (for example, last N days hourly; older data daily).
6) Partitioning, clustering, and pruning
- Partition by date (and sometimes other high-cardinality dimensions) to prune scans.
- Use clustering/sort keys to co-locate common filters and speed range scans.
- Select only needed columns; avoid SELECT *.
7) Scheduling and staggering
- Offset big extracts/refreshes away from peak usage windows by 15–30 minutes.
- Use backoff/retry windows to avoid synchronized spikes.
- Refresh different dashboards at different minutes to avoid pile-ups.
8) Observability and SLOs
- Track p50/p95/p99 query latency, queue time, and utilization by workload.
- Set error budgets and adjust scale/aggregates when SLOs are missed.
- Use dashboards to watch saturation (utilization > 70–80% during peaks).
Worked examples
Example 1 — Size for a 10-minute BI burst
Scenario: 300 users open a dashboard in 10 minutes. Each load fires 8 queries; 35% of users apply one filter adding 3 queries. Expected result cache hit rate 25%. Target p95 query latency: 3s. Max concurrent queries per cluster: 16.
- Total queries = 300×8 + 0.35×300×3 = 2400 + 315 = 2715.
- QPS over 10 minutes = 2715 / 600 ≈ 4.53 QPS.
- After cache (75% miss): 4.53 × 0.75 ≈ 3.39 QPS.
- Concurrency demand ≈ QPS × p95 ≈ 3.39 × 3 ≈ 10.2 queries in parallel.
- Headroom 30% → ≈ 13.3 → round to 14.
- Recommendation: 1 cluster can handle 16, but set multi-cluster min=1, max=2 with queue threshold to scale before saturation (for example, scale up if queue > 8). Keep a separate small warehouse for ELT.
Example 2 — Isolation rules to protect BI
Goal: BI p95 < 5s during 8–11 AM; nightly ELT occasionally overlaps.
- Create two pools: BI (interactive), ELT (batch).
- BI: smaller size, multi-cluster 1–3, strict max runtime (for example, 60s).
- ELT: larger size, 1 cluster, lower priority; throttle when BI latency rises above SLO.
- Result: BI remains responsive; ELT slows but completes later.
Example 3 — Rollup to accelerate dashboards
Dashboard asks: daily active users, orders, and revenue by country for 180 days. Raw table has billions of events.
- Create aggregate at grain: day, country.
- Columns: date_day, country, dau, orders, revenue, aau (average per active user).
- Refresh policy: hourly for last 2 days; daily for older partitions.
- Impact: query scans tens of thousands of rows instead of billions, reducing latency/cost dramatically.
Example 4 — Staggering to avoid synchronized spikes
Problem: Five dashboards all refresh at 09:00 causing timeouts.
- Offset refreshes: D1 08:52, D2 08:55, D3 08:58, D4 09:02, D5 09:05.
- Set exponential backoff on retries (for example, 1, 2, 4 minutes) to avoid new stampedes.
- Result: flatter load curve, reduced queue time.
Hands-on exercises
Try these to turn concepts into muscle memory.
Exercise 1 (ex1) — Plan BI concurrency and scaling
See the Exercises section below for full instructions and solution. Deliverables: QPS estimate, required concurrent queries, recommended min/max clusters, and queue thresholds.
Exercise 2 (ex2) — Design an aggregate + refresh policy
Define schema, refresh cadence, incremental strategy, and expected reduction factor vs raw data.
Checklist: did you cover the essentials?
- Separate BI from ELT with clear limits.
- Compute a simple QPS × p95 concurrency estimate.
- Choose scale-out vs scale-up appropriately.
- Add aggregates that match dashboard grain.
- Stagger refreshes; set cache TTLs with intent.
- Instrument p95 latency and queue time.
Common mistakes and how to self-check
- Single shared compute for everything: If BI p95 spikes when ELT runs, you need isolation.
- Scaling size when you need lanes: Many short queries benefit from more clusters, not just bigger ones.
- Skipping aggregates: If dashboards always group by day and country, roll them up.
- Unbounded concurrency: Letting one user fire hundreds of queries can starve others; enforce per-user limits.
- SELECT * everywhere: Increases scan sizes and latency; select only needed columns.
- Refreshes on the hour: Stagger schedules to avoid synchronized bursts.
Self-check prompts
- Is BI p95 below target during peak and overlapping jobs?
- What percent of queries hit aggregates/materialized views?
- Is queue time < 10–20% of total latency during peak?
- What is your cache hit rate and TTL policy?
Practical projects
- Build a BI burst playbook: forecast peak load, configure isolation, define SLOs, and document scaling rules.
- Aggregate accelerator: implement two aggregates for your top dashboards and measure before/after p95 and cost.
- Scheduler smoothing: stagger 10+ refreshes, add backoff, and show the resulting utilization chart.
Who this is for
- Analytics Engineers and BI Developers responsible for dashboard performance and warehouse efficiency.
- Data Engineers who share platform responsibility for ELT and BI compute.
Prerequisites
- Comfort with SQL and star-schema modeling.
- Basic understanding of your warehouse’s scaling model (slots/clusters/queues).
- Ability to schedule jobs and read performance metrics.
Learning path
- Start: Query performance foundations (pruning, joins, caching).
- Then: Workload isolation and auto-scaling configuration.
- Next: Aggregates/materialized views for common analytics patterns.
- Finally: Monitoring and SLO management with alerts.
Next steps
- Complete the exercises below and validate with the solutions.
- Take the quick test to check understanding. Everyone can take it; logged-in users get saved progress.
- Apply one practical project in your environment this week.
Mini challenge
In one page, propose a plan to cut your top dashboard’s p95 latency by 50% during peak. Include: isolation change, aggregate design, and scaling settings.
Quick Test
The Quick Test is available to everyone for free. If you log in, your progress will be saved.