SAP Quality Management (QM) data model
Third-normal-form mirror of the SAP QM inspection and notification tables as they land from the source ERP. Each SAP table becomes its own entity with a warehouse surrogate key plus the genuine SAP business key(s), and child characteristic/result/sample/item/task/activity tables reference their lot or notification header and the QM master tables via foreign keys. The lot → characteristic → result hierarchy (QALS/QAMV/QAMR) and the usage decision (QAVE) preserve full inspection traceability for ISO-9001 and FDA 21 CFR 820 / GxP audits. Person, vendor and customer names are PII-masked.
Normalized replica of SAP ECC/S4HANA Quality Management source tables for a warehouse staging/replication layer: inspection lot (QALS) → inspection characteristic (QAMV/QAPP) → single result (QAMR), with samples (QASE/QASR) and the one-per-lot usage decision (QAVE); and quality notifications (QMEL) with their items (QMFE), tasks (QMSM) and activities (QMMA) — keyed by the real SAP business keys (prueflos, vorglfnr, merknr, qmnum, fenum, manum) and anchored to material (MARA/MAKT), plant (T001W), vendor (LFA1), customer (KNA1), inspection type (T351), the catalog code group (QPGR/QPCD) and the master inspection characteristic (QPMK). It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 16
- tables
- 136
- columns
- 18
- relationships
- 5
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Lot → characteristic → result hierarchy mirrors SAP QM
SAP QM records an inspection as the lot QALS, the assigned inspection characteristics QAMV (per operation QAPP), and the individual recorded results QAMR; replicating that three-level hierarchy 1:1 keeps the staging layer lossless and reconcilable against the source QALS/QAMV/QAMR tables.
Usage decision modeled as a separate one-per-lot entity
SAP stores the usage decision in QAVE — exactly one accept/reject (vcode, vbewertung) decision per inspection lot, made after results are recorded; keeping it as its own entity rather than folding it onto QALS preserves the who/when (decided_by, vdatum) audit fact that quality release requires.
Catalog code group as a shared coding master
Defects, causes and activities in SAP QM are all coded against catalog/code-group/code entries (QPGR/QPCD); modeling that as one shared lookup with composite business keys (catalog_type, code_group, code) lets every notification item, task and activity reference a single authoritative coding master instead of free text.
Notification item / task / activity normalization
A quality notification QMEL fans out into defect items QMFE, tasks QMSM and activities QMMA, each a separate SAP subtable with its own item/task counter; splitting them into their own entities avoids repeating the qmnum header on every line and matches how SAP records the corrective-action lifecycle.
QM linked to material, vendor and customer for supplier quality and traceability
Anchoring inspection lots and notifications to material (MARA), vendor (LFA1) and customer (KNA1) supports supplier-quality scoring and complaint handling, and the lot→characteristic→result chain plus the QAVE usage decision delivers the end-to-end traceability ISO-9001 and FDA 21 CFR 820 / GxP audits require.
Tables in the SAP Quality Management (QM) 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.
Tables (9)
inspection_lot
Grain: one row per inspection lot (prueflos)
SAP QALS — inspection lot record.
| Column | Type | Keys |
|---|---|---|
| inspection_lot_key | bigint | PK |
| material_key | bigint | |
| plant_key | bigint | |
| inspection_type_key | bigint | |
| vendor_key | bigint | |
| prueflos | varchar | BK |
| matnr | varchar | |
| werks | varchar | |
| art | varchar | |
| herkunft | varchar | |
| losmenge | decimal | |
| mengeneinheit | varchar | |
| gesstat | varchar | |
| charg | varchar | |
| aufnr | varchar | |
| ebeln | varchar | |
| entstehdat | date | |
| paendterm | date | |
| inspection_start_date | date | |
| inspection_end_date | date |
inspection_characteristic
Grain: one row per inspection characteristic (prueflos, vorglfnr, merknr)
SAP QAMV/QAPP — inspection characteristic and operation.
| Column | Type | Keys |
|---|---|---|
| inspection_characteristic_key | bigint | PK |
| inspection_lot_key | bigint | |
| master_characteristic_key | bigint | |
| prueflos | varchar | BK |
| vorglfnr | varchar | BK |
| merknr | varchar | BK |
| verwmerkm | varchar | |
| kurztext | varchar | |
| sollwert | decimal | |
| toleranzun | decimal | |
| toleranzob | decimal | |
| masseinh | varchar | |
| steuerkz | varchar | |
| is_quantitative | boolean |
inspection_result
Grain: one row per single inspection result (prueflos, vorglfnr, merknr, result counter)
SAP QAMR — single inspection result for a characteristic.
| Column | Type | Keys |
|---|---|---|
| inspection_result_key | bigint | PK |
| inspection_characteristic_key | bigint | |
| prueflos | varchar | BK |
| vorglfnr | varchar | BK |
| merknr | varchar | BK |
| result_counter | varchar | BK |
| mittelwert | decimal | |
| mbewertg | varchar | |
| nmenge | decimal | |
| mengeneinheit | varchar | |
| original_input | varchar |
sample
Grain: one row per inspection sample (prueflos, probenr)
SAP QASE/QASR — inspection sample and sample result.
| Column | Type | Keys |
|---|---|---|
| sample_key | bigint | PK |
| inspection_lot_key | bigint | |
| prueflos | varchar | BK |
| probenr | varchar | BK |
| sample_size | decimal | |
| sample_result_valuation | varchar |
usage_decision
Grain: one row per inspection lot usage decision (prueflos)
SAP QAVE — usage decision (one per inspection lot).
| Column | Type | Keys |
|---|---|---|
| usage_decision_key | bigint | PK |
| inspection_lot_key | bigint | |
| prueflos | varchar | BK |
| vcode | varchar | |
| vdatum | date | |
| vzeit | varchar | |
| vbewertung | varchar | |
| objnr | varchar | |
| decided_by | varchar | PII |
| lvsperre | varchar |
quality_notification
Grain: one row per quality notification (qmnum)
SAP QMEL — quality notification header.
| Column | Type | Keys |
|---|---|---|
| quality_notification_key | bigint | PK |
| material_key | bigint | |
| plant_key | bigint | |
| vendor_key | bigint | |
| customer_key | bigint | |
| inspection_lot_key | bigint | |
| qmnum | varchar | BK |
| qmart | varchar | |
| qmtxt | varchar | |
| objnr | varchar | |
| erdat | date | |
| qmdat | date | |
| priok | varchar | |
| reported_by | varchar | PII |
| ausvn | date |
notification_item
Grain: one row per notification item (qmnum, fenum)
SAP QMFE — quality notification item (defect).
| Column | Type | Keys |
|---|---|---|
| notification_item_key | bigint | PK |
| quality_notification_key | bigint | |
| catalog_code_group_key | bigint | |
| qmnum | varchar | BK |
| fenum | varchar | BK |
| fetxt | varchar | |
| fekat | varchar | |
| fegrp | varchar | |
| fecod | varchar | |
| anzfehler | decimal |
notification_task
Grain: one row per notification task (qmnum, manum)
SAP QMSM — quality notification task.
| Column | Type | Keys |
|---|---|---|
| notification_task_key | bigint | PK |
| quality_notification_key | bigint | |
| qmnum | varchar | BK |
| manum | varchar | BK |
| mtext | varchar | |
| msaus | varchar | |
| task_code | varchar | |
| pstart | date | |
| pende | date | |
| completed_by | varchar | PII |
notification_activity
Grain: one row per notification activity (qmnum, manum)
SAP QMMA — quality notification activity.
| Column | Type | Keys |
|---|---|---|
| notification_activity_key | bigint | PK |
| quality_notification_key | bigint | |
| qmnum | varchar | BK |
| manum | varchar | BK |
| matxt | varchar | |
| activity_code | varchar | |
| corrective_measure | varchar |
Lookup tables (7)
material
Grain: one row per material master general record
SAP MARA/MAKT — material master (general), referenced as a lookup here.
| Column | Type | Keys |
|---|---|---|
| material_key | bigint | PK |
| matnr | varchar | BK |
| maktx | varchar | |
| mtart | varchar | |
| matkl | varchar | |
| meins | varchar |
plant
Grain: one row per plant / inspection location
SAP T001W — plant/branch check table.
| Column | Type | Keys |
|---|---|---|
| plant_key | bigint | PK |
| werks | varchar | BK |
| name1 | varchar | |
| land1 | varchar |
vendor
Grain: one row per vendor master general record
SAP LFA1 — vendor master (general), referenced as a lookup here.
| Column | Type | Keys |
|---|---|---|
| vendor_key | bigint | PK |
| lifnr | varchar | BK |
| name1 | varchar | PII |
| land1 | varchar |
customer
Grain: one row per customer master general record
SAP KNA1 — customer master (general), referenced as a lookup here.
| Column | Type | Keys |
|---|---|---|
| customer_key | bigint | PK |
| kunnr | varchar | BK |
| name1 | varchar | PII |
| land1 | varchar |
inspection_type
Grain: one row per inspection type
SAP T351 — inspection type check table.
| Column | Type | Keys |
|---|---|---|
| inspection_type_key | bigint | PK |
| art | varchar | BK |
| description | varchar | |
| usage | varchar |
catalog_code_group
Grain: one row per catalog/code-group/code (katalogart, codegruppe, code)
SAP QPGR/QPCD — catalog code group / code master (defect, cause and activity coding).
| Column | Type | Keys |
|---|---|---|
| catalog_code_group_key | bigint | PK |
| catalog_type | varchar | BK |
| code_group | varchar | BK |
| code | varchar | BK |
| description | varchar |
master_characteristic
Grain: one row per master inspection characteristic (mkmnr, werks)
SAP QPMK/QPMT — master inspection characteristic (MIC) master.
| Column | Type | Keys |
|---|---|---|
| master_characteristic_key | bigint | PK |
| mkmnr | varchar | BK |
| werks | varchar | BK |
| kurztext | varchar | |
| steuerkz | varchar | |
| masseinh | varchar |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- inspection_lot.material_key → material (many-to-one)
- inspection_lot.plant_key → plant (many-to-one)
- inspection_lot.inspection_type_key → inspection_type (many-to-one)
- inspection_lot.vendor_key → vendor (many-to-one)
- inspection_characteristic.inspection_lot_key → inspection_lot (many-to-one)
- inspection_characteristic.master_characteristic_key → master_characteristic (many-to-one)
- inspection_result.inspection_characteristic_key → inspection_characteristic (many-to-one)
- sample.inspection_lot_key → inspection_lot (many-to-one)
- usage_decision.inspection_lot_key → inspection_lot (many-to-one)
- quality_notification.material_key → material (many-to-one)
- quality_notification.plant_key → plant (many-to-one)
- quality_notification.vendor_key → vendor (many-to-one)
- quality_notification.customer_key → customer (many-to-one)
- quality_notification.inspection_lot_key → inspection_lot (many-to-one)
- notification_item.quality_notification_key → quality_notification (many-to-one)
- notification_item.catalog_code_group_key → catalog_code_group (many-to-one)
- notification_task.quality_notification_key → quality_notification (many-to-one)
- notification_activity.quality_notification_key → quality_notification (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.