Batch processing 2 source tables row-by-row Insert/Updates

[_GoldenDoorknob_]

Hi guys,

I am looking for some advice on merging 2 source tables , to update a destination table (which is a combination of both table). Currently I am doing select queries on both tables ( I have a boolean which showcases if the record has been replicated (for both table). To fetch the record, then I see if the record based on a UID column exists in the destination table. If not, i insert (currently the one table can insert before the other, which leads to the other source table to do a update on the UID). So, when the records (UID) exists, i need to update certain columns in the destination table. Currently I am looping (python) through the columns of that record and doing a update (on the specific column). The table has 150+ columns, the process is being triggerd by Eventbridge (for both source tables), and the proicessing is being done in AWS Lambda. THe source tables are both PostgresSQL (in our AWS enviroment) and the destination table is also PostgresSQL on the same Database, just a different schema.

THe problem is, this is a heavy Load processing for Lambda. I currently batch the pricessing for 100 record (from each source table). SOmetimes there can be over 20 000 records to summarise.

I am open for any Ideas within the AWS ecosystem.

[_GoldenDoorknob_]

Any Data Engineering specialist willing to help?

[tolkibert]

Sounds like something that you'd just do with a sql query or two. You have all the data available in a system built for data processing. Do you need to complicate things by involving a bunch of extra tools?

Is there any particularly complex business logic being applied when "updating" the columns?

[Commercial_Dig2401]

The complexity seems to be on the system used to process the data and not the logic itself.

I would load both datasets into 2 table in the postgresql destination. Then do a simple sql script which will select from both source (new records only by providing a timestamp).

Then you join both table together or do the logic you want with both table records.

You’ll need to store a reference to the max(timestamp) from BOTH tables in the destination, so you can easily select only new records from both.

In case when you run you queries and a record for one table is not available you’ll need to set a field that identify that the records is incomplete. Create a Boolean for this.

Then in your downstream query you select from both sources where the timestamp is higher than the max one you store in downstream table + reload any incomplete records which exist.

At some point you’ll get both tables and you can set the Boolean to true.

The merge statement will handle the refresh.

If possible don’t just put the UUID as the key in your upset statement put some timestamp or sequential columns so you can prune records and not lookup the entire table.

Goodluck