Chapter 17 · Physical Warehouse Design: Schemas, Tables, Constraints, Partitioning, Clustering, and Distribution
Clustering/Sort Keys, Distribution/Partition Keys, Co-Location, and Engine-Specific Alternatives
Reason about clustering/sort access paths and distribution/co-location as different physical mechanisms, then prove which observations are portable and which belong to one engine or execution topology.
Learning outcomes
AtlasMart has evidence that date partitioning helps date-bounded scans, but Q3 still needs a customer-led access path. Some warehouses call the next knob clustering, others sort keys, indexes, organization keys, data skipping, distribution keys, or automatic layout. These words are not interchangeable. Lesson 4 separates the mechanisms before deciding what evidence transfers across engines.
Distinguish partitioning from clustering/sort/index access and from distributed data placement.
Explain co-location as a join-locality objective rather than as a semantic relationship.
Use measured customer-hash skew to show why distribution choices require data-frequency evidence.
Interpret SQLite composite-index evidence as one engine-specific analog, not a universal clustering implementation.
Document migration risk when sort/distribution/clustering advice is copied across engines.
Physical design must not redefine the business model. The accepted Chapter 16 current controls remain 9 paid order-line facts, 7 paid orders, 11 units, 740 USD paid GMV, 450 USD cost-at-sale, and 290 USD gross profit. The benchmark harness expands those nine rows deterministically to 21,600 synthetic rows only to make physical-access differences observable. Benchmark-scale totals are test data, not new production controls. Grain, conformed dimension meaning, SCD history policy, metric formulas, source contracts, and Chapter 15–16 recovery semantics remain unchanged.
The mandatory lab is local, synthetic, and free.
Generation-time validation ran with Python 3.13.5 and SQLite
3.46.1 in UTC. SQLite is used only as a row-store/access-path
harness; it has no native cloud-warehouse clustering or
distributed distribution keys. Date/month/hash file layouts
are deterministic JSONL simulations used to calculate files
and bytes that a dispatcher would scan. The lab never claims
those numbers are Snowflake, BigQuery, Redshift, Databricks,
ClickHouse, or DuckDB behavior.
EXPLAIN QUERY PLAN evidence is SQLite-specific
and its output format is documented as unstable across SQLite
versions; the lessons interpret only the observed SCAN/SEARCH
mechanism, not the literal formatting.
1. Partitioning, clustering/sort access, and distribution solve different problems
| Mechanism | Primary purpose | Typical evidence | Failure mode |
|---|---|---|---|
| Partitioning | Exclude whole physical partitions; simplify lifecycle | Partitions/files/bytes pruned | Too many tiny partitions; predicate mismatch |
| Clustering/sort/index-like access | Improve locality/search within or across partitions | Plan, skipped blocks/pages, range access, scan bytes | Maintenance/write cost; low selectivity; engine-specific behavior |
| Distribution/sharding | Place rows across workers/nodes/buckets | Rows/bytes per bucket, shuffle/network, co-location | Skew, hotspots, repartition/shuffle |
| Co-location | Place join-related rows on same worker/bucket | Reduced remote shuffle for target join | Works for one join family, hurts another |
A database can use all three mechanisms simultaneously. Do not call a hash distribution key a “partition” in one paragraph and a semantic grain in the next; the ambiguity makes migration and incident diagnosis difficult.
2. SQLite indexes are a local analog for selective access—not cloud clustering
The local row-store harness creates
idx_fact_sales_date_product and
idx_fact_sales_customer_date. SQLite chooses SEARCH
for Q1/Q3. That proves a secondary physical access structure can
reduce rows visited for selective predicates in SQLite. It does
not prove how BigQuery clustering, Snowflake micro-partitions,
Redshift sort/distribution keys, ClickHouse ORDER BY, or another
engine will behave.
CREATE INDEX idx_fact_sales_date_product ON fact_sales(order_date, product_id);CREATE INDEX idx_fact_sales_customer_date ON fact_sales(customer_id, order_date);EXPLAIN QUERY PLANSELECT SUM(revenue_usd)FROM fact_salesWHERE customer_id='C002' AND order_date BETWEEN '2026-09-01' AND '2026-09-21';
Validated SQLite 3.46.1 output includes
SEARCH fact_sales USING INDEX
idx_fact_sales_customer_date. Treat the plan text as debugging evidence, not a stable
machine-readable API.
3. Distribution is about worker placement; single-node SQLite cannot reproduce it
A distributed hash key sends each row to a bucket/worker. If
both fact_sales.customer_id and
dim_customer.customer_id use the same compatible
hash/partition rule, a customer join can be
co-located in principle. The lab simulates the
row placement only—it cannot measure network shuffle because
SQLite executes on one node.
The simulated eight-bucket distribution is highly sparse: bucket 3 holds 16,800 rows, bucket 0 holds 4,800, bucket 5 holds the remainder, and five buckets are empty. The nonempty max/min ratio is 3.5. That is enough to reject “hash customer_id across eight workers will be balanced” for this tiny benchmark.
import jsonfrom pathlib import Pathr=json.loads(Path("atlasmart_ch17_lab/reports/benchmark_report.json").read_text())print(r["hash_distribution"]["row_bucket_counts"])print("nonempty max/min ratio:", r["hash_distribution"]["nonempty_skew_ratio"])# {'0': 4800, '1': 0, '2': 0, '3': 16800, '4': 0, '5': 0, '6': 0, '7': 0}# ratio = 3.54. Co-location is query-specific, not a global semantic win
Customer co-location may help customer/fact joins, but date-only scans still touch every customer bucket. Product-heavy workloads may prefer a different placement. A fact table cannot usually be perfectly co-located with every dimension simultaneously. Distributed design therefore depends on dominant joins, large-table relationships, dimension replication/broadcast options, skew, and concurrency.
Small dimensions are often replicated/broadcast by distributed engines rather than hash-distributed, but that strategy is engine- and size-dependent. Measure actual optimizer behavior and network/shuffle metrics in the target system.
5. Controlled failure: copy a vendor recommendation into another engine
Suppose a guide says “use order_date as the sort key and customer_id as distribution key.” Another engine may have automatic clustering, no user-visible distribution key, different pruning metadata, different write/maintenance behavior, or serverless execution. Copying the words can preserve neither the mechanism nor the economics.
Translate advice into intent: “make date-range predicates skip most data” and “reduce shuffle for the largest customer join.” Then map those intents to the target engine's supported mechanisms and benchmark again.
6. Migration checklist for physical features
| Question | Why it matters |
|---|---|
| Can the target prune on the same predicates? | Clustering/index metadata and null/collation semantics differ. |
| Who maintains layout? | Some engines require manual reclustering/vacuum/rebuild; others automate it. |
| Does distribution exist as a user control? | Serverless/disaggregated systems may hide placement. |
| What is charged? | Scan bytes, compute time, credits/slots, storage, maintenance, and egress differ. |
| What is the rollback? | Keep logical model stable so physical structures can be removed/rebuilt without semantic migration. |
7. Production judgment and bridge
Clustering/sort/index and distribution choices should be tied to the query corpus, data frequency, and actual target-engine mechanics. Prefer reversible physical structures around a stable logical model. Lesson 5 combines all evidence into a decision record rather than finishing with a generic recipe.
Knowledge check
Check your understanding
- What is the key difference between partitioning and distribution?
- What does co-location optimize?
- Why is the hash simulation not a distributed benchmark?
- What does the customer/date SQLite index prove?
- How should vendor advice be ported?
Review the answers
1. Partitioning is a physical grouping/pruning/lifecycle mechanism; distribution is placement across workers/buckets for parallelism/locality.
2. A particular family of joins by reducing remote movement when compatible rows are placed together.
3. SQLite is single-node; the lab only calculates deterministic bucket placement and skew, not network/shuffle execution.
4. That SQLite used a selective SEARCH access path for the tested query; it does not define cloud clustering behavior.
5. Translate it to workload intent, map to target mechanisms, then re-measure on the target engine.
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=ONexplicitly 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.