The Business Meaning Your Medallion Architecture Needs

The medallion architecture is a popular data warehouse design, but Bronze, Silver, and Gold layers alone don’t define what business data should mean or how it can be reused. This enhanced medallion architecture adds a canonical Core Data Model and clear data contracts to make analytics more consistent, adaptable, and easier to build.

The medallion architecture is one of the most recognisable architecture models used by data practitioners:

• Bronze: ingestion
• Silver: transformation
• Gold: analysis

The medallion architecture describes where steps from source to target structures should be modelled. It gives each layer a place in the lifecycle, but leaves much of the responsibility for structure, meaning, and transformation rules to the engineers implementing it.

This creates a problem for a data warehouse. Changes require knowledge of existing structures and dependencies, and a lack of shared structure can lead to duplicated models and fragile downstream relationships. AI can help with coding and reasoning, but it needs explicit structure and documentation to produce reliable results.

The proposed architecture adds the following components:

• an enhanced Bronze layer that preserves source data under a clear contract
• a canonical Core Data Model that represents business concepts
• a Silver layer that maps source data into the Core Data Model
• Gold Engineering and Gold layers that prepare and expose analytical structures

Each layer has a separate responsibility and a defined handoff to the next layer:

The cost of the additional structure

#

The additional layers introduce real delivery and governance costs:

• modelling effort is required to define canonical entities, relationships, keys, and history behaviour before implementation can begin
• every source requires a governed mapping into the Core Data Model, including decisions about meaning, grain, identity, and missing values
• contracts and documentation must be versioned, reviewed, validated, and kept aligned with generated physical structures
• teams need clear ownership for approving semantic changes and managing their effect on downstream models

These costs can be offset by the clarity the structure brings in the process. When making modifications, it is clear where and how they have to be applied. This makes it easier for humans and AI to apply the correct modifications.

The new structure reduces interdependency across the warehouse. A source-system change can often be handled in the Silver mapping while the Core structure and Gold models remain stable. A new reporting requirement can be developed in Gold Engineering without changing source ingestion. Multiple teams can work against the same documented Core contract, allowing source onboarding and analytical development to proceed in parallel.

The trade-offs are worthwhile when the warehouse serves multiple sources, business areas, or analytical use cases. Smaller environments with few sources and limited change may gain less from the additional governance.

Bronze layer enhanced

#

The Bronze layer is where source data lands. Its purpose is to preserve source fidelity while providing consistent metadata and history for downstream processing.

The Bronze layer has the following requirements:

• preserve a permanent store of source data and its history
• capture source changes, including soft and hard deletes
• minimise modifications to source structures
• preserve and harmonise data types
• identify surrogate and business keys
• record load time, record source, process identifiers, hashes, and CDC information

For example, a simplified invoice source can be retained in Bronze with its source attributes and ingestion metadata:

This abbreviated projection shows selected source attributes and ingestion metadata. The complete Bronze structure also retains the other source attributes used in the Silver examples below.

select
    a.[id],
    a.[sid],
    a.[invoice_no],
    a.[user],
    cast(a.[invoicedate] as datetime2(3)) as [invoicedate],
    a.[legal_entity],
    a.[primary_key_hk],
    a.[record_hk],
    a.[cdc],
    a.[load_datetime],
    a.[process_run_id],
    a.[record_source]
from [invoices] a

The Bronze layer has the following characteristics:

• all the source attributes are retained
• the original data types are enforced, if necessary by a cast function
• primary_key_hk contains the primary key in hash format
• record_hk contains a hash of the entire record
• load_datetime contains the date the record is loaded into the Bronze layer
• process_run_id contains a reference to the loading process
• record_source contains an identification of the source of the data

This structure helps simplify and standardise downstream processing.

The new canonical (Core) Data Model where the business meaning lives

#

The Core Data Model is the heart of the architecture. It contains structures with defined business meaning, relationships, keys, and history behaviour.

The Core Data Model is implemented as conceptual, logical, and physical layers, represented in YAML models. The conceptual layer contains entities and relationships. The logical layer adds attributes and provides a full representation of the Core Data Model. The physical layer translates the logical model into physical structures for the platform used.

For the invoice example, the Core can contain a sales invoice, invoice lines, invoice types, invoice party roles, party-role types, and relationships between invoices. A source table can therefore be translated into several related business concepts rather than remaining a single source-shaped structure.

The logical layer documents these structures and provides the basis for the physical YAML layer and generated implementation artefacts. It is also represented as an OKF bundle so that the same definitions can be used by people and AI systems.

YAML example of sales_invoice

#

This abbreviated logical-model excerpt shows selected keys, attributes, relationships, and history rules. The complete definition includes the remaining columns referenced by the keys and sourcemaps.

