Profile Representative BI/Ad-Hoc/ELT Workloads by Scan, Join, Shuffle, Spill, Queue, and Tail Latency

Learning outcomes

1. Realistic problem: the dashboard is fast alone and slow at 09:00

AtlasMart’s finance dashboard looks healthy in a developer’s single-query test, yet its 09:00 UTC p95 latency rises when an ELT rebuild and exploratory analyst query run concurrently. The business decision is whether certified revenue can be explored within its interactive latency objective without delaying the morning transformation. The data source is the same governed paid-line fact; the grain does not change. What changes is workload mix and resource contention.

Performance engineering is the controlled process of measuring representative work, locating resource-consuming mechanisms, changing one justified mechanism, and proving both correctness and workload-wide consequences. A fast answer with a different population, stale aggregate, hidden filter, or approximate metric is not an optimization of the same query—it is a different contract.

2. Define the signals before collecting them

3. Representative query corpus

A representative corpus must include materially different consumers. If you tune only the finance dashboard, you can accidentally regress ELT or ad-hoc exploration.

def bench(sql, runs=17, warmup=3):    for _ in range(warmup):        execute(sql)            # warm-cache disclosure    samples_ms = []    for _ in range(runs):        t0 = perf_counter()        rows = execute(sql)        samples_ms.append((perf_counter() - t0) * 1000)    return p50(samples_ms), p95(samples_ms), p99(samples_ms), checksum(rows)

4. Executed baseline and indexed evidence

The physical change added two date-leading composite indexes. Every before/after query returned the same SHA-256-normalized result checksum. The benchmark therefore demonstrates a performance change without changing output semantics on this fixture. It does not prove the same gain on another engine, data distribution, cache state, or concurrency level.

5. Read plan evidence, not folklore

SCAN fSEARCH p USING INTEGER PRIMARY KEY (rowid=?)USE TEMP B-TREE FOR GROUP BY

SEARCH f USING INDEX ix_fact_sales_date_customer (order_date>? AND order_date<?)SEARCH p USING INTEGER PRIMARY KEY (rowid=?)USE TEMP B-TREE FOR GROUP BY

The baseline performs a full fact scan. After indexing, SQLite reports a date-range index search. Both plans still use a temporary B-tree for grouping; the index does not eliminate every cost. Plan text is useful diagnostic evidence but SQLite explicitly warns that EXPLAIN QUERY PLAN output format can change between releases, so tests should assert query results and measurable behavior rather than parsing plan strings as a permanent API.

6. Controlled failure: optimize the one query that was already on your screen

A practitioner tests only POINT_HOT_PRODUCT_7D, sees a roughly 78% p95 reduction, and declares the warehouse “optimized.” That is incomplete: the protected workload also includes BI aggregates, ad-hoc exploration, and an ELT rebuild. The repair is to benchmark the corpus, preserve checksums, disclose warm/cold state, and inspect p95/p99—not merely one average.

7. Concurrency makes queueing visible

This is an explicit two-slot harness around independent SQLite connections. It proves that even unchanged query service time can produce high user-visible elapsed time when work waits for admission. It does not claim SQLite itself implements a two-slot warehouse queue.

8. Production judgment and bridge

Accept a performance change only after result semantics, security policy, freshness/history behavior, retry/replay behavior, and reconciliation still pass. Performance evidence must name the workload, dataset, engine/version, cache state, concurrency, physical layout, and percentile—not just “faster.” Treat any cost or latency number in this chapter as fixture-specific, not a universal target. Keep rollback simple: indexes/materializations can be removed, query rewrites reverted, and workload-pool assignments restored while the logical model and governed metric contracts remain unchanged.

Next: Join Strategies and Dimensional Modeling Benefits: Broadcast/Hash/Merge Concepts, Cardinality, and Skew.

9. Verification checklist

• Same query text/semantics and dataset are used before/after.
• Every result checksum matches after the physical change.
• Cache state is disclosed as warm; cold-cache behavior is not invented.
• p50, p95, and p99 are reported instead of averages only.
• Unsupported local metrics—distributed shuffle, internal spill bytes, hardware SIMD—are labeled unavailable.
• The four workload classes are retained in regression tests.

Knowledge check

Acceptance questions

1. If a dashboard query drops from 25 ms to 10 ms but revenue changes from 820 USD to 665 USD, is that an optimization?
2. What additional evidence would you require before claiming a cache-sensitive gain?
3. Why can queue wait dominate latency even when service time is unchanged?

Authoritative references

• SQLite — EXPLAIN QUERY PLANOfficial interpretation of scan/search operators, index use, temporary B-trees, and the warning that plan-output format is not a stable application API.
• SQLite — Query PlanningOfficial background on table scans, multi-column indexes, sorting, and why the planner chooses among semantically equivalent algorithms.
• SQLite — EXPLAINOfficial semantics and limitations of EXPLAIN/EXPLAIN QUERY PLAN used by the local lab.
• Python — statisticsUsed for deterministic latency summaries in the local benchmark harness.
• BigQuery — Understand reservationsNon-prerequisite vendor example showing how a cloud warehouse can isolate workloads with resource pools; the Chapter 26 concepts do not require BigQuery.

10. Lab cleanup/reset

The mandatory lab is local and synthetic. Delete ch26_lab/atlasmart_perf.sqlite and the generated benchmark JSON, then rerun the setup script to restore the deterministic 360,000-row fixture. No cloud resources, accounts, paid services, or production credentials are created.