Who this is for
BI Analysts and Analytics Engineers who write SQL for dashboards, reports, and ad‑hoc analysis and want predictable query speed and lower warehouse costs.
Prerequisites
- Basic SQL: SELECT, WHERE, JOIN, GROUP BY
- Understanding of fact/dimension (star) schemas
- Know the difference between row‑store OLTP databases and columnar/cloud warehouses at a high level
Why this matters
- Faster dashboards: Sub‑second or a few seconds vs minutes
- Lower compute spend: Less data scanned and fewer retries
- Happier stakeholders: Stable SLAs and fewer flaky timeouts
- Scale: Queries keep working as data grows
Real tasks you’ll handle
- Diagnose a slow sales dashboard and suggest an index or clustering key
- Review a teammate’s query and recommend a better access path
- Validate improvements with an EXPLAIN plan and timing comparison
Concept explained simply
Simple idea
An index is like a sorted phonebook for one or more columns so the database can jump to matching rows instead of reading the whole table. For columnar/cloud warehouses, you’ll often rely more on partitioning and clustering/sort keys, which help the engine skip big chunks of data.
Mental model
- BI queries usually do three things: filter (WHERE), join (JOIN), aggregate (GROUP BY/ORDER BY).
- Your job: give the engine a fast access path for filters and joins, and an efficient layout for scans and aggregates.
- Row‑store (e.g., transactional DBs): B‑tree or similar indexes on selective filters and join keys.
- Columnar/cloud warehouses: partition by coarse filters (often date), cluster/sort by common filter/join columns; consider materialized views for heavy aggregates.
Key terms in plain language
- Composite index: an index on multiple columns; order matters (leading column rule).
- Covering index: index includes all columns the query needs, avoiding extra lookups.
- Filtered/partial index: index only a subset of rows (e.g., status = 'OPEN'). Works best when the subset is small.
- Partitioning: physically separates data (e.g., by day/month) so other partitions can be skipped.
- Clustering/Sort key: within partitions, keeps rows with similar values near each other for more pruning.
Worked examples
Example 1 — Row‑store: last 30 days sales by store
Query pattern:
SELECT s.store_id, SUM(f.amount) AS revenue
FROM fact_sales f
JOIN dim_store s ON s.store_id = f.store_id
WHERE f.sale_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY s.store_id;
- Goal: Quick filter by recent date + fast join to store.
- Row‑store strategy: Composite index on fact_sales(sale_date, store_id). Make sure dim_store(store_id) is indexed/primary key.
- Why: Leading column sale_date supports the range filter; store_id next helps the join and grouping.
Validation steps
- Run EXPLAIN before and after. Look for "index seek/range scan" on fact_sales instead of full/seq scan.
- Compare timing on realistic data volumes.
Example 2 — Columnar warehouse: monthly revenue dashboard
Query pattern:
SELECT DATE_TRUNC('month', sale_date) AS m, SUM(amount) AS revenue
FROM fact_sales
WHERE sale_date BETWEEN DATE_TRUNC('year', CURRENT_DATE) AND CURRENT_DATE
GROUP BY 1
ORDER BY 1;
- Warehouse strategy: Partition by sale_date (day or month). Cluster/sort by sale_date (and possibly customer_id if often filtered).
- Why: Prunes old partitions, then prunes blocks within partitions. Aggregates scan fewer files/blocks.
Validation steps
- Compare scanned bytes/partitions before vs after.
- Confirm runtimes drop and become stable over time.
Example 3 — Targeted lookup: top 100 newest orders for one brand
Query pattern:
SELECT order_id, sale_date, amount
FROM fact_sales
WHERE brand = 'Acme'
ORDER BY sale_date DESC
LIMIT 100;
- Row‑store: Index on (brand, sale_date DESC). It supports both the filter and the sort, enabling a quick top‑N.
- Warehouse: Consider clustering by brand, sale_date; if this is a frequent path, a materialized view ordered by sale_date for the brand can help.
Validation steps
- Check EXPLAIN for an index‑backed top‑N plan (no full sort).
- Time the query and ensure stable latency.
Hands‑on exercises
Do these in any SQL environment you have. Focus on the choice and justification. Mirror solutions are below each exercise.
Exercise 1 — Time‑windowed sales with store join
Schema and query:
-- Tables
fact_sales(sale_id, sale_date, store_id, product_id, customer_id, qty, amount)
dim_store(store_id, store_name, region)
-- Query
SELECT d.region, SUM(f.amount) AS revenue
FROM fact_sales f
JOIN dim_store d ON d.store_id = f.store_id
WHERE f.sale_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY d.region;
Task: Propose indexing or partitioning/clustering to speed this up in both a row‑store and a columnar warehouse. Write the DDL you’d use and explain why.
Show solution
Row‑store:
CREATE INDEX idx_fact_sales_date_store ON fact_sales(sale_date, store_id);
-- Ensure dim_store PK/index on store_id exists
- Leading sale_date supports 90‑day range; store_id supports the join and grouping.
Columnar warehouse:
- Partition fact_sales by sale_date (day or month).
- Cluster/sort by sale_date, optionally store_id if commonly filtered or grouped.
Validate via EXPLAIN and scanned bytes/partitions.
Exercise 2 — Top‑N newest rows for a brand
Schema and query:
-- Table
fact_sales(order_id, sale_date, brand, amount, customer_id)
-- Query
SELECT order_id, sale_date, amount
FROM fact_sales
WHERE brand = 'Acme'
ORDER BY sale_date DESC
LIMIT 100;
Task: Propose an index (row‑store) or clustering (warehouse) that avoids a full sort and minimizes scanned data. Include verification steps.
Show solution
Row‑store:
CREATE INDEX idx_sales_brand_date_desc ON fact_sales(brand, sale_date DESC);
- Filter on brand, then read the newest sale_date first to satisfy LIMIT 100 without sorting the whole set.
Warehouse:
- Cluster by brand, sale_date (descending if supported by the platform) to keep recent brand rows co‑located.
- If this is a key workload, consider a materialized view filtered on brand with order by sale_date.
Verify: EXPLAIN shows index/cluster usage and no large global sort; runtime is stable.
Practice checklist
Common mistakes (and how to self‑check)
- Over‑indexing: Many indexes slow writes and bloat storage. Self‑check: Count indexes per table; remove unused ones.
- Wrong column order: Composite index not aligned with WHERE clauses. Self‑check: Put the most selective, consistently used filter first.
- Indexing low‑selectivity columns (e.g., boolean flags). Self‑check: Check distinct values and distribution.
- Ignoring join keys: Fact foreign keys not indexed in row‑stores. Self‑check: Ensure FK columns used in JOINs have supporting indexes (in row‑stores).
- Using row‑store habits in warehouses: Creating many secondary indexes where the engine prefers partitioning/clustering. Self‑check: Prefer partition/pruning strategies.
Practical projects
- Dashboard rescue: Pick a slow dashboard, log its SQL, design an index/partition/cluster change, and measure before/after latency and scanned bytes.
- Access path review: For three top queries, draw the WHERE/JOIN/ORDER BY columns and propose minimal structures to support them.
- Materialized summary: Build a daily revenue materialized view for a common report; schedule refresh and compare cost/latency.
Learning path
- Read query plans: Identify scans vs seeks, joins, and pruning.
- Index/cluster design: Start with most frequent filters and joins.
- Storage layout: Add partitioning where time filters dominate.
- Caching and materialization: Use materialized views for heavy, repeated aggregates.
Next steps
- Apply one improvement in your environment this week and capture metrics.
- Create a small checklist for teammates to request index/partition changes safely.
- Prepare a 5‑slide share‑out: problem, change, validation, results, next ideas.
Mini challenge
You have a 12‑month revenue dashboard with filters: date range, region, and product_category. Propose a storage/indexing design for both a row‑store and a columnar warehouse. Write 3–5 bullets each and one risk to watch. Compare how each design prunes data.
Ready to test yourself?
Take the quick test below. Everyone can take it for free; only logged‑in users will see saved progress.