Schema Is Strategy: Data Modeling Decisions That Make or Break Infra Products

A data schema is a strategic commitment, not an engineering formality: it fixes your query costs, your migration difficulty, and how easily the product can absorb new use cases for years. Get it wrong and you inherit slow queries, brittle joins, and multi-quarter migrations. Get it right and the product can flex without a rewrite.

Quick Answer: Treat schema design as a product decision with a cost curve, not a one-time technical task. Normalize for correctness and flexibility, denormalize deliberately for known hot paths, watch cardinality on every index, and keep new or unstable data schema-on-read until the shape truly stabilizes.

Why Schema Decisions Are Product Decisions, Not Just Engineering Ones

A schema encodes assumptions about what the product will need to ask of its data — and every assumption baked in early becomes a constraint later. The PM who treats schema as "an engineering detail we'll fix later" is choosing, by default, to inherit whatever constraints the first version happens to lock in.

This matters more in infrastructure products than almost anywhere else. An infra product's schema typically underlies billing correctness, alerting accuracy, and customer-facing dashboards simultaneously — a bad schema decision doesn't just slow one feature, it propagates errors into every downstream consumer.

Three reasons this is a PM problem and not purely an engineering one:

1. Schema shapes what's cheap to ask. A model optimized for writes makes certain analytical questions expensive or impossible without a rework — and "impossible without a rework" is a roadmap decision in disguise.
2. Schema shapes what's easy to change. Adding a field to a flexible document store is trivial; adding a column with a NOT NULL constraint to a table with a billion rows is a migration project.
3. Schema locks in unit economics. In infra and metering products specifically, the schema often is the cost model — every extra join, every unindexed scan, is marginal cost per customer, multiplied by scale.

If you're new to the infra PM discipline broadly, the complete guide to the infra PM role covers where schema decisions sit alongside the rest of the job — SLOs, reliability commitments, and platform roadmaps.

Normalization vs. Denormalization: The Real Tradeoff

Normalization reduces data duplication and keeps a single source of truth per fact, at the cost of more joins at query time; denormalization trades that duplication back in to make specific reads faster and cheaper. Neither is "correct" in the abstract — the right choice depends on your read-to-write ratio and how often the duplicated fields actually change.

