Data Modeling Best Practices: Naming, Keys, Nullability, PII and Documentation

Most data model problems are not caused by choosing the wrong methodology. They are caused by small inconsistencies that accumulate: a column called cust_id in one table and customer_id in another, a nullable foreign key nobody meant to allow, an email address copied into twelve tables, a fact table whose grain nobody wrote down. Each is minor. Together they make a model hard to trust and expensive to change.

This guide collects the conventions that prevent those problems. They apply whether you build Kimball stars, Data Vault, 3NF or a mix. Use the checklist at the end as a design review template.

Naming conventions

The purpose of naming conventions is predictability: someone who has never seen a table should be able to guess its columns, and someone writing a join should not need to look up whether it is cust_id, customer_id or CustomerID.

Case and characters

• Use lower snake_case for every table and column: order_line, net_amount.
• Avoid quoted identifiers, spaces and reserved words. Snowflake upper-cases unquoted identifiers, Postgres lower-cases them, BigQuery is case-sensitive for some objects. Lower snake_case without quotes behaves predictably everywhere.
• Avoid abbreviations unless they are universal in the business (sku, vat, iban). cust, amt and qty save little and add ambiguity. If you do abbreviate, publish the list.

Table names

Prefix by layer or role so the purpose is visible:

Choose singular or plural and stick to it. Singular (customer) reads naturally in joins (customer.email); either is fine if consistent.

Column names

• Keys: <entity>_key for warehouse surrogates, <entity>_id for source or natural identifiers. customer_key and customer_id should never mean the same thing.
• Foreign keys use the same name as the key they reference. fct_order_line.customer_key references dim_customer.customer_key. Role-playing references add the role: ship_date_key, order_date_key.
• Booleans: is_ or has_ prefix: is_active, has_discount.
• Dates and timestamps: _date for dates, _at for timestamps: order_date, created_at. State the time zone in the type (timestamptz) or in documentation, and prefer UTC.
• Amounts: suffix with what they are, and include currency where it varies: net_amount, net_amount_usd, tax_amount.
• Counts: _count: line_count, seat_count.
• Avoid generic names: value, data, type, status, name alone are ambiguous after a join. Use order_status, product_name.

Keys

• Every table has a declared primary key, even on warehouses that do not enforce it. Snowflake, BigQuery and Databricks accept key declarations as metadata; BI tools and query optimisers can use them.
• Every table has a declared grain that the key enforces. See what is grain in data modeling.
• Natural keys are constrained or tested for uniqueness, even when a surrogate is the primary key.
• Surrogate keys are stable across rebuilds. Never generate them with row_number() over a full reload of a table that other tables reference.
• Unknown members: dimensions include a -1 (or hashed) "Unknown" row, so facts with missing references keep inner-joinable keys.

The trade-offs between key types are covered in surrogate keys vs natural keys.

Data types

• Money: numeric/decimal with explicit precision and scale (numeric(18,2), or numeric(18,4) for unit prices and FX). Never float, which cannot represent most decimal amounts exactly.
• Identifiers: store as strings unless they are genuinely numeric and you will do arithmetic on them. Account numbers, postcodes and phone numbers with leading zeros break as integers.
• Dates vs timestamps: use date when only the day matters, so time-zone conversions cannot shift it.
• Enumerations: a short string with a check constraint or an accepted-values test is clearer than an integer code nobody remembers.
• Consistent widths: the same column (say country_code char(2)) has the same type in every table, so joins do not need casts.
• Semi-structured columns (variant, json, struct) are fine in staging. In marts, extract the fields people use into typed columns.

Nullability

Null has three different meanings in practice: unknown, not applicable, and not yet. A model should make clear which applies.

• Default to not null. Make a column nullable only when you can say why a value can be absent.
• Foreign keys on facts are not null. Point missing references at the Unknown member instead. Nullable foreign keys produce rows that disappear from inner joins and from reports.
• Measures: decide whether null means zero. A null discount_amount should usually be 0. A null temperature_reading should stay null, because zero is a real value.
• Distinguish "not yet" from "unknown". In an accumulating snapshot, shipped_date_key pointing at a "Not yet shipped" date row is clearer than null.
• Beware null in comparisons and hashes. a <> b is not true when either is null. Use is distinct from, or coalesce before hashing for change detection.

create table fct_order_line (    order_date_key   integer       not null,    customer_key     bigint        not null,  -- -1 when unknown    product_key      bigint        not null,    promotion_key    bigint        not null,  -- -1 = "No promotion"    order_id         varchar(40)   not null,    line_number      integer       not null,    quantity         integer       not null,    net_amount       numeric(18,2) not null,    discount_amount  numeric(18,2) not null default 0,    gift_message     varchar(500)             -- nullable: genuinely optional);

