Say I have a stream of deposits, withdraws and cancelations. coming in jason over rabbit mq. millions of transactions per minute.
I want to mantain two tables, one for transsctions and one for table with each customers balance. e. g.
transactions
| date | customer | opetation | amount | canceled |
|1/1/01| alice | deposit | 10 | no |
|1/1/01| alice | deposit | 2 | yes |
|1/1/01| bob | deposit | 20 | no |
|2/1/01| alice | withdraw | 5 | no |
|2/1/01| bob | deposit | 2 | no |
|2/1/01| bob | withdraw | 5 | no |
balance
| customer | balance |
| alice | 5 |
| bob | 17 |
the transaction table is well over 500gb, excluding indexes.
however, I would like to be able to massively share these tables, to a point that relational databases starts getting costly to scale.
can consider I would be the only one writting into the table, but the only control over who is reading is whatever interface I make available to the clients (e.g. jdbc, api, topic, etc.).
logging the transactions is easy enough using something like kinesis firehose, though what would be the possibles or even recomended architectures for mantaining the stateful tables transactions and balance?
Merge using deltalake or snowflake? Ive been suggested Cassandra.
How do yoy keep the these tables updated based on events instead of doing batch cycles?