Give heavy ETL and dashboard queries separate compute, so they stop competing for the same resources. Each platform has its own way to do it, and the idea is the same: different workloads, different pools.
Snowflake
Create separate virtual warehouses for each workload, such as ETL_WH, BI_WH and DS_WH, each with its own size and auto-suspend settings. They read the same tables without interfering. Make the BI warehouse multi-cluster if many users query at once, so it scales out instead of queueing. Resource monitors cap the credits of each warehouse.
BigQuery
Use editions with reservations. Create one reservation for ETL and another for BI, and assign projects to them. Each reservation has its own baseline and autoscaling slots, so a big transformation cannot take all the slots from dashboard queries. With on-demand pricing, separate projects each draw from their own slot allowance, but there is less control.
Databricks
Use separate compute for different work: job compute (or serverless jobs) for pipelines, and separate SQL warehouses for BI and for ad-hoc analysts. SQL warehouses can scale out with more clusters for concurrency. Cluster policies keep each team's compute within limits.
Other levers
- Schedule heavy jobs when users are not online. A large month-end job at 2 a.m. does not bother anyone.
- Set timeouts, so a runaway query gets stopped, and statement limits per role.
- Queues and priorities, where the platform supports them, so critical dashboards go first.
- Limit by role: analysts get a small warehouse by default, and can ask for a bigger one.
- Cache and summary tables, so dashboards do not run heavy queries live.
Check that it works
Track queue time, query duration of the BI workload while ETL runs, and cost by workload. If dashboards still slow down, look at shared bottlenecks, such as the same source tables being rewritten, or locks in an OLTP source.
State the principle clearly: isolate by workload type and importance, then control cost with limits and tags.