Thank you for the response.

From a high level it makes sense. It's the details I'm a bit unclear on. 

So when a single pipeline runs (let's say today - 10/05/2024) for your architecture it might:

1. Extract records from source system tables that have changed since last time pipeline ran in Bronze layer. Records will either have an additional attribute added (e.g., extraction date) or be stored in a folder with the current date)
2. Extract only records from Bronze that have extract date = 10/05/2024 or are in the 10-05-2024 folder, cleans them up and upserts them to the silver layer (existing records overwritten with latest change and new records appended). So sliver layer is basically just a copy of the source system at this point, except it's cleaned up.

Assuming what I've said above is correct, I'm unsure how to go about loading into a Gold dimensional model.

Initial thought was to filter silver tables by extract/ingestion date of record, join relevant tables, and merge into Gold (taking into account start_date etc). However, let's say I need to join an Employees and Office table, but for 10-05-2024 only the Employees table was updated, then the join will fail as there will be no Office data to append to (due to first filtering the data by 10-05-2024). So an incremental approach doesn't really seem possible. 

Is the only way to simply try merge all the Silver data into Gold?

I know these might seem like silly question, just want to make sure I'm building my pipeline correctly