How to document data models: grain, relationships, and lineage

Complete data model documentation explains the purpose, grain, tables, columns, keys, relationships, transformations, and origin of a model. It connects business context with technical structure so that BI developers, analytics engineers, and analysts can use the same model with confidence.

An automatically generated ER diagram helps with this, but it is not enough. It shows relationships, yet it rarely explains why a table exists, which row it represents, or which quality limits apply.

The three levels of data model documentation

Model level

Describes the purpose, business scope, owner, refresh, sources, and known limits of the entire model.

Table level

Explains the grain, role, keys, load behaviour, and relationships of a table.

Column level

Documents the business meaning, data type, allowed values, null behaviour, and sensitive content of a field.

Not every column needs a long description. The most important ones are keys, business-ambiguous fields, status values, and columns used in KPIs or relationships.

Required fields at the model level

A data model should answer at least these questions:

Start with purpose and grain

The grain defines what a single row of a table means. Document it for every fact table individually: one model can contain invoice line items, order line items, and monthly inventory balances at different levels.

Weak:

FactSales contains sales.

Robust:

One row in FactSales represents a posted invoice or credit note line item. The source system, document number, and line number identify the business transaction unambiguously.

Without a grain, users can double-count amounts, build incorrect joins, or aggregate metrics at an unsuitable level.

Also document whether cancelled, internal, or not-yet-invoiced transactions are included. Date, product, and customer are analysis attributes; they do not necessarily identify a single line item. Microsoft emphasises a consistent grain for fact tables in its introduction to star schemas for Power BI.

Describe tables by their role

In a star schema, tables serve different purposes:

• Fact tables hold measurable business transactions at a defined grain.
• Dimension tables provide descriptive attributes for analysis and filtering.
• Bridge tables model many-to-many relationships.
• Helper and mapping tables support technical rules or harmonisation.

The role label alone is not enough. Add the purpose, primary key, load type, and time behaviour.

For dimensions, for example, it matters whether changes are overwritten or historised. For facts, it should be clear whether late postings update existing rows or create new ones.

Document columns and keys

For each relevant column, keep these together:

• display name and technical name,
• business description,
• data type,
• allowed values or unit,
• null behaviour,
• key role,
• origin or transformation rule,
• usage in KPIs.

A primary key identifies a row within a table. A foreign key points to a row in another table. In analytical models, technical surrogate keys can be used that carry no business meaning. This distinction should be visible.

Make relationships understandable

A model relationship includes the tables involved, key columns, cardinality, and business meaning. In semantic models such as Power BI, further properties are added:

• source and target table,
• key columns used,
• cardinality,
• filter direction,
• active or inactive status,
• business meaning.

Example:

FactSales.customer_key references DimCustomer.customer_key as a many-to-one relationship. The dimension filters the fact table; every sales line item is assigned to exactly one customer version valid at the posting date.

Distinguish a database rule from a Power BI model relationship: a line in the semantic model does not prove that the source database enforces a foreign key. Filter direction and active status control evaluation within the model. Microsoft explains the details under model relationships in Power BI Desktop.

Foreign key and lineage are not the same

A foreign key describes a structural relationship between records: which row references which other row?

Lineage describes the origin and processing of data: which source a field comes from, which transformations it passes through, and which downstream models or KPIs use it.

Example:

• Foreign key: FactSales.product_key references DimProduct.product_key.
• Lineage: FactSales.net_revenue comes from ERP invoice line items, is reduced by returns, and feeds into the KPI net revenue.

A relationship can exist without documented lineage. Conversely, a field can come from a source without holding a foreign key to that source.

Document transformations

Not every technical row in the SQL model belongs in business documentation. Focus on rules that change meaning or quality:

• filters on business status,
• currency conversion,
• deduplication,
• mapping of codes,
• historisation,
• derivation of time periods,
• handling of late-arriving data,
• merging of several source systems.

Link to the technical code where useful, but summarise the business effect in plain language.

Build source lineage

