Physical Data Modeling And Storage Design

Why this skill matters for Data Architects

Physical data modeling turns logical models into fast, reliable tables and files. As a Data Architect, your choices on storage patterns, partitioning, clustering, indexing, and lifecycle policies directly impact query cost, latency, reliability, and governance. Mastering this skill lets you ship platforms that scale smoothly under real workloads.

Who this is for

• Data Architects and Senior Data Engineers designing data platforms.
• Analytics Engineers moving from logical modeling to physical implementation.
• DBAs/Platform engineers standardizing warehouse and lakehouse layouts.

Prerequisites

• Comfort with SQL DDL/DML and basic performance concepts (scans, filters, joins).
• Understanding of star schemas, slowly changing dimensions, and fact/dimension roles.
• Basic knowledge of a warehouse (e.g., BigQuery, Snowflake, Redshift) or lakehouse (Delta/Iceberg).

Learning path

Worked examples

-- Goal: Fast filters by event_date and customer_id
CREATE TABLE prod.analytics.events
PARTITION BY DATE(event_ts)
CLUSTER BY customer_id, event_type AS
SELECT * FROM source.raw_events; -- loaded via ETL

-- Query patterns that benefit:
-- WHERE event_date BETWEEN ... AND ...
-- AND customer_id = 'C123'
  

CREATE TABLE PROD.SALES.FACT_ORDERS (
  ORDER_ID NUMBER, CUSTOMER_ID NUMBER, ORDER_TS TIMESTAMP_NTZ,
  STORE_ID NUMBER, TOTAL_AMT NUMBER(12,2)
);

-- Add clustering on common filters
ALTER TABLE PROD.SALES.FACT_ORDERS
  CLUSTER BY (ORDER_TS, CUSTOMER_ID);

-- Set time travel and retention
ALTER TABLE PROD.SALES.FACT_ORDERS SET DATA_RETENTION_TIME_IN_DAYS = 7; -- lifecycle
  

-- Spark SQL / Delta Lake
CREATE TABLE delta.`/lake/bronze/clicks` (
  event_ts TIMESTAMP,
  user_id STRING,
  url STRING,
  device STRING
) USING DELTA
PARTITIONED BY (date_trunc('DAY', event_ts));

-- After heavy writes, compact and improve skipping
OPTIMIZE delta.`/lake/bronze/clicks` ZORDER BY (user_id);
  

CREATE TABLE fact_sessions (
  session_id BIGINT,
  customer_id BIGINT,
  started_at TIMESTAMP,
  device VARCHAR(50)
)
DISTSTYLE KEY DISTKEY(customer_id)
SORTKEY (started_at);
  

-- Pattern translates conceptually to warehouses with row access policies
CREATE TABLE reporting.orders (
  order_id BIGINT PRIMARY KEY,
  tenant_id INT NOT NULL,
  order_ts TIMESTAMP NOT NULL,
  total NUMERIC(12,2) NOT NULL
);

ALTER TABLE reporting.orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON reporting.orders
  USING (tenant_id = current_setting('app.tenant_id')::INT);
  

Drills and exercises

• Pick any fact table and write a one-sentence grain statement. If unclear, redesign columns until the grain is unambiguous.
• Choose a partition column and granularity for two datasets (events, orders). Justify the choice in 2–3 bullets.
• Propose clustering/sort keys for three common queries; validate with EXPLAIN or query statistics.
• Define a 12–24 month retention policy and outline how deletes/archives will be executed.
• Draft naming conventions for schemas, tables, and columns. Apply to five example tables.
• Sketch a multi-tenant isolation plan (schema-per-tenant vs row-policy). Note pros/cons.

Common mistakes and debugging tips

• Too-fine partitions: Hour-level partitions on medium volumes create many tiny files. Tip: prefer daily; partition by hour only if queries demand it.
• No pruning: Queries wrap partition columns in functions (e.g., DATE(event_ts)). Tip: filter using native partition columns or precomputed date columns.
• Over-indexing mindset: Traditional B-tree indexes help little in columnar warehouses. Tip: use clustering/sort/distribution keys instead.
• Inconsistent time zones: Mixing local time and UTC breaks pruning. Tip: store UTC; convert at presentation time.
• Skipping lifecycle: Old partitions accumulate, costs rise. Tip: automate deletes and compaction, and monitor table size/fragmentation.
• Weak tenant isolation: Relying only on row filters for strict compliance. Tip: combine schema/table isolation with row policies where needed.

Mini project: Analytics-grade clickstream

Goal: Design a production-ready clickstream dataset.

1. Define logical model: events (grain: one row per user action) and a user dimension.
2. Choose storage: lakehouse bronze events (append-only) and warehouse silver/gold tables.
3. Create physical tables: partition events by day; cluster by user_id and event_type.
4. Add lifecycle: 13 months hot, archive older to cold storage; automate daily deletes.
5. Implement multi-tenant isolation: schema-per-tenant in warehouse; row policy in shared reporting views.
6. Validate: run three representative queries and record scan size and latency before/after clustering/optimize.

Subskills

• Choosing Storage Patterns — Decide warehouse vs lakehouse placement, append vs upsert, and file/table layout based on workload.
• Partitioning And Clustering Strategy — Select partition keys and clustering/sort columns that align with real filters and joins.
• Table Design Standards — Set types, nullability, keys, and consistent timestamp and schema rules.
• Indexing Concepts For Warehouses — Use clustering, sorting, and distribution strategies over traditional indexes.
• Lakehouse Table Formats Basics — Understand Delta/Iceberg features: ACID, schema evolution, time travel, compaction.
• Data Retention And Lifecycle Policies — Automate deletes, tiering, compaction, and vacuum to control cost and risk.
• Multi Tenant Data Isolation — Apply schema/table isolation and row policies for secure, auditable access.
• Naming Conventions — Enforce clear, predictable names for schemas, tables, and columns.

Next steps

• Work through each subskill below and complete the drills.
• Build the mini project and measure query performance before and after each design change.
• When ready, take the skill exam. Everyone can take it; logged-in users have their progress saved.