Intuitas Data Modelling Framework

Return to home

Updated 17/9/2026

This resource provides a lightweight framework for describing and developing data models. It is based on a range of suggested practices, with references provided where appropriate.

Note: See Modelling Standards and Conventions for notation and modelling standards.

Table of Contents

• Modelling Concepts
  • Concepts (Entities)
  • Perspectives
  • Modelling levels
    • Conceptual (Information) Models
      • Subtypes and Supertypes
    • Logical (Information) Models
    • Physical (Data) Models
• Modelling Lifecycle
• Enterprise Context
  • Domain Topology
  • Domain Models
  • Canonical Models
• Business Process Models
• Data Warehouse Models
  • Non-Negotiables
  • Intuitas approach to the EDW zone
  • Keys and identity resolution
  • Alignment with dbt, Data Vault, HOOK and Kimball
  • Data Vault Model
  • Dimensional (Kimball) Model
  • Dimensional Bus Matrix
• Semantic Layers
  • Measures and Metrics
• Master Data and Reference Data
  • Physical Implementation
• Hierarchies
  • Modelling Hierarchies: Subtypes vs. Relationships
  • Examples

Modelling Concepts

We distinguish between business information design and technical data implementation:

• Information Models (Conceptual and Logical): Semantic models focused on business meaning, relationships, and rules. Technology-agnostic, designed for business stakeholders, analysts, and architects.
• Data Models (Physical): Implementation-specific models focused on storage, performance, and technical constraints. Designed for database developers and engineers.

Some organisations use “data model” as an umbrella term for all three layers, which is also acceptable.

Concepts (Entities)

Concept (Entity) – The foundational unit of information modelling is the business concept, often referred to as an entity.

• A business concept represents a real or abstract thing of business significance and is defined by:
  • A clear glossary definition
  • A set of attributes or properties
  • Relationships to other concepts, where applicable

A business concept is defined in the glossary. The glossary has the term, the definition and the identifiers for the concept. Rules about its data, such as survivorship, are master data rules. They are set by the concept owner and recorded in the governance metadata for the concept. Entities, keys and integration are aligned to the business concept, and the concept registry is its physical form for keying (see Platform Standards). “Concept” in this blueprint means business concept.

Concepts may be organised from multiple architectural perspectives and expressed in different types of models, each serving a distinct purpose.

Perspectives

Domains decompose the enterprise into functional or capability-based areas of responsibility.

• Concepts may be referenced within the scope of one or more domains
• For governance purposes, each concept must have one and only one domain owner
• Domain ownership establishes accountability for definition, quality, and change
• See: Domain Models
• See: Level 2: Domain Architecture

Subject areas describe the enterprise information space, organised by broad information topics.

• Subject areas apply across domains and systems
• They may loosely align to business functions in some cases, but are not synonymous with functions or organisational structures
• Subject areas are primarily an information classification construct, not a governance boundary

The Enterprise Data Model (EDM) describes concepts that are materially relevant across the organisation.

• Focus is placed on concepts, definitions, and standards that are:
• Used by multiple domains
• Implemented across multiple systems
• Required for enterprise-wide integration and reporting
• The EDM is a primary basis for interoperability, consistency, and reuse
• See Level 1: Enterprise Architecture

A Solution (or System) Architecture is scoped to a specific business context, process set, and delivery objective.

• Solutions refine and apply concepts within a defined scope
• They frequently act as a validation and elaboration point for subject areas and business concepts
• Solution models must align to, and not redefine, enterprise and domain concepts

Modelling levels

Concepts may be expressed at multiple modelling levels, depending on intent and audience. Conceptual and logical modelling are informational in nature, even when labelled “data models”. Physical models are data models

Conceptual (Information) Models

For naming standards and conventions, see Conceptual Models in Modelling Standards and Conventions.

Conceptual models represent business meaning without implementation concerns.

What to Include:

• Core business concepts (entities) that are identifiable, meaningful, and properly named
• Key relationships between concepts
• High-level business rules

What to Exclude:

• Primary or surrogate keys
• Data types
• Precise cardinality (beyond “one” vs “many”)

Use the business glossary to map domain-specific terms as synonyms to canonical enterprise terms.

Subtypes and Supertypes

Choosing the Right Level:

• Too abstract: Not practical (e.g., “Thing”)
• Too granular: Should be attributes instead (e.g., “Customer Type”)
• Appropriate: Meaningful specialisations with distinct data requirements (e.g., “Individual Customer” vs “Corporate Customer”)

Subtype Rules:

• Mutually exclusive: Each instance belongs to one subtype only
• Collectively exhaustive: Desirable but flexible, as models change over time

Avoid multiple classification hierarchies; choose the primary classification dimension.

Logical (Information) Models

For naming standards and conventions, see Logical Models in Modelling Standards and Conventions.

Logical models extend conceptual models with structure and precision while remaining technology-independent.

What to Include:

