Schema/Database Organization, Naming, Ownership, Constraints, and Environment/Domain Boundaries

Learning outcomes

AtlasMart can have a perfectly designed star schema and still become unsafe if every team writes to one shared namespace, constraints are assumed but not enforced, and SQL contains production database names. Physical organization is therefore an operational contract: object location, owner, environment, and integrity behavior must be explicit before partitioning or clustering is useful.

1. Database/schema boundaries organize deployment—not analytical grain

A database or catalog is an engine-defined top-level namespace/storage/security boundary. A schema is usually a namespace within a database/catalog. A domain boundary is an ownership/semantic boundary such as sales, inventory, or shared dimensions. An environment boundary separates dev/test/prod lifecycle and credentials. None of those is the fact grain.

“One schema per day” is not date partitioning. “One database per customer” is not a customer dimension. Physical namespaces should support ownership, access, deployment, retention, and discovery without encoding accidental business semantics.

2. A small naming contract beats ad-hoc object discovery

The exact namespace syntax differs by engine. The useful design information is owner, purpose, write path, and consumer contract—not whether the separator is a period or a catalog/schema pair.

3. Constraints are executable assumptions when the engine enforces them

The local fact_sales table declares a composite primary key, NOT NULL columns, positive quantity, non-negative revenue/cost, and foreign keys to customer/product/store dimensions. SQLite foreign-key enforcement is connection-scoped, so the fixture explicitly runs PRAGMA foreign_keys=ON. Other analytical engines may parse but not enforce some constraints, use them only for optimization, or enforce them differently.

PRAGMA foreign_keys=ON;CREATE TABLE fact_sales(  order_date TEXT NOT NULL,  order_id TEXT NOT NULL,  line_no INTEGER NOT NULL,  customer_id TEXT NOT NULL REFERENCES dim_customer(customer_id),  product_id TEXT NOT NULL REFERENCES dim_product(product_id),  store_id TEXT NOT NULL REFERENCES dim_store(store_id),  quantity INTEGER NOT NULL CHECK(quantity > 0),  revenue_usd INTEGER NOT NULL CHECK(revenue_usd >= 0),  cost_usd INTEGER NOT NULL CHECK(cost_usd >= 0),  gross_profit_usd INTEGER NOT NULL,  PRIMARY KEY(order_id,line_no));

The lab injects two invalid rows. Quantity 0 is rejected by the CHECK constraint and customer C999 is rejected by the foreign key. That is observable evidence that the local assumptions are enforced.

4. Environment promotion should change configuration, not business SQL

A brittle model contains prod_dw.dw_core.fact_sales in every query and requires string replacement for test. A safer build resolves environment-specific catalogs, credentials, object roots, retention settings, and compute classes from configuration while keeping the transformation graph identical. Promotion means moving a tested artifact/configuration through environments—not copying a mutable notebook until it “works in prod.”

{  "dev":  {"catalog": "atlasmart_dev",  "raw_root": "./raw/dev",  "role": "dw_dev_writer"},  "test": {"catalog": "atlasmart_test", "raw_root": "./raw/test", "role": "dw_test_writer"},  "prod": {"catalog": "atlasmart_prod", "raw_root": "s3://example-placeholder/not-used-in-lab", "role": "dw_prod_pipeline"}}// The lab remains local; the prod URI is illustrative configuration, not executed cloud storage.

Do not put secrets in that file. Identity/credential injection belongs to the deployment system. The lesson separates configuration shape from secret material.

5. Controlled failure: constraints and ownership exist only in a slide deck

If an analytical engine does not enforce foreign keys, a declared constraint may be useful documentation but cannot replace reconciliation. If every developer can write directly to certified schemas, ownership labels do not prevent semantic drift. The repair is defense in depth: enforced constraints where supported, data tests where not, least-privilege write identities, code review, lineage, and consumer certification.

6. Hands-on: inspect the negative constraint evidence

import jsonfrom pathlib import Pathr=json.loads(Path("atlasmart_ch17_lab/reports/benchmark_report.json").read_text())print(r["constraint_tests"])# Expected keys: negative_quantity and orphan_customer# Both values begin with "REJECTED:" in the validated fixture.

This proves enforcement only for the local SQLite connection that enabled foreign keys. If you port the schema, rerun negative tests on the target engine instead of assuming DDL words have identical force.

7. Production judgment and bridge

Organization and constraints reduce accidental ambiguity, but they do not choose a partition key. Lesson 3 uses the fixed workload to decide whether date, coarser range, or hash partitioning can actually prune work—and shows why tiny/high-cardinality partition schemes create their own operational cost.

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.