Skip to main content

Databricks Medallion Architecture for food manufacturing

Source systems

ERP
MES / OT
WMS

Bronze

Preserve

Silver

Contextualize

Gold

Publish

Consume

Power BI
SQL
AI
Explore the model Implementation guide

Medallion model

Three layers. Three different data contracts.

Each layer should have a clear responsibility and exit condition before data moves to the next.

01 · Bronze

Preserve

Keep

Source truth

Keys, timestamps and original payload semantics.

Avoid

Business interpretation

Do not reconcile or redefine the process yet.

Ready when

The source can be reproduced.

02 · Silver

Contextualize

Align

Manufacturing context

Order, line, batch, recipe, time, units and quality.

Resolve

Cross-system differences

Make ERP, MES and WMS describe the same physical process.

Ready when

The systems describe one operational reality.

03 · Gold

Publish

Model

Decision-ready data

Stable facts, dimensions and governed metrics.

Serve

Reusable data products

Production, yield, inventory, reporting and AI.

Ready when

Consumers no longer rebuild source logic.

The important boundary is Silver. That is where separate ERP, MES and WMS records become one governed model of the food-manufacturing process.

Bronze layer

Bronze: preserve source truth before improving it

Bronze is the replayable contract with the source. It should protect you against source changes, late-arriving records and transformation mistakes without pretending the data is already business-ready.

Keep in Bronze

Original source identifiers

Order numbers, machine IDs, lot numbers and source-specific status codes remain available for reconciliation.

Source and ingestion time

Preserve the business timestamp separately from when Databricks received the record.

Enough history to rebuild

Keep append, CDC or snapshot history at the fidelity required to recreate downstream state.

Ingestion metadata

Capture file path, connector metadata, batch identifiers or other provenance needed to investigate gaps.

Keep out of Bronze

Enterprise key replacement

Do not remove the original key just because a conformed identifier exists elsewhere.

Cross-system business joins

Order-to-line, batch genealogy and temporal reconciliation belong in the contextual layer.

Final KPI definitions

OEE, yield, giveaway and shelf-life eligibility should not be embedded in the raw landing contract.

Lakeflow SQL example

Incrementally land MES event files into a Bronze streaming table
SQL · Bronze streaming table
CREATE OR REFRESH STREAMING TABLE bronze_mes_events AS
SELECT
  *,
  current_timestamp() AS _ingested_at,
  _metadata.file_path AS _source_file
FROM STREAM read_files(
  '/Volumes/factory/raw/mes',
  format => 'json'
);

Silver layer

Silver: turn separate systems into one food-manufacturing process

This is where most manufacturing data engineering effort belongs. Cleaning alone is not enough; Silver has to resolve the operational relationships that ERP, MES, WMS and quality systems express differently.

The contextual layer

Silver should describe the physical process, not the source application

order × line × batch × recipe × time × quality state

Identifiers

Conform keys

Map source product, line, machine, order and batch identifiers without losing lineage.

Time

Align event windows

Handle shifts crossing midnight, late ERP postings and production-order reopenings explicitly.

Food context

Model batch and recipe state

Keep recipe version, lot genealogy, quality release and effective-dated attributes usable over time.

Semantics

Normalize without hiding truth

Standardize units and reason hierarchies while retaining the original source value for auditability.

Temporal correctness is usually harder than the SQL

A machine event can happen before the ERP posting arrives, a batch can change quality state, and a recipe or line mapping can change over time. Model effective windows and late-arriving data deliberately instead of joining on today's master data.

Lakeflow SQL example

Contextualize MES events with line and production-order windows
SQL · Silver contextualization
CREATE OR REFRESH MATERIALIZED VIEW silver_production_events AS
SELECT
  e.event_ts,
  m.site_id,
  m.line_id,
  e.machine_id,
  o.production_order_id,
  o.product_id,
  o.batch_id,
  e.state_code,
  e.duration_seconds
FROM bronze_mes_events e
LEFT JOIN silver_machine_master m
  ON e.machine_id = m.source_machine_id
LEFT JOIN silver_order_windows o
  ON e.machine_id = o.machine_id
 AND e.event_ts >= o.start_ts
 AND e.event_ts <  o.end_ts;

Gold layer

Gold turns manufacturing context into reusable data products

Gold gives BI, SQL and AI a stable business interface without exposing the source-system logic resolved in Silver.

Production performance

Decision-ready interface

One production order × line × shift

Declared grain

Plan

12,000 kg

Released

10,580 kg

Largest loss

45 min material wait

Plan attainment 88.2%
output good output runtime downtime losses

Gold contract

