Users want to create dynamic pipelines for a greater reusability and ease of operations. This guide walks users through on 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 a fully dynamic MERGE queries there are different aspects that needs to be parameterized. As an 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
