Real-Time Manufacturing Analytics with Streaming SQL: Quality Control, Yield, SPC, and Lean Dashboards

Real-Time Manufacturing Analytics with Streaming SQL: Quality Control, Yield, SPC, and Lean Dashboards

·

Updated

·

19 min read

Real-time manufacturing analytics with streaming SQL means statistical process control, defect detection, first-pass yield, OEE, takt adherence, inventory position, supplier scorecards, and energy intensity are all maintained as materialized views that update the moment an inspection result, cycle completion, WMS scan, or meter reading arrives, so a line supervisor acts during the shift instead of reading about it at shift end. This guide builds the full plant analytics layer on RisingWave, a PostgreSQL-compatible streaming database, with MES, ERP, and QMS context joined in through CDC.

Why End-of-Shift Reports Are Too Late

Quality defects caught at the production line cost a fraction of those discovered after shipment, and the cost rises with every station a bad part travels through. Yet the standard manufacturing analytics loop still looks like this: inline sensors write to local databases, a batch extract runs at shift end, a quality engineer reviews a control chart and finds a drift that began six hours ago.

The same latency problem appears everywhere on the floor:

  • Control charts plotted against batch-sampled data detect a tool wearing toward the upper control limit after the fact. Real-time SPC catches the Western Electric rule violation within seconds.
  • Defect alerts are single-point threshold checks that miss a gradual drift crossing the spec limit on part 47 of 50, and traceability to the tool change or material lot that caused it is reconstructed afterward.
  • OEE arrives as a shift-end report when nobody can intervene. A 2 percent scrap anomaly becomes a 15 percent event within an hour if the parameter is not corrected.
  • Inventory positions reconciled nightly in the ERP are useless for a line running 2,000 units a shift.
  • Supplier scorecards computed quarterly tell procurement what happened last quarter, too late to stop this week's shipment from the same supplier with the same defect.
  • Energy is measured from the monthly utility invoice, while 20 to 30 percent of consumption in discrete manufacturing happens during idle and standby.

Every one of these is a windowed aggregation over a stream of events, sometimes joined to reference data. RisingWave maintains them as incrementally updated materialized views: only the affected station, machine, SKU, or supplier row changes on each event, and the result is queryable over the PostgreSQL protocol by Grafana, the MES, or a supervisor's dashboard, or pushed to Kafka for SCADA holds, ERP reorders, and operator HMIs. RisingWave is open source under Apache 2.0, written in Rust, and checkpoints state to S3.

Sources: Inspection, Cycles, Process Events, WMS, ERP, and Meters

The plant produces several streams. Inline gauges, vision systems, and CMMs publish measurements and pass/fail results; PLCs and the MES publish cycle completions and machine state; the MES also publishes tool changes, lot changes, and operator logins; the WMS publishes receipts and issues; the QMS publishes incoming inspection; the ERP publishes deliveries; IoT gateways publish meter readings. Production order context comes from the MES database through CDC.

