Streams, tables, and joins
A query that keeps updating
How Streaming SQL maintains a changing result table, how inserts, replacements, and retractions affect a sum, and which state and output choices remain behind the query.
Learn with an AI assistant
Copy the prompt and paste it into a chat with an AI assistant.
You are a tutor for the course "Stream Processing".Topic: "A query that keeps updating"In short: How Streaming SQL maintains a changing result table, how inserts, replacements, and retractions affect a sum, and which state and output choices remain behind the query.Topic text in Markdown: https://stream-processing-systems.github.io/tables/a-query-that-keeps-updating.mdBase your explanations on the topic text. First ask how I want to study: a short summary, a step-by-step walkthrough, or a deep dive with questions that check my understanding. Use the text's example and terms. If you add anything the text does not say, point it out.Our AI assistant’s usage processor keeps a token total for each account. It reads a usage record, recognizes repeats, finds the account’s total, adds the tokens, and sends a replacement total to the usage page. The team wants to express that calculation as a query and let a processing engine maintain the answer.
SQL, the language used to ask questions of database tables, can describe “add the tokens for each account.” A query run once answers using the rows available to that run. Usage keeps arriving, so what would it mean to keep the query running and its answer up to date?
Describe the result in SQL
Section titled “Describe the result in SQL”Call the input table accepted_usage. Each row describes a distinct counted
request, with usage_id, account_id, and an integer tokens value.
Repeated deliveries have already been removed. At the start of our
calculation, the table is empty. The first rows are the familiar u-101,
u-102, u-103, and u-104 for acme, contributing 1,000, 400, 600, and
500 tokens.
This query in Apache Flink 2.3 SQL describes the totals:1
SELECT account_id, SUM(tokens) AS total_tokensFROM accepted_usageGROUP BY account_id;GROUP BY account_id puts each account’s rows in its own group. SUM(tokens)
adds the tokens within that group. The result has one row per account,
containing the account ID and its sum, named total_tokens.
The query describes the calculation. The definitions of the input table and output destination connect it to Kafka and the usage page.
A continuous query maintains a table
Section titled “A continuous query maintains a table”A continuous query stays active and maintains its result when the input table changes. SQL used this way is often called Streaming SQL. In Flink’s model, the logical result corresponds to running the query over a snapshot of the input table at each stage of its evolution.2
For acme, the result evolves like this:
Change in accepted_usage | Current result row for acme |
|---|---|
Insert u-101, 1,000 tokens | 1,000 |
Insert u-102, 400 tokens | 1,400 |
Insert u-103, 600 tokens | 2,000 |
Insert u-104, 500 tokens | 2,500 |
The engine can use incremental computation: update the affected group from its previous state. It needs 1,400 plus the next 600 to get 2,000; it need not reread all the previous requests. SQL describes which answer to maintain, and the engine chooses the steps that maintain it.
The input table is a useful way to define that answer. It does not require the engine to store a separate physical copy of every input row. For this sum, compact per-account state is enough for the aggregation, while the upstream deduplication step needs its own state for recognized IDs.
The result never becomes one final lifetime total while more usage can arrive. If the report should show totals per month, month belongs in the grouping rule, for example through a time window. That changes the question and brings back the rules for when a monthly result is emitted, corrected, and closed.
Updating input changes the same calculation
Section titled “Updating input changes the same calculation”Suppose the source contract changes. A table request_usage now holds one
current row per usage_id. It supports corrections: request u-103 was
recorded as 600 tokens but should have been 650. The table replaces that row.
The same grouped sum over this table should become 2,550.
Appending a new independent row of 650 would instead make the total 3,150. The engine must be told that the input changes an existing row. One encoding removes the old row and adds the new one; the removal is called a retraction.2 For this sum, the correction subtracts 600 and adds 650. Deleting a request’s row removes its contribution entirely. Our report would interpret that as withdrawing the counted usage, so such a delete must be an intentional part of the input contract.
This is the distinction between contributions and replacements
at the query boundary. The grouping and sum stay the same; for the correcting
input table, use FROM request_usage. The input-table definition determines
which facts the query represents. A JSON record
with the same usage_id as an earlier one does not make an ordinary
append-only source update a row automatically.
Deliver changes the receiver understands
Section titled “Deliver changes the receiver understands”The aggregation’s output is a changing table too. acme, 2,500 replaces
acme, 2,000. The usage page needs to apply that change instead of adding
both numbers.
| Output encoding | Change from 2,000 to 2,500 | What the receiver needs |
|---|---|---|
| Upsert | Send acme, 2,500 | A unique account key and replacement semantics |
| Retraction and addition | Remove acme, 2,000; add acme, 2,500 | Support for removing old rows as well as adding new ones |
| Append only | Cannot express replacement by itself | A different result contract, such as final rows for closed windows |
Flink’s upsert Kafka connector, for example, writes result changes under a primary key and uses a null value for a delete. It also keeps the order of changes for each key by writing them to the same Kafka partition.3 A receiver reading that topic must interpret those keys and deletes correctly.
The Flink 2.3 documentation describes this contract but currently lists no compatible connector release.3
The intermediate totals in our table describe the logical calculation. They do not promise one visible network message for every input. The engine or connector can buffer and combine changes; readers may see 1,000 followed directly by 2,500. Output timing, recovery, and external effects still depend on the configured path.
State remains behind the query
Section titled “State remains behind the query”The grouped sum retains a total for every account represented in the input.
If new accounts keep appearing, the number of groups keeps growing. Flink
warns that a time-to-live setting can limit state but affect result accuracy.1
If the engine forgets acme’s 2,500 after a quiet week, its next 500 tokens
cannot produce the correct lifetime total of 3,000 from an empty state.
Other queries can need much more state. The join of starts and completions retains events while waiting for matches. The join with a historical price retains plan versions and waits for both inputs’ progress. Expressing either in SQL preserves those requirements. A short query can also cause many output changes, as when one current-price update revalues all usage for an account.
The engine must route related keys together, save a consistent checkpoint, and resume from the corresponding input positions. It also needs an output that can safely receive updated results. SQL makes the calculation concise; it leaves these choices to the running system.
Several technologies can maintain the same per-account total. We can now compare them against one concrete calculation: what reads its input, what keeps its state, and how a changed total reaches the usage page.
What to remember
Section titled “What to remember”- A continuous query maintains a changing result table from changing input tables. The engine can update only the affected state.
- An inserted contribution, a corrected row, and a replacement total have different meanings. Table definitions and changelog encodings carry them.
- The receiver must support the changes a query produces. Logical updates do not determine when each one becomes visible.
- Streaming SQL still needs state, input identity, time rules, recovery, and a suitable output contract.
References
Section titled “References”Further reading
Section titled “Further reading”- Tyler Akidau, Slava Chernyak, and Reuven Lax, Streaming Systems (O’Reilly Media, 2018), chap. 8, “Streaming SQL.” The section on time-varying relations explains how familiar queries apply to tables that evolve over time. Its proposed SQL extensions illustrate the model; the query above uses Flink’s actual syntax.
© 2026 Grig Alex · CC BY-NC-SA 4.0
