Food & Beverage / CPG EDW data model
Data Vault 2.0 raw vault for food & beverage: hubs (product/ingredient/supplier/batch/facility), links (product-ingredient recipe, batch-product, batch-facility, ingredient-supplier), and satellites for product, ingredient, supplier, and facility history plus a batch-status effectivity satellite for recall traceability and a quality-test transactional satellite. Hash keys and load metadata are generated by the DDL layer.
Food & Beverage / CPG enterprise data warehouse as a Data Vault 2.0 raw vault: product, ingredient, supplier, batch, and facility hubs; a product-ingredient recipe link plus batch and supply links; and descriptive, effectivity, and quality satellites — designed to consolidate product, recipe, and lot-genealogy data from many ERP, MES, QMS, and supplier systems with the full historization, forward/backward traceability, and recall capability FSMA and HACCP require. It is a Data Vault 2.0 raw vault: hubs for business keys, links for relationships and satellites for descriptive history, with hash keys and hash diffs generated in the DDL.
- 16
- tables
- 99
- columns
- 14
- relationships
- 1
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Snowflake). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Recipe modeled as a product-ingredient link
A bill-of-materials is a many-to-many relationship that evolves with reformulations; a product-ingredient link keeps the recipe as data so PLM, ERP, and labeling sources attach without reworking the hubs.
Batch hub anchors lot genealogy for recall
FSMA traceability requires one-up/one-down lot tracking; a batch hub tied to product, facility, and ingredient links makes forward and backward recall queries a graph traversal over the vault.
Batch status as an effectivity satellite
A lot moves from in-process to released, quarantined, or recalled over time; an effectivity satellite on the batch hub records the valid period of each status so a recall can be reconstructed as-of any date.
Quality tests as a transactional satellite
HACCP/QC tests are immutable measurement events keyed to a batch; a transactional satellite captures each test with spec limits and result and its load metadata, providing the audit trail for release decisions.
Tables in the Food & Beverage / CPG EDW model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Hubs (5)
hub_product
Grain: one row per unique hub_product business key
Finished product by stock-keeping unit.
| Column | Type | Keys |
|---|---|---|
| product_sku | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_ingredient
Grain: one row per unique hub_ingredient business key
Raw material / ingredient by ingredient code.
| Column | Type | Keys |
|---|---|---|
| ingredient_code | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_supplier
Grain: one row per unique hub_supplier business key
Supplier by vendor identifier.
| Column | Type | Keys |
|---|---|---|
| supplier_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_batch
Grain: one row per unique hub_batch business key
Production batch / lot by its lot number.
| Column | Type | Keys |
|---|---|---|
| batch_lot_number | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
hub_facility
Grain: one row per unique hub_facility business key
Manufacturing / packaging facility by identifier.
| Column | Type | Keys |
|---|---|---|
| facility_id | varchar | BK |
| load_date | timestamp | |
| record_source | varchar |
Links (4)
lnk_product_ingredient
Grain: one row per lnk_product_ingredient relationship
Recipe / bill-of-materials: ingredients composing a product.
| Column | Type | Keys |
|---|---|---|
| product_hk | binary | |
| ingredient_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_batch_product
Grain: one row per lnk_batch_product relationship
Which product a batch produced.
| Column | Type | Keys |
|---|---|---|
| batch_hk | binary | |
| product_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_batch_facility
Grain: one row per lnk_batch_facility relationship
Where a batch was produced.
| Column | Type | Keys |
|---|---|---|
| batch_hk | binary | |
| facility_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
lnk_ingredient_supplier
Grain: one row per lnk_ingredient_supplier relationship
Suppliers approved for an ingredient.
| Column | Type | Keys |
|---|---|---|
| ingredient_hk | binary | |
| supplier_hk | binary | |
| load_date | timestamp | |
| record_source | varchar |
Satellites (6)
sat_product_details
Grain: one row per product_hk per change
Product descriptive history.
| Column | Type | Keys |
|---|---|---|
| product_hk | binary | PK |
| load_date | timestamp | PK |
| product_name | varchar | |
| brand | varchar | |
| category | varchar | |
| net_weight | decimal | |
| weight_unit | varchar | |
| shelf_life_days | integer | |
| storage_condition | varchar | |
| gtin | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_ingredient_details
Grain: one row per ingredient_hk per change
Ingredient descriptive history.
| Column | Type | Keys |
|---|---|---|
| ingredient_hk | binary | PK |
| load_date | timestamp | PK |
| ingredient_name | varchar | |
| ingredient_type | varchar | |
| is_allergen | boolean | |
| allergen_class | varchar | |
| is_organic | boolean | |
| country_of_origin | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_supplier_details
Grain: one row per supplier_hk per change
Supplier descriptive history.
| Column | Type | Keys |
|---|---|---|
| supplier_hk | binary | PK |
| load_date | timestamp | PK |
| supplier_name | varchar | |
| contact_email | varchar | PII |
| city | varchar | |
| country | varchar | |
| approval_status | varchar | |
| food_safety_certification | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_facility_details
Grain: one row per facility_hk per change
Facility descriptive history.
| Column | Type | Keys |
|---|---|---|
| facility_hk | binary | PK |
| load_date | timestamp | PK |
| facility_name | varchar | |
| city | varchar | |
| state | varchar | |
| country | varchar | |
| fda_registration_number | varchar | |
| facility_type | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_batch_status
Grain: one row per batch per status effectivity change (traceability / recall)
Effectivity satellite on the batch hub — drives recall and lot genealogy.
| Column | Type | Keys |
|---|---|---|
| batch_hk | binary | PK |
| load_date | timestamp | PK |
| batch_status | varchar | |
| production_date | date | |
| expiration_date | date | |
| quantity_produced | decimal | |
| effective_from | timestamp | |
| effective_to | timestamp | |
| is_current | boolean | |
| load_end_date | timestamp | |
| record_source | varchar |
sat_quality_test
Grain: one row per batch per quality test measurement (transactional satellite)
Transactional satellite — QC/HACCP test measurements on a batch.
| Column | Type | Keys |
|---|---|---|
| batch_hk | binary | PK |
| load_date | timestamp | PK |
| test_id | varchar | |
| test_ts | timestamp | |
| test_type | varchar | |
| measured_value | decimal | |
| unit | varchar | |
| spec_min | decimal | |
| spec_max | decimal | |
| test_result | varchar | |
| load_end_date | timestamp | |
| record_source | varchar |
Lookup tables (1)
ref_allergen
Grain: one row per regulated major allergen
| Column | Type | Keys |
|---|---|---|
| allergen_code | varchar | BK |
| allergen_name | varchar | |
| regulatory_region | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- lnk_product_ingredient.product_hk → hub_product (many-to-one)
- lnk_product_ingredient.ingredient_hk → hub_ingredient (many-to-one)
- lnk_batch_product.batch_hk → hub_batch (many-to-one)
- lnk_batch_product.product_hk → hub_product (many-to-one)
- lnk_batch_facility.batch_hk → hub_batch (many-to-one)
- lnk_batch_facility.facility_hk → hub_facility (many-to-one)
- lnk_ingredient_supplier.ingredient_hk → hub_ingredient (many-to-one)
- lnk_ingredient_supplier.supplier_hk → hub_supplier (many-to-one)
- sat_product_details.product_hk → hub_product (many-to-one)
- sat_ingredient_details.ingredient_hk → hub_ingredient (many-to-one)
- sat_supplier_details.supplier_hk → hub_supplier (many-to-one)
- sat_facility_details.facility_hk → hub_facility (many-to-one)
- sat_batch_status.batch_hk → hub_batch (many-to-one)
- sat_quality_test.batch_hk → hub_batch (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.