Real-Time Supplier Quality Monitoring with SQL

Manufacturers can catch supplier quality problems at the receiving dock—not on the assembly line—by streaming incoming inspection results into RisingWave and maintaining live supplier scorecards as SQL materialized views. Every lot inspection, defect code, and delivery deviation updates supplier ratings in real time, enabling procurement teams to act before defective parts reach production.

Why Real-Time Supplier Quality Monitoring Matters

Supplier quality escapes are among the most expensive failures in manufacturing. A bad lot that clears receiving inspection and reaches the assembly line can cause line stoppages, rework, warranty claims, and customer returns. The cost multiplies at every stage: a defect caught at receiving costs roughly 1× to fix; the same defect caught in warranty costs 100–1000×.

Traditional supplier scorecards are monthly or quarterly summaries computed from ERP batch extracts. They tell procurement what happened last quarter—too late to prevent this week's shipment from the same supplier with the same problem. Suppliers with deteriorating quality often fly under the radar for weeks before a scorecard review triggers a corrective action.

Real-time supplier quality monitoring changes the intervention point. When a supplier's rolling defect rate crosses a threshold, procurement receives an alert the same day—or the same hour—rather than at the next scorecard cycle. Corrective action requests, hold decisions, and containment plans can be initiated while the problem is still contained to the incoming lot.

The Streaming SQL Approach

Incoming quality inspection events—lot acceptance, defect codes, measurement results—flow from the quality management system into Kafka. Purchase order and delivery data flows from the ERP. RisingWave joins these streams to maintain supplier scorecards, defect-rate trend views, and delivery-performance metrics as continuously updated materialized views.

The scorecard model tracks three dimensions:

Quality: incoming defect rate (PPM), lot acceptance rate, defect code distribution
Delivery: on-time delivery rate, lead-time variance
Volume: lots received, units inspected

Building It Step by Step

Step 1: Data Source

