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_tablefor a table, orpartitioned_table.j_partitioned_tablefor a partitioned table. - Wrap each result to use it in Python:
Tablefor a table, orPartitionedTablefor 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
SyncTableFilterwhen all tables are peers. Each table contributes equally to determining which rows to show. -
Use
LeaderTableFilterwhen 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:
- A static table.
- A refreshing table marked add-only or append-only.
- A time table that is not a blink table.
time_tablemarks it append-only. - The result of filtering a qualifying table with
where, as long as thewherefilters are not themselves refreshing. - The result of
update,update_view,view,select,drop_columns, orrename_columnson a qualifying table. A formula that reads another row, such asPrice_[ii+1], can drop the attribute.
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:
LeaderTableFilterchecks it when you callbuild, and throws anIllegalArgumentExceptionif the leader or a follower table fails it.SyncTableFilterdoesn't check it when you callbuild. 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
IllegalStateExceptionand 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.
- If an input table removes or shifts rows, the synchronized result tables fail with an
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_datahasSeqNum1, 2, and 3, butvolume_dataandbid_ask_dataonly go up toSeqNum2. The highest ID common to all three is 2, so only theSeqNum2 rows appear in the synchronized results. - For
GOOGL,price_dataandvolume_datahaveSeqNum1 and 2, butbid_ask_dataonly hasSeqNum1. The highest common ID is 1, so only theSeqNum1 rows appear. - If
bid_ask_datawere a refreshing table that later receivedSeqNum2 forGOOGL, the filter would replace theSeqNum1 rows with theSeqNum2 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
SeqNuminslow_feed. - Each time
slow_feedadds a row, both results drop the previousSeqNumrow and show the new one. - The newer rows in
fast_feedstay hidden untilslow_feedreaches theirSeqNum.
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,
ClientandSessionId, are the follower's names for the leader'sClientandSessioncolumns.
In this example:
- The
sync_logleader 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: (TradeId100,MessageId1) and (TradeId101,MessageId2).trade_logandmessage_logfully match both. However, only the most recent match appears in the synchronized results. That match isTradeId101 andMessageId2. - Even though
trade_loghasId102 andmessage_loghasMsgId3, 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 leader and every follower partitioned table have unique keys, so each key has one constituent table.
- All partitioned tables have the same number of partition key columns.
- Partition key columns have the same data types in every partitioned table.
- Partition key columns appear in the same order in every partitioned table, because the filter matches them by position.
- Every constituent table is add-only. The filter checks each constituent once the leader and every follower have a constituent for its partition key, including constituents that arrive after you call
build.
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:
- An execution context. Without it,
buildfails when an input table is refreshing. - The update graph's shared lock. Without it,
buildthrows anIllegalStateExceptionwhen an input table is refreshing.
To provide both:
- Capture the context with
get_exec_ctxon the console thread. - Open the context on the worker thread with a
withstatement. - Wrap the call in
auto_locking_ctx, and pass it the PythonTableobjects 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:
auto_locking_ctx: works on an external thread and inside a listener callback, because it skips the lock in a callback.shared_lock: works on an external thread only.
See Update graph locks and thread safety for more on when explicit locking is needed.
Related documentation
- Deephaven's table types
- Incremental update model
- Execution context
- Engine locking
- Filters
- Partitioned tables
- Create a time table
- Use jpy
- Listen to ticking tables
new_tablestring_collong_coldouble_coltime_tableupdateupdate_viewviewselectdrop_columnsrename_columnswhereTable.assertAddOnlyJavadocauto_lockingPydocauto_locking_ctxPydocget_exec_ctxPydocshared_lockPydocUpdateGraphLockJavadocSyncTableFilterJavadocLeaderTableFilterJavadocSyncTableFilter.BuilderJavadocLeaderTableFilter.TableBuilderJavadocLeaderTableFilter.PartitionedTableBuilderJavadocTablePydocPartitionedTablePydoc