> When should you use Kafka instead of storing rows in SQL with a timestamp so you can replay them/fetch them if needed?

When the use case warrants it and when downstream consumers need to be notified of a change in the data or its state. That is, in reactive architectures.

A data streaming platform (of which Kafka is one example) will push the data, whereas a database (relational or not) requires to be explicitly polled (queried)[1]. A poll for new data is inefficient, is computationally expensive (a waste of CPU cycles when the data has not changed) and is prone to create creating delays in the data processing (when the polling interval is too long). Data streaming platforms will deliver the changes in near realtime.

Also, Kafka, being a distributed append only log, keeps an entire temporal history of all data changes. That is, if an event or an object has undergone a series of state transitions, such as create -> update 1 -> update 2 -> update 3 -> delete, it will be fully available in a topic as five separate events. If a consumer is only interested in the latest state change, a series of events can be compacted into an equivalent of a database table (naturally, it is called a K-table in Kafka) and the consumer can use the K-table instead of having to process the entire event stream[2]. K-tables and event streams can be flipped over with no extra effort.

Kafka is, effectively, your databases's own transaction log sans the query engine.

> Why do you need a sharded Kafka cluster?

Kafka cluster are not sharded. At least not in the database sense, as it is a cluster. A consumer sees one living thing[3] – the cluster, and the cluster can be dynamically resized without the consumer(s) knowing or noticing it. The cluster will then rebalance itself.

> Where/why add Kafka to that stack?

Kafka is typically used to get the data out of data sources and deliver a temporal series of changes to interested consumers. If the data needs to queried ad-hoc or explicitly, Kafka is then fronted by one or many (usually, domain specific) event stores. An event store can be a database (a RDBMS or a document one), a document search engine (e.g. Elastic Search or a vector database) or anything else that has a query engine in it. It can be a cache as well when either the speed of retrieval or affinity to the data are important.

[1] It is possible to mimic the Kafka temporal history via the use of triggers that would inject a copy of the row being changed into an «audit» table before allowing a update to occur, but, with time and in high data volume environments, the table size will balloon and will drag the database down.

[2] Most databases now have change event streams that abstract the database transaction log away and deliver raw row changes as a temporal history of events, which is a functional equivalent of data/event streaming (using Kafka or similar) sans the scalability and the distributed processing – the onus to implement either or both is entirely on the consumer and that part is hard. Therefore, a database change event stream is oftentimes hooked directly into a Kafka cluster.

[3] Not entire true in most implementations, but it is rabbit hole for another time to go down into.