CREATE SOURCE quality_measurements (
    measurement_id    VARCHAR,
    part_id           VARCHAR,
    production_order  VARCHAR,
    station_id        VARCHAR,
    line_id           VARCHAR,
    plant_id          VARCHAR,
    characteristic    VARCHAR,          -- 'DIMENSION_A', 'TORQUE', 'WEIGHT', 'SURFACE_ROUGHNESS'
    nominal_value     DOUBLE PRECISION,
    usl               DOUBLE PRECISION, -- upper spec limit
    lsl               DOUBLE PRECISION, -- lower spec limit
    measured_value    DOUBLE PRECISION,
    operator_id       VARCHAR,
    tool_id           VARCHAR,
    gauge_id          VARCHAR,
    event_ts          TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'manufacturing.quality_measurements',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

CREATE SOURCE inspection_results (
    inspection_id      VARCHAR,
    line_id            VARCHAR,
    machine_id         VARCHAR,
    station_id         VARCHAR,
    part_serial        VARCHAR,
    part_number        VARCHAR,
    shift_code         VARCHAR,
    result             VARCHAR,         -- 'PASS', 'FAIL', 'MARGINAL'
    defect_code        VARCHAR,
    defect_severity    INT,             -- 1 minor, 2 major, 3 critical
    tool_id            VARCHAR,
    material_lot_id    VARCHAR,
    operator_id        VARCHAR,
    inspect_time       TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'manufacturing.inspection.results',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

CREATE SOURCE process_events (
    event_id      VARCHAR,
    line_id       VARCHAR,
    machine_id    VARCHAR,
    event_type    VARCHAR,              -- 'TOOL_CHANGE', 'LOT_CHANGE', 'OPERATOR_LOGIN', 'PARAM_CHANGE'
    reference_id  VARCHAR,
    event_time    TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'manufacturing.process.events',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

CREATE SOURCE production_cycle_events (
    event_id          VARCHAR,
    line_id           VARCHAR,
    station_id        VARCHAR,
    machine_id        VARCHAR,
    shift_code        VARCHAR,
    part_number       VARCHAR,
    cycle_time_sec    FLOAT,
    takt_time_sec     FLOAT,            -- target from the production schedule
    part_result       VARCHAR,          -- 'PASS', 'FAIL', 'SCRAP', 'REWORK'
    scrap_code        VARCHAR,
    machine_state     VARCHAR,          -- 'RUNNING', 'IDLE', 'FAULT', 'CHANGEOVER'
    operator_id       VARCHAR,
    event_time        TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'manufacturing.production.cycles',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'latest'
) FORMAT PLAIN ENCODE JSON;

CREATE TABLE production_orders (
    production_order  VARCHAR PRIMARY KEY,
    part_number       VARCHAR,
    part_description  VARCHAR,
    customer_id       VARCHAR,
    quantity_ordered  INTEGER,
    started_ts        TIMESTAMPTZ
) WITH (
    connector = 'postgres-cdc',
    hostname = 'mes-db', port = '5432',
    username = 'replicator', password = 'secret',
    database.name = 'mes', schema.name = 'public', table.name = 'production_orders'
);

Statistical Process Control: Control Limits, Cpk, and Rule Violations

SPC distinguishes natural variation from assignable causes. Traditional implementations compute control limits once from a baseline study and evaluate charts after data is collected. Streaming SPC computes X-bar, sigma, control limits, and process capability from the current window and fires out-of-control signals as each measurement arrives.

CREATE MATERIALIZED VIEW spc_control_chart AS
SELECT window_start, window_end, station_id, line_id, characteristic,
       AVG(measured_value)                                   AS x_bar,
       STDDEV_POP(measured_value)                            AS sigma,
       COUNT(*)                                              AS sample_size,
       AVG(measured_value) + 3 * STDDEV_POP(measured_value)  AS ucl,
       AVG(measured_value) - 3 * STDDEV_POP(measured_value)  AS lcl,
       AVG(measured_value) + 2 * STDDEV_POP(measured_value)  AS warning_ucl,
       AVG(measured_value) - 2 * STDDEV_POP(measured_value)  AS warning_lcl,
       MAX(measured_value) - MIN(measured_value)             AS range_value,
       MAX(usl) AS usl, MIN(lsl) AS lsl,
       LEAST((MAX(usl) - AVG(measured_value)) / NULLIF(3 * STDDEV_POP(measured_value), 0),
             (AVG(measured_value) - MIN(lsl)) / NULLIF(3 * STDDEV_POP(measured_value), 0)) AS cpk,
       (MAX(usl) - MIN(lsl)) / NULLIF(6 * STDDEV_POP(measured_value), 0)                   AS cp
FROM TUMBLE(quality_measurements, event_ts, INTERVAL '20 MINUTES')
GROUP BY window_start, window_end, station_id, line_id, characteristic;

Individual measurements are evaluated against the limits of their window, which implements Western Electric Rule 1 and the spec limits, and the window statistics flag low capability:

CREATE MATERIALIZED VIEW spc_ooc_alerts AS
SELECT m.measurement_id, m.part_id, m.production_order, m.station_id, m.line_id, m.characteristic,
       m.measured_value, m.usl, m.lsl, cl.ucl, cl.lcl, cl.cpk, m.operator_id, m.tool_id, m.event_ts,
       CASE WHEN m.measured_value > m.usl  THEN 'SPEC_VIOLATION_HIGH'
            WHEN m.measured_value < m.lsl  THEN 'SPEC_VIOLATION_LOW'
            WHEN m.measured_value > cl.ucl THEN 'ABOVE_UCL'
            WHEN m.measured_value < cl.lcl THEN 'BELOW_LCL'
            WHEN cl.cpk < 1.0              THEN 'LOW_CPK'
            WHEN cl.cpk < 1.33             THEN 'MARGINAL_CPK' END AS ooc_rule
FROM quality_measurements m
JOIN spc_control_chart cl
  ON m.station_id = cl.station_id AND m.characteristic = cl.characteristic
 AND m.event_ts >= cl.window_start AND m.event_ts < cl.window_end
WHERE m.measured_value > m.usl OR m.measured_value < m.lsl
   OR m.measured_value > cl.ucl OR m.measured_value < cl.lcl
   OR cl.cpk < 1.33;

Joining measurements to the CDC production order table gives every alert the part number, customer, operator, and tool revision the quality engineer needs to act, and an hourly first-pass yield per line falls out of the same enriched stream:

CREATE MATERIALIZED VIEW enriched_measurements AS
SELECT m.*, p.part_number, p.part_description, p.customer_id,
       CASE WHEN m.measured_value > m.usl THEN 'ABOVE_USL'
            WHEN m.measured_value < m.lsl THEN 'BELOW_LSL'
            ELSE 'IN_SPEC' END AS spec_status
FROM quality_measurements m
LEFT JOIN production_orders p ON p.production_order = m.production_order;

CREATE MATERIALIZED VIEW hourly_first_pass_yield AS
SELECT window_start, window_end, line_id, plant_id, characteristic,
       COUNT(*)                                          AS parts_inspected,
       COUNT(*) FILTER (WHERE spec_status = 'IN_SPEC')   AS parts_in_spec,
       ROUND(100.0 * COUNT(*) FILTER (WHERE spec_status = 'IN_SPEC') / NULLIF(COUNT(*), 0), 2) AS first_pass_yield_pct
FROM TUMBLE(enriched_measurements, event_ts, INTERVAL '1 HOUR')
GROUP BY window_start, window_end, line_id, plant_id, characteristic;

Additional Western Electric rules, such as two of three points beyond two sigma or eight consecutive points on one side of the center line, are LAG-based window functions over the measurement stream partitioned by station and characteristic.

Defect Detection and Root Cause Correlation

Pass/fail inspection results carry the defect code, severity, tool, lot, and operator. A rolling 30-minute defect rate per machine and defect category, with the dominant tool and lot in the window, is the detection view; joining it to process events in the preceding hour is the root cause view.

CREATE MATERIALIZED VIEW defect_rate_rolling AS
SELECT line_id, machine_id, defect_code, window_start, window_end,
       COUNT(*)                                                     AS total_inspections,
       COUNT(*) FILTER (WHERE result = 'FAIL')                      AS fail_count,
       COUNT(*) FILTER (WHERE result = 'MARGINAL')                  AS marginal_count,
       COUNT(*) FILTER (WHERE defect_severity = 3)                  AS critical_defects,
       COUNT(*) FILTER (WHERE result = 'FAIL')::FLOAT / NULLIF(COUNT(*), 0) AS defect_rate,
       MODE() WITHIN GROUP (ORDER BY tool_id)                       AS dominant_tool,
       MODE() WITHIN GROUP (ORDER BY material_lot_id)               AS dominant_lot
FROM TUMBLE(inspection_results, inspect_time, INTERVAL '30 MINUTES')
GROUP BY line_id, machine_id, defect_code, window_start, window_end;

CREATE MATERIALIZED VIEW defect_process_correlation AS
SELECT d.line_id, d.machine_id, d.window_start, d.defect_rate, d.fail_count, d.dominant_tool, d.dominant_lot,
       p.event_type AS recent_process_change, p.reference_id AS changed_item, p.event_time AS change_time
FROM defect_rate_rolling d
JOIN process_events p
  ON d.machine_id = p.machine_id
 AND p.event_time BETWEEN d.window_start - INTERVAL '1 HOUR' AND d.window_end
WHERE d.defect_rate > 0.05;

CREATE MATERIALIZED VIEW defect_alerts AS
SELECT line_id, machine_id, defect_code, window_start, window_end, defect_rate, fail_count, critical_defects, dominant_tool, dominant_lot,
       CASE WHEN critical_defects >= 3  THEN 'MACHINE_HOLD_REQUIRED'
            WHEN defect_rate >= 0.10    THEN 'HIGH_DEFECT_RATE'
            ELSE 'DEFECT_RATE_WARNING' END AS alert_type
FROM defect_rate_rolling
WHERE defect_rate >= 0.05 OR critical_defects >= 3;

Three critical defects in a 30-minute window publish a machine hold to SCADA through the alert sink before the next cycle. A defect spike 20 minutes after a TOOL_CHANGE on the same machine names its own root cause candidate.

OEE, Scrap Rate, Takt Adherence, and the Lean Dashboard

Overall Equipment Effectiveness is availability times performance times quality. Computed from cycle events as they arrive, each component is live per line and shift, and the lean dashboard adds the station-level view operators need: cycle time against takt, first-pass yield, cycle time stability, and WIP accumulating between stations.

CREATE MATERIALIZED VIEW oee_by_line_shift AS
SELECT line_id, shift_code, window_start, window_end,
       COUNT(*)                                                         AS total_cycles,
       COUNT(*) FILTER (WHERE part_result = 'PASS')                     AS pass_count,
       COUNT(*) FILTER (WHERE part_result = 'SCRAP')                    AS scrap_count,
       AVG(cycle_time_sec)                                              AS avg_cycle_time_sec,
       COUNT(*) FILTER (WHERE part_result = 'PASS')::FLOAT / NULLIF(COUNT(*), 0)              AS quality_rate,
       AVG(takt_time_sec) / NULLIF(AVG(cycle_time_sec), 0)                                     AS performance_rate,
       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;

CREATE MATERIALIZED VIEW yield_alerts AS
SELECT *, 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'
            ELSE 'OEE_BELOW_THRESHOLD' END AS alert_type
FROM oee_by_line_shift
WHERE quality_rate < 0.95 OR performance_rate < 0.85 OR availability_rate < 0.90
   OR quality_rate * performance_rate * availability_rate < 0.75;

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,
       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;

CREATE MATERIALIZED VIEW station_kpis_1h AS
SELECT station_id, line_id, part_number, window_start, window_end,
       COUNT(*)                                                              AS total_cycles,
       AVG(cycle_time_sec)                                                   AS avg_cycle_sec,
       MAX(takt_time_sec)                                                    AS takt_time_sec,
       ROUND(COUNT(*) FILTER (WHERE cycle_time_sec <= takt_time_sec) * 100.0 / NULLIF(COUNT(*), 0), 1) AS takt_adherence_pct,
       ROUND(COUNT(*) FILTER (WHERE part_result = 'PASS') * 100.0 / NULLIF(COUNT(*), 0), 2)            AS first_pass_yield_pct,
       ROUND((STDDEV(cycle_time_sec) / NULLIF(AVG(cycle_time_sec), 0))::NUMERIC, 3)                    AS cv_cycle_time,
       COUNT(DISTINCT operator_id)                                           AS operators_active
FROM TUMBLE(production_cycle_events, event_time, INTERVAL '1 HOUR')
GROUP BY station_id, line_id, part_number, window_start, window_end;

CREATE MATERIALIZED VIEW takt_breach_alerts AS
SELECT station_id, line_id, part_number, window_start, window_end,
       AVG(cycle_time_sec) AS avg_cycle_sec, MAX(takt_time_sec) AS takt_time_sec,
       ROUND((AVG(cycle_time_sec) * 100.0 / NULLIF(MAX(takt_time_sec), 0))::NUMERIC, 1) AS cycle_to_takt_pct
FROM TUMBLE(production_cycle_events, event_time, INTERVAL '5 MINUTES')
GROUP BY station_id, line_id, part_number, window_start, window_end
HAVING AVG(cycle_time_sec) > MAX(takt_time_sec) * 1.1;

The cycle time coefficient of variation is the lean stability indicator: a station whose variation rises before its mean does is about to miss takt. A WIP transfer stream aggregated per receiving station in 15-minute windows exposes overproduction, the first of the seven wastes, as inventory piling up between stations. Operators watch station_kpis_1h and takt_breach_alerts, supervisors watch scrap_rate_rolling_15m, plant managers watch oee_by_line_shift, and all of it is one event stream.

Inventory Position, WIP, Reorder Points, and Turns

Inventory is the largest balance sheet item most manufacturers control directly. Running positions from WMS deltas, WIP from MES start, complete, and scrap events, and a reorder view against a parameter table replace the nightly ERP reconciliation.

CREATE SOURCE wms_events (
    event_id VARCHAR, event_type VARCHAR,        -- 'receipt', 'issue', 'adjust', 'hold'
    sku_id VARCHAR, location_id VARCHAR, qty_delta NUMERIC, unit_cost NUMERIC, event_ts TIMESTAMPTZ
) WITH (connector = 'kafka', topic = 'wms.inventory.events', properties.bootstrap.server = 'kafka:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;

CREATE SOURCE mes_events (
    order_id VARCHAR, sku_id VARCHAR, event_type VARCHAR,    -- 'start', 'complete', 'scrap'
    qty NUMERIC, scrap_qty NUMERIC, work_center_id VARCHAR, event_ts TIMESTAMPTZ
) WITH (connector = 'kafka', topic = 'mes.production.events', properties.bootstrap.server = 'kafka:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;

CREATE TABLE sku_reorder_params (sku_id VARCHAR PRIMARY KEY, reorder_point NUMERIC, order_qty NUMERIC, lead_time_days INT, safety_stock NUMERIC);

CREATE MATERIALIZED VIEW inventory_position AS
SELECT sku_id, location_id,
       SUM(qty_delta)                                   AS on_hand_qty,
       SUM(qty_delta * unit_cost)                       AS on_hand_value,
       COUNT(*) FILTER (WHERE event_type = 'hold')      AS hold_count,
       MAX(event_ts)                                    AS last_movement_ts
FROM wms_events
GROUP BY sku_id, location_id;

CREATE MATERIALIZED VIEW wip_position AS
SELECT work_center_id, sku_id,
       SUM(qty) FILTER (WHERE event_type = 'start')     AS units_started,
       SUM(qty) FILTER (WHERE event_type = 'complete')  AS units_completed,
       SUM(scrap_qty)                                   AS total_scrap,
       SUM(qty) FILTER (WHERE event_type = 'start') - SUM(qty) FILTER (WHERE event_type = 'complete') - SUM(scrap_qty) AS wip_qty
FROM mes_events
GROUP BY work_center_id, sku_id;

CREATE MATERIALIZED VIEW reorder_alerts AS
SELECT p.sku_id, p.location_id, p.on_hand_qty, r.reorder_point, r.order_qty, r.lead_time_days,
       p.on_hand_qty - r.reorder_point AS qty_below_rop
FROM inventory_position p
JOIN sku_reorder_params r ON p.sku_id = r.sku_id
WHERE p.on_hand_qty < r.reorder_point;

CREATE MATERIALIZED VIEW daily_issues AS
SELECT sku_id, window_start AS day, SUM(ABS(qty_delta)) FILTER (WHERE event_type = 'issue') AS issued, SUM(qty_delta) AS net_change
FROM TUMBLE(wms_events, event_ts, INTERVAL '1 DAY')
GROUP BY sku_id, window_start;

CREATE MATERIALIZED VIEW inventory_turns_30d AS
SELECT sku_id, day,
       SUM(issued) OVER (PARTITION BY sku_id ORDER BY day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)       AS issues_30d,
       AVG(net_change) OVER (PARTITION BY sku_id ORDER BY day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW)   AS avg_daily_change_30d
FROM daily_issues;

CREATE MATERIALIZED VIEW scrap_rate_alerts AS
SELECT work_center_id, sku_id, window_start, window_end,
       SUM(scrap_qty) AS scrap_qty_1h,
       ROUND(SUM(scrap_qty) * 100.0 / NULLIF(SUM(qty) FILTER (WHERE event_type = 'start'), 0), 2) AS scrap_rate_pct
FROM TUMBLE(mes_events, event_ts, INTERVAL '1 HOUR')
GROUP BY work_center_id, sku_id, window_start, window_end
HAVING SUM(scrap_qty) * 100.0 / NULLIF(SUM(qty) FILTER (WHERE event_type = 'start'), 0) > 3.0;

The reorder view sinks to the ERP's purchasing topic, and because sku_reorder_params is a table, a buyer changing a reorder point is an UPDATE that takes effect on the next movement.

Supplier Quality: PPM, Lot Acceptance, and Delivery Performance

Supplier escapes that clear receiving and reach assembly cause line stops and warranty claims. Incoming inspection from the QMS and purchase order deliveries from the ERP feed a live scorecard with parts per million defective, lot acceptance, top defect code, on-time delivery, and fill rate per supplier and part.

CREATE SOURCE incoming_inspections (
    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'
    inspection_ts TIMESTAMPTZ
) WITH (connector = 'kafka', topic = 'quality.incoming.inspections', properties.bootstrap.server = 'kafka:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;

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;

CREATE MATERIALIZED VIEW supplier_quality_scorecard AS
SELECT supplier_id, part_number,
       SUM(units_received)                                                           AS total_units_received,
       SUM(units_defective)                                                          AS total_defective,
       ROUND(SUM(units_defective) * 1000000.0 / NULLIF(SUM(units_received), 0), 0)   AS incoming_ppm,
       COUNT(*) FILTER (WHERE disposition = 'accept')                                AS lots_accepted,
       COUNT(*) FILTER (WHERE disposition = 'reject')                                AS lots_rejected,
       ROUND(COUNT(*) FILTER (WHERE disposition = 'accept') * 100.0 / NULLIF(COUNT(*), 0), 2) AS lot_acceptance_rate_pct,
       MODE() WITHIN GROUP (ORDER BY defect_code)                                    AS top_defect_code,
       MAX(inspection_ts)                                                            AS last_inspection_ts
FROM incoming_inspections
WHERE inspection_ts > NOW() - INTERVAL '90 DAYS'
GROUP BY supplier_id, part_number;

CREATE MATERIALIZED VIEW supplier_delivery_scorecard AS
SELECT supplier_id, part_number,
       COUNT(*)                                                                      AS total_deliveries,
       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,
       ROUND(SUM(qty_delivered) * 100.0 / NULLIF(SUM(qty_ordered), 0), 2)            AS fill_rate_pct
FROM po_deliveries
WHERE delivery_ts > NOW() - INTERVAL '90 DAYS'
GROUP BY supplier_id, part_number;

CREATE MATERIALIZED VIEW ppm_breach_alerts AS
SELECT supplier_id, part_number, window_start, window_end,
       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
FROM TUMBLE(incoming_inspections, 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;

A lot rejection is a row in incoming_inspections with disposition = 'reject', and a filtered view of it sinks to procurement the moment the inspector enters the disposition. Joining the supplier scorecard to defect_rate_rolling by part and lot closes the loop: a defect spike on the line traced to a material lot becomes a supplier quality event the same day.

Energy per Unit, Idle Waste, and Shift Emissions

Energy is the third-largest cost after labor and materials, and Scope 2 reporting now requires kWh per unit by machine and shift rather than by building and month. Joining hourly meter aggregates to hourly production counts gives energy intensity; ten-minute windows over idle readings flag waste.

CREATE SOURCE energy_meter_readings (
    meter_id VARCHAR, machine_id VARCHAR, line_id VARCHAR, plant_id VARCHAR,
    active_power_kw NUMERIC, power_factor NUMERIC,
    machine_state VARCHAR,        -- 'running', 'idle', 'standby', 'off'
    reading_ts TIMESTAMPTZ
) WITH (connector = 'kafka', topic = 'factory.energy.meter-readings', properties.bootstrap.server = 'kafka:9092', scan.startup.mode = 'latest')
FORMAT PLAIN ENCODE JSON;

CREATE MATERIALIZED VIEW energy_hourly AS
SELECT machine_id, line_id, plant_id, window_start, window_end,
       AVG(active_power_kw)                       AS avg_power_kw,
       MAX(active_power_kw)                       AS peak_power_kw,
       SUM(active_power_kw) * (30.0 / 3600.0)     AS kwh_consumed,         -- one reading every 30 seconds
       AVG(power_factor)                          AS avg_power_factor,
       ROUND(COUNT(*) FILTER (WHERE machine_state IN ('idle', 'standby')) * 100.0 / NULLIF(COUNT(*), 0), 1) AS idle_pct
FROM TUMBLE(energy_meter_readings, reading_ts, INTERVAL '1 HOUR')
GROUP BY machine_id, line_id, plant_id, window_start, window_end;

CREATE MATERIALIZED VIEW production_hourly AS
SELECT machine_id, window_start, window_end, COUNT(*) FILTER (WHERE part_result = 'PASS') AS units_produced
FROM TUMBLE(production_cycle_events, event_time, INTERVAL '1 HOUR')
GROUP BY machine_id, window_start, window_end;

CREATE MATERIALIZED VIEW energy_intensity AS
SELECT e.machine_id, e.line_id, e.plant_id, e.window_start, e.window_end, e.kwh_consumed, p.units_produced,
       ROUND(e.kwh_consumed / NULLIF(p.units_produced, 0), 4) AS kwh_per_unit, e.idle_pct, e.avg_power_factor
FROM energy_hourly e
JOIN production_hourly p ON e.machine_id = p.machine_id AND e.window_start = p.window_start;

CREATE MATERIALIZED VIEW idle_waste_alerts AS
SELECT machine_id, line_id, machine_state, window_start, window_end,
       AVG(active_power_kw) AS avg_idle_power_kw,
       ROUND(AVG(active_power_kw) * (10.0 / 60.0), 3) AS estimated_waste_kwh
FROM TUMBLE(energy_meter_readings, reading_ts, INTERVAL '10 MINUTES')
WHERE machine_state IN ('idle', 'standby')
GROUP BY machine_id, line_id, machine_state, window_start, window_end
HAVING AVG(active_power_kw) > 5.0;

CREATE MATERIALIZED VIEW shift_energy_summary AS
SELECT plant_id, window_start AS shift_start, window_end AS shift_end,
       SUM(kwh_consumed) AS total_kwh, SUM(units_produced) AS total_units,
       ROUND(SUM(kwh_consumed) / NULLIF(SUM(units_produced), 0), 4) AS plant_kwh_per_unit,
       ROUND(SUM(kwh_consumed) * 0.233, 2) AS co2e_kg                  -- grid factor, make it a table in production
FROM energy_intensity
GROUP BY plant_id, window_start, window_end;

A machine drawing more than 5 kW while idle for ten minutes is the lowest-hanging fruit in a factory's energy program, and it is invisible on a monthly invoice.

Routing to SCADA, HMI, ERP, and Dashboards

Each alert view sinks to the system that acts on it. Out-of-spec parts and machine holds go to the quality system and SCADA; SPC violations go to the operator HMI; reorder alerts go to the ERP; supplier PPM breaches go to procurement; yield metrics go to the analytics database behind shift reports.

CREATE SINK spc_alert_sink FROM spc_ooc_alerts
WITH (connector = 'kafka', topic = 'quality.alerts.spc', properties.bootstrap.server = 'kafka:9092')
FORMAT PLAIN ENCODE JSON;

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

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

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

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

CREATE SINK yield_metrics_sink FROM hourly_first_pass_yield
WITH (connector = 'jdbc', jdbc.url = 'jdbc:postgresql://analytics-db:5432/manufacturing',
      table.name = 'hourly_first_pass_yield', type = 'upsert', primary_key = 'window_start,line_id,plant_id,characteristic');

Grafana and the MES query the KPI views directly over PostgreSQL, so the dashboard an operator sees at the station and the number a plant manager sees in the morning come from the same view.

Shift-End Batch vs Streaming SQL on the Plant Floor

MetricBatch practiceStreaming SQL (RisingWave)
Control charts and CpkEnd of shift, static limitsPer measurement, limits from the current window
Defect root causeReconstructed from logsJoined to tool and lot changes in the preceding hour
OEE and scrapShift-end reportHourly OEE, 15-minute scrap rate, takt breach in 5 minutes
Inventory positionNightly ERP reconciliationRunning sum per SKU and location, reorder alert on the event
Supplier scorecardQuarterlyRolling 90-day, PPM breach within the 30-day window, lot rejection immediately
Energy intensityMonthly invoiceHourly kWh per unit per machine, idle waste in 10 minutes
Changing a thresholdRedeploy a reportUpdate a parameter table or recreate a view

Frequently Asked Questions

How do I implement the full set of Western Electric rules? Rule 1 is the control limit check above. Rules 2 through 4 look at sequences of points, which are LAG window functions over quality_measurements partitioned by station and characteristic, compared against the limits of the current spc_control_chart window.

Can control limits be frozen from a baseline study instead of rolling? Yes. Store the baseline mean and sigma per characteristic in a table and join measurements to it instead of to spc_control_chart. Many plants run both: frozen limits for compliance and rolling limits for drift detection.

How does RisingWave handle a machine that reports every 30 seconds versus one that reports per cycle? Windows are defined by event time, so sources with different rates aggregate correctly. The kwh_consumed estimate assumes a fixed 30-second meter interval; for variable intervals, integrate power over the actual gap between readings using LAG.

Where should the ERP write reorder decisions back? Publish purchase orders to a Kafka topic and ingest it as a source. A view of open orders joined to inventory_position gives projected availability including inbound stock, which prevents the reorder view from firing twice for the same shortfall.

Can one cluster serve several plants? Yes. Every view carries plant_id or line_id, so one set of views serves all plants and dashboards filter by plant. Partition Kafka topics by plant so each plant's events stay local to a set of workers.

The engine for real-time streaming data.
Analyze, search, and decide.
GitHubXLinkedInSlackYouTube
Sign up for our to stay updated.