• Keys and identifiers: Natural and alternate keys
• Cardinality: Precise relationships (1:1, 1:M, M:N)
• Optionality: Mandatory vs optional relationships and attributes
• Attribute definitions: Clear definitions and business rules
• Normalisation: Applied where relevant for data integrity

Physical (Data) Models

For naming standards and conventions, see Physical Models in Modelling Standards and Conventions.

Physical models implement conceptual and logical models in specific technologies. They focus on storage, performance, and technical constraints for database developers and engineers.

Common Physical Formats:

• Relational (3NF): Traditional normalised models for transactional systems (OLTP). Used in PostgreSQL, SQL Server, Oracle.
• Dimensional: Star/snowflake schemas optimised for analytics (OLAP). See Dimensional Models.
• Semi-Structured: JSON, Parquet, Avro supporting nested data. Used in data lakes, MongoDB, Delta Lake, Databricks.
• Graph: Node/edge structures for relationship-heavy queries. Used in Neo4j, Amazon Neptune.
• Key-Value: Simple stores optimised for speed and scale. Used in DynamoDB, Cosmos DB, Redis.

Note: Physical models often exist without documented conceptual/logical models, but these can be reverse-engineered to understand business meaning.

Modelling Lifecycle

Recommended approach:

• Enterprise and domain models define the authoritative concepts, meaning, ownership, and standards for the organisation
• Solution models apply and extend those concepts to meet specific project needs
• Solutions depend on enterprise and domain models for consistency, reuse, and interoperability
• Enterprise and domain models depend on solutions to validate, refine, and evolve based on real delivery experience
• Governance controls the feedback loop, promoting solution learnings to domain or enterprise level where they are materially reusable
• The relationship is iterative and cumulative, whereby each project improves the enterprise baseline over time

Top down:

• Start with industry reference model
• Engage business domains to refine domain models
• Review existing reports and documentation

Middle out:

• Start with use case requirements
• Examine business processes and associated concepts in detail
• Reconcile business concepts with actual source data

Bottom up:

• Infer logical and conceptual models from applications, data and metadata

Enterprise Context

Enterprise-wide modelling is challenging due to semantic differences across functional areas. Instead, we focus on common touch points using Domain Models and Canonical Models where systems and domains integrate.

Domain Topology

Refer to Domain-Centric Design

Domains represent functional and organisational boundaries. Each domain encapsulates responsibilities, services, processes, information, expertise, and governance. Domains serve their own objectives while offering value to other domains and the enterprise.

A Domain Topology maps domains across the enterprise and their relationships, typically organised by functional areas (though other dimensions like region may apply).

Illustative example domains for a Healthcare organisation

Domain Models

For entity naming conventions in domain models, see Entity Naming in Modelling Standards and Conventions.

Under Domain-Centric Design, domains are authoritative for their information definitions. They own concept meanings and define lifecycle rules, and are the authoritative source of semantics for their scope.

A Domain Model describes business concepts relevant to a domain, including:

• Business Objects: Operational entities with identity, lifecycle, and direct participation in business processes (e.g., Patient, Order, Product)
• Supporting Concepts: Qualities, rules, conditions, and classifications (e.g., Risk, Compliance, Status)

Relationship to Business Processes:

• Domain Models define WHAT (objects, attributes, relationships)
• Business Process Models define HOW (creation, transformation, consumption)
• Processes show which objects are important, their lifecycles, the rules that apply, and the business language used
• Clear domain models provide a shared vocabulary for process design

Terminology: We prefer “Domain” over “Subject Area” as it aligns with governance and ownership boundaries, which supports clear accountability for data products and outcomes.

Canonical Models

For canonical entity naming conventions, see Entity Naming in Modelling Standards and Conventions.

Canonical models represent authoritative, agreed-upon definitions for concepts standardised across domains and systems (e.g., APIs, interoperability, conformed dimensions).

Characteristics:

• Conceptual and logical at minimum; physical where applicable
• Represent standardised information contracts
• Support interoperability and consistent interpretation

Use canonical models where:

• Multiple domains integrate
• Semantic consistency is required
• Cross-domain processes exchange information

Role in Cross-Domain Processes:

• Define integration points and standardised entities (e.g., canonical “Customer”)
• Enable process flows across domains (e.g., Sales → Fulfilment → Finance)
• Document data contracts at each handoff point

Business Process Models

Business process models illustrate activity sequences, decision points, participants, and work flows within and across domains. They define business objects (e.g., Customer, Order, Invoice) that form the basis of conceptual models within Domain Models and Fact Tables and Business Processes in Dimensional Modeling.

Data Warehouse Models

Data warehouses transform data through layers optimised for performance and analysis while preserving conceptual and logical semantics:

• Conform to canonical standards
• Apply reference data and business terminology
• Ensure data quality and reliability
• Preserve history for time-based analysis
• Enrich with metadata for discovery and governance
• Standardise and integrate across sources (keymaps, survivorship)
• Optimise for consumption (dimensional, in the Infomart)
• Aggregate for analytical use