Normalization (per Edgar Codd's original relational model, still the reference point taught in every database systems course) organizes data so each fact lives in exactly one place. This is the safer default when:

• Write correctness matters more than read latency (billing ledgers, audit logs).
• The data changes frequently and duplicating it risks drift.
• You don't yet know your dominant query patterns — normalized schemas are more adaptable to new questions.

Denormalization intentionally duplicates data to avoid joins, and is the right call when:

• A specific read path is hit constantly (a dashboard query run on every page load).
• The duplicated fields rarely change, so staleness risk is low.
• You've already measured the join cost and it's the actual bottleneck — not a guess.

The practical rule: normalize by default, denormalize surgically once you have production query data proving where the cost actually is. Denormalizing speculatively — before you've measured — is how teams end up maintaining three copies of a field nobody reads twice a day.

Cardinality and Index Cost: The Silent Budget Killer

Cardinality — the number of distinct values a field can take — determines whether an index actually helps or quietly becomes dead weight that still costs you on every write. High-cardinality fields (user IDs, trace IDs, timestamps) index well and support selective queries; low-cardinality fields (boolean flags, status enums with three values) often don't justify a dedicated index at all.

This is where telemetry and metering products get into trouble fastest, because their core objects — events, spans, usage records — are naturally high-volume and high-cardinality on multiple dimensions at once.

Three cardinality mistakes that recur across infra products:

1. Indexing every dimension "just in case." Each index adds write amplification — every insert now updates N index structures, not one. A table with six indexes on a high-write telemetry path can see write latency dominated by index maintenance rather than the actual insert.
2. Composite keys in the wrong order. An index on (customer_id, event_type, timestamp) serves very different queries than (event_type, customer_id, timestamp) — leading-column order determines which queries can use the index at all, per how B-tree index range scans work in every major relational engine.
3. Unbounded cardinality growth from user-generated dimensions. Letting customers attach arbitrary key-value tags to events (a common metering feature request) can silently explode cardinality on an indexed field, degrading performance for every tenant sharing that table.

The pattern to watch for: a metrics or observability system where a dimension like tag_value is allowed to be arbitrary user input and is also indexed. This is the single most common way infra products self-inflict a cardinality explosion — a well-documented failure mode across time-series and metrics systems generally, not specific to any one vendor.

If you're setting reliability targets on top of this data, speccing SLOs as a PM walks through how query cost and cardinality decisions directly affect what SLOs are even measurable cheaply enough to sustain.

Schema-on-Write vs. Schema-on-Read: Choosing Your Flexibility Budget

Schema-on-write enforces structure at insert time (relational databases, strongly typed event schemas), catching bad data early but making structural changes expensive. Schema-on-read defers structure interpretation to query time (data lakes, JSON blobs, wide-column stores), accepting looser guarantees in exchange for near-zero-cost field additions.

Schema-on-write is the right default when:

• Downstream consumers (billing, compliance reporting) need guaranteed structure — an invalid record breaking a query is safer than an invalid record silently succeeding and corrupting a report.
• The domain is genuinely stable — you've validated the shape against multiple real use cases already.
• You need database-level constraints (foreign keys, NOT NULL, uniqueness) to prevent classes of bugs entirely.

Schema-on-read is the right default when:

• You're still discovering what fields matter — an early telemetry product exploring which event attributes actually predict churn or usage patterns.
• Different producers (client SDKs, partner integrations) will send meaningfully different shapes of the "same" event, and forcing one shape loses information.
• The cost of being wrong about the shape today is higher than the cost of a slightly messier query later.

A practical middle path many infra products land on: schema-on-write for the core, stable entities (accounts, invoices, subscriptions), schema-on-read for the exploratory edges (custom event properties, arbitrary metadata, experimental telemetry fields). This isn't indecision — it's matching flexibility budget to actual uncertainty, entity by entity.

A Worked Entity Model for a Metering Product

Metering products need to answer three recurring questions cheaply: what happened, how much of it happened, and who gets billed for it. A workable entity model separates the immutable event stream from the aggregated, billable rollups derived from it — because those two things have completely different read/write patterns and completely different tolerance for schema rigidity.

Core entities:

The usage_event table is where schema-on-read earns its keep: a core set of required columns (event_id, customer_id, event_type, occurred_at) enforced with constraints, plus a flexible attributes field (JSON or similar) for whatever each integration sends. This lets new event types onboard without a migration, while the billing-critical usage_aggregate and plan_rate_card tables stay strictly normalized and typed, because a silent type error there is a revenue-accuracy incident, not an inconvenience.

Cardinality guardrails for this model specifically: index usage_event on (customer_id, occurred_at) for the dominant "recent events for this customer" query, and resist indexing inside the attributes blob unless a specific, measured query pattern justifies it. Every unindexed attribute you allow to stay flexible is optionality you haven't paid for yet.

When to Lock a Schema vs. Keep It Flexible

Lock a schema once three conditions hold simultaneously: the entity is consumed by more than one downstream system, changing its shape would require a coordinated migration across teams, and you've validated the shape against at least two to three real use cases rather than one. Absent any of those, keep it flexible longer — locking early is the more expensive mistake to reverse.

A simple rule of thumb:

Lock when the cost of being wrong about flexibility exceeds the cost of being wrong about rigidity. Billing, compliance, and cross-team-consumed entities lock early. Exploratory, single-consumer, evolving entities stay flexible until usage proves out the shape.

Signals it's time to lock:

1. Two or more services now read the entity and would both need updating on a shape change.
2. You've shipped the same "quick migration" for this table twice already.
3. The entity underlies a customer-facing number (an invoice total, an SLA metric) where a schema bug is a trust problem, not just a bug.

Signals it should stay flexible:

1. You're still in discovery on what a "unit" of usage even means for a new product line.
2. Only one internal consumer reads the field, and it's the team that owns the write path.
3. Customer or partner feedback is actively reshaping what data even needs capturing — locking now would just mean unlocking again in a quarter.

Because migrations are never purely technical once real customers depend on the old shape, it's worth treating them as their own roadmap item rather than incidental cleanup work — migrations as products on the roadmap covers how to scope and sequence that work. And because the pain of a late migration is really a pain of changed requirements arriving late, grounding schema decisions in jobs-to-be-done thinking or a mapped customer journey up front reduces how often you discover the "real" shape only after you've already locked the wrong one.

Pressure-Testing a Schema Before It Becomes Permanent

The riskiest moment in schema design is the gap between "this looks right on a whiteboard" and "this is now a migration nobody wants to touch." Most infra teams don't discover a cardinality problem or a bad normalization call until it's already in production and expensive to reverse.

Key Takeaways

• Schema is a strategic commitment, not an engineering afterthought — it fixes your query cost, migration difficulty, and flexibility for years.
• Normalize by default, denormalize surgically — only after production query data proves where the actual cost is, not on a guess.
• Cardinality determines index value — high-cardinality fields index well; indexing arbitrary user-generated dimensions is a common, avoidable cost explosion.
• Match schema style to certainty — schema-on-write for stable, cross-consumed, billing-critical entities; schema-on-read for exploratory or evolving ones.
• A metering model should separate raw events from billable aggregates — they have different write/read patterns and different tolerance for rigidity.
• Lock a schema when multiple systems depend on it and the shape is validated — not before; premature locking is usually the more expensive mistake.
• Pressure-test relationships and cardinality before committing — tools that generate DDL from an entity model make this cheap to do early.