Data Modeling & Schema Design

Why This Matters#

Data modeling is not a question about drawing entity boxes. It is a question about which queries you are promising to make cheap, forever. Code can be rewritten in a sprint. A schema holding 4 TB of production data, read by 11 services and a warehouse pipeline, cannot. The data model is the longest-lived artifact in almost every system — it routinely outlives the service, the framework, the language, and the team that wrote it.

Most candidates model the domain: User, Order, Product, foreign keys between them, third normal form. That is a correct answer to "what are the nouns?" and an incomplete answer to "what will this system do all day?" Staff candidates start from the access patterns — the top 5 queries by volume and the top 3 by business criticality — and derive the tables, keys, and denormalizations that make those queries a single seek. The domain model tells you what is true; the access patterns tell you how to store it.

The Principal layer adds ownership. Every table has exactly one team that may write it, a set of consumers who read it, and a contract about how it evolves. The most expensive data-modeling failures in large organizations are not bad schemas — they are shared schemas: a users table written by four services, read by twenty, where no one can add a column without a cross-team meeting and no one can remove one at all.

The 60-Second Version#

• Access patterns first, entities second. List the top queries with their QPS, latency budget, and consistency need before drawing a table. A model that makes the 50K QPS query a scan is wrong no matter how normalized it is.
• Normalize for write correctness; denormalize for read cost — and name who maintains the copy. Every denormalized field is a second source of truth with an update path that can fail.
• In relational stores, start normalized and denormalize with evidence. In key-value/wide-column stores (DynamoDB, Cassandra), model one table per query shape, because joins don't exist.
• Schema evolution is expand → migrate → contract. Add the new shape, dual-write, backfill, switch reads, stop writing the old, remove it. Each step is independently deployable and reversible — except the last.
• Never make a breaking schema change in one deploy. At any moment during a rollout, old and new code run side by side for minutes to hours; the schema must support both.
• One writer per table. Shared write access to a table is a distributed system with no protocol. Other teams get an API or an event stream.
• Backfills are production traffic. A 2B-row backfill at an unthrottled rate is indistinguishable from a DDoS on your own primary. Budget ~10–20% of write headroom and plan days, not minutes.

How Data Modeling Works#

The Basic Idea#

A data model makes four decisions:

1. Entities and relationships — what exists and how it relates (the domain).
2. Keys — how each record is identified and, in distributed stores, where it lives (the partition key).
3. Shape — which attributes live together in one row/document, and which are split or duplicated (normalization vs denormalization).
4. Evolution rules — how the schema changes without breaking readers and writers that are deployed at different times.

L5 candidates spend their time on (1). Production outages come from (2) and (4). Cost comes from (3).

Key Terms#

Where Models Live#

🎯 Staff Insight: "I don't start with the entities. I start with the five queries that pay the bills — their QPS, latency budget, and whether they can be stale. The entities fall out of that; the keys and duplications are what I'm actually designing."

Core Strategies#

Strategy 1: Access-Pattern-First Design#

Write the query list before the schema.

# Access patterns for a marketplace orders service
AP1  Get order by id                         30K QPS  p99 < 10ms   strong
AP2  List a buyer's orders, newest first     8K QPS   p99 < 50ms   read-your-writes
AP3  List a seller's open orders by due date 2K QPS   p99 < 100ms  stale 5s OK
AP4  Daily revenue per seller                batch    hours        warehouse
AP5  Find orders by payment_intent_id        200 QPS  p99 < 50ms   strong (webhooks)

# Derived decisions
orders           PK order_id                         serves AP1
index            (buyer_id, created_at DESC)         serves AP2
seller_queue     (seller_id, status, due_at) partial serves AP3  -- or a projection table
payment_ref      UNIQUE (payment_intent_id)          serves AP5, enforces 1:1
AP4              CDC → warehouse                     not on the OLTP primary

