doing sequential operation in an incremental table

Help
,
1

The problem I’m having

I want to perform sequential operation on the final_target table from multiple snowflake tables.
This means in the end I want to have the table name ‘final_table’ created if not already created and I want to update this table in incremental basis. I want to perform steps in the following sequence.

  1. Final table ‘Final_table’ with columns objid, auf_ordernum, auf_ordertype, last_update_auf, create_date)
  2. Insert all the objid from the HC table if they not exist in the final_table. Only ObjId from HC which has been changed during last 3 days. We can calculate this from column glchangetime in HC table.
  3. Once we have id in the final table, I want to join the final_table with auf_table on objid column , only for the objId in auf table which has max(glchangetime) >= final_table.last_update_auf. To update column values auf_ordernum, auf_ordertype in the final table.
  4. in the final_table where final_table.last_update_auf is null, I want to update auf_ordernum, auf_ordertype by just simply joining with auf table without any date condition

This is is what i want to achieve, I have complete the insertion part which is to insert new records if not exists but I am stuck in the update operation which I want to perform after the insertion in the final_table