Reduce work without changing analytical meaning
Partition/Cluster Pruning, Predicate Pushdown, Materialization, Approximation, and Query Rewrites
Use pruning, pushdown, materialization, and exact rewrites only when result semantics remain governed and observable.
Learning outcomes
Explain partition/cluster pruning and predicate/projection pushdown as work-avoidance mechanisms rather than magic syntax.
Use materialization only with an explicit grain, lineage, refresh timestamp, and reconciliation contract.
Distinguish exact query rewrites from approximations and quantify the semantic error boundary.
Detect rewrites that disable index/pruning opportunities despite returning the same rows.
Use before/after evidence plus result checksums to decide whether reduced work is real and acceptable.
Chapter 26 begins from the accepted AtlasMart state established through Chapters 01–25: 10 current paid lines, 8 orders, 12 units, 820 USD gross revenue, 495 USD cost, and 325 USD gross profit. Source progress remains committed through sequence 208; Chapter 20 metric contracts, Chapter 21 certified marts, Chapter 22 security controls, Chapter 23 lineage/ownership, Chapter 24 tests, and Chapter 25 SLO/incident practices remain authoritative. The 360,000-row benchmark below is a clearly labeled scale fixture for performance mechanics only; it never replaces these business control totals.
Runtime: Python 3.13.5 + SQLite 3.46.1. Benchmark grain: one synthetic paid sales line per row. Scale fixture: 360,000 rows across 180 dates, 100 products, and 5,000 customers; product 1 intentionally receives about 30% of rows and North receives about 50% of customer-linked rows. Storage: a local SQLite database, WAL journal mode, temporary structures configured in memory. Timing: 3 warm-up executions then 17 measured executions; reported cache state is warm. The operating-system cold cache is not forcibly cleared, so no “cold-cache” claim is made. Security: synthetic identifiers only. Distributed limitations: SQLite does not expose distributed shuffle bytes, warehouse queues, or hardware SIMD counters; those mechanisms are explained conceptually and, where useful, modeled explicitly rather than mislabeled as SQLite measurements.
1. Realistic problem: can we make finance faster without changing finance?
The seven-day finance dashboard scans far more fact data than its date window requires. AtlasMart wants lower latency and scan cost, but the governed revenue metric must remain exact and current enough for its contract. This lesson separates five mechanisms: partition/cluster pruning, predicate pushdown, projection pushdown, materialization, and approximation. Only the last one intentionally changes exactness.
2. Work avoidance mechanisms
| Mechanism | What it avoids | Correctness precondition |
|---|---|---|
| Partition pruning | Whole partitions outside a predicate range. | Partition key semantics align with the predicate and engine can recognize it. |
| Cluster/data skipping | Blocks/row groups whose statistics cannot match. | Useful ordering/statistics exist; engine actually uses them. |
| Predicate pushdown | Rows rejected close to storage before expensive operators. | Predicate is semantically movable and supported by storage/engine. |
| Projection pushdown | Unneeded columns read/decompressed. | Requested expressions do not require hidden columns. |
| Materialization | Repeated join/aggregation work. | Declared grain + freshness + invalidation + reconciliation contract. |
| Approximation | Exact computation cost. | Consumer explicitly accepts error semantics; never silently substitute for governed exact metric. |
3. Measured index/pruning proxy in SQLite
| Query | Baseline plan | After physical change | p95 before → after |
|---|---|---|---|
| BI 7-day category | full fact SCAN | date-range indexed SEARCH | 24.93 → 10.19 ms |
| Ad-hoc 30-day | full fact SCAN | date-range indexed SEARCH | 85.27 → 79.03 ms |
| ELT daily-product | fact SCAN + temp group B-tree | scan in date/product index order | 256.02 → 162.26 ms |
SQLite indexes are not cloud table partitions or columnar clustering, but the observable mechanism is analogous: organize data so a selective query avoids some work. Whether another engine prunes partitions, row groups, micro-partitions, or uses a B-tree is engine-specific.
4. Boundary failure: wrap the filter key in an expression
WHERE f.order_date BETWEEN '2026-09-15' AND '2026-09-21'WHERE substr(f.order_date, 1, 10) BETWEEN '2026-09-15' AND '2026-09-21'Executed on the indexed fixture, the direct predicate had p95 about 10.19 ms; the expression-wrapped predicate reverted to a full fact scan and p95 about 33.94 ms. The rows are semantically equivalent for ISO dates, but the planner cannot use the same range-search path. Do not generalize this exact number to other engines.
5. Materialization: enormous speedup, new freshness responsibility
| Path | p50 ms | p95 ms | p99 ms | Result checksum |
|---|---|---|---|---|
| Atomic fact + product join | 8.81 | 10.19 | 10.93 | 7ad048ac5bd140d9… |
| Daily-category aggregate | 0.0146 | 0.0302 | 0.0673 | 7ad048ac5bd140d9… |
The local materialized table returns the same normalized result checksum and reduces repeated work dramatically. That does not make it “free”: Chapter 19’s freshness/invalidation/reconciliation contract now applies. A stale aggregate that answers quickly is still wrong for a freshness-sensitive consumer.
6. Approximation is a different metric contract
The fixture has 4,999 distinct active customers in the selected 30-day window. A deterministic 1% row sample sees 562 distinct customers. Naively multiplying that by 100 yields 56,200—physically impossible because the whole customer dimension has only 5,000 members. This controlled failure shows why exact distinct counts cannot be approximated by scaling a row-sample distinct count. Approximate algorithms require their own error model and explicit consumer acceptance.
7. Query rewrites must preserve metric algebra
Pushing a paid-order filter down is safe if “paid” is already
part of the governed revenue definition. Pre-aggregating
additive revenue by date/product before joining a product
category can be safe when category history resolution has
already occurred at event time. Replacing an exact distinct
customer metric with COUNT(*), dropping late rows,
or using a current dimension version instead of the historical
version is not a performance rewrite; it changes the analytical
contract.
8. Controlled failure: celebrate reduced latency after dropping expensive semantics
A developer removes the customer-history join and returns current segment only. The query becomes faster and still “looks plausible,” but historical segment revenue changes. The repair is simple in principle: freeze the golden result set/checksum, run the optimized query, and reject any performance result whose business output differs unless a versioned metric/model change was intentionally approved.
When is approximation acceptable?
Show answer
Only when the metric contract explicitly permits an approximate result, documents the algorithm/error behavior, and the consumer decision tolerates that uncertainty. It must not silently replace an exact certified metric.
9. 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: Workload Management, Queues/Warehouses/Resource Groups, Concurrency Scaling, and Noisy-Neighbor Isolation.
10. Verification checklist
- Work avoidance is demonstrated by plan/scan evidence, not inferred from syntax.
- Direct and materialized results reconcile exactly for exact metrics.
- Materializations expose refresh state and lineage.
- Approximate paths use a separate semantic contract.
- Rewrites do not alter historical joins, filters, currency, units, or metric grain.
Knowledge check
Check your understanding
- What is the central mechanism in “Partition/Cluster Pruning, Predicate Pushdown, Materialization, Approximation, and Query Rewrites”, and which AtlasMart grain or metric contract must remain unchanged?
- Which observable evidence in this lesson distinguishes the correct design from the controlled failure?
- Which assumptions are local or engine-specific, and what must be re-checked before production use?
Review the answers
1. Preserve the lesson’s declared business grain, history semantics, governed metric definitions, and reconciliation controls while changing only the mechanism under study.
2. Use the lesson’s counts, sums, checksums, plans, traces, timing/cost calculations, or failure-state evidence—not a green task status or naming convention alone.
3. Re-check runtime/version, data scale and distribution, cache/concurrency, storage layout, security context, pricing/region where relevant, and the exact product guarantees before production adoption.
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.
11. 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.