Each layer has distinct physical structures aligned to underlying logical models.

Non-Negotiables

The following apply whichever modelling method an organisation adopts under this blueprint.

1. Conceptual model, glossary and identifiers first. Conceptual modelling is iterative. At a minimum, a conceptual model, clear glossary definitions and agreed business identifiers (see Keys and identity resolution) must be established for the scope of the use case and the surrounding entities that are highly likely to be related, before those concepts are physically modelled. All subsequent modelling depends on them.
2. Layer separation. Layers must be separated, and the separation must be maintained with discipline.
3. No presumed consumption paradigm. The EDW (Silver) zone must not presume how data will be consumed. Dimensions, APIs, one-big-table and other forms must remain possible, and the design must be ready for all latencies, including an increasing share of real-time delivery.
4. Change history. Change records from source systems (CDC) must be retained where history matters. Type 2 history is built from them for the attributes whose changes matter to the business, and the business determines which attributes these are.
5. Lowest grain. The lowest grain must be retained in the EDW (Silver) zone.
6. Survivorship and conformed entities created once. Survivorship rules and conformed entities have enterprise value. They must be created once and not repeatedly recreated by consumers.
7. Conformance upstream. Conformance logic is resolved upstream in the EDW (Silver) zone.
8. Dimensional modelling as consumption. Dimensional modelling is a consumption requirement, hence conformed dimensions are delivered in the Infomart (Gold) zone.
9. One standard. Standards must not be mixed. One method is applied across the organisation.
10. Supportability. The design must be simple, must limit its dependence on bespoke tools, and must be supportable by the team that operates it. Where two designs meet the same requirement, the simpler one is chosen.

Intuitas approach to the EDW zone

Our proposed approach adopts conventions and ideas from dbt best practice, Data Vault, HOOK and Kimball, and can be adapted depending on requirements. It can be likened to dbt best practice, while taking some philosophies and constructs from HOOK and deprecating Data Vault 2.0, which was a Blueprint 1.0 standard. Kimball dimensional modelling is retained as a slice-and-dice construct for BI.

These are stated as preferences, and not all organisations will want or need to adopt them. Whichever method an organisation adopts, the non-negotiables apply. They lead to the default method of this blueprint.

This blueprint builds on dbt best practice. The cleansed and standardised sub-layers correspond to dbt staging, the integrated entity to intermediates, and the Infomart to marts. Consumers reference marts and do not reference staging.

The Intuitas approach is not Data Vault, and the comparison with it is an analogy. There is no raw vault. The method is HOOK-like: source data is kept as loaded, point-in-time history is in pds, and history is materialised downstream only where required. Keymaps resolve identity, as HOOK’s hooks and keysets do and as Data Vault’s hubs and same-as links do. The approach extends HOOK in two ways. Survivorship rules and conformed entities have enterprise value, hence they are resolved once in the integrated entity and are not recreated by each consumer. Patterns are provided to materialise or virtualise the standardised and integrated models. Kimball is the Gold approach for BI, with conformed dimensions built once from the integrated entities.

In Data Vault terms, the source-scoped standardised entities do the job of business vault satellites, and each includes the concept key resolved through the keymap where one exists. The integrated entity does the job of the business vault’s resolved record. Links are not built, as relationships are expressed by concept keys on the entities.

Every concept has one integrated entity, which is the final step of standardisation. It is a view over the single source-scoped standardised entity until a second source, Type 2 history or read cost requires it to be materialised. The rules are in Platform Standards.

Keys and identity resolution

Integration depends on knowing when two records are the same thing. This section sets out which identifiers to use, in what order, and what has to be confirmed before a keyset is registered. The mechanics are in Platform Standards.

Terms

Term	Meaning	Example
Source identifier	The identifier value as it is in the source, whatever kind of key it is	C-1042
Business key	An identifier the business uses and recognises. One kind of source identifier	Employee number, ABN
System key	An identifier a system issues for its own records. The other kind of source identifier	CRM account id
Issuing system	The system or authority that created the source identifier. It may be upstream of the system the data is read from	crm, sap, abr
System of record	The authoritative source for a concept’s attributes, as named by governance. Usually the issuing system, but not always	Workday for employees
Keyset	One set of identifiers from one issuer, {issuing_system}_{scope}	crm_customer
Qualified key	The keyset and the source identifier joined with a pipe, {keyset}|{source_identifier}. Readable, not hashed. This is the value that is hashed. It is called qualified because the keyset says which system’s C-1042 it is, in the same way a schema qualifies a table name	crm_customer|C-1042
Concept key	The one key for a business concept, a hash of the qualified key. Where a concept has more than one keyset, the surviving key	customer_key
Keymap	The table that maps qualified keys from every keyset to the concept key	keymap__customer

A keyset is a set of identifiers, not a system. One issuer may have several, as SAP does with sap_customer and sap_order, which is why the qualifier is the keyset and not the system name: it has to keep identifier sets apart, not only systems. The issuer is usually a system, and may be an external authority, as with abr_abn. Where no reliable identifier exists anywhere and a key is derived from attributes, that derivation is registered as a keyset of its own. The term is used as HOOK defines it (HOOK Cookbook, KeySets).

