How T‑TDSQL Enables Real‑Time Incremental Reconciliation and Temporal Flashback

Incremental Computation in T‑TDSQL

Based on T‑TDSQL’s full‑temporal data storage, incremental queries, extraction, and calculations can be performed easily. For a single‑table incremental extraction or computation, T‑TDSQL first uses a snapshot‑difference read to obtain the data set for the given snapshot range, then applies user‑defined rules with built‑in aggregate functions such as SUM, AVG, and GROUP BY to achieve incremental results. The same principle works for any historical time interval.

For multi‑table incremental computation, T‑TDSQL introduces a “snapshot‑difference join” that first obtains two snapshot‑difference sets R and S, joins them, and then applies aggregate functions to complete the calculation.

1. Reconciliation Business

Internet finance requires extremely high data accuracy; inconsistencies can cause serious financial risk, so reconciliation is essential. In Tencent’s billing system, the balance table ( user) and transaction log table ( water) are compared on an hourly or daily basis to detect mismatches.

Traditional reconciliation follows a fixed‑interval approach (minute/hour/day). It suffers from three main problems:

Low timeliness: Errors are discovered only after the fixed interval elapses.

Imprecise locating: When multiple transactions occur in a day, a daily reconciliation cannot pinpoint the exact erroneous transaction, requiring manual inspection of all transactions for the affected user.

Inflexibility: Cross‑day reconciliation needs data from multiple tables and may alter the workflow.

2. Reconciliation Optimization with Incremental Computation

Using the incremental computation model, T‑TDSQL can compare the balance and transaction tables at the granularity of individual transaction rows, instantly detecting and locating erroneous transactions.

The optimized process follows three steps:

Total‑level reconciliation: Read all account data blocks within the reconciliation window [s_start, s_stop] and compute total end‑balance minus total start‑balance versus total transaction change. Any imbalance triggers the next steps.

Precise reconciliation – join phase: Execute a snapshot‑difference join between the balance blocks and the corresponding transaction blocks. Example SQL:

SELECT *
FROM (
  User READVIEW START s_start TO s_stop AS A ORDER BY User_id, Init_trx_id DESC
  FULL OUTER JOIN User READVIEW START s_start TO s_stop AS B ORDER BY User_id, Init_trx_id DESC
  ON A.trx_id = B.init_trx_id
) FULL OUTER JOIN Water READVIEW START s_start TO s_stop AS C ORDER BY User_id, Trx_id DESC
ON C.trx_id = A.trx_id

Precise reconciliation – verification phase: For each result row, verify the equation After‑Balance - Before‑Balance = Change. Any violation indicates an erroneous transaction.

The following diagram illustrates the precise reconciliation result set:

Typical error categories include missing transaction logs, incorrect transaction amounts, missing balance updates, and spurious rows. These are summarized in a reference table (omitted here for brevity) and should trigger alerts during reconciliation.

4.2 Security – UNDO SEGMENT and Online Flashback

T‑TDSQL stores the original row values of any modified transaction in an “UNDO SEGMENT”. This enables full‑temporal data management and provides an online flashback capability that can retrieve the database state at any past point.

Three flashback operations are supported:

Flashback query – view the database as of a specific timestamp.

Flashback delete – restore a dropped table and its indexes.

Flashback archive – make a table revertible to any historical point.

4.3 Business Analysis – Data Replay and Multi‑Dimensional Analysis

T‑TDSQL’s dual‑temporal model (historical state + transitional state) records transaction timestamps, allowing reconstruction of data evolution and transaction activity at any moment. This enables data replay, where past workloads and data changes can be simulated for capacity planning or debugging.

The system also supports the “5W” analytical framework:

Why (reason): Goal of data mining.

What (object): Operations performed on data items (LineAge).

Where (location): Physical storage location of the data item.

When (time): Dual‑temporal attribute.

Who (person): Association of users with data items via UID.

Leveraging these dimensions, AI‑driven analytics can perform roll‑up, drill‑down, slice, dice, and pivot operations on multi‑dimensional datasets, enabling deep user profiling, workload prediction, and automated resource allocation.

4.3.1 Data Replay

By replaying historical transaction streams, T‑TDSQL can infer system load patterns and validate how data evolves over time, which is valuable for performance testing and capacity forecasting.

4.3.2 Data Analysis

Historical state data combined with dual‑temporal features allows analysts to conduct fine‑grained, time‑aware investigations of user behavior, transaction anomalies, and system performance trends.

References (selected):

Li et al., “Efficient Time‑interval Data Extraction in MVCC‑based RDBMS”, WWW Journal, 2018.

Jiang et al., “Temporal Database Research Progress”, Computer Engineering & Applications, 2005.

Dharavath & Kumar, “A Scalable Generic Transaction Model for Distributed NoSQL Databases”, JSS, 2015.

Tom Yong, “Introduction to Temporal Databases”, 2004.

Li, Feng, Fan, “The Art of Database Transaction Processing”, China Machine Press, 2017.

Lomet et al., “Transaction Time Support Inside a Database Engine”, ICDE, 2006.