topic PKEY Upserting Pattern With Older Runtimes in Data Engineering

PKEY Upserting Pattern With Older Runtimes

ChristianRRL — Sat, 16 Nov 2024 02:06:49 GMT

Hi there,

I'm aware that nowadays newer runtimes of Databricks support some great features, including primary and foreign key constraints. I'm wondering, if we have clusters that are running older runtime versions, are there Upserting patterns that have been used before and may be recommended?

For additional context, our clusters are mainly in Runtime 13.3 and we do have one running 14.1.

Re: PKEY Upserting Pattern With Older Runtimes

Walter_C — Tue, 19 Nov 2024 16:12:07 GMT

For clusters running older Databricks runtime versions, such as 13.3, you can still implement upserting patterns effectively, even though they may not support the latest features like primary and foreign key constraints available in newer runtimes.

One common upserting pattern involves using the MERGE INTO statement, which allows you to merge a source dataset into a target dataset based on a specified condition. This pattern is useful for handling both inserts and updates in a single operation. Here is a basic example of how you can use the MERGE INTO statement:

MERGE INTO target_table AS target USING source_table AS source ON target.id = source.id WHEN MATCHED THEN UPDATE SET target.column1 = source.column1, target.column2 = source.column2 WHEN NOT MATCHED THEN INSERT (id, column1, column2) VALUES (source.id, source.column1, source.column2)

This approach ensures that if a record with the same id exists in the target table, it will be updated with the values from the source table. If the record does not exist, it will be inserted.

Re: PKEY Upserting Pattern With Older Runtimes

szymon_dybczak — Tue, 19 Nov 2024 16:46:58 GMT

As @Walter_C mentioned, merge is a a proper way to perform upsert. I just want to add that in Databricks primary and foreign constraints are informational only constraints and thus are not enforced. So, @ChristianRRL be aware of that fact, because I see a lot of folks from RDBMS world that ran into issues, because they assumed that it works like in standard RDBMS engine.