The life of a key

One customer, followed through four epochs, with the systems on one side and the warehouse on the other. The systems are SAP (sap), which creates customers and raises sales orders itself, Gentrack (gentrack), which later takes over invoicing, and TechnologyOne (techone), which later replaces SAP. A system code names the system and is never renamed, so a replacement gets a code of its own. The dbt models are in the worked example.

Epoch	Upstream	Data team	Identity in the source	ods, pds, edw__cleansed	edw__standardised	im
1. First system	SAP is implemented. It creates the customer and raises sales orders itself. SAP issues both numbers and is the system of record.	Register Customer and Order. Profile the numbers. Register keysets sap_customer and sap_order. Build cleansed, standardised and mart models.	Value Issuing system Keyset Business key none – – System key, customer 100812 sap sap_customer System key, order SO-1001 sap sap_order There is no governed business key for customers, so the customer number is a system key.	sap__customer.customer_number = 100812, unchanged through all three. pds adds the SCD columns.	Source system against keyset Entity Source system (scope) Customer keyset customer_key customer__sap sap sap_customer computed order__sap sap sap_customer computed customer__sap source_identifier keyset customer_source_key customer_key 100812 sap_customer sap_customer|100812 hash(sap_customer|100812) order__sap gets the same customer_key from the number on the order, no lookup. No keymap. customer and order are views.	dim_customer customer_key customer_number_sap customer_id_techone hash(sap_customer|100812) 100812 fact_order carries the same customer_key.
2. Second system for transactions	Gentrack takes over invoicing. It issues invoice numbers and carries the SAP customer number on each invoice. SAP stays system of record.	Register Invoice. Profile the invoice number and the carried number. Register keyset gentrack_invoice. Note on sap_customer that Gentrack carries it. Build the invoice models.	Value Issuing system Keyset Business key none – – System key, customer 100812 sap (carried by gentrack) sap_customer System key, invoice INV-5501 gentrack gentrack_invoice Gentrack carries the customer number and does not issue it, so the keyset stays sap_customer.	gentrack__invoice.customer_ref = 100812, unchanged.	Source system against keyset Entity Source system (scope) Customer keyset customer_key invoice__gentrack gentrack sap_customer computed, carried key invoice__gentrack invoice_source_key customer_source_key customer_key gentrack_invoice|INV-5501 sap_customer|100812 hash(sap_customer|100812) Same customer_key as SAP, no keymap, one customer keyset. invoice is a view.	fact_invoice.customer_key = hash(sap_customer|100812), joins dim_customer. dim_customer unchanged.
3. First system replaced	TechnologyOne replaces SAP. It migrates the customer with a new id and a cross-reference, takes over orders, and is named system of record. Closed orders stay in SAP. Gentrack carries the new id from cutover.	Profile the new ids. Register techone_customer (with cutover date) and techone_order. Introduce keymap__customer. Add customer__techone and order__techone. Materialise customer with survivorship. Union order. Qualify invoices by cutover.	Value Issuing system Keyset Business key none – – System key, customer (SAP) 100812 sap sap_customer System key, customer (TechOne) C-1042 techone (carried by gentrack from cutover) techone_customer System key, order SO-20001 techone techone_order The customer now has two system keys under two keysets. The issuing system changed, the customer did not. TechnologyOne is the system of record, so its attributes win.	techone__customer.customer_id = C-1042, with legacy_ref = 100812, unchanged. SAP objects stay as they were.	Source system against keyset Entity Source system (scope) Customer keyset customer_key customer__sap sap sap_customer computed customer__techone techone techone_customer keymap order__techone techone techone_customer keymap invoice__gentrack gentrack sap_customer before cutover, techone_customer after computed, then keymap customer__techone source_identifier keyset customer_source_key customer_key C-1042 techone_customer techone_customer|C-1042 hash(sap_customer|100812) keymap__customer keyset source_identifier customer_source_key customer_key sap_customer 100812 sap_customer|100812 hash(sap_customer|100812) techone_customer C-1042 techone_customer|C-1042 hash(sap_customer|100812) customer is a table, one row, TechnologyOne wins. order is a union view, each order keeps its own key. invoice__gentrack after cutover invoice_source_key customer_source_key customer_key gentrack_invoice|INV-7120 techone_customer|C-1042 hash(sap_customer|100812)	dim_customer, one row customer_key customer_number_sap customer_id_techone hash(sap_customer|100812) 100812 C-1042 Every fact_order and fact_invoice row for the customer carries the same customer_key.
4. First system retired	SAP is switched off. Its data stays in pds.	Mark sap_customer and sap_order retired. Keep the keymap rows open. Keep customer__sap and order__sap over the frozen pds. Keep customer materialised.	No new values. sap_customer and sap_order are retired keysets. techone_customer is the only keyset still issuing customer ids.	No change.	No value changes. Source systems and keysets are as in epoch 3. Only the keyset status changes.	No change.