PII and sensitive data

Personal data needs to be designed for, not bolted on.

• Classify columns at design time: direct identifiers (name, email, phone, national ID), quasi-identifiers (date of birth, postcode), sensitive categories (health, financial), and non-personal data. Record the classification as column metadata or tags.
• Keep PII in as few tables as possible. Facts should reference a customer by surrogate key, not carry the email. In Data Vault, put PII in its own satellite.
• Separate identifying attributes from analytical ones. A dim_customer with segment, country and tenure serves most analysis. Name and email can live in a restricted dim_customer_pii or be masked.
• Use platform controls. Masking policies and row access policies in Snowflake, column masks and row filters in Databricks Unity Catalog, policy tags in BigQuery. Design the model so these can be applied by column and by table without rewriting queries.
• Plan for deletion. Right-to-erasure requests are much easier when PII is isolated and keyed. If a customer's email is embedded in event payloads across many tables, deletion becomes a search exercise.
• Pseudonymise join keys when sharing data. A hashed identifier with a secret salt lets analysts count unique customers without seeing who they are.

In healthcare, patient identifiers and clinical details are typically subject to stricter rules. Treat the encounter fact and the patient identity dimension as separate security domains.

Relationships and integrity

• Declare foreign keys in the model and the DDL, even where the platform does not enforce them. They document join paths and let tools draw ERDs and infer relationships.
• Test them. A relationship test checks that every fct_order_line.customer_key exists in dim_customer.
• Record cardinality. One-to-many and many-to-many relationships behave differently in joins. Many-to-many relationships need a bridge table and a note on how to avoid double counting.
• Avoid circular join paths. If there are two ways to get from a fact to a dimension, queries will disagree. Make one path canonical.

Documentation

Documentation that is separate from the model goes stale. Keep it next to the definitions.

For every table:

• A one-sentence description and the grain.
• The owner (team, not person).
• The source and refresh frequency.
• Known caveats ("orders before 2023 have no channel").

For every column:

• A description in business terms, not a restatement of the name. "Net amount" is not a description; "Line amount after line-level discounts, excluding tax and shipping, in order currency" is.
• Allowed values for enumerations.
• Units and currency.
• Sensitivity classification.

comment on table fct_order_line is  'Grain: one row per product line on a customer order. Source: shop platform. Refreshed hourly. Owner: commerce analytics.';comment on column fct_order_line.net_amount is  'Line amount after line-level discounts, excluding tax and shipping, in order currency.';

In dbt, the same information goes in schema.yml, where it also feeds tests and generated docs. For business-level meaning that spans tables, use an ontology and a semantic layer; see how to build an ontology for your data.

Testing

Tests are executable documentation. At a minimum:

• Primary key / grain uniqueness on every table.
• not null on required columns.
• Referential integrity for every foreign key.
• Accepted values for enumerations.
• Freshness on source-fed tables.
• Business rules where they exist (end date after start date; balances reconcile to the ledger).

Change management

• Additive changes (new table, new nullable column) are safe.
• Breaking changes (rename, type change, grain change, removed column) need a new version or a deprecation period. Keep the old name as a view for a release cycle.
• Version the model in source control, and review changes like code.

The checklist

Use this in design reviews:

• Every table has a declared grain written in business language.
• Every table has a primary key; natural keys are unique-tested.
• Names follow the convention: snake_case, prefixes, _key/_id, is_, _date/_at.
• Foreign key columns share names with the keys they reference.
• Money is numeric with explicit scale; identifiers are strings.
• Columns are not null unless absence is meaningful and documented.
• Fact foreign keys point to Unknown members rather than null.
• Measures are stored as additive components, not ratios.
• PII columns are classified, isolated and covered by masking or access policies.
• Relationships are declared with cardinality; many-to-many uses a bridge.
• Every table and column has a business description, owner and source.
• Tests cover grain, nulls, relationships and accepted values.
• Breaking changes have a migration or deprecation plan.

Practise these habits in offdataai Learn

Every track in offdataai Learn bakes these practices into its exercises. You build models in the Model Builder, and an instant automated check flags missing grains, unconstrained keys, nullable foreign keys, ratio columns and undocumented PII, so the habits stick. It is hands-on and included in the Explorer plan at $1/month.

To see these conventions applied across complete models, open any of the templates, then build your own in the Model Builder.