When to use: always. Failure mode: designing only for today's patterns. Mitigate by asking "which access pattern is most likely to appear in the next 12 months?" — and making sure the partition key doesn't forbid it.

Strategy 2: Normalized Relational Model#

Store each fact once. Use foreign keys and transactions to enforce invariants.

users(id PK, email UNIQUE, created_at)
orders(id PK, buyer_id FK→users, status, total_cents, currency, created_at)
order_items(order_id FK→orders, line_no, sku FK→products, qty, unit_price_cents,
            PRIMARY KEY (order_id, line_no))
products(sku PK, name, current_price_cents)

Note unit_price_cents on order_items: it looks like denormalization but isn't — it's a historical fact (the price at purchase time), a different fact from current_price_cents. Confusing "copy of current value" with "snapshot of a past value" is one of the most common modeling bugs.

When to use: write correctness matters, access patterns are varied or unknown, and the dataset fits a single primary (up to single-digit TB and ~10–50K writes/s on modern hardware). Failure mode: read paths that need 5+ joins at high QPS; the fix is a targeted projection, not abandoning normalization.

Strategy 3: Denormalized / Query-Shaped Model#

Duplicate data so each query reads one partition.

# DynamoDB single-table style: item collection per buyer
PK = BUYER#b42    SK = PROFILE                  → name, tier
PK = BUYER#b42    SK = ORDER#2026-09-28#o991    → status, total, item_count, first_item_name
PK = ORDER#o991   SK = META                     → buyer_id, status, total, ...
PK = ORDER#o991   SK = ITEM#001                 → sku, qty, price

# AP2 = Query(PK='BUYER#b42', SK begins_with 'ORDER#', ScanIndexForward=false, Limit=20)
# status now lives in two items → every status change writes both (TransactWriteItems)

When to use: high-QPS reads with known shapes, stores without joins, and cross-region or cross-shard reads where a join would be a network fan-out. Failure mode: divergent copies. Status updated on ORDER#o991 but the write to the buyer's copy failed or was reordered. You need a transaction, a CDC-driven projector with idempotent upserts, or a reconciliation job — and someone who owns the drift metric.

The Core Tradeoff#

🎯 Staff Move: "Orders are normalized in Postgres as the system of record. The buyer order list is a projection maintained from CDC, keyed by (buyer_id, created_at), with a lag SLO of 2 seconds. For the buyer who just placed an order, the client inserts it optimistically, so read-your-writes holds without making the projection synchronous."

Schema Evolution and Migrations#

This is the hard sub-problem. Designing a schema takes an afternoon. Changing one that has 3 TB of data, 40K QPS, and six readers — without downtime — takes a quarter if you haven't planned for it.

Why It's Hard: Old and New Code Coexist#

During a rolling deploy, some instances run version N and some run N+1 — for minutes in a typical deploy, for hours during a canary, for months on mobile clients and for years in event consumers replaying history. The schema must be valid for every version still running.

The Expand → Migrate → Contract Pattern#

# Goal: rename users.name → users.display_name (or split into first/last, or move to a new table)

Phase 1  EXPAND     ALTER TABLE users ADD COLUMN display_name text NULL;          -- deploy DDL alone
Phase 2  DUAL-WRITE deploy code writing BOTH name and display_name; reads still use name
Phase 3  BACKFILL   UPDATE users SET display_name = name WHERE id BETWEEN $a AND $b
                    AND display_name IS NULL;                                        -- batches of ~1-10K, throttled
Phase 4  VERIFY     SELECT count(*) WHERE display_name IS DISTINCT FROM name;       -- must be 0; check continuously
Phase 5  SWITCH     deploy code reading display_name (behind a flag; canary 1% → 100%)
Phase 6  STOP-WRITE deploy code that no longer writes name
Phase 7  CONTRACT   ALTER TABLE users DROP COLUMN name;                             -- the only one-way door

Rules:

