Quality Management Analytics data model
Kimball warehouse for quality management: an inspection fact feeding first-pass-yield and PPM, an accumulating nonconformance snapshot tracking open->capa->closed milestones, and a defect-level fact over conformed product/supplier/defect-type/plant/inspector dimensions.
Quality management analytics covering inspections, the nonconformance/CAPA lifecycle, and individual defects — a Kimball star schema with conformed product (SCD2), supplier, defect-type, plant, and inspector dimensions for first-pass-yield, PPM, CAPA cycle-time, and defect-Pareto reporting. It is a Kimball dimensional model: fact tables at a declared grain surrounded by conformed dimensions, ready for BI tools and a semantic layer.
- 13
- tables
- 142
- columns
- 26
- relationships
- 2
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: Databricks). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Accumulating snapshot for nonconformance/CAPA
A nonconformance report moves through open, CAPA, and closed milestones with measurable lags; an accumulating snapshot supports cycle-time and cost-of-quality analysis on one progressively updated row.
Inspection and defect as separate facts
Inspection is at lot grain (yield, PPM) while defects are at the individual-defect grain needed for Pareto; separating them keeps counts additive and avoids a fan-trap when many defects map to one inspection.
Supplier modeled as SCD2
Supplier certification status and quality rating change over time and must reflect the version in effect when a defect or NCR occurred for accurate supplier-quality scorecards.
Defect type as a conformed dimension with severity
Defect category, severity, and safety flags are shared across inspection, nonconformance, and defect facts, so they are conformed to enable consistent severity-weighted reporting.
Tables in the Quality Management Analytics 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.
Fact tables (4)
fct_inspection
Grain: one row per inspection lot
Transaction-grain inspection fact feeding first-pass-yield and PPM.
| Column | Type | Keys |
|---|---|---|
| inspection_key | bigint | PK |
| date_key | bigint | |
| product_key | bigint | |
| supplier_key | bigint | |
| plant_key | bigint | |
| inspector_key | bigint | |
| inspection_type_key | bigint | |
| disposition_key | bigint | |
| inspection_id | varchar | |
| lot_number | varchar | |
| lot_size | integer | |
| sample_size | integer | |
| inspected_quantity | integer | |
| accepted_quantity | integer | |
| rejected_quantity | integer | |
| defect_count | integer | |
| first_pass_yield_pct | decimal | |
| ppm_defective | decimal | |
| disposition | varchar | |
| is_lot_accepted | boolean |
fct_nonconformance
Grain: one row per nonconformance report (accumulating snapshot open->capa->closed)
Accumulating snapshot of the nonconformance/CAPA lifecycle.
| Column | Type | Keys |
|---|---|---|
| nonconformance_key | bigint | PK |
| opened_date_key | bigint | |
| product_key | bigint | |
| supplier_key | bigint | |
| plant_key | bigint | |
| defect_type_key | bigint | |
| inspector_key | bigint | |
| root_cause_key | bigint | |
| capa_date_key | bigint | |
| closed_date_key | bigint | |
| ncr_number | varchar | |
| affected_quantity | integer | |
| open_to_capa_days | decimal | |
| capa_to_close_days | decimal | |
| total_cycle_days | decimal | |
| cost_of_quality | decimal | |
| scrap_cost | decimal | |
| rework_cost | decimal | |
| disposition | varchar | |
| is_capa_required | boolean | |
| is_closed | boolean |
fct_defect
Grain: one row per defect observed
Transaction-grain defect fact for Pareto and root-cause analysis.
| Column | Type | Keys |
|---|---|---|
| defect_key | bigint | PK |
| date_key | bigint | |
| product_key | bigint | |
| supplier_key | bigint | |
| plant_key | bigint | |
| defect_type_key | bigint | |
| inspector_key | bigint | |
| defect_id | varchar | |
| inspection_id | varchar | |
| defect_quantity | integer | |
| location_on_part | varchar | |
| detection_stage | varchar | |
| defect_cost | decimal | |
| is_repairable | boolean | |
| is_escaped | boolean |
fct_capa
Grain: one row per corrective/preventive action (accumulating snapshot)
Accumulating snapshot of the CAPA lifecycle (initiate->verify->close).
| Column | Type | Keys |
|---|---|---|
| capa_key | bigint | PK |
| initiated_date_key | bigint | |
| product_key | bigint | |
| supplier_key | bigint | |
| plant_key | bigint | |
| root_cause_key | bigint | |
| inspector_key | bigint | |
| verified_date_key | bigint | |
| closed_date_key | bigint | |
| capa_number | varchar | |
| ncr_number | varchar | |
| action_count | integer | |
| init_to_verify_days | decimal | |
| verify_to_close_days | decimal | |
| total_cycle_days | decimal | |
| implementation_cost | decimal | |
| estimated_savings | decimal | |
| effectiveness_score | decimal | |
| is_effective | boolean | |
| is_recurring | boolean | |
| is_closed | boolean |
Dimension tables (5)
dim_date
Grain: one row per calendar day
| Column | Type | Keys |
|---|---|---|
| date_key | bigint | PK |
| full_date | date | BK |
| day_of_week | smallint | |
| month_number | smallint | |
| quarter | smallint | |
| year | smallint | |
| fiscal_period | varchar | |
| is_business_day | boolean |
dim_product
Grain: one row per product per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| product_key | bigint | PK |
| product_id | varchar | BK |
| part_number | varchar | |
| product_name | varchar | |
| product_family | varchar | |
| product_line | varchar | |
| spec_revision | varchar | |
| aql_level | varchar | |
| is_regulated | boolean | |
| is_active | boolean |
dim_supplier
Grain: one row per supplier per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| supplier_key | bigint | PK |
| supplier_id | varchar | BK |
| supplier_name | varchar | |
| supplier_tier | varchar | |
| country | varchar | |
| quality_rating | decimal | |
| is_certified | boolean | |
| certification_body | varchar | |
| is_approved | boolean |
dim_plant
Grain: one row per manufacturing plant
| Column | Type | Keys |
|---|---|---|
| plant_key | bigint | PK |
| plant_id | varchar | BK |
| plant_name | varchar | |
| plant_code | varchar | |
| city | varchar | |
| state | varchar | |
| country | varchar | |
| region | varchar | |
| quality_certification | varchar |
dim_inspector
Grain: one row per inspector per SCD2 version
SCD type 2
| Column | Type | Keys |
|---|---|---|
| inspector_key | bigint | PK |
| inspector_id | varchar | BK |
| inspector_name | varchar | PII |
| employee_number | varchar | PII |
| qualification_level | varchar | |
| certification | varchar | |
| home_plant | varchar | |
| is_active | boolean |
Lookup tables (4)
dim_defect_type
Grain: one row per defect type
| Column | Type | Keys |
|---|---|---|
| defect_type_key | bigint | PK |
| defect_code | varchar | BK |
| defect_name | varchar | |
| defect_category | varchar | |
| severity | varchar | |
| is_safety_related | boolean |
dim_inspection_type
Grain: one row per inspection type
| Column | Type | Keys |
|---|---|---|
| inspection_type_key | bigint | PK |
| inspection_type_code | varchar | BK |
| inspection_type_name | varchar | |
| sampling_plan | varchar | |
| is_destructive | boolean |
dim_disposition
Grain: one row per disposition code
| Column | Type | Keys |
|---|---|---|
| disposition_key | bigint | PK |
| disposition_code | varchar | BK |
| disposition_name | varchar | |
| affects_yield | boolean | |
| requires_approval | boolean |
dim_root_cause
Grain: one row per root-cause category
| Column | Type | Keys |
|---|---|---|
| root_cause_key | bigint | PK |
| root_cause_code | varchar | BK |
| root_cause_name | varchar | |
| cause_category | varchar | |
| is_systemic | boolean |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- fct_inspection.date_key → dim_date (many-to-one)
- fct_inspection.product_key → dim_product (many-to-one)
- fct_inspection.supplier_key → dim_supplier (many-to-one)
- fct_inspection.plant_key → dim_plant (many-to-one)
- fct_inspection.inspector_key → dim_inspector (many-to-one)
- fct_inspection.inspection_type_key → dim_inspection_type (many-to-one)
- fct_inspection.disposition_key → dim_disposition (many-to-one)
- fct_nonconformance.opened_date_key → dim_date (many-to-one)
- fct_nonconformance.product_key → dim_product (many-to-one)
- fct_nonconformance.supplier_key → dim_supplier (many-to-one)
- fct_nonconformance.plant_key → dim_plant (many-to-one)
- fct_nonconformance.defect_type_key → dim_defect_type (many-to-one)
- fct_nonconformance.inspector_key → dim_inspector (many-to-one)
- fct_nonconformance.root_cause_key → dim_root_cause (many-to-one)
- fct_defect.date_key → dim_date (many-to-one)
- fct_defect.product_key → dim_product (many-to-one)
- fct_defect.supplier_key → dim_supplier (many-to-one)
- fct_defect.plant_key → dim_plant (many-to-one)
- fct_defect.defect_type_key → dim_defect_type (many-to-one)
- fct_defect.inspector_key → dim_inspector (many-to-one)
- fct_capa.initiated_date_key → dim_date (many-to-one)
- fct_capa.product_key → dim_product (many-to-one)
- fct_capa.supplier_key → dim_supplier (many-to-one)
- fct_capa.plant_key → dim_plant (many-to-one)
- fct_capa.root_cause_key → dim_root_cause (many-to-one)
- fct_capa.inspector_key → dim_inspector (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.