The story shows three things. A concept key is born from the first qualified key and never changes, whatever happens to the systems afterwards. A keymap is needed only where the same thing has identifiers under two keysets, and once it exists it is kept, because history carries the old identifiers. And retiring a system retires its keyset, not its keys.

What makes an identifier fit to use

An identifier is fit to be the basis of a concept key when all of the following hold:

• It is unique within its issuer.
• It is never null.
• It does not change once issued.
• It is not reused for a different thing after the first is deleted or closed.
• It is present in every system that carries the concept, in the same format.

Some business keys pass these tests. Some do not, such as an email address, a name, or a customer number that is recycled after closure.

Order of preference

The level is chosen per concept, recorded in the concept registry, and not left to whoever builds the model.

1. A governed business key that passes the tests and is carried by every system. The keyset is the issuing authority, which may be external, such as abr_abn. No keymap is needed.
2. The identifier issued by the system of record, carried by the other systems. The keyset is that system. Every system that carries the identifier uses the same keyset. No keymap is needed.
3. Separate identifiers from each system with no common key. One keyset per issuer, and a keymap to resolve them. Each pair of keysets has a recorded match rule, and survivorship is applied once in the integrated entity.
4. No reliable identifier anywhere. A key is derived from a combination of attributes. This is the least preferred, as the key changes when the attributes change. It is treated as a keyset of its own with a recorded match rule.

Most estates have concepts at each level. The point of the order is that the choice is deliberate and recorded.

Pre-work before a keyset is registered

Each candidate identifier is profiled and confirmed before it is registered. pds answers the questions about change over time. The output is the row in keysets.csv and the match rule where one is needed.

Check	Question	Evidence
Uniqueness	Is each value used once within the issuer?	Count of values against count of records
Nulls	Is the value ever missing?	Null rate per system
Stability	Does the value ever change for the same record?	Change history in pds
Reuse	Is a value reissued after the record is deleted or closed?	Deleted or closed records against later records
Format	Is the value stored the same way in every system?	Pattern and length per system
Coverage	Does every system that carries the concept have the value?	Share of records with the value, per system
Issuer	Which system creates the value, and do downstream systems pass it through or re-key?	Confirmed with the system owner
System of record	Which system does governance name as authoritative for the attributes?	Confirmed with the data owner

Issuing system and system of record

• The keyset follows the issuer. Where a downstream system carries an identifier created upstream, the keyset is the upstream one.
• Survivorship defaults to the system of record. Where the issuer and the system of record differ, the keyset comes from one and the winning attributes from the other.
• The scope on a standardised entity names the system the attributes were read from. The keyset names who issued the key. They can differ, and that is expected. e.g: employee__workday may carry the sap_employee keyset where the employee number was issued by SAP and retained in Workday.
• Where a downstream system re-keys records with identifiers of its own, that is a new keyset, and the keymap resolves it to the upstream one.

Alignment with dbt, Data Vault, HOOK and Kimball

The table shows which construct does each job under each method. The subsections state where this blueprint differs and why.

Methods compared by job

Job	Data Vault	HOOK	This blueprint
Keep what arrived, unchanged	Staging or persistent staging	The data lake, unaltered	ods and pds
Technical clean-up with no change of meaning	Raw vault (hard rules)	Not applied	Cleansed sub-layer
Align keys to business concepts across systems	Hubs and same-as links	Hooks and keysets	Keyset-qualified keys, and the keymap keymap__{concept}
Conform names and code values to the glossary	Business vault	Business logic, no prescribed structure	Standardised sub-layer
Resolve conflicts between systems	Business vault	Business logic, no prescribed structure	Integrated entity
Shape for consumption	Information mart	Consumption layer	Infomart

Key resolution. Data Vault seeks a true business key first, one the business recognises across systems, and hubs are built on it. This blueprint treats a business key that passes the tests as the preference and a luxury, and expects to work from system keys for most concepts. Data Vault also holds that resolving keys across systems in staging breaks the raw vault rules, as the raw vault records what each source said, and it does that resolution in the business vault, through same-as links. Doing key resolution as a business vault activity is close to this blueprint, which resolves once in the keymap and the integrated entity, after standardisation and before any consumption model. HOOK qualifies keys and stops, leaving resolution to each consumer. Kimball resolves in the ETL that loads the dimension. See Keys and identity resolution.

dbt best practice

Shared

• Models are layered as staging, intermediate and marts. Only staging reads from raw sources, reporting tools read only from marts, and dbt tests and YAML metadata control quality.

Different

• Staging is split into cleansed and standardised, as technical cleaning and glossary naming are separate tasks, often done by different people. Separate layers make a fault traceable to one step.
• A persistent raw zone, ods for current state and pds for change history, exists below staging. dbt does not load data, so the ingestion tool loads it.

Data Vault

Shared

