Data Models: Keeping Competing Possibilities Visible
A data model can keep competing possibilities and assumptions visible when it is designed with explicit documentation, versioning, and branching, but this visibility is not automatic—it requires deliberate practices. By treating the model as a living artifact with recorded decisions, alternative structures, and rationale, teams can surface trade-offs and avoid hidden biases. This article explains how data models can preserve competing possibilities, the techniques that make assumptions explicit, and the common pitfalls that hide them.
What Visibility Means in a Data Model
Visibility in a data model means that anyone reading it can see not only the current structure but also the alternatives that were considered and the reasons for the choices made. A model that simply shows final tables, columns, and relationships hides the decision process. For example, a customer table with a single status field implies that a customer has one status at a time, but the team might have debated whether a customer could be both active and vip. Without a record of that debate, the model silently enforces a single-status assumption.
Visibility is achieved through three layers: the schema itself, the metadata (descriptions, tags, and constraints), and the version history. Schema shows what is; metadata explains why; version history shows what changed and what was considered. When all three are present, competing possibilities remain visible as alternatives in the history or as commented-out designs.
Why Models Hide Assumptions
Most data models are built to solve a specific problem at a specific time. The pressure to deliver quickly leads to a single design that appears natural and inevitable. Assumptions become embedded in the structure: a price column might assume a single currency, a birth_date might assume a Gregorian calendar, or a user_id might assume one user per account. These assumptions are not documented because they seem obvious at the moment.
Another reason is that tools and conventions favor a single canonical model. Databases enforce one schema, and most modeling tools show one diagram. Alternatives exist only in discussions, emails, or whiteboards, which are not part of the model. Over time, those alternatives are forgotten, and the model appears to be the only possible representation.
Techniques to Keep Alternatives Explicit
Several practices help keep competing possibilities visible without sacrificing clarity:
- Versioned schemas with migration history: Use migration files (e.g., SQL scripts or tools like Flyway or Liquibase) that record every change. Each migration can include a comment explaining why the change was made and what alternative was rejected.
- Model documentation as part of the code: Embed descriptions in the schema (e.g., column comments in SQL, docstrings in Python data classes) that state the assumption and the considered alternative. For example:
-- Assumption: one currency per order. Alternative: multi-currency orders rejected due to scope. - Branching in version control: Keep a branch or a tagged version that explores an alternative structure. Even if the alternative is not adopted, the branch remains in the repository and can be revisited.
- Decision records (ADRs): Write a short document for each significant modeling decision, listing the options, the trade-offs, and the chosen option. Store these in the same repository as the model.
- Modeling tools with “what-if” views: Some tools (like dbt with multiple environments or Erwin with subject areas) allow you to create alternative views of the same data without changing the physical model.
When to Use Which Technique
The choice of technique depends on the team size, the model’s complexity, and the regulatory environment. For a small team with a simple model, a comment in the schema might be enough. For a large enterprise with many stakeholders, ADRs and branching are more robust.
Consider a team designing an e-commerce data warehouse. They need to decide whether to store order_amount as a decimal or as an integer in cents. The alternatives are: (a) decimal with 2 places, (b) integer cents, (c) decimal with 4 places for future precision. A migration comment can say: “Chose decimal(10,2) for readability; integer cents considered but rejected due to reporting complexity.” This makes the alternative visible to anyone who reads the migration history.
Common Problems and How to Avoid Them
Even with good intentions, visibility can fail. Here are common problems and their fixes:
- Over-documentation: Too many comments and branches can clutter the model and obscure the current state. Fix: Keep documentation concise and use tags to mark active vs. historical alternatives.
- Stale branches: Alternative branches that are never merged or deleted become noise. Fix: Regularly review branches and archive or delete those that are no longer relevant.
- Tool limitations: Some databases do not support column comments, and some modeling tools do not integrate with version control. Fix: Use a separate documentation file that references the model elements.
- Assumption creep: New team members may not read the history and may introduce conflicting assumptions. Fix: Include a README in the model directory that summarizes key decisions and links to ADRs.
Comparing Approaches: Inline vs. External Documentation
Inline documentation (comments in schema) is always visible in the code but can be verbose. External documentation (ADRs, wiki pages) is more readable but can drift from the actual model. A hybrid approach works best: inline comments for quick reference, external ADRs for detailed rationale.
For example, a column comment might say: “status can be active, inactive, or pending. Alternative: separate boolean flags rejected for simplicity.” The ADR would explain the full discussion, including the use case for a pending state and why a single field was chosen over three booleans.
Costs and Benefits of Visible Alternatives
Keeping competing possibilities visible has a cost: time to document, maintain branches, and review decisions. For a small project, this overhead might be unnecessary. For a large, long-lived project, the cost is justified by the benefit of avoiding costly rework when an assumption is later found to be wrong.
A tangible benefit is the ability to quickly revert to an alternative if the chosen design fails. For instance, if a company initially models customer_address as a single string but later needs to support multiple addresses, the branch that explored a separate addresses table can be resurrected, saving weeks of design work.
Frequently Asked Questions
Q: How do I document an assumption without making the schema unreadable?
A: Use a concise comment on the column or table, and put the full rationale in an ADR. For example, a comment like “-- Assumption: one active subscription per user. See ADR-014.” keeps the schema clean while pointing to the details.
Q: Can I keep alternatives visible in a data warehouse without affecting performance?
A: Yes, you can create views or logical models that represent alternatives without duplicating physical tables. These views can be used for exploration and are dropped if not needed.
Q: What if my team uses a visual modeling tool that doesn’t support comments?
A: You can export the model to a text format (like SQL or a diagram-as-code tool) and add comments there. Or maintain a separate markdown file that maps each entity to its alternatives and assumptions.
Q: Is it worth keeping branches for alternatives that are clearly inferior?
A: Yes, if the alternative might become relevant later due to changing requirements. But label them clearly as “exploratory” to avoid confusion.
Q: How do I get my team to actually read the documentation?
A: Make documentation part of the code review process. Require that any schema change includes a comment or ADR update, and review that as part of the pull request.
Q: What’s the difference between an assumption and a constraint?
A: A constraint is a rule enforced by the database (e.g., NOT NULL). An assumption is a belief about the data that is not enforced but is relied upon (e.g., “all prices are in USD”). Both should be documented, but assumptions are more likely to be hidden.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.