You can use the IDENTITY or generated columns in mappings and mapping tasks.
When you write to a table with IDENTITY or generated columns, the values in the columns are automatically generated based on a user-specified function in the Databricks table.
You can use the IDENTITY column for insert, update, upsert, and delete operations. The query can be used for connected and unconnected ports for matching the columns based on the query in the CREATE TABLE statement.
The following table shows the behavior of the operations for each type of generated column:
Type of query
INSERT [INSERT INTO]
UPDATE [Merge]
UPSERT [Merge]
DELETE [Merge]
GENERATED ALWAYS AS
Port needs to be unconnected
Can be used for matching
Port needs to be unconnected and cannot be used for matching
Can be used for matching
GENERATED ALWAYS AS IDENTITY
Port needs to be unconnected
Can be used for matching
Port needs to be unconnected and cannot be used for matching
Can be used for matching
GENRATED BY DEFAULT AS IDENTITY
Port can be connected or unconnected
Can be used for matching
Port can be connected and can be used for matching
Can be used for matching
Note: You cannot define an IDENTITY column when you create a new target at runtime and the source table has generated columns.