Time Travel Queries: The Data Time Machine Fixing Bugs & Solving Disputes in Real-Time Systems

What Are Time Travel Queries? A Time Travel Query allows you to retrieve a table’s state at a specific point in time. Instead of only accessing the latest data, you can “rewind the clock” and inspect previous records. In RisingWave, time travel queries use the FOR SYSTEM_TIME AS OF clause: SELECT * FROM table_name FOR SYSTEM_TIME AS OF timestamp_expression; Where timestamp_expression can be: A Unix timestamp (e.g., 1721024455) A datetime string (e.g., '2025-03-12T14:30:00-08:00') A relative time expression (e.g., NOW() - INTERVAL '10' SECOND) Real-World Use Cases for Time Travel Queries Time travel queries are particularly valuable in event-driven analytics, debugging, and compliance monitoring. Let’s explore how they can be applied in real-world scenarios. 1. Debugging Trade Execution Errors in Capital Markets Scenario: A high-frequency trading system notices an unexpected trade execution. The team wants to investigate the state of the order book at the exact moment the trade occurred. Solution: Using a time travel query, they can replay the order book as it was at 2:30 PM: SELECT * FROM order_book FOR SYSTEM_TIME AS OF '2024-03-12T14:30:00-08:00'; This allows traders to verify if market conditions justified the trade execution. 2. Investigating Banking Transaction Discrepancies Scenario: A customer claims they had $5,000 in their account, but after a withdrawal, their balance seems incorrect. Solution: The bank can retrieve the account balance before the transaction: SELECT * FROM accounts FOR SYSTEM_TIME AS OF NOW() - INTERVAL '1 MINUTE' WHERE user_id = 123; This helps resolve disputes by providing an exact snapshot of the account state. 3. Analyzing E-Commerce Flash Sales Performance Scenario: An e-commerce site runs a flash sale. The marketing team wants to analyze how fast stock levels dropped. Solution: By querying inventory levels at different timestamps, they can track depletion trends: SELECT * FROM inventory FOR SYSTEM_TIME AS OF '2024-03-12T08:00:00' WHERE product_id = 'ABC123'; SELECT * FROM inventory FOR SYSTEM_TIME AS OF '2024-03-12T09:00:00' WHERE product_id = 'ABC123'; SELECT * FROM inventory FOR SYSTEM_TIME AS OF '2024-03-12T10:00:00' WHERE product_id = 'ABC123'; This allows the team to optimize pricing and restocking strategies for future sales. 4. Backtesting Trading Strategies with Historical Market Data Scenario: A hedge fund wants to simulate trades using past market conditions. Solution: Instead of relying on static CSVs, they can query historical market data in real time: SELECT * FROM stock_prices FOR SYSTEM_TIME AS OF '2024-02-01T09:30:00-05:00'; By running multiple queries at different points in time, they can test and refine trading models. Managing Time Travel Data Efficiently Since time travel queries store past table states, they consume additional storage. RisingWave implements automatic storage cleanup to manage resource usage. Key Cleanup Mechanisms: Meta Store Cleanup: Happens asynchronously in the background. Removes stale metadata from meta store. Object Store Cleanup: Happens asynchronously in the background. Removes stale objects from object storage.

Mar 27, 2025 - 08:41

Time Travel Queries: The Data Time Machine Fixing Bugs & Solving Disputes in Real-Time Systems

What Are Time Travel Queries?

A Time Travel Query allows you to retrieve a table’s state at a specific point in time. Instead of only accessing the latest data, you can “rewind the clock” and inspect previous records.

In RisingWave, time travel queries use the FOR SYSTEM_TIME AS OF clause:

SELECT * FROM table_name FOR SYSTEM_TIME AS OF timestamp_expression;

Where timestamp_expression can be:

A Unix timestamp (e.g., 1721024455)
A datetime string (e.g., '2025-03-12T14:30:00-08:00')
A relative time expression (e.g., NOW() - INTERVAL '10' SECOND)

Real-World Use Cases for Time Travel Queries

Time travel queries are particularly valuable in event-driven analytics, debugging, and compliance monitoring. Let’s explore how they can be applied in real-world scenarios.

1. Debugging Trade Execution Errors in Capital Markets

Scenario: A high-frequency trading system notices an unexpected trade execution. The team wants to investigate the state of the order book at the exact moment the trade occurred.

Solution: Using a time travel query, they can replay the order book as it was at 2:30 PM:

SELECT * FROM order_book FOR SYSTEM_TIME AS OF '2024-03-12T14:30:00-08:00';

This allows traders to verify if market conditions justified the trade execution.

2. Investigating Banking Transaction Discrepancies

Scenario: A customer claims they had $5,000 in their account, but after a withdrawal, their balance seems incorrect.

Solution: The bank can retrieve the account balance before the transaction:

SELECT * FROM accounts FOR SYSTEM_TIME AS OF NOW() - INTERVAL '1 MINUTE' WHERE user_id = 123;

This helps resolve disputes by providing an exact snapshot of the account state.

3. Analyzing E-Commerce Flash Sales Performance

Scenario: An e-commerce site runs a flash sale. The marketing team wants to analyze how fast stock levels dropped.

Solution: By querying inventory levels at different timestamps, they can track depletion trends:

SELECT * FROM inventory FOR SYSTEM_TIME AS OF '2024-03-12T08:00:00' WHERE product_id = 'ABC123';
SELECT * FROM inventory FOR SYSTEM_TIME AS OF '2024-03-12T09:00:00' WHERE product_id = 'ABC123';
SELECT * FROM inventory FOR SYSTEM_TIME AS OF '2024-03-12T10:00:00' WHERE product_id = 'ABC123';

This allows the team to optimize pricing and restocking strategies for future sales.

4. Backtesting Trading Strategies with Historical Market Data

Scenario: A hedge fund wants to simulate trades using past market conditions.

Solution: Instead of relying on static CSVs, they can query historical market data in real time:

SELECT * FROM stock_prices FOR SYSTEM_TIME AS OF '2024-02-01T09:30:00-05:00';

By running multiple queries at different points in time, they can test and refine trading models.

Managing Time Travel Data Efficiently

Since time travel queries store past table states, they consume additional storage. RisingWave implements automatic storage cleanup to manage resource usage.

Key Cleanup Mechanisms:

Meta Store Cleanup:
Happens asynchronously in the background.
Removes stale metadata from meta store.
Object Store Cleanup:

Happens asynchronously in the background.
Removes stale objects from object storage.