Pretty much all databases are already a transaction log that gets materialized. Postgres and many other RDBMS take in changes, write to the WAL, then provide tables that are views of the latest data of each row.
If you want total history and the database doesn't support this automatically then you can easily insert news rows instead (like you described) and then just create an SQL view that then shows the latest versions of each row, while also adding views for all kinds of other data access patterns. Companies have been doing this for decades because it's self-contained, fast, and reliable.
Using Kafka and separate processes to do this is deconstructing the RDBMS into separate layers that you now have to manage yourself. Useful if you really have that kind of scale but at 100GB of data, it's just silly. Use kafka as a work queue but leave the database work to actual database software.
If you want total history and the database doesn't support this automatically then you can easily insert news rows instead (like you described) and then just create an SQL view that then shows the latest versions of each row, while also adding views for all kinds of other data access patterns. Companies have been doing this for decades because it's self-contained, fast, and reliable.
Using Kafka and separate processes to do this is deconstructing the RDBMS into separate layers that you now have to manage yourself. Useful if you really have that kind of scale but at 100GB of data, it's just silly. Use kafka as a work queue but leave the database work to actual database software.