data-model-design

name	data-model-design
description	Design conceptual, logical, and physical data models that match the workload. Use when modeling a new domain, refactoring a legacy schema, or building an analytics warehouse.

Data Model Design

A model serves either transactional integrity (3NF) or analytical scan efficiency (star/wide). Choose deliberately, derive one from the other, and never let them collide in the same store.

Stack Baseline (2026)

Concern	Recommended
Conceptual modeling	Domain Storytelling, EventStorming, C4
Logical modeling	ER/Studio, dbdiagram.io, PlantUML, Mermaid ER
Physical OLTP	Postgres 17, MySQL 8.4, CockroachDB
Physical OLAP	ClickHouse 24, DuckDB 1.0, BigQuery, Snowflake
Lakehouse	Iceberg + Polaris, Delta + Unity
Transformation	dbt 1.8, SQLMesh, Coalesce
Modeling style	Kimball star, Data Vault 2.0, One Big Table for ML

When to Use

• Greenfield bounded context or analytics mart.
• Refactoring a god-table or EAV pattern.
• Designing a semantic layer over a lakehouse.
• Replacing nightly ETL with streaming-friendly models.

Prerequisites

• Ubiquitous language and event narrative agreed with domain experts.
• Top 20 queries with cardinalities and SLOs.
• Source system schemas and their CDC feasibility.
• Governance: PII classification, retention.

Instructions

1. Build the conceptual model from events

Run an EventStorming session. Identify aggregates, commands, events, policies. The aggregate boundary becomes the OLTP write boundary.

erDiagram
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ ORDER_LINE : contains
    PRODUCT ||--o{ ORDER_LINE : referenced_by
    CUSTOMER {
      uuid id PK
      text email
      text country
    }
    ORDER {
      uuid id PK
      uuid customer_id FK
      timestamptz placed_at
      bigint total_cents
    }

2. Normalize the OLTP model to 3NF

CREATE TABLE customer (
  id uuid PRIMARY KEY,
  email citext UNIQUE NOT NULL,
  country_code char(2) NOT NULL REFERENCES country(code),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE "order" (
  id uuid PRIMARY KEY,
  customer_id uuid NOT NULL REFERENCES customer(id),
  placed_at timestamptz NOT NULL,
  total_cents bigint NOT NULL CHECK (total_cents >= 0),
  status text NOT NULL
);
CREATE INDEX ix_order_customer ON "order"(customer_id, placed_at DESC);

Use natural keys only when stable; otherwise UUIDv7 for sortable surrogates.

3. Derive the analytical star schema

-- Conformed dimension
CREATE TABLE dim_customer (
  customer_sk bigint PRIMARY KEY,
  customer_id uuid NOT NULL,
  email text,
  country_code char(2),
  valid_from timestamptz,
  valid_to   timestamptz,
  is_current boolean
);  -- SCD Type 2

-- Fact at grain = order line
CREATE TABLE fact_order_line (
  order_line_sk bigint PRIMARY KEY,
  date_sk int    NOT NULL,
  customer_sk bigint NOT NULL,
  product_sk  bigint NOT NULL,
  quantity int NOT NULL,
  net_amount_cents bigint NOT NULL
);

State the grain in one sentence at the top of every fact table.

4. Choose denormalization with intent

Pattern	When	Cost
Star schema	BI, ad-hoc OLAP	join-time engine work
Data Vault	many sources, auditability	many small joins
One Big Table	ML feature store, sub-second BI	rebuild cost on change
Document embed	always-co-read child	duplication on update

5. Model with dbt

models:
  - name: fact_order_line
    config:
      materialized: incremental
      unique_key: order_line_sk
      on_schema_change: append_new_columns
    columns:
      - name: order_line_sk
        tests: [not_null, unique]
      - name: customer_sk
        tests:
          - relationships:
              to: ref('dim_customer')
              field: customer_sk

6. Validate with the top queries

Run the top-20 queries against representative volume. Tune indexes, partitioning, and clustering. For ClickHouse, choose ORDER BY by selectivity; for Postgres, validate with EXPLAIN (ANALYZE, BUFFERS).

7. Publish a data contract

Bind the physical model to a data contract (schema, SLAs, owner, PII tags) and register it in OpenMetadata or DataHub.

Common Pitfalls

Pitfall	Why it hurts	Mitigation
EAV "flexible" tables	Unindexable, opaque	Use JSONB with explicit checks
Surrogate + natural key confusion	Joins double-count	Pick one per table, document
OLTP joins for BI	Locks, slow scans	Replicate to ClickHouse/DuckDB
Missing fact grain statement	Wrong sums forever	Document grain on row 1
Premature OBT	Rebuild storms	Start with star, flatten when needed
SCD Type 1 on regulated attrs	Lost audit trail	Use SCD Type 2 with valid_*

Output Format

Deliver: conceptual ER diagram, logical ERD, physical DDL, SCD strategy per dimension, dbt project skeleton, top-query benchmark report, and the registered data contract URL.

Authoritative References

• Kimball, The Data Warehouse Toolkit, 3rd ed.
• Linstedt and Olschimke, Building a Scalable Data Warehouse with Data Vault 2.0
• dbt best practices - https://docs.getdbt.com/best-practices
• ClickHouse data modeling - https://clickhouse.com/docs/en/data-modeling
• UUIDv7 RFC 9562 - https://www.rfc-editor.org/rfc/rfc9562