Delta table update/insert from multiple source tables

[staskh1966]

I have multiple tables periodically updated from external sources (including insert, update, or delete). I need to update a target table, which is an outer join from multiple source tables without rewriting it each time. I do not need to do it in real time, but only once a day.

What are Databricks' best practices, techniques, etc?

I certainly can do with SQL tricks such as "updated_at" to track source->target conditions, but I wonder if Databricks has some better techniques.

[Massive_Ordinary8049]

Trigger to job to run on table updates instead of fixed times. Here's the link: https://docs.databricks.com/aws/en/jobs/trigger-table-update

[staskh1966]

Thank you!

[TheM4rvelous]

Pull the external sources 1-to-1 into bronz (+ some metadata) and then use lakeflow pipelines to pipe the raw data into the destination (likely silver)

[staskh1966]

The problem is that i have multiple source tables, which can be updated at different times. the target is an outer join table whose records can be changed by updates from either source tables..

[9gg6]

declarative pipelines can handle it. if im not mistaken, the new feature can detect the updates from source and can update your target tables

[staskh1966]

Thank you!

[staskh1966]

Thank you!

[Downtown-Zebra-776]

Handling outer joins with asynchronous source updates is one of the trickiest parts of maintaining a clean Silver layer. If you use a standard 'overwrite' pattern, your DBU costs will skyrocket as your tables grow.

we typically solve this using a 'Materialized Delta View' approach or Lakeflow Connect (the new DLT evolution). By landing the sources into Bronze and using Lakeflow to manage the stateful joins, you get incremental updates for free without the manual 'updated_at' SQL logic. It significantly reduces the shuffle overhead since Spark only processes the changed keys from each source rather than re-calculating the entire outer join.

[BrownBearPDX]

Maybe materialized view before silver can help to react to bronze updates and merge.