As data format is transaction not sure is needed, maybe better just use parquet file.

You can read data source of dataframes in chunks and use append mode.

If data is often updated I think you would need to consider structured streaming.