id: logical.sales_invoice
label: Sales invoice
entity_type: regular
derived_from: conceptual.sales_invoice
keys:
  primary:
    - sales_invoice_key
  alternate:
    - sales_invoice_id
    - record_source
    - sales_invoice_type_key
columns:
  - name: sales_invoice_key
    type: string
    description: Stable key for the canonical invoice
  - name: sales_invoice_type_key
    type: string
    description: Classifies the invoice
  - name: invoicedate
    type: datetime
    description: Date of the invoice
relationships:
  - name: contains
    target: sales_invoice_lines
    cardinality: 1:N
  - name: involves
    target: sales_invoice_party_roles
    cardinality: 1:N
history:
  strategy: driving_key_set
  driving_keys:
    - sales_invoice_key

The generated documentation in Markdown files captures entity descriptions, keys, relationships, columns, history behaviour, and Silver ingestion templates. It serves both humans and AI.

The Silver layer: contract-driven ingestion

#

Silver is the controlled boundary between source-shaped Bronze data and the canonical Core Data Model. Its purpose is to interpret source attributes and map them into the entities, relationships, keys, and history rules defined by the Core contract.

The Silver layer does not invent its target structure. For each Core entity, the modelling process generates a predefined ingestion template so that a uniform shape is enforced. The template specifies:

• the target entity and its required columns
• the primary and foreign-key components
• the expected business attributes and data types
• required metadata and history columns
• the relationships that must be resolved

Source-specific SQL is used to transform source structures into the shape desired by the template. It identifies the relevant Bronze attributes, applies business rules, derives missing values, resolves references, and produces a view with the exact shape required by the Core entity.

A single source table can provide data for several Core entities. For example, an invoice table in the source may populate the Sales Invoice, Party, Party Type, and Sales Invoice Party Role entities. Each mapping is implemented independently, but every output conforms to a documented Core contract.

This gives Silver a consistent responsibility: source interpretation happens in Silver, while canonical business meaning is defined and governed by the Core Data Model.

The Silver example

#

The invoices table contains the following attributes:

• [id] –> surrogate key in source system
• [sid] –> id for the client (a party in a role)
• [setupdate] –> the date the invoice was set up (an attribute of the invoice)
• [invoicedate] –> the date of the invoice (an attribute of the invoice)
• [invoice_no] –> the business-facing invoice number (an attribute of the invoice)
• [currency] –> currency of invoice (an attribute of the invoice)
• [whopays] –> the id of the debtor (a party in a role)
• [legal_entity] –> the id of the issuer of the invoice (a party in a role)
• [credited] –> flag indicating if the invoice was credited
• [credit_id] –> id of the credit invoice (a relationship with another invoice)

This one table feeds several canonical entities:

The generic ingestion template for sales invoice

#

The generated template defines the required output columns and key components. The source-specific SQL fills the template with the relevant Bronze attributes and business rules.

-- Silver output: one row per sales invoice
select
    'sales invoice' as sales_invoice_type,
    cast(bi.invoice_no as varchar(50)) as business_key,
    cast(bi.invoice_no as varchar(50)) as business_description,
    bi.id as sales_invoice_id,
    bi.invoicedate,
    bi.amount,
    bi.record_source,
    bi.load_datetime,
    bi.cdc
from bronze_invoices bi

The output conforms to the sales_invoice contract. The source-specific mapping supplies the values; the target structure comes from the predefined template:

• the type of the sales invoice is added
• descriptive attributes are determined: business_key, business_description
• the identifier from the source system is determined, and the name of the attribute is converted to the standard name in the contract

The party-role mapping demonstrates a different transformation pattern. Source columns become rows in the canonical relationship structure:

-- Silver output: one row per invoice-party-role relationship
with cte_party_role_definition as
(
    select
        'sid' as party_type_attribute,
        'site' as party_type_name,
        'client' as sales_invoice_party_role_name
    union all
    select
        'whopays',
        'site',
        'debtor'
    union all
    select
        'legal_entity',
        'organisation',
        'legal_entity'
),
cte_invoice_parties as
(
    select
        r.sales_invoice_id,
        r.party_id,
        r.source_attribute
    from
    (
        select
            id as sales_invoice_id,
            sid,
            whopays,
            legal_entity
        from bronze_invoices
    ) src
    unpivot
    (
        party_id for source_attribute in (
            sid,
            whopays,
            legal_entity
        )
    ) r
)
select
    ip.sales_invoice_id,
    ip.party_id,
    rd.sales_invoice_party_role_name as role_name,
    rd.party_type_name as party_type
from cte_invoice_parties ip
inner join cte_party_role_definition rd
    on ip.source_attribute = rd.party_type_attribute;

