Three ways to MERGE into Delta, and when to use each

Loading Delta is easy until you apply changes. Then you choose how updates and deletes merge, and the wrong choice shows up as a warehouse bill rather than an error message.

Version 9.6.4 added native Databricks support with dedicated flow types for Delta: bulk load through COPY INTO, streaming CDC, and message-queue ingestion. Three MERGE strategies come with it.

DELETE/INSERT

Delete the matching keys from the target, then insert the new versions.

Predictable and easy to reason about. It rewrites more data than a true merge, so on a wide table with a small change set you pay to move columns that did not change. Use it when the change ratio is high relative to table size, or when you want behavior you can explain to whoever debugs it later.

Native MERGE

A single MERGE INTO that Databricks plans and executes.

This is the default for most CDC workloads. Updates touch matched rows, inserts land, and the engine handles file pruning. It behaves best when your merge key aligns with the table's partitioning or clustering, because that is what lets Databricks skip files instead of scanning them. If the merge predicate cannot prune, a native MERGE on a large table becomes an expensive full scan, and it shows up in DBU spend before it shows up in run time.

Native MERGE with separate DELETE

The merge handles inserts and updates. Deletes run as their own statement.

Useful when deletes are rare compared to upserts, or when the combined statement produces a plan you do not like. Splitting them gives the optimizer two simple problems instead of one complicated one, and it makes delete volume visible separately in query history.

Schema drift and TEXT-only staging

The other thing that breaks Delta loads is an upstream schema change. A source adds a column, changes a type from int to bigint, or starts sending a numeric field with a stray character, and the load fails on type conversion in the staging table.

Databricks flow types can create the staging temp table with all columns as TEXT. Everything lands, conversion happens on the way into the target, and an upstream type change stops being an outage. You give up conversion strictness at the staging boundary in exchange for a load that survives a source you do not control. For most teams ingesting from another department's system, that trade is correct.

Naming and authentication

Unity Catalog three-part naming (catalog.schema.table) is supported directly, so you are not fighting the namespace.

Authentication is a personal access token or an OAuth service principal. Use the service principal for anything scheduled. A PAT belongs to a human, and humans leave, change roles, and rotate credentials. A pipeline that stops overnight because someone's token expired is an avoidable class of incident.

Throughput controls added in 9.7.11

The first release covered correctness. The follow-up covered speed:

• Combine staged files into a single COPY INTO. Fewer, larger operations instead of one per file. Usually the first thing to turn on with many small source files.
• Direct INSERT COPY. Skips the staging table and loads straight into the target. Fastest path, correct only for append-only data where you do not need staging to deduplicate or transform. Use it with change data and you get duplicates.
• Custom Format options. Pass through COPY INTO format settings when the defaults do not match your files.
• Override COPY INTO SQL. Replace the generated statement entirely.

That last one exists because generated SQL is a good default and a bad absolute. When you have a partitioning scheme the generator cannot infer, you should be able to write the statement yourself rather than fight the tool or leave the platform.

The same release made bulk-load tasks open and close database connections lazily, cutting idle connection count during a load.

Start here

Loading Delta for the first time: native MERGE, TEXT-only staging, OAuth service principal, and combined COPY INTO if your source produces many files. Measure, then reach for the rest when you have a reason.

What runs into and out of Databricks, including CDC and reverse ETL, is on the Databricks integration page.