Merge Tables
The Write Data In-DB tool supports Merge Tables for workflows connected to Databricks Unity Catalog. This option merges a set of updates or deletions based on the incoming records (the Source) into a target Delta table (the Target).
It uses a MERGE INTO statement as described in the Databricks documentation.
To configure Merge Tables in the Write Data In-DB tool...
Create a Databricks Unity Catalog connection through the Manage In-DB Connections dialog (Options > Advanced Options > Manage In-DB Connections).
To create the Source data, choose 1 of these options:
Connect a Connect In-DB tool to the Databricks Unity Catalog connection and map it to the table that will be used as the Source data for Merge Tables.
Use a Data Stream In tool to bring data from an external data set into Databricks to be used as the Source data.
Connect a Write Data In-DB tool to the workflow and select Merge Tables as the Output Mode. Merge Tables is only available if the tool can detect a Databricks Unity Catalog connection.
Enter the Target Table Name. This is the table being modified. The table must be a Delta table.
Select Apply to save the Target Table name and to update the Merge Fields.
Choose the Merge Fields from the dropdown. At least one set of Merge Fields has to be selected.
Select the Match Action:
Delete: Delete all matching table rows in the Target Table.
Update: Update the matching Target table row with the Source data.
Auto Config by Name: Automatically map the fields from the Source data to the Target table. Extra fields in the Source data are ignored. Missing fields cause the workflow to fail.
Custom: Manually match the fields from the Source data to the Target table. All fields must be mapped, Target fields cannot be
[none]. Missing fields cause the workflow to fail.