Choose Physical Layout, Materialization, Marts, Workload Isolation, and Cloud/Lakehouse Integration from Measurements

Learning outcomes

1. Problem frame: performance claims are invalid if the result changes

AtlasMart’s executive BI workload repeatedly asks for seven-day revenue by product. A developer can make the query “instant” by changing filters, dropping dimensions, or querying stale precomputed data. That is not optimization of the same workload. The capstone therefore treats result equality as the first performance assertion and measures physical alternatives only after the semantic contract is fixed.

2. Representative corpus and scale fixture

The production teaching fact remains ten rows. To make scan behavior measurable without falsifying production controls, the harness creates a separate deterministic 120,000-row performance fixture by repeating the same column shapes across 60 synthetic dates. It is labeled benchmark data and is never reconciled as production revenue. The repeated BI query groups revenue by product for a seven-day range.

SELECT product_id, SUM(revenue_usd)FROM fact_scaleWHERE order_date BETWEEN '2026-02-20' AND '2026-02-26'GROUP BY product_idORDER BY product_id;

Each benchmark path executes the same query semantics. Warm-cache runs follow five warmups; SQLite’s page cache is warm, while the host operating-system cache is not controlled. That limitation is part of the evidence, not hidden.

3. Before/after distributions: raw scan, indexed fact, materialized aggregate

These values are the executed local results on this runtime, not universal tuning numbers. They can change with CPU, filesystem, SQLite build, cache, data distribution, concurrency, and query range. The meaningful capstone conclusion is narrower: for this fixture and query corpus, the materialized higher-grain table preserves the result and dramatically reduces local work.

4. Plan evidence shows access path, not business correctness

indexed atomic fact:SEARCH fact_scale USING INDEX idx_scale_date_product (order_date>? AND order_date<?)USE TEMP B-TREE FOR GROUP BYmaterialized daily-product:SEARCH agg_daily_product USING INDEX idx_agg_date_product (order_date>? AND order_date<?)USE TEMP B-TREE FOR GROUP BY

Both plans use date-range indexes and a temporary structure for grouping. The materialized table wins because it has only 180 day/product rows, not because the plan text promises universal superiority. SQLite also warns that EXPLAIN QUERY PLAN output is primarily interactive diagnostic information rather than a stable application API.

5. Materialization contract: acceleration must preserve lineage and freshness

The acceleration table is declared at one order-date × product grain with additive revenue, cost, and units. It is a derived product, not a replacement for the atomic fact. Its publication record must include source waterline/snapshot identity, refresh timestamp, metric version, owner, and reconciliation to the base fact for the refreshed scope.

A stale aggregate can be faster and wrong. Therefore latency, freshness, maintenance cost, and semantic coverage are one decision surface.

6. Marts remain dependent on conformed dimensions and governed metrics

Finance, marketing, and operations may each need domain-specific tables, but they do not get independent definitions of Customer or revenue. A wide reporting table can be a consumer convenience if its lineage and refresh contract remain explicit. It must not become an undocumented permanent API whose embedded filters drift from gross_revenue_usd.v1.

7. Workload isolation: distinguish simulation from engine measurement

The capstone includes a deterministic two-slot scheduler model to illustrate noisy neighbors. It is not a SQLite concurrency benchmark. With five long ELT jobs mixed into a shared two-slot pool, interactive query wait has p95/max of 1.0 second. With one logical slot reserved for interactive work and one for ELT, interactive p95/max wait is 0.0 seconds in the same synthetic arrival pattern.

Production engines expose different warehouses, queues, resource groups, reservations, slots, or concurrency controls. This simulation justifies a hypothesis; real deployment must measure the actual engine’s queueing, throughput, and cost.

8. Cloud/lakehouse integration: preserve system-of-record and serving ownership

Chapter 28 established that medallion layers and open table formats do not replace dimensional semantics. In the capstone, the operational ERP remains the business source; immutable/raw evidence supports replay; validated integration state may live in warehouse or lakehouse storage; the dimensional/semantic layer owns BI meaning; and materialization/federation decisions are explicit. A “one-copy” architecture is not automatically fresher, cheaper, or more governed.

9. Cost model: explicit dimensions, fictional rates

The capstone teaching calculation uses 0.10 TB-month storage at 0.25 USD/TB-month, two compute units for three hours/day at 0.40 USD/unit-hour, 30 days, and 5 GB monthly egress at 0.05 USD/GB. The arithmetic yields 72.275 USD/month. These rates are deliberately fictional and exist only to make cost dimensions observable.

storage = 0.10 * 0.25compute = 2 * 3 * 0.40 * 30egress  = 5 * 0.05monthly = storage + compute + egressassert round(monthly, 3) == 72.275# Replace every rate with current region/contract/provider pricing in production.

Credits, slots, bytes scanned, compute seconds, storage, and egress are not interchangeable units. A valid cloud comparison maps the same workload into each provider’s current pricing semantics.

10. Controlled failure: cherry-pick the 0.009 ms result

Diagnosis: a workload-specific local measurement has been promoted into universal folklore. Coverage, freshness, p95/p99, maintenance, and correctness were dropped from the claim.

Repair: retain the complete benchmark distribution and query corpus, assert result equality, disclose cache/environment, declare supported grains, record refresh/cost, and keep an atomic fallback/rollback path.

11. Local lab: reproduce the performance evidence

# Separate benchmark fixture -- not production control dataassert fact_scale_rows == 120_000assert daily_product_rows == 180assert query(atomic_fact) == query(daily_product)# Executed warm-cache distributions on this machineraw_p50_ms = 7.185indexed_p50_ms = 4.929materialized_p50_ms = 0.009# Keep distributions, not averages onlyassert indexed_p95_ms == 6.530assert materialized_p99_ms == 0.018# Scheduler evidence is a model, not a DB measurementassert shared_pool_p95_wait_s == 1.0assert isolated_pool_p95_wait_s == 0.0

12. Verification checklist

• Every physical/query alternative returns the same governed result for the same corpus.
• Dataset size, engine/version, warmup/cache disclosure, and p50/p95/p99 are recorded.
• Materialization grain, lineage, refresh timestamp, and rollback route are declared.
• Domain marts consume conformed dimensions and governed metrics.
• Workload-isolation numbers are labeled simulation unless measured in the target engine.
• Cloud/lakehouse choices preserve system-of-record and serving ownership.
• Cost dimensions and assumptions are explicit; fictional teaching rates are never presented as provider prices.

13. Production judgment and bridge to Lesson 4

The measured design now has a defensible reason for an aggregate, an explicit workload-isolation hypothesis, and a portable hybrid boundary. Production readiness still requires proving that these choices survive defects and incidents. Lesson 4 deliberately breaks data, metrics, access, historical materialization, and the primary database, then verifies repair, backfill, and recovery.

Knowledge check

Authoritative references

• SQLite — EXPLAIN QUERY PLAN for the executed local plan evidence and its stability caveat.
• SQLite — Query Planning for local index/planner behavior.
• Kimball Group — DW/BI resources for aggregate, mart, and dimensional design context.
• Apache Iceberg documentation as one product-specific open-table reference; Delta/Hudi guarantees must be checked independently.