Supply chain disruptions cost businesses an average of $184 million per year, according to Interos research. The root cause is rarely the disruption itself. It is the lag between when a problem occurs and when someone notices it. A shipment sits delayed at a port for 36 hours before anyone checks the tracking spreadsheet. Warehouse stock drops below reorder thresholds over the weekend, and Monday morning starts with a scramble.
Traditional supply chain monitoring relies on batch processing: nightly ETL jobs that aggregate yesterday's data into dashboards that show what already went wrong. By the time a delay shows up in your BI tool, the customer is already frustrated. Real-time supply chain monitoring with streaming SQL changes this entirely by processing events as they happen and maintaining always-fresh views of your entire logistics operation.
In this article, you will build a complete real-time supply chain monitoring system using RisingWave, a streaming database that lets you write standard SQL to process streaming data. You will create materialized views that track shipments in transit, detect delays as they happen, flag low warehouse inventory, and score suppliers on delivery performance. All in SQL you already know.
Why Batch Processing Fails for Supply Chain Visibility
Supply chains generate a continuous flow of events: GPS pings from trucks, warehouse scans, purchase order updates, quality inspection results. Each event carries time-sensitive information. A batch pipeline that runs every hour (or worse, every night) creates blind spots where problems fester undetected.
Consider a typical scenario. A shipment from your primary sensor component supplier gets flagged as "delayed" by the carrier at 2:00 PM. Your batch ETL runs at midnight. Your supply chain team sees the delay at 9:00 AM the next day, 19 hours after it happened. During those 19 hours, you could have rerouted from a secondary supplier, adjusted production schedules, or proactively notified downstream customers.
The core problem with batch architectures for supply chain monitoring:
- Stale data: Dashboards show what happened hours or days ago, not what is happening now.
- Missed SLAs: Delay detection happens after the SLA has already been breached.
- Reactive firefighting: Teams spend their time investigating problems rather than preventing them.
- No correlation across signals: Batch jobs process shipments, inventory, and supplier data in separate pipelines, making it hard to see that a delayed shipment from Supplier A will trigger a stockout at Warehouse East.
Stream processing solves this by treating every supply chain event as it arrives, maintaining continuously updated views of the entire operation.
Modeling Supply Chain Data as Streams
Before building materialized views, you need to model the key data streams in a supply chain. In a production system, these would typically come from Kafka topics, each fed by different source systems (TMS, WMS, ERP). RisingWave supports direct ingestion from Kafka, Pulsar, Kinesis, and other streaming sources.
For this tutorial, we use standard tables to focus on the SQL logic. In production, you would replace these with CREATE SOURCE or CREATE TABLE ... WITH (connector = 'kafka', ...) statements.
Shipment tracking events
The shipments table captures every shipment moving through your logistics network:
CREATE TABLE shipments (
shipment_id VARCHAR PRIMARY KEY,
order_id VARCHAR,
supplier_id VARCHAR,
origin_warehouse VARCHAR,
destination VARCHAR,
status VARCHAR,
shipped_at TIMESTAMPTZ,
estimated_arrival TIMESTAMPTZ,
actual_arrival TIMESTAMPTZ,
carrier VARCHAR,
weight_kg DOUBLE PRECISION
);
Each row represents the latest state of a shipment. The status field cycles through values like shipped, in_transit, delayed, and delivered. In a streaming setup, upstream systems push status updates as new events.
Warehouse inventory levels
The warehouse_inventory table tracks stock levels across your distribution network:
CREATE TABLE warehouse_inventory (
warehouse_id VARCHAR,
product_id VARCHAR,
product_name VARCHAR,
quantity INT,
reorder_threshold INT,
last_updated TIMESTAMPTZ,
PRIMARY KEY (warehouse_id, product_id)
);
Each warehouse-product pair has a current quantity and a reorder_threshold. When quantity drops below the threshold, you need to trigger a replenishment order.
Supplier delivery records
The supplier_deliveries table logs every delivery from your suppliers, capturing both timeliness and quality:
CREATE TABLE supplier_deliveries (
delivery_id VARCHAR PRIMARY KEY,
supplier_id VARCHAR,
supplier_name VARCHAR,
promised_date TIMESTAMPTZ,
actual_date TIMESTAMPTZ,
quantity_ordered INT,
quantity_delivered INT,
quality_score DOUBLE PRECISION
);
This table provides the raw data needed to build supplier scorecards: on-time delivery rates, fill rates, and quality metrics.
Building Real-Time Shipment Tracking with Materialized Views
A materialized view in RisingWave is a precomputed query result that is incrementally maintained as new data arrives. Unlike traditional database materialized views that you refresh on a schedule, RisingWave materialized views update automatically within seconds of the underlying data changing. This makes them ideal for supply chain monitoring where freshness matters.
Learn more about how materialized views work in RisingWave.
Live shipment status dashboard
The first materialized view gives your logistics team a real-time view of every shipment, enriched with transit duration and delivery performance classification:
CREATE MATERIALIZED VIEW mv_shipment_status AS
SELECT
shipment_id,
order_id,
supplier_id,
origin_warehouse,
destination,
status,
carrier,
shipped_at,
estimated_arrival,
actual_arrival,
CASE
WHEN actual_arrival IS NOT NULL
THEN EXTRACT(EPOCH FROM (actual_arrival - shipped_at)) / 3600.0
ELSE NULL
END AS transit_hours,
CASE
WHEN status = 'delivered' AND actual_arrival <= estimated_arrival THEN 'on_time'
WHEN status = 'delivered' AND actual_arrival > estimated_arrival THEN 'late'
WHEN status = 'delayed' THEN 'delayed'
ELSE 'in_progress'
END AS delivery_performance
FROM shipments;
Query the view to see the current state of all shipments:
SELECT shipment_id, destination, status, carrier,
ROUND(transit_hours::numeric, 1) AS transit_hours,
delivery_performance
FROM mv_shipment_status
ORDER BY shipment_id;
shipment_id | destination | status | carrier | transit_hours | delivery_performance
-------------+-------------+------------+-------------+---------------+----------------------
SH-1001 | Chicago, IL | in_transit | FastFreight | | in_progress
SH-1002 | Denver, CO | delivered | QuickShip | 53.5 | on_time
SH-1003 | Miami, FL | in_transit | FastFreight | | in_progress
SH-1004 | Seattle, WA | delayed | OceanLine | | delayed
SH-1005 | Austin, TX | delivered | QuickShip | 50.8 | on_time
SH-1006 | Boston, MA | shipped | FastFreight | | in_progress
This view updates automatically. When a carrier system pushes a status change (say, SH-1001 changes from in_transit to delivered with an actual_arrival timestamp), the materialized view recalculates transit_hours and delivery_performance within seconds.
Automatic delay detection
The next materialized view focuses specifically on problematic shipments, filtering for delays and late deliveries:
CREATE MATERIALIZED VIEW mv_shipment_delays AS
SELECT
shipment_id,
order_id,
supplier_id,
carrier,
destination,
estimated_arrival,
status,
CASE
WHEN status = 'delayed' THEN 'ACTION REQUIRED'
WHEN status = 'delivered' AND actual_arrival > estimated_arrival
THEN 'DELIVERED LATE'
ELSE 'ON TRACK'
END AS alert_level,
CASE
WHEN status = 'delivered' AND actual_arrival > estimated_arrival
THEN ROUND((EXTRACT(EPOCH FROM (actual_arrival - estimated_arrival))
/ 3600.0)::numeric, 1)
ELSE NULL
END AS hours_late
FROM shipments
WHERE status = 'delayed'
OR (status = 'delivered' AND actual_arrival > estimated_arrival);
SELECT shipment_id, carrier, destination, status,
alert_level, hours_late
FROM mv_shipment_delays
ORDER BY shipment_id;
shipment_id | carrier | destination | status | alert_level | hours_late
-------------+-----------+-------------+---------+-----------------+------------
SH-1004 | OceanLine | Seattle, WA | delayed | ACTION REQUIRED |
This view acts as a live alert feed. In a production system, you could connect a RisingWave sink to Kafka or a webhook that triggers PagerDuty alerts or Slack notifications whenever a new row appears in this view.
Monitoring Warehouse Inventory in Real Time
Stockouts are among the most expensive supply chain failures. A product sitting at zero inventory means lost sales, expedited shipping costs for emergency orders, and damaged customer relationships. Real-time inventory monitoring catches low-stock situations before they become stockouts.
Low stock and critical alerts
This materialized view continuously evaluates inventory levels against reorder thresholds and classifies each product's stock status:
CREATE MATERIALIZED VIEW mv_warehouse_alerts AS
SELECT
warehouse_id,
product_id,
product_name,
quantity,
reorder_threshold,
CASE
WHEN quantity <= reorder_threshold * 0.5 THEN 'CRITICAL'
WHEN quantity <= reorder_threshold THEN 'LOW'
ELSE 'OK'
END AS stock_status,
reorder_threshold - quantity AS units_below_threshold
FROM warehouse_inventory
WHERE quantity <= reorder_threshold;
SELECT warehouse_id, product_name, quantity,
reorder_threshold, stock_status, units_below_threshold
FROM mv_warehouse_alerts
ORDER BY stock_status, warehouse_id;
warehouse_id | product_name | quantity | reorder_threshold | stock_status | units_below_threshold
--------------+-----------------------+----------+-------------------+--------------+-----------------------
WH-West | Power Supply Unit | 8 | 30 | CRITICAL | 22
WH-West | Industrial Sensor Kit | 12 | 50 | CRITICAL | 38
WH-East | Industrial Sensor Kit | 45 | 50 | LOW | 5
Two products at Warehouse West are at critical levels, and one product at Warehouse East is running low. This view updates the moment any warehouse scan changes an inventory quantity. When a pick-and-pack operation decrements stock from 46 to 45, the view automatically recalculates and, if the product crosses the threshold, it appears in the results.
The units_below_threshold column tells procurement teams exactly how much to reorder, not just that they need to reorder.
Supplier Performance Scoring
Not all suppliers are equal. Some consistently deliver on time with full quantities and high quality. Others are chronically late or ship partial orders. Real-time supplier scoring lets procurement teams make data-driven decisions about which suppliers to prioritize, renegotiate with, or replace.
Composite supplier scorecard
This materialized view calculates a composite score for each supplier based on three weighted factors: quality (40%), fill rate (30%), and on-time delivery (30%):
CREATE MATERIALIZED VIEW mv_supplier_performance AS
SELECT
supplier_id,
supplier_name,
COUNT(*) AS total_deliveries,
ROUND(AVG(quality_score)::numeric, 2) AS avg_quality_score,
ROUND(
(SUM(quantity_delivered)::double precision
/ NULLIF(SUM(quantity_ordered), 0) * 100)::numeric,
1
) AS fill_rate_pct,
SUM(CASE WHEN actual_date <= promised_date THEN 1 ELSE 0 END)
AS on_time_count,
ROUND(
(SUM(CASE WHEN actual_date <= promised_date THEN 1 ELSE 0 END)::double precision
/ COUNT(*)::double precision * 100)::numeric,
1
) AS on_time_pct,
ROUND(
(AVG(quality_score) * 0.4
+ (SUM(quantity_delivered)::double precision
/ NULLIF(SUM(quantity_ordered), 0) * 10) * 0.3
+ (SUM(CASE WHEN actual_date <= promised_date THEN 1 ELSE 0 END)::double precision
/ COUNT(*)::double precision * 10) * 0.3
)::numeric,
2
) AS composite_score
FROM supplier_deliveries
GROUP BY supplier_id, supplier_name;
SELECT supplier_name, total_deliveries, avg_quality_score,
fill_rate_pct, on_time_pct, composite_score
FROM mv_supplier_performance
ORDER BY composite_score DESC;
supplier_name | total_deliveries | avg_quality_score | fill_rate_pct | on_time_pct | composite_score
------------------+------------------+-------------------+---------------+-------------+-----------------
Beta Electronics | 2 | 9.7 | 99.9 | 50.0 | 8.38
Acme Components | 2 | 9.15 | 98.8 | 50.0 | 8.12
Central Parts Co | 2 | 7.05 | 91.7 | 0.0 | 5.57
Beta Electronics leads with an 8.38 composite score, driven by near-perfect quality and fill rates. Central Parts Co lags at 5.57 due to zero on-time deliveries and lower quality. These scores update continuously as new delivery records flow in, giving procurement teams a live leaderboard rather than a quarterly review spreadsheet.
The composite score formula weighs quality highest (40%) because defective components create cascading costs: returns, rework, production halts. Fill rate and on-time delivery each get 30% weight, reflecting their importance for production planning.
Connecting the Signals: From Tables to Action
The real power of streaming SQL for supply chain monitoring comes from combining these views. Each materialized view answers a specific question, but together they provide full supply chain visibility.
Consider a realistic scenario:
- A delayed shipment (visible in
mv_shipment_delays) is carrying Industrial Sensor Kits from Supplier A. - Warehouse West already has a CRITICAL stock level for Industrial Sensor Kits (visible in
mv_warehouse_alerts). - Supplier A's on-time delivery rate is only 50% (visible in
mv_supplier_performance).
With all three views queryable in the same database, your supply chain application can join them to create actionable insights:
SELECT
d.shipment_id,
d.carrier,
d.alert_level,
a.warehouse_id,
a.product_name,
a.stock_status,
p.supplier_name,
p.on_time_pct,
p.composite_score
FROM mv_shipment_delays d
JOIN shipments s ON d.shipment_id = s.shipment_id
JOIN mv_warehouse_alerts a ON s.origin_warehouse = a.warehouse_id
JOIN mv_supplier_performance p ON d.supplier_id = p.supplier_id;
This query correlates three different signals into a single picture: this shipment is delayed, it is heading to (or from) a warehouse with critical stock levels, and the supplier behind it has a track record of late deliveries. This kind of cross-signal correlation is nearly impossible with siloed batch pipelines.
Production architecture
In a production deployment, the data flow typically looks like this:
graph LR
A[TMS / Carrier APIs] -->|Shipment events| K[Kafka]
B[WMS / Barcode Scans] -->|Inventory updates| K
C[ERP / PO System] -->|Supplier deliveries| K
K --> R[RisingWave]
R -->|mv_shipment_status| D[Dashboard / Grafana]
R -->|mv_shipment_delays| E[Alerting / PagerDuty]
R -->|mv_warehouse_alerts| F[Procurement System]
R -->|mv_supplier_performance| G[Supplier Portal]
R -->|Sink to PostgreSQL| H[Analytics / BI]
RisingWave sits at the center, ingesting events from Kafka and maintaining materialized views that power different downstream consumers. The sink connector can push results to PostgreSQL for long-term analytics, to Kafka for downstream microservices, or directly to BI tools via the PostgreSQL-compatible wire protocol.
Scaling This Approach
The SQL examples in this article work with a handful of rows, but production supply chains process millions of events daily. A few considerations for scaling:
- Partitioned Kafka topics: Partition shipment events by
carrierorregionto parallelize ingestion. RisingWave scales horizontally across compute nodes to handle increased throughput. - Retention and cleanup: Use temporal filters in your materialized views to focus on recent data. For example, filter shipments from the last 30 days rather than all historical data.
- Cascading materialized views: Build views on top of other views. For example, create a
mv_supply_chain_riskview that joinsmv_shipment_delays,mv_warehouse_alerts, andmv_supplier_performanceto produce a unified risk score. - Sinks for downstream systems: Use RisingWave sinks to push results to PostgreSQL, Apache Iceberg, or Kafka topics that trigger automated actions in your ERP or procurement system.
What is real-time supply chain monitoring?
Real-time supply chain monitoring is the practice of continuously tracking shipments, inventory levels, and supplier performance as events occur, rather than relying on periodic batch reports. A streaming database like RisingWave processes supply chain events (GPS updates, warehouse scans, delivery confirmations) as they arrive and maintains always-current materialized views. This gives logistics and procurement teams second-by-second visibility into their operations, enabling them to detect delays, prevent stockouts, and evaluate suppliers based on live data.
How does streaming SQL improve supply chain visibility compared to batch ETL?
Streaming SQL improves supply chain visibility by eliminating the lag between event occurrence and insight availability. Batch ETL pipelines typically run on hourly or daily schedules, meaning a shipment delay that happens at 2 PM might not appear in dashboards until the next morning. With streaming SQL, materialized views update within seconds of the underlying data changing. This means delay alerts fire immediately, inventory warnings trigger before stockouts, and supplier scores reflect the latest delivery. The operational benefit is a shift from reactive firefighting to proactive intervention.
Can I use RisingWave with my existing Kafka and warehouse management systems?
Yes. RisingWave integrates natively with Apache Kafka for ingestion, so your existing TMS, WMS, and ERP systems can continue publishing events to Kafka topics. RisingWave consumes these topics directly using CREATE SOURCE or CREATE TABLE with the Kafka connector. On the output side, RisingWave sinks can push processed results back to Kafka, PostgreSQL, Apache Iceberg, or other destinations. This means you can add real-time supply chain monitoring without replacing any existing infrastructure.
When should I use materialized views versus ad-hoc queries for supply chain analytics?
Use materialized views for metrics and alerts that need to be continuously fresh and instantly queryable, such as shipment delay detection, low-stock alerts, and supplier scorecards. Materialized views precompute results incrementally, so querying them returns instantly without re-scanning raw data. Use ad-hoc queries for one-off investigations or exploratory analysis, like diagnosing why a specific shipment was late or analyzing seasonal patterns in supplier performance. RisingWave supports both: materialized views for operational monitoring and standard SQL queries for deeper analysis.
Conclusion
Supply chain visibility should not depend on when your batch job last ran. The approach outlined here, using streaming SQL and materialized views, turns supply chain monitoring from a retrospective reporting exercise into a real-time operational capability.
Key takeaways:
- Model supply chain events as streams: Shipment updates, inventory scans, and supplier deliveries are naturally streaming data. Process them as they arrive.
- Use materialized views for continuous monitoring: Each view answers a specific operational question (shipment status, delay detection, stock alerts, supplier scoring) and stays fresh automatically.
- Combine signals for actionable insights: The real value comes from correlating across views. A delayed shipment matters more when the destination warehouse is already low on stock from a supplier with poor delivery history.
- Standard SQL, no new languages: Everything in this article is written in PostgreSQL-compatible SQL. If your team knows SQL, they can build and maintain these views.
- Incremental computation scales: Materialized views process only the changes, not the entire dataset, making them efficient even at millions of events per day.
All SQL in this article was tested on RisingWave 2.8.0.
Ready to build real-time supply chain visibility? Try RisingWave Cloud free, no credit card required. Sign up here.
Join our Slack community to ask questions and connect with other stream processing developers.
