The introspection-based approach like spark.table().columns introduces fragility
- Concurrency conflicts: Schema reads during MERGE can race with parallel writes, leading to partial column lists
- Column explosion/mutation: NoSQL sources evolve rapidly https://docs.databricks.com/aws/en/delta/column-mapping
- Performance overhead: Runtime schema diffs on TB-scale tables add 10-30s latency per MERGE (from catalog scans); in streaming/DLT, this cascades to checkpoint lags
- Error prone quoting/escaping: Dynamic interpolation mishandles reserved names (e.g., id, date)
Alternatives
- Delete+Insert (Recommended for NoSQL Overwrite)
Full Replace Partition (Bronze Layer)
Column Mapping + Rename/Drop (Metadata-Only):
Enable on table: ALTER TABLE test_table SET TBLPROPERTIES (delta.columnMapping.mode = 'name');
Then MERGE ... UPDATE SET * ignores physical mismatches
https://docs.databricks.com/aws/en/delta/column-mapping
An option like MERGE ... UPDATE SET * NULL_MISSING would standardize NoSQL-to-Delta syncs cutting custom logic by 80% vs. delete+insert overhead (~2x write amp). Docs confirm UPDATE SET * skips missing source cols intentionally —nulling them aligns with INSERT * semantics for true overwrites