-- Incoming inspection results from QMS
CREATE SOURCE inspection_results (
    inspection_id   VARCHAR,
    lot_id          VARCHAR,
    supplier_id     VARCHAR,
    part_number     VARCHAR,
    po_number       VARCHAR,
    units_received  INT,
    units_inspected INT,
    units_defective INT,
    defect_code     VARCHAR,
    disposition     VARCHAR,   -- 'accept', 'reject', 'conditional'
    inspector_id    VARCHAR,
    inspection_ts   TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'quality.incoming.inspections',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

-- Purchase order delivery events from ERP
CREATE SOURCE po_deliveries (
    po_number       VARCHAR,
    supplier_id     VARCHAR,
    part_number     VARCHAR,
    promised_date   DATE,
    actual_date     DATE,
    qty_ordered     INT,
    qty_delivered   INT,
    delivery_ts     TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'erp.po.deliveries',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

Step 2: Core Materialized View

-- Rolling 90-day supplier quality scorecard
CREATE MATERIALIZED VIEW supplier_quality_scorecard AS
SELECT
    supplier_id,
    part_number,
    -- Quality metrics
    SUM(units_received)                                    AS total_units_received,
    SUM(units_defective)                                   AS total_defective,
    -- PPM (parts per million defective)
    ROUND(
        SUM(units_defective) * 1000000.0
        / NULLIF(SUM(units_received), 0), 0
    )                                                      AS incoming_ppm,
    -- Lot acceptance rate
    COUNT(*) FILTER (WHERE disposition = 'accept')         AS lots_accepted,
    COUNT(*) FILTER (WHERE disposition = 'reject')         AS lots_rejected,
    COUNT(*)                                               AS lots_total,
    ROUND(
        COUNT(*) FILTER (WHERE disposition = 'accept') * 100.0
        / NULLIF(COUNT(*), 0), 2
    )                                                      AS lot_acceptance_rate_pct,
    -- Most common defect code for this supplier/part
    MODE() WITHIN GROUP (ORDER BY defect_code)             AS top_defect_code,
    MAX(inspection_ts)                                     AS last_inspection_ts
FROM inspection_results
GROUP BY supplier_id, part_number;

-- Defect trend: rolling 30-day PPM by week
CREATE MATERIALIZED VIEW supplier_ppm_weekly AS
SELECT
    supplier_id,
    part_number,
    window_start,
    window_end,
    SUM(units_received)   AS units_received,
    SUM(units_defective)  AS units_defective,
    ROUND(
        SUM(units_defective) * 1000000.0
        / NULLIF(SUM(units_received), 0), 0
    )                     AS weekly_ppm,
    COUNT(*)              AS lots_inspected
FROM TUMBLE(inspection_results, inspection_ts, INTERVAL '7 days')
GROUP BY supplier_id, part_number, window_start, window_end;

-- Delivery performance scorecard
CREATE MATERIALIZED VIEW supplier_delivery_scorecard AS
SELECT
    supplier_id,
    part_number,
    COUNT(*)                                             AS total_deliveries,
    COUNT(*) FILTER (WHERE actual_date <= promised_date) AS on_time_count,
    ROUND(
        COUNT(*) FILTER (WHERE actual_date <= promised_date) * 100.0
        / NULLIF(COUNT(*), 0), 2
    )                                                    AS otd_rate_pct,
    AVG(actual_date - promised_date)                     AS avg_days_variance,
    MAX(actual_date - promised_date)                     AS max_days_late,
    SUM(qty_ordered)                                     AS total_ordered,
    SUM(qty_delivered)                                   AS total_delivered,
    ROUND(
        SUM(qty_delivered) * 100.0 / NULLIF(SUM(qty_ordered), 0), 2
    )                                                    AS fill_rate_pct
FROM po_deliveries
GROUP BY supplier_id, part_number;

Step 3: Alerts and Aggregations

-- PPM breach alert: supplier PPM > 1000 in last 30 days
CREATE MATERIALIZED VIEW ppm_breach_alerts AS
SELECT
    supplier_id,
    part_number,
    SUM(units_received)  AS units_received_30d,
    SUM(units_defective) AS units_defective_30d,
    ROUND(
        SUM(units_defective) * 1000000.0
        / NULLIF(SUM(units_received), 0), 0
    )                    AS ppm_30d,
    window_start,
    window_end
FROM TUMBLE(inspection_results, inspection_ts, INTERVAL '30 days')
GROUP BY supplier_id, part_number, window_start, window_end
HAVING SUM(units_defective) * 1000000.0
       / NULLIF(SUM(units_received), 0) > 1000;

CREATE SINK ppm_breach_sink
AS SELECT * FROM ppm_breach_alerts
WITH (
    connector = 'kafka',
    topic = 'quality.alerts.supplier-ppm',
    properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;

-- Lot rejection alert: supplier lot rejected (real-time)
CREATE MATERIALIZED VIEW lot_rejection_alerts AS
SELECT
    inspection_id,
    lot_id,
    supplier_id,
    part_number,
    po_number,
    units_received,
    units_defective,
    defect_code,
    disposition,
    inspection_ts
FROM inspection_results
WHERE disposition = 'reject';

CREATE SINK lot_rejection_sink
AS SELECT * FROM lot_rejection_alerts
WITH (
    connector = 'kafka',
    topic = 'quality.alerts.lot-rejection',
    properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;

Comparison Table

Approach	Scorecard Freshness	Alert on Lot Rejection	PPM Trend Visibility	Development Effort
Monthly ERP report	Monthly	Email next day	Monthly	Low
Quarterly QMS extract	Quarterly	None automated	Quarterly	Low
Custom ETL + BI tool	Daily	Email next day	Weekly	High
RisingWave streaming SQL	Real-time	Sub-second	Rolling weekly	Low (SQL)

FAQ

Can I join supplier scorecard data with approved vendor list (AVL) rules?

Yes. Store your AVL and supplier tier classifications in a RisingWave table. Join the supplier_quality_scorecard view against this table in a downstream view or at the BI query layer. When a supplier's PPM crosses a tier threshold, a CASE expression can automatically compute the new tier classification.

How do I handle suppliers who ship to multiple plants?

Add a plant_id field to both source schemas. Group materialized views by (supplier_id, part_number, plant_id) to get plant-level scorecards, then aggregate without plant_id for enterprise-level scorecards. Both views update from the same event stream.

What if inspection data arrives late because the QMS has processing delays?

Configure a watermark on the inspection_results source. RisingWave will wait for the configured late-arrival window before closing a time bucket, ensuring late inspection results are incorporated into the correct weekly or monthly aggregation.

Key Takeaways

Stream incoming inspection results into RisingWave to compute PPM, lot acceptance rate, and defect code distributions as continuously updated materialized views.
Lot rejection alerts fire within seconds of a disposition event—before the rejected lot moves deeper into the facility.
Rolling 30-day PPM breach alerts give procurement actionable early warning rather than waiting for a quarterly scorecard review.
Joining quality and delivery scorecard views provides a composite supplier performance index that updates every time a new inspection or delivery event arrives.