Synchronize multiple tables

In a real-time application, related tables often tick at different rates. This guide shows how to use SyncTableFilter and LeaderTableFilter to coordinate updates across those tables, so your queries see only rows that belong together.

Note

These filters have no Python API wrapper, so you use them through jpy:

  • Pass each input's underlying Java object to the builder: table.j_table for a table, or partitioned_table.j_partitioned_table for a partitioned table.
  • Wrap each result to use it in Python: Table for a table, or PartitionedTable for a partitioned table.

The synchronization problem

Deephaven does not provide cross-table or cross-partition transactions. A partition is one independently updating slice of a table's data, such as one Kafka partition or the data for one date.

To maximize throughput, Deephaven typically updates each source table independently. It updates each partition of a source independently too. As a result, a row created later in one partition may appear in your query before a row created earlier in a different partition.

This independence can cause consistency issues when you have multiple tables that contain correlated data. For example:

  • A trading system might have separate tables for orders, executions, and messages that all share a common transaction ID.
  • A data pipeline might split events across multiple tables, each tagged with a sequence number.
  • A multi-source system might need to wait until all sources report data for a given timestamp.

Both SyncTableFilter and LeaderTableFilter solve this problem by ensuring that only coordinated rows appear in the filtered results.

Both filters match rows on two kinds of columns. A key column groups related rows, such as a Symbol column or a client session ID column. An ID column holds a number that never decreases within each key, such as a sequence number or transaction ID. With SyncTableFilter, rows from different tables that have the same key and the same ID belong together. With LeaderTableFilter, the leader table has a separate ID column for each follower, so the IDs don't need to match across tables. Each leader row names the ID to take from each follower for its key.

Choose a filter

Choose the filter based on your table relationships:

  • Use SyncTableFilter when all tables are peers. Each table contributes equally to determining which rows to show.

  • Use LeaderTableFilter when one table should control synchronization. The leader table contains ID values that dictate which rows from follower tables to show. This is useful when one table acts as a coordination log or contains the authoritative sequence of events.

Requirements

Both SyncTableFilter and LeaderTableFilter require input tables that meet these conditions:

  • Add-only tables: Tables must not modify, shift, or remove rows.
  • Long ID columns: Every ID column must be of type long, including the leader table's ID columns.
  • Non-decreasing IDs: For each key, ID values must never go backward. Many rows can share one ID, but once the filter matches a later ID, it ignores rows that arrive with an earlier ID.
  • Atomic updates: All rows for a given ID of a given table must appear in the same update. If some of those rows arrive in a later update, the results show a partial set of rows for that ID until the late rows arrive.
  • Matching key columns: Each table supplies the same number of key columns, in the same order, with the same types. Column names can differ between tables.

Make your input tables add-only

A table meets the add-only requirement when it is static or has the add-only or append-only attribute. LeaderTableFilter checks this same condition. Qualifying tables include:

For either filter, mark a refreshing table that you know only adds rows by calling assertAddOnly through j_table. If the table later breaks that promise, the marked table fails instead of passing wrong rows to the filter:

The two filters enforce the add-only requirement differently:

  • LeaderTableFilter checks it when you call build, and throws an IllegalArgumentException if the leader or a follower table fails it.
  • SyncTableFilter doesn't check it when you call build. A table that breaks the requirement causes one of these problems:
    • If an input table removes or shifts rows, the synchronized result tables fail with an IllegalStateException and stop updating.
    • If an input table modifies rows, the filter processes them again as if they were new rows, which can leave stale rows in the results.

SyncTableFilter

SyncTableFilter synchronizes peer tables whose rows carry matching ID values for each key.

How it works

For each key, the filter finds the highest ID for which every input table has a matching row, and passes through only the rows at that ID. When the tables receive new data and reach a higher common ID, the filter removes the previous ID's rows and adds the new ID's rows.

Example

This example synchronizes three tables that share Symbol as a key and use SeqNum as the ID:

In this example:

  • For AAPL, price_data has SeqNum 1, 2, and 3, but volume_data and bid_ask_data only go up to SeqNum 2. The highest ID common to all three is 2, so only the SeqNum 2 rows appear in the synchronized results.
  • For GOOGL, price_data and volume_data have SeqNum 1 and 2, but bid_ask_data only has SeqNum 1. The highest common ID is 1, so only the SeqNum 1 rows appear.
  • If bid_ask_data were a refreshing table that later received SeqNum 2 for GOOGL, the filter would replace the SeqNum 1 rows with the SeqNum 2 rows.

Refreshing example

This example shows the filter advancing as rows arrive in refreshing tables. Two time tables add rows at different rates, and each new row gets the next SeqNum for the AAPL key:

