Users want to create dynamic pipelines for a greater reusability and ease of operations. This guide walks users through how to create a dynamic pipeline to use for MERGE use-cases. For context on dynamic pipelines, please refer to this article Dynamic pipelines.
Background
Data Fusion provides BQ Execute action that can be used to execute MERGE queries. To create fully dynamic MERGE queries, there are different aspects that need to be parameterized. For example:
Merge Query
Dataset Name
Source Table
Target Table
Join Keys
Fields
Data Fusion supports recursive macros that can be used to fully parameterize Merge queries which will require a nested macro.
Solution
Parameterize the BQ Execute action with a single parameter for the SQL query.
2. Construct the merge query using parameters:
MERGE ${dataset}.${target_table} A
USING ${dataset}.${source_table} H
ON A.${join_key} = H.${join_key}
WHEN NOT MATCHED THEN
INSERT (${fields})
VALUES (${fields})
3. Pass in the parameters required during runtime:
