ksqlDB foreign-key joins maintain relationships when either table changes

Changing a customer row can require updating many joined order rows, even though none of the orders changed. That is the maintenance work behind the foreign-key table joins in ksqlDB 0.19. Confluent Cloud's July 19 release entry makes the version available in the hosted service. The July 20 technical announcement explains the new many-to-one relationship.

The left table can join through a non-key column to the right table's primary key. The output retains the left table's primary key. Input updates on either side maintain the result, and an update on the right can affect several output rows. The announcement also removes the co-partitioning requirement for this join type, while limiting the left join operand to a column in this release.

I find this useful because a stream-backed table should express relationships without forcing every entity to use the same primary key. Kafka's partition key remains important, but it need not also become the identity of every related business object.

A foreign key is a relationship between two identities

Consider an order whose primary key identifies the order and whose customer field identifies another row. Several orders can reference one customer without sharing the order's own identity. A join on that customer reference needs to retain each order separately in its output.

This differs from joining two tables whose rows already share a primary key. The referenced customer key determines where the lookup happens, while the order key determines which output row is updated. An implementation needs both pieces of information. Discarding the order key during a lookup would lose the identity under which to return the result.

The existing Kafka Streams KTable API exposes this distinction through separate key types. Its foreign-key join takes a function that extracts the other table's key from a value. The returned table retains the original left key type. This is the stream-processing API context for the relational feature exposed in SQL.

A changing reference needs subscription maintenance

The Streams subscription sender shows how changes to the left table become relationship updates. It examines old and new values and extracts their foreign keys. If the serialized keys differ, it sends a deletion for the old relationship and a subscription for the new one.

This is necessary even when the left row's own primary key stays the same. An order reassigned from one customer to another must stop receiving updates from the old customer. Creating only the new relationship would leave stale subscription state. A later change to the previous customer could then be directed toward a row that no longer references it.

The implementation carries the left primary key in its subscription wrapper. That lets the receiving side identify the row that registered interest. It also includes a hash derived from the new left value. These are internal processing details, distinct from a SQL application having to manually create a subscription table.

When explicit serializers are absent, the sender obtains defaults from the processor context. Serialization participates in both comparing foreign keys and computing the left-value hash, so these steps depend on the configured representation.

Deletion has its own instruction. If a left row previously existed but its new value is absent, the sender forwards a delete-and-propagate instruction for the old foreign key. That accounts for both removing the maintained relationship and communicating the effect onward. The old reference is still needed to know which stored subscription must be removed.

A referenced update finds the dependent rows

The subscription processor for right-side changes uses a timestamped key-value store. It converts the changed right key into a prefix and scans the corresponding range of stored subscriptions. Each matching subscription identifies a left primary key that needs a response.

The code then forwards a response using that left key and the new right value. That explains the many-row effect of changing one referenced entity. The work is proportional to finding the dependent relationships represented in the store. The system does not obtain that effect merely by executing a single stateless lookup when an order arrives.

The range scan also checks the prefix of each returned key because the range endpoint is inclusive. That is a small implementation detail with a clear correctness role. A neighboring entry at the boundary must not receive a response intended for another foreign key.

These mechanisms make the physical cost of normalization visible. Separate tables avoid copying the referenced value into every source row, but maintaining a joined view still requires tracking which rows depend on it. Updating a popular referenced row can produce work for many dependants. The SQL syntax removes manual wiring while leaving that data relationship intact.

Missing references still need defined behavior

The sender distinguishes inner and left joins when creating a subscription for a new left row. A left join can request propagation even when no right value exists. An inner join requests propagation only when the referenced value is available. That difference is represented as an explicit instruction carried to the next stage.

The KTable API documents a null extracted foreign key as an invalid update that is ignored. The sender also records dropped records for null foreign keys. A missing reference therefore deserves attention in an input model, rather than being treated as interchangeable with every other unmatched relationship.

I regard the maintained subscription as the key idea in this feature. A table join has to react when either entity changes, including when a row changes which entity it references. ksqlDB 0.19 exposes that relationship through table SQL, while the underlying Streams code makes the required state and update propagation concrete.