Performance Tuning Basics

Why performance tuning matters for BI Analysts

Performance tuning turns slow, costly reports into fast, trustworthy insights. As a BI Analyst, you often sit between stakeholders and data platforms. Small query and modeling choices can cut costs, improve dashboard load times, and unlock interactive analysis at scale. Mastering the basics lets you deliver reliable dashboards, reduce warehouse spend, and support more users without constant firefighting.

Who this is for

• BI Analysts who build queries, models, and dashboards on cloud data warehouses or relational databases.
• Analysts who want to speed up dashboards, lower compute costs, or support more concurrent users.
• Power users of BI tools who work with extracts, live connections, and scheduled refreshes.

Prerequisites

• Basic SQL (SELECT, WHERE, GROUP BY, JOIN).
• Familiarity with your BI tool (e.g., how to create dashboards and data sources).
• Access to a data warehouse or sample database for practice.

Learning path (roadmap)

1. Quick wins: reduce scans and useless work

   Prune columns, filter early, limit rows, and avoid SELECT *. Use partitions/clusters and push filters to the source.

2. Pre-aggregate heavy queries

   Create summary tables or materialized views for common rollups (daily, weekly, product-level). Refresh incrementally.

3. Choose data access patterns

   Decide when to use extracts/caching vs. live connections. Balance freshness, concurrency, and cost.

4. Model for the engine

   Understand indexing/partitioning/sort-keys/clustering for your platform. Join on compatible keys and avoid row explosions.

5. Optimize dashboards

   Reduce number of queries, reuse sources, apply result caching, and defer non-critical widgets.

6. Monitor and iterate

   Track slow queries, bytes scanned, and cache hit rates. Fix the top offenders first.

Worked examples

-- Before: scans full table
SELECT product_id, SUM(net_sales)
FROM fact_sales
WHERE order_status = 'COMPLETED'
GROUP BY product_id;

-- After: filter partitions + columns only
SELECT product_id, SUM(net_sales) AS sales
FROM fact_sales
WHERE order_status = 'COMPLETED'
  AND order_date >= DATE '2025-01-01'  -- partition filter
  AND order_date <  DATE '2025-02-01'
GROUP BY product_id;

-- Before: expensive distinct
SELECT COUNT(DISTINCT user_id) FROM events WHERE event_date >= CURRENT_DATE - INTERVAL '30' DAY;

-- Option A: approximate distinct (engine-specific)
SELECT APPROX_COUNT_DISTINCT(user_id)
FROM events
WHERE event_date >= CURRENT_DATE - INTERVAL '30' DAY;

-- Option B: pre-aggregated daily uniques
-- Materialize daily unique users once, then sum
SELECT SUM(daily_uniques)
FROM daily_unique_users
WHERE event_date BETWEEN DATE '2025-01-01' AND DATE '2025-01-31';

-- Create a daily product sales summary
CREATE TABLE IF NOT EXISTS agg_sales_daily AS
SELECT order_date, product_id, SUM(net_sales) AS sales, SUM(quantity) AS qty
FROM fact_sales
GROUP BY order_date, product_id;

-- Dashboard queries now read agg_sales_daily instead of fact_sales
SELECT product_id, SUM(sales)
FROM agg_sales_daily
WHERE order_date BETWEEN DATE '2025-01-01' AND DATE '2025-01-31'
GROUP BY product_id;

CREATE MATERIALIZED VIEW mv_kpi_28d AS
SELECT user_id,
       DATE_TRUNC('day', event_time) AS d,
       COUNT(*) AS events
FROM events
WHERE event_time >= CURRENT_DATE - INTERVAL '35' DAY
GROUP BY 1,2;

-- KPI query
aSELECT SUM(events)
FROM mv_kpi_28d
WHERE d BETWEEN CURRENT_DATE - INTERVAL '28' DAY AND CURRENT_DATE;

-- Rebuild only the recent time window
DELETE FROM agg_sales_daily
WHERE order_date >= CURRENT_DATE - INTERVAL '7' DAY;

INSERT INTO agg_sales_daily
SELECT order_date, product_id, SUM(net_sales), SUM(quantity)
FROM fact_sales
WHERE order_date >= CURRENT_DATE - INTERVAL '7' DAY
GROUP BY 1,2;

Drills and quick exercises

• Replace 3 SELECT * queries with explicit column lists.
• Add a date filter to 2 dashboards so they default to the last 30 days.
• Identify 1 high-traffic query and cut scanned bytes by 50% using partitions or clustering.
• Create one pre-aggregation table and switch 2 charts to use it.
• Enable caching or extracts for a busy dashboard and measure load time before/after.

Common mistakes and how to debug

• Scanning full tables: Add partition filters and avoid SELECT *.
• Too many small queries per dashboard: Combine tiles or reuse a shared dataset.
• Over-joining: Ensure join keys are selective and correct; remove unnecessary dimensions.
• Ignoring engine features: Use clustering/sort keys where available; choose suitable file formats (columnar when possible).
• Neglecting refresh strategy: Schedule incremental refreshes; avoid rebuilding all history daily.

Mini project: Fast, cost-aware sales dashboard

Goal: Build a sales performance dashboard that loads in under 3 seconds for a 30-day window.

1. Map queries

   List all dashboard tiles and the queries they use. Identify heavy ones by scanned bytes/time.

2. Create pre-aggregations

   Build a daily rollup table for sales and quantity. Add incremental refresh (7-day window).

3. Tune filters and caching

   Default to last 30 days, enable BI cache or extracts with hourly refresh.

4. Optimize queries

   Remove SELECT *, add partition filters, and verify partition/cluster pruning in the profile.

5. Measure and iterate

   Record load time and cost before/after. Aim for >50% cost reduction and snappy loads.

Subskills

• Reducing Query Cost And Scan Size: Prune columns, filter early, use partition/cluster pruning, and avoid unnecessary DISTINCTs.
• Pre Aggregation Strategies: Build summary tables for common rollups; refresh incrementally.
• Materialized Views Concepts: Use engine-managed incremental updates for repeated, deterministic aggregations.
• Indexing Awareness For BI Workloads: Understand when indexes help (row stores) and when partitioning/sort/clustering is better (column stores).
• Caching And Extract Versus Live Decisions: Balance freshness, concurrency, and cost using BI caching or extracts where appropriate.
• Incremental Refresh Concepts: Refresh only the hot data window; protect history.
• Optimizing Dashboard Load Times: Reduce query count, reuse sources, defer heavy tiles, and cap time windows.
• Monitoring Slow Queries And Bottlenecks: Track scanned bytes, queue time, cache hits, and fix the top offenders first.

Next steps

• Apply one optimization per week to your most-used dashboards.
• Create a simple runbook: how to profile a query, when to pre-aggregate, and refresh rules.
• Share wins with your team to build a performance culture.