I'm not sure I fully understand the question, you are right to avoid loops as they are very inefficient in spark. Some techniques that might help you:

Self join using date_add

Join the table to itself on system identifiers, but the date-1, ie 

select T1.*, T2.log_date as log_date_yest, T2.serial as serial_yest
from yourTable T1
inner join yourTable T2
on T1.system = T2.system
and T1.log_date = date_add(T2.log_date, -1) 

date add docs: https://docs.databricks.com/en/sql/language-manual/functions/date_add.html

Merge into

If you wanted a table with only the latest values in it, you could use MERGE INTO. Say you wanted a table that only reflected the latest values, the code might look something like:

MERGE INTO yourTable USING newData
  ON yourTable.system = newData.system 
  AND yourTable.logDate = newData.logDate
  AND yourTable.name = newData.name
  WHEN NOT MATCHED THEN INSERT *
  WHEN MATCHED AND yourTable.version > newData.version THEN UPDATE SET * --you'd have to create logic to define what data is new  

Much better examples here:

https://docs.databricks.com/en/sql/language-manual/delta-merge-into.html

Language

These are in SQL, you could rewrite them in python it won't have much impact on the performance. 

Tuning

Hopefully, this will run much faster. As for right sizing your cluster you have two approaches:

1. learn spark internals. learn detailed VM infrastructure. follow this giant guide https://www.databricks.com/discover/pages/optimize-data-workloads-guide. Time estimate: 100+ hours
2. use serverless. Time estimate: 5 seconds. 

Hope this is useful! Let us know how you get on. Holly