Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 37 ยท Table API and SQL

Dynamic Tables and Changelogs

Learn how Flink turns streams into dynamic tables and back: append-only versus updating results, changelog row kinds, retractions and upserts, with runnable Java stand-ins.

SQL was designed for tables that sit still. A stream never sits still. Flink bridges the two with one idea: the dynamic table. A stream is treated as a table that changes over time, a query over that table is a query that keeps updating its result, and the result is itself a dynamic table that can be turned back into a stream. Once you understand this, the behaviour of every Flink SQL query, including why some queries need much state, becomes predictable.

This lesson explains the model and the changelog that connects tables and streams. Real Flink SQL is shown read-only; the Java programs are stand-ins that perform the same bookkeeping. It follows Flink SQL Queries.

Stream to table and back

Stream Events arrive over time.
Dynamic table Rows change as events arrive.
Changelog Inserts, updates, deletes.

A table is a stream that was folded; a stream is a table's history.

1 / 2

A stream is a changelog

Each event appended to a stream can be seen as an insert into a table. A Kafka topic with click events becomes a table clicks; every new record is a new row. A query on it is conceptually run again after each insert, and Flink maintains the result incrementally, doing only the work needed to account for the new row.

The two result shapes are:

  • Append-only results: the query only adds rows. A filter or projection (SELECT ... WHERE ...) is append-only: each input row yields at most one output row, and it never changes.
  • Updating results: new input can change rows that were already emitted. SELECT customer, COUNT(*) FROM orders GROUP BY customer is updating: the count for ana goes from 1 to 2 to 3.

Changelog row kinds

To ship an updating result into a stream (or a sink), Flink labels every row with a RowKind:

Kind Symbol Meaning
INSERT +I a new row
UPDATE_BEFORE -U the old version of a row that is being updated (a retraction)
UPDATE_AFTER +U the new version of the row
DELETE -D the row is gone

For the aggregation above, the changelog for the first four events (ana, ana, li, ana) looks like this:

TEXT
+I (ana, 1)
-U (ana, 1)
+U (ana, 2)
+I (li, 1)
-U (ana, 2)
+U (ana, 3)

A consumer that reads this changelog and applies each record to a table always ends up with the correct current result. That is the contract between a Flink query and a changelog-aware sink.

The program below produces this changelog for a running count and then replays it into a "materialized view" map to prove the contract.

Java Stand-in: a running count as a changelog, applied to a table

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Retract streams versus upsert streams

Sending both -U and +U is the most general form, a retract stream. When the table has a unique key, the -U is redundant: the new row simply overwrites the old row with the same key. This is an upsert stream. It is smaller and is what sinks such as upsert-kafka and JDBC need, which is why those sinks require a PRIMARY KEY. Deletes are still sent (-D, or a Kafka tombstone).

SQL
CREATE TABLE user_totals (
  user_id STRING,
  total   BIGINT,
  PRIMARY KEY (user_id) NOT ENFORCED
) WITH ('connector' = 'upsert-kafka', ...);

INSERT INTO user_totals
SELECT user_id, COUNT(*) FROM events GROUP BY user_id;

Which changelog mode a source produces or a sink accepts is part of the connector: Kafka with JSON is append-only, while CDC connectors (Debezium, MySQL CDC, Postgres CDC) produce full changelogs with all four kinds. That makes CDC a natural way to keep a Flink table in sync with a database table, and Flink can run SQL over it as if the table were ordinary.

Java Stand-in: upsert applies only the latest row per key, with a delete

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Why some queries are expensive

Incremental maintenance is the reason Flink SQL is efficient, and also the reason some queries need lots of state:

  • GROUP BY a key keeps one accumulator per distinct key. Bounded if the key space is bounded.
  • Regular joins (a JOIN b ON a.id = b.id) must remember all rows of both sides forever, because a new row on either side could match an old row on the other. State grows without bound unless you add a TTL or use a time-bounded join (SQL Windows, Joins and Top-N).
  • DISTINCT remembers every value seen.
  • Windowed aggregation is the best-behaved: when the watermark passes a window's end, its state is cleaned up and the result is final (append-only).

EXPLAIN shows the changelog mode of each operator (look for changelogMode=[I,UA,D] and similar). If a sink rejects your query with a message about unsupported changelog modes, you are producing updates for a sink that only accepts appends. Fix it by using an upsert sink with a primary key, by switching to a windowed aggregation, or by choosing a connector that handles updates.

Java Stand-in: a regular join must remember both sides

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Either side can arrive first and the join still finds the match, because both sides are stored. That memory is the cost of correctness.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which query is append-only?

    A filter and projection emit at most one final row per input row and never change it. Aggregations update earlier rows.
  2. 2. What are the RowKind symbols for an update?

    A retract-style update is sent as -U with the old row and +U with the new row.
  3. 3. Why do upsert sinks need a primary key?

    The key identifies which stored row to overwrite. Flink does not enforce uniqueness; it trusts the declaration.
  4. 4. Why does a regular streaming join need unbounded state?

    Without a time bound or TTL Flink cannot know when a row can no longer match.

Summary

  • A stream is a table that changes; a continuous query maintains its result incrementally as a dynamic table.
  • Results are append-only (filters, projections, finished windows) or updating (aggregations, regular joins, distinct).
  • A changelog uses row kinds +I, -U, +U, -D; upsert streams drop -U when a primary key exists.
  • Sinks must accept the changelog mode a query produces; CDC sources produce full changelogs.
  • Regular joins, distinct and group-by keep state, so bound it with windows, time-bounded joins or TTL.

Next: SQL Windows, Joins and Top-N