Document the chain from source to usage:

1. source system and source object
2. staging or transformation model
3. analytical table and field
4. semantic model or measure
5. KPI and report

For each connection, mark whether it is verified, manually described, or only assumed. A documented dependency chain is only as complete as the relationships captured; missing edges must not count as proof of missing impact. How the reports at the end of this chain are documented is shown in the guide document Power BI reports.

Owner, refresh, and operational knowledge

A model needs at least one technical owner. For critical business rules, a business role should also be named.

Refresh documentation covers:

• schedule and time zone,
• expected availability of sources,
• incremental or full load,
• typical runtime,
• failure behaviour,
• contact person,
• visible data currency.

This lets a user tell whether an unusual value is genuinely notable or has simply not been refreshed yet.

Keep quality notes visible

Quality issues do not belong in tickets alone. Document known limitations directly on the model or field:

• missing history before a cut-off date,
• delayed source,
• incomplete region,
• high null share,
• manual mapping,
• business-unresolved status value.

Note the impact, the person responsible, and the planned treatment. A quality note without context otherwise only creates uncertainty.

Worked example: sales star schema

Model: Sales Analytics Model

The following model is a fictional example for analysing posted invoices and credit notes.

Purpose: Analysis of invoiced revenue and margin by customer, product, region, and time

Central fact table: FactSales

Grain: One posted invoice or credit note line item; a unique business key from source system, document number, and line number

Dimensions: DimDate, DimCustomer, DimProduct, DimRegion, DimSalesOrganization

Primary key of the fact table: Technical sales_line_key

Foreign keys: date_key, customer_key, product_key, region_key, sales_org_key

Sources: ERP orders and invoices; CRM customer assignment; product master

Key transformations: Returns reduce net revenue at the posting date; currency conversion to EUR using a monthly average rate; internal sales organisations are excluded.

Refresh: On business days after ERP replication completes

Quality note: Historical region assignment is only available at country level before January 2024.

Linked KPIs: Net revenue and gross margin

Limit: Order intake cannot be fully derived from invoice line items alone. That requires a dedicated order fact table with an order date and a separately defined grain.

Sign off the documentation with three checks

1. Uniqueness: Check whether the documented business key really identifies a single row. If not, the grain is missing a document version or partial line item, for example.
2. Join behaviour: Compare the row count and revenue sum before and after a dimension assignment. An unexpected multiplication points, for instance, to several matching dimension versions.
3. Time behaviour: Trace a credit note back to an older invoice. The documentation must show in which month it takes effect and whether historical analyses change.

These checks happen in the data or BI system. In the documentation, you record the check result, the date, and known limitations; sample data should be fictional.

Review checklist

• Purpose and business scope are understandable.
• The grain of every fact table is unambiguous.
• Table roles and keys are documented.
• Cardinality and filter direction of relationships are correct.
• Important columns have business descriptions.
• Transformations explain their business effect.
• Foreign keys and lineage are not confused.
• Sources and downstream KPIs are linked.
• Owner, refresh, and failure behaviour are named.
• Quality notes include impact and responsible person.

Common mistakes

Providing only a diagram

An ERD shows structure, but rarely grain, transformations, or business limits.

Leaving the grain implicit

Without an unambiguous row meaning, double-counting and incorrect joins arise.

Documenting every column in the same depth

Prioritise keys, business-critical attributes, status values, and KPI-relevant fields. Completeness without value is quickly no longer maintained.

Treating foreign keys as complete lineage

Key relationships show no source systems, transformations, or downstream usage.

Keeping quality issues in the backlog only

Users must see known limitations where they select and interpret the model.

Technical names without a business translation

A field name like net_rev_adj_amt is not a definition. Add an understandable description and unit.

Frequently asked questions

Conclusion

Good data model documentation starts with purpose and grain and extends to keys, transformations, lineage, and quality limits. It combines technical precision with business clarity and makes models safe to reuse.

Related articles

• How to document Power BI reports the right way