• Data is organised around business concepts, and loaded data is retained and not overwritten.
• The qualified key serves the purpose of Data Vault’s business key, and the keymap does the job of the hub and its same-as link. The analogy for satellites and the resolved record is in Intuitas approach to the EDW zone.

Different

• There are no satellites, link tables or raw vault. Attributes remain on the standardised entities, and relationships are expressed through concept keys. Satellites write change records for every group of attributes, which pds already retains, and reading an entity from hubs, links and satellites requires a high number of joins.
• Data Vault 2.0 was a Blueprint 1.0 standard and is deprecated from release 2.1. It remains useful where an organisation depends heavily on Type 2 history across many entities and needs an as-at view of the integrated state for every attribute, which its insert-only satellites and point-in-time tables provide systematically. Under this blueprint, Type 2 history is materialised only for the attributes the business selects, and full reconstruction from pds is a contingency.

HOOK

Aligned. No raw vault is built. Source data is kept as loaded, and it is not restructured into hubs, links and satellites.

Adopted. Identity is handled as HOOK handles it, with the order of preference in Keys and identity resolution. Keys are aligned to business concepts from a governed glossary, and each key value is qualified by a keyset, {keyset}|{business_key} (HOOK Cookbook, KeySets).

Extended. Three constructs are added:

• the keymap, keymap__{concept}, which resolves different identifiers for the same thing and records when a mapping changed;
• the cleansed and standardised sub-layers, which make source data technically usable and conform names and code values to the glossary;
• the integrated entity, one per concept, materialised or virtualised as described in Intuitas approach to the EDW zone.

For an enterprise data warehouse, this blueprint prefers to standardise centrally and, where necessary, to integrate across systems and master survivorship once, for broad consumption. HOOK standardises identity through hooks and keysets, and leaves names, code values and conflicting attributes to each consumer (HOOK Cookbook, Architecture).

Kimball

Shared

• The Infomart is dimensional. Facts, dimensions and conformed dimensions are planned through a bus matrix, measures are classified as additive, semi-additive or non-additive, and a semantic layer is provided over the marts.

Different

• Dimensional modelling is a consumption product and is not applied in the EDW (Silver) zone. A dimensional model there would bind the data to an assumed consumption pattern. The foundational data is kept decomposed, and marts are views over integrated entities. See Dimensional (Kimball) Model.
• The canonical model is the authority for an entity’s meaning. A conformed dimension is one physical implementation of it.

Data Vault Model

Data Vault 2.0 was a warehouse modelling standard in Blueprint 1.0 and is deprecated from release 2.1. The comparison with this blueprint is under Data Vault above. The edw__rv and edw__bv schema conventions remain in Platform Standards for organisations that already operate a Data Vault.

Dimensional (Kimball) Model

For dimensional model naming standards, SCD columns, and key conventions, see Dimensional Models in Modelling Standards and Conventions.

Dimensional modelling is delivered in the Infomart (Gold) zone as a consumption product and is not applied in the EDW (Silver) zone, where entities are unprefixed and are not shaped as facts or dimensions.

The conformance logic, including key resolution, survivorship and history, is resolved upstream in the integrated entity, which is the authority for the conformed dimension. In a federated model, the integrated entity is located in the domain that owns it. That domain builds the conformed dimension once as an Infomart product, while other domains reference it and do not build their own.

Kimball Dimensional Modelling creates Information Marts using Star Schemas:

• Fact tables: Quantitative metrics/events from business processes (sales, shipments, payments)
• Dimension tables: Descriptive context (customer, product, date, region)

Star schemas are denormalised for query performance and ease of analysis.

Conformed Dimensions: Conformed dimensions are physical implementations of canonical models in Dimensional models:

• Ensure consistent definitions across fact tables and marts (e.g., shared Customer dimension across Sales, Support, Marketing)
• Enable cross-process analysis and drill-across queries
• Achieve “single version of truth” across analytical systems

Dimensional Bus Matrix

A Bus Matrix maps intersections between business processes (facts) and dimensions for scalable warehouse design.

Structure:

• Rows: Business processes (Orders, Shipments, Payments)
• Columns: Dimensions (Customer, Product, Time, Geography)
• Cells: ✓ indicates dimension usage

Benefits:

• Identifies common/conformed dimensions for consistency
• Supports modular, independent implementation
• Enables cross-process analytics

Example:

Process \ Dimension	Customer	Product	Time
Orders	✓	✓	✓
Shipments	✓	✓	✓

Guides star schema implementation and dimension standardisation.

Semantic Layers

For business-facing naming standards used in semantic layers, see Business Names vs Physical Names in Modelling Standards and Conventions.

Semantic layers are business-oriented abstraction layers that present data in business terms rather than physical structures.

Purpose:

• Translate physical structures into business concepts
• Encapsulate business logic, measures, and calculations
• Provide consistent metric definitions for analytics
• Simplify access for reporting and self-service use

Implementation options: Power BI semantic models, dbt Semantic Layer, and Unity Catalog metric views (Business Semantics)

