Merge multiple tables

Archive
1

hello there!

I try to merge multiple tables into a single table in a single dbt model, let’s say I want table A as the base table, and table B will try to upsert into table A (e.g. update records in A if duplicated key found in B, insert records if no records in A but in B).

Following is a simple SQL statement:

MERGE INTO table_A AS t
USING table_B AS s
ON t.id = s.id
WHEN matched THEN
UPDATE SET t.flag = s.flag,
t.str = CONCAT(t.str, ‘||’, s.str)
WHEN NOT matched then
INSERT
VALUES
(
s.id,
s.flag,
s.str
)

The problem:

I try to use incremental materialization, but it seems to do a self-merge on its own table with __tmp_table been created when there are new records.

Other solutions I have tried:

  1. Follow with drew’s solution to amend the end result, it will update the records in table A from table B but it will miss out the new records from B.

  2. I also think about performing inner join both tables first for UPDATE and then union unique records for INSERT, but it will require 3 JOIN operations which is not efficient.

  3. Or I can UNION ALL first and then AGGREGATE all records.

There must be a simple solution for this, can someone please show me how to merge multiple tables in a single model? Many thanks