• Each phase is its own deploy; every phase before 7 can be rolled back by redeploying the previous version.
• Wait out the longest-lived reader between 6 and 7: batch jobs, replicas feeding the warehouse, cached ORMs, and any consumer of CDC events that includes the column.
• Backfill throttling: watch replication lag and p99 of the primary; pause when lag > ~2–5s. 500M rows at 5K rows/s is ~28 hours — that's normal. A backfill that "finished in 10 minutes" usually took the primary down with it.
• Idempotent backfill: use WHERE new_col IS NULL or compare-and-set so a restarted batch doesn't clobber rows the dual-write already updated.

Evolving Schemas You Don't Control the Readers Of#

Events and APIs are schemas with unknown readers. Use a schema registry with compatibility modes:

🎯 Staff Move: "The rename is seven deploys over two weeks, not one migration file. I'd schedule the contract step only after the warehouse pipeline and the two CDC consumers have confirmed they've moved — and I'd happily leave the old column for a quarter, because a dead nullable column costs nothing and a premature drop costs an incident."

Ownership of Data#

A schema is also an organizational contract.

The Staff default: one writing service per table, full stop. Readers outside the team consume an API (for request-time reads) or a change event stream / CDC (for projections and analytics), both versioned as contracts. Direct replica reads by other teams are allowed only with a documented, versioned view as the interface — never the base table.

# Ownership record in the schema catalog
table:          orders
owner:          team-checkout            # the only writer
on_call:        checkout-primary
contracts:
  api:          orders.v1 (gRPC)
  events:       orders.order_changed.v3 (Avro, FULL compatibility)
  views:        analytics.orders_v2 (read-only, warehouse)
pii:            buyer_email (tokenized), shipping_address (restricted)
retention:      7 years (financial record)

Visual Guide#

From Access Patterns to Schema#

Expand / Migrate / Contract Timeline#

Ownership Topology#

Implementation Patterns#

Bounded vs Unbounded Relationships#

The single most useful modeling question: "How many can there be?"

Embedding an unbounded collection is how you get 16 MB MongoDB document limits, 100 MB Cassandra partitions, and DynamoDB item-size errors (400 KB) — in production, for your biggest customer first.

Soft Deletes, History, and Temporal Data#

deleted_at columns look cheap and cost you forever: every query needs WHERE deleted_at IS NULL, unique constraints need partial indexes, and "deleted" PII is still stored (a GDPR problem). Prefer: hard delete + an append-only audit/event log for history; or move deleted rows to an archive table. For data that changes over time and must be queried "as of" (prices, entitlements, contract terms), model it explicitly: (entity_id, valid_from, valid_to) rows, never overwrite.

Money, Time, and Identifiers#

• Money: integer minor units (total_cents bigint) + ISO currency code. Never floats.
• Time: store UTC timestamptz; store the user's time zone separately when business logic needs local time (billing cycles, "deliver by 9am").
• IDs: surrogate, opaque, time-ordered (UUIDv7/ULID/snowflake) for B-tree locality; never expose auto-increment externally; never use email or phone as a primary key.
• Enums: store as text or small lookup tables; adding a value must not require a table rewrite, and consumers must tolerate unknown values.

Polymorphism Without Pain#

Three ways to store "a payment method is a card, bank account, or wallet":

Staff default: common columns + a validated JSONB details column, with a JSON schema per type version in code.

Multi-Tenancy in the Schema#

Put tenant_id as the leading column of every primary key and every index on tenant-scoped tables. It enables row-level security, tenant-scoped deletes, tenant-by-tenant migration to a dedicated database later, and sharding by tenant. Retrofitting tenant_id onto 200 tables is a multi-quarter project; adding it on day one costs one column.

Backfill Harness#

