I have a table in redshift and I'm trying to do some ETL on the data.
The structure of the table is from_account, to_account, amount, currency, timestamp
I'm using pyspark to run the process and I'm looking to calculate the running totals for each account after every transaction.
For eg. If the first transaction is from=1,to=2, amount=100,currency=usd, timestamp=09/09/22T12:22:00
I want to aggregate this into another table where there are two new entries.
account=1, balance=-100, currency=usd, timestamp=09/09/22T12:22:00
account=2, balance=100, currency=usd, timestamp=09/09/22T12:22:00
Is this possible to do with pyspark in an efficient manner? The next time it's run for one of these two accounts, I need to reference their old balance for the same currency and then add new entries with their new updated balances.
As another wrench to add I want to be able to know the last row processed so I am not going through the entire table again.
Any help or guidance would be greatly appreciated.