Stable facts. Conformed dimensions.

Consumers work with business entities instead of reconstructing ERP, MES and WMS joins.

Fact

production_order_performance

Measures at one declared operational grain.

Dimensions

Product · line · shift

Shared attributes for consistent slicing and filtering.

Food context

Batch · recipe · quality

Expose only the context needed by the decision.

Metric Views sit above Gold

Gold defines the reusable data product. Metric Views can add shared measures and dimensions for BI and AI.

Power BI SQL AI

Implementation guidance

How we would implement medallion on Azure Databricks

Do not translate Bronze, Silver and Gold directly into storage folders and stop there. Define the quality contract, ownership, table type and tests at each transition, then choose physical Unity Catalog boundaries that match governance rather than diagram colors.

Land source history before contextualization

Lakeflow Connect, Auto Loader, streaming or another ingestion pattern should feed a recoverable Bronze contract. The connector choice and medallion layer solve different problems.

Use Unity Catalog as the governance boundary

Prefer managed tables for lakehouse data where appropriate, use volumes for landing zones and unstructured files, and map catalogs/schemas to ownership and access needs.

Put quality gates at layer transitions

Bronze checks ingestion completeness; Silver checks mappings, units and temporal relationships; Gold reconciles declared facts and business totals.

Keep logical layers separate from physical layout

A domain catalog with Bronze/Silver/Gold schemas can be valid; so can environment/domain catalogs. Do not create a catalog solely because the architecture has three named layers.

Catalog design

Compare two practical Unity Catalog layouts

Domain catalog, layer schemas

factory.bronze
factory.silver
factory.gold

Useful when domain ownership is the primary boundary.

Environment / domain catalogs

prd_factory.bronze
prd_factory.silver
prd_factory.gold

Useful when environment isolation and workspace binding drive governance.

Quality gates

What should be tested at each transition?

Bronze → Silver

Completeness, duplicate source keys, schema drift, expectation failures and invalid timestamps.

Inside Silver

Mapping coverage, unit normalization, temporal overlap, orphan orders/batches and effective-dated relationships.

Silver → Gold

Production quantities, inventory balances, genealogy and known KPI examples reconciled to agreed operational sources.

Recommended starting point

Define the responsibility of Silver before debating every catalog and table name

For food manufacturers, the architectural risk is usually not whether the folder is called Bronze or Raw. It is whether order, line, batch, recipe, time and quality state are reconciled once in a governed contextual model before Gold data products are published.

FAQ

Frequently asked questions

Practical answers about Databricks medallion architecture in food manufacturing.

What is Databricks medallion architecture?

Medallion architecture is a layered data-design pattern in which data quality and business readiness improve as data moves from Bronze to Silver to Gold. Databricks recommends the pattern, but it is a logical design approach rather than a mandatory product feature.

What should the Bronze layer contain in food manufacturing?

Bronze should preserve source fidelity and rebuildability: original ERP, MES and WMS identifiers, source timestamps, source-level status values, ingestion metadata and enough history to reproduce downstream state.

Should Bronze always be raw files?

No. Bronze is a quality responsibility, not a file-format rule. Source data can be persisted in Delta tables while retaining the original source semantics and ingestion metadata needed for replay, audit and reconciliation.

What belongs in Silver for food manufacturing?

Silver is where source records become operational context. Typical responsibilities include product and asset mappings, order-to-line relationships, batch and recipe context, unit normalization, quality state and temporal alignment across ERP, MES and WMS.

Where should ERP, MES and WMS joins happen?

Most repeatable cross-source contextualization belongs in Silver. Gold should consume those conformed entities rather than recreating report-specific joins for every dashboard, metric or AI question.

What should Gold contain?

Gold should expose stable business-facing facts, dimensions and data products at declared grains, such as production-order performance, yield and giveaway, inventory and shelf-life, or batch traceability.

Do Bronze, Silver and Gold require separate Unity Catalog catalogs?

No. The medallion layers are logical quality boundaries. Catalogs and schemas should follow ownership, environment isolation, access and governance requirements rather than a rule that every layer must have its own catalog.

Where do Databricks Metric Views fit in medallion architecture?

Metric Views can provide governed measures and dimensions above stable Gold data products. They complement the Gold model; they do not remove the need to define reliable facts, dimensions and grains underneath.

Practical next step

Review your Bronze, Silver and Gold responsibilities before scaling the platform

Bring one ERP/MES/WMS flow and one recurring food-manufacturing decision. We map what should remain source-faithful, what belongs in the contextual Silver layer and what should become a stable Gold data product on Azure Databricks.