backfill(table, batch_size=5000, max_lag_s=3):
    cursor = load_checkpoint() or MIN_ID
    while cursor < MAX_ID:
        if replica_lag() > max_lag_s or primary_p99_ms() > 50:
            sleep(5); continue                             # adaptive throttle
        rows = update_batch(table, cursor, cursor + batch_size)   # idempotent: WHERE new_col IS NULL
        save_checkpoint(cursor + batch_size)                # resumable after crash/deploy
        emit("backfill.rows_per_sec", rows)
        cursor += batch_size

The Numbers in Context#

How This Shows Up in Interviews#

Scenario 1: "Design the data model for X"#

Don't start drawing tables. "Let me list the access patterns first." Five lines with QPS, latency, and freshness. Then the aggregate boundaries ("an order and its line items change together; the product catalog does not"), the partition key if the data will be distributed, and one explicit denormalization with its maintenance path. Close with ownership: "The orders service is the only writer; everything else reads via API or CDC."

Scenario 2: "We need to split the users table — it's 300 columns and 8 teams write to it" (Full Walkthrough)#

Step 1 — Find the seams. "First I'd map which columns each team writes and reads — from query logs, not from asking. Usually it clusters: identity and auth, profile, billing, notification preferences, and a long tail of feature flags. Those clusters are the future tables, and the teams that write them are the future owners."

Step 2 — Establish ownership before moving bytes. "Each cluster gets an owning team and an API. In phase one, other teams stop writing columns they don't own — writes go through the owner's API, even though the data hasn't moved. That's the org change; the storage change is easier after it."

Step 3 — Expand/contract, one cluster at a time. "Start with the lowest-risk cluster — notification preferences. Create notification_prefs(user_id PK, ...), dual-write from the owning service, backfill at ~5K rows/s with lag-based throttling, verify with a continuous diff, switch reads behind a flag, stop writing the old columns, and only drop them after the warehouse and CDC consumers have migrated. Each cluster is 4–8 weeks."

Step 4 — Keep the join path cheap. "All new tables keep user_id as the leading key, so if we shard by user later, they co-locate. Hot read paths that need profile + prefs together get a cached composite from the profile service, not a cross-service join."

Step 5 — Metrics and owner. "migration.diff_rows must stay at 0 for a week before switching reads; db.replication_lag_seconds gates the backfill; users_table.unowned_writes counts writes from services outside the owner list and must reach 0. The identity team owns the program; each cluster's new owner owns its migration."

Why this is a Staff answer: it treats the problem as ownership first, uses log evidence to find seams, sequences migrations by risk, and defines exit criteria with metrics.

Scenario 3: "The order list and order detail show different statuses"#

Two copies of status diverged — a denormalized list item and the order record, updated in separate writes. Fix: make the list a projection from CDC with idempotent, version-guarded upserts (UPDATE ... WHERE version < $new), so reordering can't regress it; add a reconciliation job comparing samples and a projection.drift_count metric. Or, if the store allows, write both in one transaction. Name the owner of the projection.

Scenario 4: "Model this for DynamoDB"#

