Logical Dimensional Model vs Physical Implementation in Row Stores, Column Stores, and Cloud Warehouses

Learning outcomes

AtlasMart has a trusted dimensional model and a recoverable incremental pipeline. Analysts now complain that a daily revenue query, a seven-day product query, and a customer-window query scan more data than necessary. The wrong response is to redesign the grain because a query is slow. The correct question is: which physical representation lets the same logical facts answer the same questions with less work?

1. The logical model is the contract; the physical model is an implementation

The logical dimensional model states what one fact row means, which dimensions provide context, which keys preserve identity/history, and which measures are valid at that grain. A physical implementation chooses database/schema boundaries, table/index structures, file layout, partitions, clustering/sort order, distribution, compression, and engine-specific storage options.

Changing from a heap-like row table to an indexed table, partitioned files, a column store, or a disaggregated cloud warehouse should not change “one row = one paid order line,” nor should it turn 740 USD into a different revenue definition. If physical optimization changes query results, the optimization has crossed the semantic boundary and must be treated as a model change.

2. Row stores, column stores, and cloud warehouses optimize different mechanics

These are implementation families, not mutually exclusive product labels. A system can combine row-oriented metadata, columnar data, remote object storage, caches, indexes, and automatic clustering. Therefore the design artifact must state the engine and evidence, not merely “we use a cloud warehouse.”

3. One query corpus, two SQLite access paths

The local harness loads the same 21,600 benchmark rows into two SQLite databases. One has only the fact-table primary key; the other adds composite indexes on (order_date, product_id) and (customer_id, order_date). The logical row checksum is identical.

import jsonfrom pathlib import Pathr=json.loads(Path("atlasmart_ch17_lab/reports/benchmark_report.json").read_text())for q in ["Q1_daily_gmv","Q3_customer_window"]:    print(q)    print(" heap   ", r["plans"][q]["heap_plan"])    print(" indexed", r["plans"][q]["indexed_plan"])# Generation environment:# Q1 heap:    SCAN fact_sales# Q1 indexed: SEARCH fact_sales USING INDEX idx_fact_sales_date_product (order_date=?)# Q3 indexed: SEARCH fact_sales USING INDEX idx_fact_sales_customer_date (...)

4. Measured timings are secondary evidence, not universal promises

The generator executed each query 15 measured times after three warm-ups without flushing the SQLite or operating-system cache. Q1 measured 0.6883 ms median on the heap database versus 0.0585 ms on the indexed database in this environment; Q4, the full-table aggregate, measured 1.0088 ms versus 1.0346 ms. The important lesson is not those tiny numbers. It is that an access structure helped selective queries but did not remove work from a logically full scan.

Never turn a local microbenchmark into a production SLA. Report dataset size, cache state, concurrency, engine, storage, query text, warm-up policy, and measured distribution—or use deterministic scan calculations when those are the real decision variable.

5. Controlled failure: change grain to “make the table faster”

A team may propose daily pre-aggregation as the new base fact because the dashboard asks for daily GMV. That can accelerate one query but destroys order-line evidence needed for product/customer drilldown, returns, restatements, and future metrics. The repair is to preserve the atomic fact and add a physical access path or explicitly governed aggregate later. Physical design should optimize access to the logical model, not silently replace it.

6. Hands-on: run the physical harness

Save the Chapter 17 lab script from Lesson 5 as ch17_lab.py and run it with the same Python interpreter used for the course. It creates disposable databases, file layouts, a benchmark report, and a physical-design decision record.

python ch17_lab.py# Expected identity lines:# runtime: Python 3.13.5 / SQLite 3.46.1 / UTC# continuity controls: (9, 7, 11, 740, 450, 290)# benchmark rows: 21600# decision logical_model_changed: False# cleanup: rm -rf atlasmart_ch17_lab

Use the printed runtime rather than assuming your SQLite build matches the generation environment. If your version differs, re-read the plan and re-measure.

7. Production judgment and bridge

The physical-design question is “what work can be avoided or localized for the representative workload while preserving semantic correctness?” The answer may involve partitions, indexes, clustering, sort order, distribution, aggregates, caching, or nothing at all. Chapter 17 evaluates those choices from evidence and rollback cost, not from fashion.

Lesson 2 makes the deployment boundary explicit: schemas/databases, naming, ownership, constraints, and environment/domain separation determine who can safely operate and evolve these physical objects.

Knowledge check

Authoritative references

• SQLite — EXPLAIN QUERY PLANOfficial documentation for interpreting SQLite SCAN/SEARCH plan evidence; the output format is explicitly not an application contract.
• SQLite — Query PlanningBackground on index-assisted row access in the local row-store harness.
• SQLite — Foreign Key SupportOfficial constraint behavior; the fixture enables PRAGMA foreign_keys=ON explicitly per connection.
• SQLite — CREATE TABLEDDL reference for PRIMARY KEY, CHECK, and REFERENCES used in the local constraint test.
• Python — sqlite3Standard-library interface used to keep the mandatory lab dependency-free.
• Python — hashlibStable SHA-256 hashing for benchmark checksums and deterministic hash-bucket assignment.
• Kimball Group — Dimensional Modeling TechniquesBackground for preserving declared fact/dimension grain while changing the physical implementation.