Unity Catalog metric views (governed runtime option):

Metric views (GA and open-sourced as at July 2026) define measures, dimensions and joins once, as a first-class Unity Catalog object over mart tables. They are the first semantic-layer option where the metric definition is itself a governed catalog asset:

• Governed by Unity Catalog privileges, lineage, tags and certification like any other securable
• Queryable at runtime from SQL warehouses, AI/BI Dashboards, Genie, and partner BI tools, so one definition serves many consumers
• Named mv_{subject} in the mart schema, per Modelling Standards and Conventions

Note (Fabric / Power BI compatibility): Metric views are a Databricks-native construct, queried through Databricks SQL (including the MEASURE() aggregation syntax). They do not appear as measures in Power BI or Microsoft Fabric semantic models. Measure semantics are not transferred across the connector, so any measure consumed in Power BI must still be declared in the semantic model in DAX. Where Power BI is the primary consumption surface, treat metric views as the upstream, governed definition of record rather than a direct serving layer.

Where to define measures:

As a general rule, materialise what must be auditable, govern what must be shared, and leave only what is presentational to the BI tool.

• In the dbt mart model (a physical column): store the number in the table itself.

  • Use it when:
    • the figure has to be exact, tested and defensible later (enterprise KPIs, regulatory calculations)
    • every consumer must see the same value
  • Do not use it when:
    • the number should change with the user’s slicing or filtering, as a fixed column cannot do that

• In a Unity Catalog metric view: define the measure once, in the catalog, for many tools.

  • Use it when:
    • several teams and tools (SQL, AI/BI Dashboards, Genie) need the same governed definition
    • lineage and certification on the metric itself matter
  • Do not use it when:
    • the audience uses Power BI, as metric-view measures do not translate into DAX and only fixed-grain workarounds exist
    • Unity Catalog is not in use

• In the dbt Semantic Layer: metrics are defined as code, alongside the transformations.

  • Use it when:
    • the team is dbt-centric and wants metric definitions versioned and reviewed alongside the models that supply them
  • Do not use it when:
    • metrics must be governed in the platform catalog rather than a dbt project
    • the semantic-layer runtime is not part of the deployment
    • dbt Cloud lock-in is a concern, as the Semantic Layer runtime is a dbt Cloud service and is not available with dbt Core alone

• In Power BI (a DAX measure): define it in the report layer.

  • Use it when:
    • the measure is presentational (it reacts to slicers and filter context, varies per report, or is exploratory)
  • Do not use it when:
    • it is an enterprise KPI consumed anywhere outside Power BI. Consider defining it upstream so there is one definition rather than one per report

These options are complementary. Base measures are defined in the mart, shared metrics are promoted to a governed layer, and presentation logic stays in the BI tool.

Semantic layers depend on domain and canonical models for base entities but establish authoritative definitions for derived metrics within their consumption context.

Measures and Metrics

For measures and metrics naming standards and suffix conventions, see Measures and Metrics and Quantities and Measures in Modelling Standards and Conventions.

Measures and metrics are first-class semantic layer concepts that quantify business performance.

Measures: Numeric values directly aggregated from data (e.g., Sales Amount, Bed Days)

• Derived from fact events/transactions at a clear grain (per encounter, per day)
• Aggregated using simple functions (SUM, COUNT, AVG)
• Sensitive to filter context (time, domain, segment)

Metrics: Business expressions combining measures (e.g., Readmission Rate, Gross Margin %)

• Express performance or quality vs raw volume
• Often use complex logic (conditionals, windows, exclusions)
• Require clear definitions and assumptions

Additivity: Classify measures by aggregation behaviour:

• Additive: Sum across all dimensions (e.g., Sales Amount)
• Semi-additive: Sum across some dimensions only (e.g., Inventory Level - products yes, time no)
• Non-additive: Cannot sum meaningfully (e.g., ratios, rates)

Metrics inherit these behaviours. Record additivity as metadata to constrain aggregations in marts and BI tools.

Logical Definition: Measures/metrics should be defined against domain and canonical models and documented in the business glossary with:

• Name, description, owning domain, stakeholders
• Formal definition and calculation rule
• Input entities, attributes, filters
• Validity period and version history
• Related KPIs, dashboards, reports

Physical Implementation: base measures are columns on fact tables (e.g., amount, quantity), and the choice of where to define anything beyond that is set out in Where to define measures.

Example: Base measure Total Encounters defined in dbt. Power BI DAX adds interactive filter-aware metric: Readmission Rate (Current Filters) = DIVIDE([Readmission Count], [Total Encounters])

Governance:

• Govern like reference data with review/approval process
• Retain effective dates and versions
• Notify consumers of changes
• Make discoverable via catalog/glossary (calculation, dependencies, usage)

Master Data and Reference Data

For reference data naming standards, logical modelling, and physical implementation conventions, see Reference Data in Modelling Standards and Conventions.

Master Data: Core business entities critical to operations and shared across systems (e.g., Patient, Product, Provider, Location, Organisation, Payer)

