Hi,
I am currently developing an incremental table within Dataform and require more granular control over the merge process than the default configuration offers. While I appreciate the ability to customize the ON clause, my use case necessitates further flexibility. Specifically, I am seeking to:
- Implement Custom Merge Conditions: I need to prevent data merging under specific circumstances, such as when all or a subset of columns between the source and target tables are identical. This deviates from the standard behavior of merging based solely on key equality.
- Control Column Updates: I want to selectively update only certain columns during the merge process. This is crucial for preserving the original creation timestamp of records, which should remain unchanged during subsequent updates.
To further illustrate my requirements, consider the following scenario. We have a table with the following columns: id, price, creation_ts, and updated_ts.
Desired Merge Behavior:
- Conditional Update: Records should only be updated if the price value has changed. In more complex scenarios, this condition could involve comparing a hash value computed from multiple columns.
- Selective Column Exclusion: When a record is updated, the creation_ts column should remain unchanged, preserving the original timestamp.
Sample-Code:
MERGE ${self()} AS target
USING (
SELECT *
FROM ${ref("new_data")}
) AS source
ON target.id = source.id
WHEN MATCHED AND target.price != source.price THEN
UPDATE SET price = source.price, updated_ts = CURRENT_TIMESTAMP()
WHEN NOT MATCHED THEN
INSERT ROW
I hope this example demonstrates the need for fine-grained control over both the merge conditions and the specific columns updated during the merge process.
Could you please provide guidance on whether Dataform currently supports these advanced merge customizations? If not, are there any alternative approaches or workarounds that could achieve similar results?
Thank you for your time and assistance.