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
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:
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.
I also think about performing inner join both tables first for
UPDATEand then union unique records for
INSERT, but it will require 3
JOINoperations which is not efficient.
Or I can
UNION ALLfirst and then
There must be a simple solution for this, can someone please show me how to merge multiple tables in a single model? Many thanks