Real-Time Production Yield Monitoring with Streaming SQL

Production yield losses are often visible in the data minutes before they become significant scrap events—if the data is processed in real time. RisingWave, a PostgreSQL-compatible streaming database, ingests machine sensor events and cycle completion records to maintain continuously updated OEE and scrap rate views, enabling floor supervisors to intervene before yield drops compound across a shift.

Why Real-Time Yield Monitoring Matters in Manufacturing

Overall Equipment Effectiveness (OEE) measures three dimensions of production performance: Availability (is the machine running?), Performance (is it running at its target rate?), and Quality (are parts passing first-pass inspection?). Each dimension is eroded by different causes, and all three require continuous monitoring to act on.

The problem with batch-based yield monitoring:

• Shift-end OEE reports arrive too late for supervisors to intervene. By the time a low-OEE alert fires, the shift has ended.
• Scrap rate spikes that begin as 2% anomalies become 15% events within an hour if the process parameter causing them is not corrected.
• Cycle time drift signals machine degradation, tooling wear, or material variation before parts begin failing quality inspection.
• Micro-stoppages lasting less than five minutes each are often invisible in shift summaries but cumulatively represent significant availability loss.

Real-time yield monitoring means OEE components are computed continuously as each cycle completion event arrives, giving floor supervisors a live view of production health rather than a post-mortem report.

How Streaming SQL Solves This

RisingWave consumes cycle completion events, quality inspection results, and machine state changes from Kafka, and maintains materialized views that compute OEE components incrementally. Floor supervisors and MES systems query these views like any PostgreSQL table, always seeing current data.

Specific capabilities:

• Per-line, per-shift OEE computation updated with every cycle completion
• Rolling scrap rate with configurable window sizes to detect both short bursts and sustained degradation
• Cycle time percentile tracking to detect drift before it crosses spec limits
• Automatic sink to MES and SCADA systems via Kafka for loop closure

Building the System

Step 1: Data Source

