Skip to content

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.md
Base 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?

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_tokens
FROM accepted_usage
GROUP 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 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_usageCurrent result row for acme
Insert u-101, 1,000 tokens1,000
Insert u-102, 400 tokens1,400
Insert u-103, 600 tokens2,000
Insert u-104, 500 tokens2,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.

Updating input changes the same calculation Replacing u-103’s contribution of 600 with 650 changes acme’s total from 2,500 to 2,550, which the receiver applies as a replacement. CHANGED ROW NEW TOTAL Correct a request_usage row usage_id u-103: 600 → 650 Update the sum for acme 2,500 − 600 + 650 = 2,550 Replace the result row account_id acme: 2,500 → 2,550

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.

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 encodingChange from 2,000 to 2,500What the receiver needs
UpsertSend acme, 2,500A unique account key and replacement semantics
Retraction and additionRemove acme, 2,000; add acme, 2,500Support for removing old rows as well as adding new ones
Append onlyCannot express replacement by itselfA 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.

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.

  • 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.
  1. Apache Flink 2.3: Group Aggregation.

  2. Apache Flink 2.3: Dynamic Tables, Continuous Queries and Table to Stream Conversion.

  3. Apache Flink 2.3: Upsert Kafka SQL Connector.

  • 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