• Managed as authoritative “single source of truth”
• Subject to governance and quality processes
• Is the basis of conformed dimensions in data warehousing
• Survivorship rules, which decide whose attributes win where systems disagree, are master data rules. They are set by the concept owner, recorded in the governance metadata for the concept, and applied once in the integrated entity

Reference Data: Stable, standardised values for categorisation and validation (e.g., Status Code, Country Code, Product Category, Location Type, Therapeutic Class, Unit of Measure)

• May be externally standardised (ISO) or internally maintained
• Can be simple (code/name pairs) or hierarchical (see Hierarchies)
• Managed enterprise-wide with optional source-specific variants

Physical Implementation

Physical form depends on analytical use, query patterns, history requirements, complexity, and performance needs.

Physical Forms:

1. Simple Lookup Tables

• Basic code-to-description translation
• Minimal attributes (code, name, description)
• No history tracking
• Example: ref_status_code, ref_currency

2. Dimension Tables (Star Schema)

• Rich attributes and hierarchies
• History tracking via SCD (Slowly Changing Dimensions)
• Used for slicing, filtering, grouping
• Example: dim_product, dim_provider, dim_location

3. Embedded in Fact Tables (Denormalised)

• Attributes embedded directly for performance
• Uses more storage to improve speed (avoids joins)
• Example: Fact includes status_code, status_name columns

For reference data modelling standards, see Reference Data Standards and Conventions.

Hierarchies

Hierarchies are structured parent-child relationships representing natural groupings and aggregation levels.

Across Model Types:

• Conceptual/Logical: Business classifications and organisational structures (product categories, regions, org units)
• Physical: Implemented via self-referencing foreign keys, hierarchy tables, or path encodings
• Dimensional: Enable drill-down/roll-up analysis (Day → Month → Quarter → Year)
• Reference data: Standardised classification schemes

Modelling Hierarchies: Subtypes vs. Relationships

Use Subtypes for “Is A” Taxonomies:

• Type distinctions, following the rules in Subtypes and Supertypes
• Example: Individual Customer is a Customer; Inpatient Encounter is an Encounter

Use Relationships for Other Hierarchies:

• Compositional (Part Of), categorical (Belongs To), organisational (Reports To)
• Support multiple concurrent hierarchies
• Example: Location entities (Room, Ward, Building, Campus) related via “Part Of”

Examples

Relationship Hierarchy: Healthcare Facility Structure

Hospital Campus
├── Building A
│   ├── Ward 1 → Room 101, Room 102
│   └── Ward 2 → Room 201
└── Building B
    └── Outpatient Clinic → Consultation Room 1

Physical Representations:

1. Parent-Child (Adjacency List): Each record references its parent for recursive traversal.

location_id	location_name	location_type	parent_location_id
1	Hospital Campus	Facility	NULL
2	Building A	Facility	1
3	Ward 1	Ward	2
4	Room 101	Room	3
5	Room 102	Room	3

2. Flattened Dimension: Lowest-grain entity with hierarchy level columns.

room_key	campus_name	building_name	ward_name	room_name	effective_from_datetime	effective_to_datetime	updated_datetime
1001	Hospital Campus	Building A	Ward 1	Room 101	2024-01-01 00:00:00	NULL	2024-01-01 00:00:00
1002	Hospital Campus	Building A	Ward 1	Room 102	2024-01-01 00:00:00	NULL	2024-01-01 00:00:00

Adjacency lists enable flexible navigation, while flattened structures optimise star schema query performance.

Reference Data Hierarchy: Product Classification

All Products
├── Medical Equipment
│   ├── Diagnostic Equipment → Imaging Systems (MRI, CT Scanners)
│   └── Therapeutic Equipment
└── Pharmaceuticals
    ├── Prescription Drugs
    └── Over-the-Counter

Physical Representations:

1. Parent-Child (Adjacency List):

product_classification_id	classification_name	parent_classification_id
1	All Products	NULL
2	Medical Equipment	1
3	Diagnostic Equipment	2
4	Imaging Systems	3
5	MRI Scanners	4

2. Flattened Dimension:

product_key	classification_lvl1	classification_lvl2	classification_lvl3	classification_lvl4	effective_from_datetime	effective_to_datetime	updated_datetime
100	Medical Equipment	Diagnostic Equipment	Imaging Systems	MRI Scanners	2024-01-01 00:00:00	NULL	2024-01-01 00:00:00
101	Medical Equipment	Diagnostic Equipment	Imaging Systems	CT Scanners	2024-01-01 00:00:00	NULL	2024-01-01 00:00:00
102	Pharmaceuticals	Prescription Drugs			2024-01-01 00:00:00	NULL	2024-01-01 00:00:00

Note: Naming follows modelling standards: surrogate keys use _key suffix; SCD Type 2 uses effective_from_datetime, effective_to_datetime, updated_datetime; entities are singular; hierarchy attributes use logical prefixes with incremental numbering.