Data modeling standards preserve meaning across systems and time

A data modeling standard is a governed contract for representing facts, entities, events and relationships consistently enough that producers and consumers can evolve independently. Naming conventions help, but they are only the visible edge. A useful standard covers grain, identity, ownership, types, units, time semantics, nullability, constraints, history, classification, metadata, lineage, compatibility and change control. Its purpose is interoperable meaning and reliable decisions, not aesthetic uniformity.

State the grain before listing columns: what does one row represent, at which event or snapshot boundary, and can the same real-world occurrence appear more than once? Fact tables need an explicit event, measurement or periodic-snapshot grain. Dimensions need a business entity and history policy. Mixing order, item and payment grains creates double counting even when every column name follows the style guide.

Keys carry different guarantees. A natural or business key comes from the domain and may change or be reused; a surrogate key provides warehouse or storage identity but does not prove real-world uniqueness; a primary key enforces row identity within its relation; a foreign key can enforce referential integrity within its supported boundary. Document scope, stability, generation, tenant component, collision behavior and reconciliation. Never infer that an opaque UUID makes duplicate entities impossible.

Choose types for semantics, precision and portability. Money normally needs exact numeric representation plus currency; floating point is inappropriate where exact decimal equality matters. A timestamp must state whether it is an instant, local civil time or date-only value, its time zone and daylight-saving behavior. Durations, intervals and business dates are distinct. Boolean is not a substitute for multi-state lifecycle. Free-form text can postpone modeling but shifts validation and compatibility cost downstream.

Null, absent, empty, zero, false, unknown, not applicable and not yet observed are not interchangeable. Define which states are valid and whether the system needs separate reason or status fields. Defaults can silently convert missing producer intent into apparently known data; a schema default also has format-specific read/write semantics. Make requiredness reflect the lifecycle stage and migration plan, not an ideal final state imposed abruptly on old records.

Constraints are executable documentation where the platform can enforce them: not-null, unique, check, primary and foreign keys. Use them for invariants inside the database boundary, with validated ingestion and contract checks across distributed systems. Do not claim a check constraint proves an external lookup or time-varying business rule unless its implementation actually can. Choose referential actions deliberately; cascade delete can be correct ownership semantics or catastrophic surprise.

Naming should optimize comprehension, consistency and tool portability. Prefer stable domain language, predictable case and separators, singular/plural policy, explicit units and time suffixes, and avoid reserved words or quoted-case traps. Names cannot carry the entire definition: customer_id still needs its namespace, source, persistence and tenant scope. Renaming can be breaking to SQL, dashboards, files and generated clients, so use aliases or staged migrations and update metadata.

Shared dimensions and reference data require semantic governance. A conformed customer, product, location or calendar dimension uses compatible keys, attributes and definitions across facts so measures can be combined. Conformance does not require one physical table, but it requires controlled mappings and ownership. Codes need authoritative source, allowed values, effective dates, labels, deprecation and unknown handling. Never overload a code because an existing value seems close.

History policy is part of meaning. Type 1 dimension handling overwrites an attribute and serves current-state questions; Type 2 adds a new dimension row for a tracked change so facts can join as-was attributes (effective dates or a current flag are common implementations, not the definition); other patterns may store limited previous values, separate mini-dimensions or events. Define valid time versus system/processing time, interval inclusivity, late-arriving changes, corrections, backfills and current-row derivation. Prevent overlapping effective intervals where the model promises one state at a time.

Schema evolution is consumer-specific. Adding an optional field can still break strict readers, generated code, signatures or SELECT-star pipelines. Removing, renaming, narrowing, changing units or meaning, altering requiredness or reusing identifiers is usually riskier. Avro field defaults are used when a reader schema has a field the writer's data lacks; they do not make the field optional at write time or backfill stored files, and union defaults must match a permitted branch. Parquet logical annotations distinguish semantic types from physical storage. Test representative readers and stored historical data instead of calling every additive change safe.

Metadata turns a schema into a usable data product. Record business definition, grain, owner and steward, source, lineage, quality expectations, refresh/freshness, classification, permitted use, retention, version, known limitations and examples. DCAT can describe datasets, distributions and services; CSVW describes tabular structure and dialect; lineage events connect jobs, runs and datasets. A catalogue entry is not ownership unless a real person or team maintains it.

Standards need governance proportional to interoperability risk. Maintain a versioned catalogue of required, recommended and optional rules; provide examples, templates, linters and migration guidance; allow time-bound exceptions with owner and rationale. Peer review should include producers, consumers, data engineering, analytics, security/privacy and domain experts. Measure adoption, semantic defects, reconciliation work, change failures and user comprehension—not only lint compliance.

Test standards against adversarial cases: duplicate business identities, tenant collision, leap day and daylight-saving transitions, currency conversion, precision overflow, unknown codes, late facts, dimension changes, deletion, privacy restriction, old reader/new writer and backfill. Validate both structure and meaning with source reconciliation and representative analytical queries. A standard is successful when independently built data combines predictably and changes without silent semantic corruption.