Access patterns first, then keys: partition key for the entity or aggregate that is queried together, sort key to encode hierarchy and time (ORDER#2026-09-28#id), GSIs only for patterns you've listed — and state that GSIs are eventually consistent. Call out hot partitions (a celebrity seller) and the 400 KB item limit. See DynamoDB.

What Interviewers Probe#

When NOT to Denormalize#

• The write rate on the source is high and the copy count is large. A seller name copied onto 40M orders turns one rename into 40M writes — store a reference, or treat the name at order time as a snapshot and say so.
• The read QPS doesn't justify it. A join at 200 QPS is free; a projection pipeline is not.
• Correctness requires a single value. Balances, inventory counts, and entitlements have one source of truth; readers that need them fresh read the SoR.
• No one will own the maintenance path. An unowned projection will drift, and nobody will notice until a customer does.

Advanced Patterns#

Failure Modes & Operational Reality#

Failure Scenario: The Enum That Broke Billing#

t=0       Orders team adds status 'PARTIALLY_REFUNDED' — additive, passes review, deploys.
t=+1h     First partial refund. CDC event carries the new value.
t=+1h     Billing consumer's exhaustive switch throws on unknown status; message retried, then DLQ'd.
t=+6h     DLQ holds 14K events. Nobody alerts on DLQ depth for this consumer.
t=+3d     Month-end invoicing runs from billing's projection; 14K orders missing. Revenue under-reported.
t=+3d+4h  Fix: consumer maps unknown statuses to a safe default + alert; DLQ replayed.

Detection: consumer.dlq_depth alert at > 0 for financial consumers; consumer.unknown_enum_count. Prevention: enum additions are treated as potentially breaking in the schema standard; consumers must tolerate unknown values; the producing team notifies registered consumers of new enum values one release ahead. Owner: the producer owns the announcement; each consumer owns tolerance.

The Principal Lens#

Why L7 Sees This Problem Differently#

At Staff level, data modeling is about getting one system's schema right and evolving it safely. At Principal level, the data model is the org chart made durable — and the company's ability to reorganize, split services, comply with regulations, and build analytics is capped by how data ownership was drawn years earlier. The Principal sees three portfolio-level risks a single team can't: shared tables that freeze multiple teams at once, the same entity (customer, account) modeled five incompatible ways across services, and PII scattered across dozens of stores without an owner who can answer a deletion request.

The Org-Level Fault Line#

One canonical enterprise data model vs bounded-context models with contracts between them. A single canonical Customer everyone shares sounds like consistency and becomes a coordination bottleneck and a lowest-common-denominator schema. Fully independent models per service create five different definitions of "active customer" and a warehouse full of reconciliation logic.

Cost Model#

Assumptions: engineer ≈ $25K/month fully loaded; migrations measured in engineer-weeks; incident cost excluded unless noted.

The 3-Year Evolution Path#

One-Way Doors vs Two-Way Doors#

The Standard I'd Write#

RFC-DATA-002: Data Ownership and Schema Evolution

Scope: All persistent stores and event topics holding production data.

Mandatory (MUST):

1. Every table and topic MUST have exactly one owning team registered in the data catalog; only the owner's services may write it.
2. Cross-team reads MUST go through a versioned API, event stream, or published view — never base tables.
3. Schema changes MUST follow expand → migrate → contract; column/field removals MUST wait for all registered consumers to confirm and at least 30 days.
4. Migrations MUST set lock_timeout and use online DDL on tables > 10 GB; backfills MUST be throttled on replica lag and resumable.
5. Event schemas MUST be registered with FULL compatibility; consumers MUST tolerate unknown enum values.
6. PII fields MUST be classified at creation.

Recommended (SHOULD): tenant-leading keys for multi-tenant data; outbox pattern for event publication; integer minor units for money.

Exceptions: data platform review within 5 business days; time-boxed.

Success metrics: tables with a single registered owner (100% in 3 quarters); schema-related Sev2+ incidents (−50% YoY); median time to fulfill a data-deletion request (< 7 days).

What I'd Tell the VP#

"Our biggest data risk isn't a bad schema — it's that several core tables are written by many teams, so every change needs a cross-team meeting and some changes can't be made at all. That's slowing roadmap work and making privacy requests hard to answer. I'm proposing clear ownership for every table, with other teams reading through stable interfaces, plus tooling that makes safe migrations the default. It costs about two engineers on the data platform and a few quarters of migration work spread across teams. The payoff is faster feature delivery on core entities, fewer migration outages, and a defensible answer when a regulator asks where customer data lives."

Principal Interview Signals#

Staff answers that L7 interviewers find insufficient:

• "We'll do expand/contract for this migration" — correct, but silent on why the org keeps needing risky migrations on shared tables.
• "Let's normalize it properly" — optimizes one schema while ignoring the five other services that model the same entity.
• "Each team owns its data" — without contracts, a registry, or a shared-ID standard, this is fragmentation, not ownership.

🧭 Principal Move: "I'd make 'who is the one writer of this table?' a required field in the catalog and a CI check on migrations. Most of our data-model debt is ownership debt, and it's cheapest to pay down before the next reorg."

In the Wild#

Amazon: Single-Table Design in DynamoDB#

AWS's public DynamoDB guidance and re:Invent talks popularized access-pattern-first, single-table design: list every query, then design partition and sort keys (and a minimal set of GSIs) so each query is one Query call, co-locating heterogeneous entity types in item collections.

Staff insight: this is the extreme version of "model the queries, not the entities." In interviews, it's the clearest example of why access patterns must come before the schema in stores without joins.

GitHub: gh-ost for Online Schema Migrations#

GitHub built and open-sourced gh-ost, a triggerless online schema migration tool for MySQL that copies rows to a shadow table while tailing the binlog for ongoing changes, with throttling on replica lag and a controlled cut-over. It exists because ALTER TABLE on very large, hot MySQL tables was an operational risk.

Staff insight: at scale, schema changes are production operations with throttles, pause buttons, and cut-over plans. Mentioning lag-based throttling signals you've run a real migration.

Confluent Schema Registry and Event Contracts#

Kafka ecosystems commonly use a schema registry (Confluent's being the most widespread) to enforce Avro/Protobuf/JSON Schema compatibility modes — backward, forward, full — on every producer write, preventing a producer from publishing a schema that would break existing consumers.

Staff insight: events are schemas with unknown readers and long replay horizons. Enforcing compatibility at publish time turns a social contract into a mechanical one.

Staff Calibration#

What Staff Engineers Say (That Seniors Don't)#

Staff Sentence Templates#

"The top access pattern is [query] at [QPS] with a [latency] budget, so the key is [partition key, sort key]."

"I'm duplicating [field] into [projection] because [read path] runs at [QPS]; it's maintained by [mechanism], lag SLO [seconds], and [team] owns the drift metric."

"This change is [N] deploys: expand, dual-write, backfill at [rows/s] gated on [lag threshold], verify, switch, stop-write, contract after [longest reader] migrates."

"[Team] is the only writer of [table]; everyone else reads through [API / event / view], versioned as [contract]."

"[Relationship] is [bounded at N / unbounded], so it's [embedded / its own partition keyed by (parent, time)]."

Common Interview Traps#

• Starting with an ER diagram. Without access patterns, you can't justify keys or denormalization.
• Embedding unbounded collections. Always ask "how many can there be?" — and plan for the largest customer.
• Denormalizing without a maintenance path. Every copy needs an owner, an update mechanism, and a drift check.
• One-step breaking migrations. Rename, drop, and NOT NULL additions break running code.
• Using natural keys as primary keys. Emails and phone numbers change.
• Floats for money, local time for timestamps. Both are classic, permanent data-quality bugs.
• Shared write access across services. It's a distributed system with no protocol.
• Forgetting analytics. Designing OLTP tables to also serve monthly reports ruins both.

Practice Drill#

Prompt: "A B2B SaaS app stores all tenant data in one Postgres database with no tenant_id on most tables — tenancy is derived through joins to accounts. Your largest customer wants data residency in the EU, and two others want dedicated databases. What do you do?"

Where This Appears#

• Database Selection — how access patterns drive the choice of store before the schema
• Database Sharding — partition keys as a data-model decision
• Payment Processing — ledgers, money representation, and immutable history
• News Feed — denormalized, precomputed feeds vs normalized fan-out-on-read
• Chat Messaging — time-bucketed, unbounded message partitions
• Data Pipeline Patterns — CDC, outbox, and projections
• Database Indexing — indexes as the physical side of access patterns
• Sharding & Partitioning — choosing the key that decides where data lives

Related Technologies: PostgreSQL · DynamoDB · Cassandra · Apache Kafka