Update on the theory we are looking at.

It'd be similar to below (with necessary changes to support best practices for MERGE such as reducing the search space):

-- View for deduping pre-merge

 CREATE OR REPLACE TEMPORARY VIEW {view} AS SELECT * EXCEPT (dedupe_key) FROM (SELECT DISTINCT *, ROW_NUMBER() OVER (PARTITION BY {id} ORDER BY {timestamp}) AS dedupe_key FROM {bronze_table}) WHERE dedupe_key = 1;

-- Simple full table merge w/o tombstone handling/deletions

 MERGE INTO {silver_table} USING {view} ON {silver_table}.{id} = {view}.{id} WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *;

The above doesn't solve the problem of schema drift though. We do have some situations where there's incompatible schema changes that require a bit of wrangling to resolve. We're not looking to ever delete columns or rewrite columns when there's incompatible type changes though. Type changes (compatible or otherwise) will be added as a new column. We do this currently with append only.

So that leads to a related question of how we do that in this merge use-case.

Do we alter the silver table as we do with the bronze table? Generating ALTER TABLE ... ADD COLUMN statements as required? Or, do we use a REPLACE TABLE {silver_table} USING DELTA AS SELECT * FROM {view} LIMIT 0; before MERGE?

Again, we're looking at this and are concerned with the inefficiencies. We're aware of APPLY CHANGES INTO and other DLT features, but again they're all heavily dependent on Notebooks.