Building a Digital Twin with Streaming SQL

A digital twin backed by streaming SQL is a continuously updated virtual model of a physical asset—a motor, a building, a production line—whose state always reflects the latest sensor readings. RisingWave, a PostgreSQL-compatible streaming database, maintains this live model automatically through incremental materialized views.

Why Digital Twins Need Streaming SQL

Digital twins have been a manufacturing and infrastructure buzzword for years, but most implementations fall short on the "real-time" promise. The typical architecture stores raw telemetry in a data lake, runs batch jobs to update the twin model, and exposes a dashboard that lags by minutes or hours. This is a simulation snapshot, not a live digital twin.

A true digital twin must reflect the physical world with sub-second fidelity. When a bearing temperature rises 5°C, the twin model should update immediately. When a pump's flow rate deviates from its set point, the deviation should be visible in the virtual model before an operator notices in the physical plant.

Streaming SQL turns a static model into a living one. Every sensor event that flows through Kafka automatically updates the materialized view that represents the asset's state. Operators, engineers, and automated control systems all query the same live model.

How Streaming SQL Solves This

RisingWave's materialized views act as the persistent state store for the digital twin. Each view represents a layer of the twin:

• Raw telemetry view: the latest reading from each sensor on each asset
• Derived state view: computed properties (efficiency, wear index, thermal gradient)
• Health score view: a composite score derived from multiple sensors
• Simulation boundary view: flags when observed state diverges from expected state

All layers update incrementally as new events arrive. Downstream consumers—dashboards, control systems, simulation engines—always see a consistent, fresh snapshot.

Step-by-Step Tutorial

Step 1: Connect Your Data Source

CREATE SOURCE asset_telemetry (
    asset_id      VARCHAR,
    asset_type    VARCHAR,
    sensor_id     VARCHAR,
    parameter     VARCHAR,   -- 'temperature', 'vibration', 'flow_rate', 'power_kw'
    value         DOUBLE,
    set_point     DOUBLE,
    event_time    TIMESTAMPTZ
) WITH (
    connector = 'kafka',
    topic = 'asset-telemetry',
    properties.bootstrap.server = 'kafka:9092',
    scan.startup.mode = 'earliest'
) FORMAT PLAIN ENCODE JSON;

Also bring in the static asset registry from your operational database:

CREATE SOURCE asset_registry (
    asset_id       VARCHAR,
    asset_name     VARCHAR,
    asset_type     VARCHAR,
    location       VARCHAR,
    rated_power_kw DOUBLE,
    install_date   DATE
) WITH (
    connector = 'postgres-cdc',
    hostname = 'db.example.com',
    port = '5432',
    username = 'rw_reader',
    password = 'secret',
    database.name = 'operations',
    schema.name = 'public',
    table.name = 'asset_registry'
);

Step 2: Build the Real-Time View

The core of the digital twin is the live asset state view—the latest value of every parameter for every asset:

CREATE MATERIALIZED VIEW digital_twin_state AS
SELECT DISTINCT ON (asset_id, parameter)
    t.asset_id,
    r.asset_name,
    r.location,
    t.parameter,
    t.value,
    t.set_point,
    t.value - t.set_point        AS deviation,
    t.event_time                 AS last_updated
FROM asset_telemetry t
JOIN asset_registry r USING (asset_id)
ORDER BY asset_id, parameter, event_time DESC;

Step 3: Window-Based Aggregations

Compute a rolling efficiency metric. For a pump, efficiency is actual flow divided by rated flow at the current power draw:

CREATE MATERIALIZED VIEW asset_efficiency_5min AS
SELECT
    asset_id,
    window_start,
    window_end,
    AVG(CASE WHEN parameter = 'flow_rate'  THEN value END) AS avg_flow,
    AVG(CASE WHEN parameter = 'power_kw'   THEN value END) AS avg_power,
    AVG(CASE WHEN parameter = 'vibration'  THEN value END) AS avg_vibration,
    AVG(CASE WHEN parameter = 'temperature' THEN value END) AS avg_temp
FROM TUMBLE(asset_telemetry, event_time, INTERVAL '5 MINUTES')
GROUP BY asset_id, window_start, window_end;

Combine into a health score view:

CREATE MATERIALIZED VIEW asset_health_score AS
SELECT
    e.asset_id,
    r.asset_name,
    r.location,
    e.window_end AS scored_at,
    GREATEST(0, LEAST(100,
        100
        - CASE WHEN e.avg_temp      > 80  THEN 20 ELSE 0 END
        - CASE WHEN e.avg_vibration > 5.0 THEN 30 ELSE 0 END
        - CASE WHEN e.avg_power     > r.rated_power_kw * 1.1 THEN 15 ELSE 0 END
    )) AS health_score
FROM asset_efficiency_5min e
JOIN asset_registry r USING (asset_id);

Step 4: Alerts and Sinks

Detect when the twin diverges significantly from expected state and sink alerts to a notification topic:

CREATE MATERIALIZED VIEW twin_divergence_alerts AS
SELECT
    asset_id,
    parameter,
    value,
    set_point,
    ABS(deviation) / NULLIF(set_point, 0) * 100 AS pct_deviation,
    last_updated
FROM digital_twin_state
WHERE ABS(deviation) / NULLIF(set_point, 0) > 0.15;  -- > 15% off set point

CREATE SINK twin_alerts_sink
FROM twin_divergence_alerts
WITH (
    connector = 'kafka',
    topic = 'twin-alerts',
    properties.bootstrap.server = 'kafka:9092'
) FORMAT PLAIN ENCODE JSON;

Comparison Table

FAQ

Can the digital twin model include physics-based calculations?

Yes. RisingWave supports arbitrary SQL expressions including mathematical formulas. You can compute derived properties like thermal resistance, mechanical efficiency, or power factor directly in materialized view SELECT clauses.

How do I version the twin model as asset configurations change?

Store configuration changes in a CDC-connected table. Use temporal joins in your materialized views to apply the correct configuration snapshot for each event's timestamp. This ensures historical analysis uses the right parameters.

Can multiple systems consume the digital twin state simultaneously?

Yes. Because the twin state lives in materialized views queryable via PostgreSQL protocol, any number of clients—dashboards, SCADA systems, simulation engines, REST APIs—can read it concurrently without affecting the streaming pipeline.

Key Takeaways

• RisingWave maintains a continuously updated digital twin state by joining live telemetry streams with static asset registry data from a CDC source.
• Health scores and efficiency metrics are computed in 5-minute tumble windows and are always fresh.
• Divergence alerts fire when observed state deviates more than 15% from set points, enabling proactive intervention.
• The PostgreSQL-compatible interface means any existing tool can query the live twin without custom integration.

Get started with RisingWave | Join the community on Slack