CREATE SOURCE production_cycle_events (
    event_id          VARCHAR,
    line_id           VARCHAR,
    machine_id        VARCHAR,
    shift_code        VARCHAR,   -- e.g. 'A','B','C' or 'DAY','NIGHT'
    part_number       VARCHAR,
    event_time        TIMESTAMPTZ,
    cycle_time_sec    FLOAT,
    target_cycle_sec  FLOAT,
    part_result       VARCHAR,   -- 'PASS','FAIL','SCRAP','REWORK'
    scrap_code        VARCHAR,   -- defect category code if SCRAP
    machine_state     VARCHAR,   -- 'RUNNING','IDLE','FAULT','CHANGEOVER'
    downtime_code     VARCHAR    -- reason code if not RUNNING
)
WITH (
    connector = 'kafka',
    topic = 'manufacturing.production.cycles',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

Step 2: Real-Time View

Compute OEE components per line per shift, updated with every incoming cycle event:

CREATE MATERIALIZED VIEW oee_by_line_shift AS
SELECT
    line_id,
    shift_code,
    window_start,
    window_end,
    COUNT(*)                                                  AS total_cycles,
    COUNT(*) FILTER (WHERE machine_state = 'RUNNING')         AS running_cycles,
    COUNT(*) FILTER (WHERE part_result = 'PASS')              AS pass_count,
    COUNT(*) FILTER (WHERE part_result IN ('FAIL','SCRAP','REWORK')) AS defect_count,
    COUNT(*) FILTER (WHERE part_result = 'SCRAP')             AS scrap_count,
    AVG(cycle_time_sec)                                       AS avg_cycle_time_sec,
    AVG(target_cycle_sec)                                     AS avg_target_cycle_sec,
    -- OEE Quality component
    CASE WHEN COUNT(*) > 0
         THEN COUNT(*) FILTER (WHERE part_result = 'PASS')::FLOAT / COUNT(*)
         ELSE 0 END AS quality_rate,
    -- OEE Performance component
    CASE WHEN AVG(cycle_time_sec) > 0
         THEN AVG(target_cycle_sec) / AVG(cycle_time_sec)
         ELSE 0 END AS performance_rate,
    -- Availability approximation
    COUNT(*) FILTER (WHERE machine_state = 'RUNNING')::FLOAT /
        NULLIF(COUNT(*), 0)                                   AS availability_rate
FROM TUMBLE(
    production_cycle_events,
    event_time,
    INTERVAL '1 hour'
)
GROUP BY line_id, shift_code, window_start, window_end;

Track rolling 15-minute scrap rate to detect emerging quality issues:

CREATE MATERIALIZED VIEW scrap_rate_rolling_15m AS
SELECT
    line_id,
    machine_id,
    window_start,
    window_end,
    COUNT(*) FILTER (WHERE part_result = 'SCRAP')::FLOAT / NULLIF(COUNT(*), 0) AS scrap_rate,
    COUNT(*) FILTER (WHERE part_result = 'SCRAP') AS scrap_count,
    COUNT(*) AS total_parts,
    MODE() WITHIN GROUP (ORDER BY scrap_code) AS dominant_scrap_code
FROM TUMBLE(
    production_cycle_events,
    event_time,
    INTERVAL '15 minutes'
)
GROUP BY line_id, machine_id, window_start, window_end;

Step 3: Alerts

Alert on scrap rate spikes and OEE degradation with routing to the production supervisor notification system:

CREATE MATERIALIZED VIEW yield_alerts AS
SELECT
    line_id,
    shift_code,
    window_start,
    window_end,
    quality_rate,
    performance_rate,
    availability_rate,
    quality_rate * performance_rate * availability_rate AS oee,
    CASE
        WHEN quality_rate < 0.95                           THEN 'QUALITY_BELOW_TARGET'
        WHEN performance_rate < 0.85                       THEN 'PERFORMANCE_DEGRADED'
        WHEN availability_rate < 0.90                      THEN 'AVAILABILITY_LOSS'
        WHEN quality_rate * performance_rate * availability_rate < 0.75 THEN 'OEE_BELOW_THRESHOLD'
        ELSE 'NORMAL'
    END AS alert_type
FROM oee_by_line_shift
WHERE quality_rate < 0.95
   OR performance_rate < 0.85
   OR availability_rate < 0.90;

CREATE SINK yield_alerts_sink
FROM yield_alerts
WITH (
    connector = 'kafka',
    topic = 'manufacturing.yield.alerts',
    properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;

Comparison Table

FAQ

Q: How do I compute OEE accurately when cycle time events include planned downtime and changeovers? Filter or categorize events by machine_state in your view logic. Planned downtime (changeover, scheduled maintenance) is typically excluded from availability loss calculations. You can add a is_planned_downtime flag to your source events or join against a maintenance schedule table to classify downtime events correctly.

Q: Can RisingWave handle multiple product types with different target cycle times on the same line? Yes. Join production_cycle_events against a product specification table on part_number to retrieve the correct target_cycle_sec per part type. RisingWave supports streaming-static joins where the specification table is a batch source updated periodically.

Q: How does this integrate with our existing MES? RisingWave can write to Kafka topics that the MES consumes, or use a JDBC sink to write directly to the MES database. Many MES platforms also expose REST APIs that a downstream service can call based on the Kafka alert stream.

Key Takeaways

• Real-time OEE monitoring requires processing cycle completion events as they arrive—batch reporting cannot provide the response time needed for in-shift intervention.
• RisingWave's windowed SQL aggregations compute OEE availability, performance, and quality components continuously per production line and shift.
• Rolling 15-minute scrap rate views detect quality degradation in minutes rather than hours, enabling rapid response to tooling or process parameter issues.
• Streaming SQL eliminates the complexity of building custom streaming pipelines while providing the same low-latency results, integrating natively with Kafka-connected MES systems.

Get started | Slack