Process-Time Temporal Joins란 무엇인가?

Source: Dev.to

Imagine you run an online store where product prices change frequently. A customer places an order, and five minutes later you update the price of an item they bought. When you review the order, how do you determine the correct price the customer paid? A standard database join would likely show you the newest price, which is incorrect for that past order.

This is a common challenge in real‑time data systems. You need to know what the data looked like at the exact moment when the data is processed. In RisingWave, you can solve this easily with process‑time temporal joins.

What Are Process‑Time Temporal Joins?

A process‑time temporal join is a special type of join that connects a data stream to a table based on the time the system processes each event. Instead of joining with the latest version of a record, it joins with the version of the record that was active at the moment the event from the stream is processed.

This is perfect for the e‑commerce scenario: we can join an orders stream with a product_prices table to find the price that was active at the exact moment each order was processed.

A Step‑By‑Step Demo: Tracking Prices for Orders

Let’s build a real‑time materialized view that correctly calculates the total cost of an order using the product price at the time of purchase.

Step 1: Set Up Your Tables

First, we need two data structures. One will be a table to store product prices (updates allowed). The other will be an append‑only stream of new orders.

-- 1. Tracks product prices with automatic processing time
CREATE TABLE product_prices (
    product_id INT PRIMARY KEY,   -- a primary key is needed
    price FLOAT
);

-- 2. Contains historical orders (append‑only source)
CREATE TABLE orders (
    order_id   INT,
    product_id INT,
    quantity   INT
) APPEND ONLY;

Step 2: Insert Sample Data

Add some products and their initial prices, then immediately update them to simulate a price change.

-- Initial prices (updated at 08:00)
INSERT INTO product_prices VALUES
(101, 110.0),
(102, 200.0);

Now add two customer orders that are processed after the price updates.

-- Orders processed at 08:30
INSERT INTO orders VALUES
(1, 101, 2),
(2, 102, 1);

Step 3: Create a Materialized View with a Temporal Join

The FOR SYSTEM_TIME AS OF PROCTIME() syntax tells RisingWave to join each order with the version of the product price that was valid at the moment the order was processed.

CREATE MATERIALIZED VIEW order_with_price AS
SELECT
    o.order_id,
    o.product_id,
    p.price AS price_at_purchase
FROM orders o
JOIN product_prices FOR SYSTEM_TIME AS OF PROCTIME() p
    ON o.product_id = p.product_id;

Step 4: Query the Results and See It in Action

SELECT * FROM order_with_price;

Result (illustrative):

order_id | product_id | price_at_purchase | price_update_time | order_process_time
--------+------------+-------------------+-------------------+-------------------
1       | 101        | 110               | 08:00:00          | 08:30:00
2       | 102        | 200               | 08:00:00          | 08:30:00

Now update the prices again.

-- Prices updated at 09:00
INSERT INTO product_prices VALUES
(101, 150.0),
(102, 250.0);

Querying the view again shows that the existing rows remain unchanged because the temporal join “locks in” the price from when each order was first processed.

SELECT * FROM order_with_price;
-- Output is the same as before

Add a new order after the latest price change.

-- Order processed at 09:30
INSERT INTO orders VALUES
(3, 102, 3);

SELECT * FROM order_with_price;

Final result:

order_id | product_id | price_at_purchase | price_update_time | order_process_time
--------+------------+-------------------+-------------------+-------------------
1       | 101        | 110               | 08:00:00          | 08:30:00
2       | 102        | 200               | 08:00:00          | 08:30:00
3       | 102        | 250               | 09:00:00          | 09:30:00

Optimizing Your Joins: Append‑Only vs. Non‑Append‑Only

RisingWave chooses the join implementation based on the properties of the left‑hand side stream:

• Append‑Only Temporal Join – Used in the demo. Efficient because the left side (orders) never updates or deletes rows, so the system does not need to maintain extensive state.
• Non‑Append‑Only Temporal Join – Supported when the left side can receive updates or deletions. This requires more resources because RisingWave must track changes and potentially retract earlier results.

For best performance, use an append‑only source or table on the left side of a temporal join whenever possible.

Conclusion

Process‑time temporal joins are a powerful feature in RisingWave for building applications that require point‑in‑time accuracy. By using the FOR SYSTEM_TIME AS OF PROCTIME() clause, you can ensure that each event is joined with the correct version of reference data, preserving historical correctness while still benefiting from real‑time processing.