fast_feed adds a row every second and slow_feed adds one every three seconds, so fast_feed is never behind slow_feed. As the tables tick:

  • Each synchronized table shows one row: the latest SeqNum in slow_feed.
  • Each time slow_feed adds a row, both results drop the previous SeqNum row and show the new one.
  • The newer rows in fast_feed stay hidden until slow_feed reaches their SeqNum.

API

Create a builder with the ID column name and key column names:

Add each table with a unique name:

When a table's ID or key column names differ from the builder's defaults, pass the ID column and every key column to addTable, including key columns whose names match the defaults. Unlike LeaderTableFilter, this builder doesn't fill in missing key columns:

Build and retrieve the synchronized tables:

LeaderTableFilter

LeaderTableFilter synchronizes multiple tables using a leader-follower pattern.

How it works

The leader table has one ID column for each follower table, so each leader row names one ID per follower.

A follower's ID is satisfied when either of these is true:

  • The follower table has a row with that ID for the key.
  • The ID is null. A null ID contributes no rows from that follower.

For each key, the filter:

  • Finds the most recent leader row whose follower IDs are all satisfied.
  • Shows that leader row and the matching rows from each follower table.
  • Replaces them when a later leader row for the key is fully satisfied.

Example

This example uses a synchronization log, sync_log, as the leader table. Its rows are keyed by Client and Session, and its TradeId and MessageId columns name the trade and message to show for each key.

The follower tables use different column names than the leader, so each addTable call names them:

  • The ID mapping, such as "TradeId=Id", pairs the leader's ID column with the follower's.
  • The key columns, Client and SessionId, are the follower's names for the leader's Client and Session columns.

In this example:

  • The sync_log leader table controls which trades and messages appear. The filter shows only the most recent fully matched leader row for each key.
  • For ClientA/S1, the leader has two rows: (TradeId 100, MessageId 1) and (TradeId 101, MessageId 2). trade_log and message_log fully match both. However, only the most recent match appears in the synchronized results. That match is TradeId 101 and MessageId 2.
  • Even though trade_log has Id 102 and message_log has MsgId 3, they don't appear because the leader hasn't referenced them yet.
  • For ClientB/S2, only trade 200 and message 5 appear.

API

Create a builder with the leader table and key columns:

Add each follower table with:

  • A unique name
  • The follower table
  • An ID column mapping in the form "leaderIdCol=followerIdCol", or a single column name when the leader and follower ID columns have the same name
  • The follower's key columns, which must match the leader's key column types

Omit the follower key columns to reuse the leader's key column names.

A leader table can have up to 32 follower tables. build throws an IllegalArgumentException if you add more.

Build and retrieve the synchronized tables:

Partitioned table variant

When the leader and follower tables are partitioned tables, use LeaderTableFilter.PartitionedTableBuilder instead of the table builder. It takes partitioned tables as input, matches their constituent tables by partition key, and returns partitioned tables of filtered constituents.

The key columns you pass to the builder are the columns inside each constituent table that group related rows, as with the table builder. The partition key columns come from the partitioned tables themselves, so you don't pass them to the builder.

Access it through jpy:

The partitioned builder also requires:

The results contain a constituent for a partition key only once the leader and every follower have a constituent for that key.

Thread safety

Console and script-session code already runs under the update graph's exclusive lock. The examples above call build directly, which is safe in the console even when the input tables are refreshing.

If you call one of these builders from an external worker or timer thread, the thread needs two things:

To provide both:

  1. Capture the context with get_exec_ctx on the console thread.
  2. Open the context on the worker thread with a with statement.
  3. Wrap the call in auto_locking_ctx, and pass it the Python Table objects you built the filter from.

auto_locking is on by default. While it's on, auto_locking_ctx takes the shared lock when one of the tables you pass is refreshing and the thread holds no update graph lock.

Pass the Table objects, not their j_table values. A j_table value has no is_refreshing attribute, so auto_locking_ctx treats it as static and takes no lock.

This example builds the synchronized tables from the refreshing example on a worker thread:

When the worker finishes, worker_fast and worker_slow show the same rows as synced_fast and synced_slow.

The script starts the worker thread and doesn't wait for it. Console code holds the exclusive lock until the script finishes. If the script waited for the worker, for example with join, the worker could never acquire the shared lock.

Don't take the lock inside a listener callback. A listener callback runs on a thread that processes updates, and UpdateGraphLock rejects lock attempts from those threads. build doesn't need the lock there.

Choose the locking call by where your code runs:

See Update graph locks and thread safety for more on when explicit locking is needed.