sid, whopays and legal_entity are source attributes, while the Core Data Model represents them as relationships. Silver converts those columns into rows in sales_invoice_party_roles and assigns each relationship a controlled role such as debtor or legal_entity. Parties can have different party types. In this illustrative source, the party type organisation contains just the party name, while a site is a party with full details. An invoice identifies its client and debtor through site records and its issuing legal entity through an organisation record. The mapping therefore keeps the source record’s type separate from its role on the invoice.

From source mappings to Core keys

#

The SQL examples above show the base sourcemap transformations. They supply identifiers, attributes, and role values in a standardised shape. The dbt macros then use the key definitions in the Core model to generate the hash keys and resolve the relationship keys used by the Core entities.

By the time Gold Engineering reads these entities, the invoice, party, and party-role type keys are available as sales_invoice_hk, party_hk, and sales_invoice_party_role_types_hk. The following example uses those keys to join the documented Core structures. The macro implementation and key-generation rules are covered in the dbt implementation track.

Gold built on top of the Core Data Model

#

The Gold layer is the layer in which the structures for analysis are created. A prime candidate for the architecture here is a dimensional model. This provides a clear way of organising the data into structures that can be used by analytical tools.

Having a Core Data Model as the source helps the Gold layer to have a well-documented, well-structured source. All the source entities are clearly defined and adhere to the same principles. This makes creating source structures a lot easier. No need to try to find how information is stored. All the business concepts are already laid out and the meaning is well documented. We just build on the core model to build metrics and KPIs where needed.

Example: Invoice dimension

#

A simplified invoice dimension would be created from the invoice itself and its related client, debtor, and legal_entity.

The invoice details are to be found in the sales invoice entity. The parties related are found in sales invoice party role, with the different types listed in sales invoice party role type.

To flatten the structure we need to pivot the results of sales invoice party role for each type that we select. That is achieved in the Gold Engineering layer. The example outputs one row per invoice, with the parties that are related to it.

This example assumes at most one current party relationship for each invoice and selected role. Before applying the pivot, the input must be restricted to current relationships and validated for uniqueness on invoice and role. Under that condition, max(party_hk) returns the single party key for each role; invoices with multiple parties in the same role require a different analytical structure that preserves those relationships.

with cte_sales_party_roles_selection as (
  select
    sipr.sales_invoice_hk
    , sipr.party_hk
    , siprt.business_key as sales_invoice_party_roles_type_business_key
  from [sales_invoice_party_roles] sipr
  inner join [sales_invoice_party_role_types] siprt
    on sipr.sales_invoice_party_role_types_hk = siprt.sales_invoice_party_role_types_hk
  where siprt.business_key in ('debtor', 'legal_entity', 'client')
)
, cte_sales_party_roles as (
  select
      p.sales_invoice_hk
    , p.debtor as party_hk_debtor
    , p.legal_entity as party_hk_legal_entity
    , p.client as party_hk_client    
  from cte_sales_party_roles_selection src
  pivot (
    max(party_hk)
    for sales_invoice_party_roles_type_business_key in ([debtor], [legal_entity], [client])
  ) p
)
select si.sales_invoice_hk
  , si.business_key as invoice
  , si.invoicedate
  , pip.business_key as debtor  
  , ple.business_key as legal_entity
  , pcl.business_key as client
from [sales_invoice] si
    left join cte_sales_party_roles ipr on si.sales_invoice_hk = ipr.sales_invoice_hk
    left join [party]  pip on ipr.party_hk_debtor = pip.party_hk
    left join [party]  ple on ipr.party_hk_legal_entity = ple.party_hk
    left join [party]  pcl on ipr.party_hk_client = pcl.party_hk

Gold Engineering prepares this flattened structure; Gold exposes the resulting invoice dimension for reporting and analysis.

AI usage

#

The structured Core Data Model is the basis that AI can use to generate Gold models. The business concepts listed are recognisable, which allows reasoning and generation of code.

Automation with dbt macros

#

The pattern laid out in the example will repeat itself for many dimensions, and lends itself to dbt macros that simplify the generation of the end result. There are several patterns that can be implemented as macros to speed up the creation of Gold structures and simplify maintenance.

• generation of SCD type 2 dimensions from related core entities
• generation of integer surrogate keys to optimise joins in business intelligence tooling

The architectural benefit is a shared, governed definition of business meaning. Silver maps source data into that definition, and Gold Engineering prepares it for analytical use. These boundaries give teams a clear place to handle source changes and reporting requirements, reducing the coordination needed across the warehouse.

The next article examines the Universal Data Model principles behind the Core Data Model. These principles explain how entities, types, subtypes, and roles provide a reusable vocabulary for representing business meaning across sources and analytical use cases. They also explain why the documented Core can guide valid joins, help map new sources into established business concepts, and provide a reliable foundation for generating Gold models.