@kapa.ai we're using Databricks Lakehouse destination v3.3.7 with T+D (Incremental + Deduped) and have a question about how typing handles absent JSON fields. Our custom source emits sparse tombstones for soft deletes — only

{primary_key, cursor_field, _dip_deleted_at}

are present in the JSON, all other columns are absent. We believe T+D extracts absent keys as

NULL

during the typing step, which then overwrites existing target values in the MERGE — so a tombstone for

je_header_id=102

ends up NULLing out all 89 other columns even though the row already exists in the final table with real values. Is that correct? If so — is there any T+D config or pattern that handles this gracefully without carrying the full row in the tombstone? Ideally something like

WHEN MATCHED AND source._dip_deleted_at IS NOT NULL THEN UPDATE SET target._dip_deleted_at = source._dip_deleted_at

so only the deletion flag is updated and existing column values are preserved. Right now our workaround is storing the full last-known row in SQLite (~72GB for 240M rows) and reconstructing the full tombstone — works but very expensive. Any recommended pattern for soft deletes with non-CDC API sources + T+D would be super helpful! 🙏

I'm sorry, but the knowledge sources don't contain enough information to give you a fully confident answer on all aspects of your question, but I can address the core points based on what's available. Your Understanding Is Correct Yes, your diagnosis is accurate. In Incremental + Deduped (T+D) mode, the MERGE logic works like this:

WHEN MATCHED AND source.cursor > target.cursor THEN UPDATE SET
  col1 = source.col1,
  col2 = source.col2,
  ...  -- ALL columns, including absent ones typed as NULL

Absent JSON keys are extracted as

NULL

during the typing step, and the

UPDATE SET

clause replaces all target columns with source values — so a sparse tombstone will indeed NULL out all 89 other columns. [CDC Handling] This is also confirmed by a similar open issue with MongoDB → BigQuery where soft deletes in Deduped mode caused all fields except

_id

to be set to NULL. [BigQuery Issue] What T+D Soft Delete Actually Does The built-in soft delete mode in Airbyte is designed for CDC sources that emit the

_ab_cdc_deleted_at

field. In soft delete mode, the MERGE simply upserts the tombstone record (with

_ab_cdc_deleted_at

populated) — but it still overwrites all columns with whatever the source emitted. There is no partial-update / patch semantics built in. [CDC Implementation] Your field

_dip_deleted_at

is a custom field (not

_ab_cdc_deleted_at

), so Airbyte's CDC handling won't apply to it at all. What's Not Supported Out of the Box The knowledge sources do not describe any T+D configuration option that would produce a partial

UPDATE SET target._dip_deleted_at = source._dip_deleted_at

— i.e., a column-selective update only when a deletion flag is present. That kind of merge logic is not exposed as a user-configurable option in the Databricks Lakehouse destination. Recommended Patterns (Based on Available Info) Given the constraints, here are the realistic options: 1. Carry the full row in the tombstone — your current SQLite approach. Expensive but correct within T+D semantics. 2. Use Incremental Append mode instead of Deduped, and handle deduplication downstream (e.g., in a dbt model or Databricks SQL view) with logic like:SELECT * EXCEPT(rn) FROM ( SELECT *, ROW_NUMBER() OVER ( PARTITION BY primary_key ORDER BY cursor_field DESC ) AS rn FROM raw_table ) WHERE rn = 1 AND _dip_deleted_at IS NULLThis avoids the destructive MERGE entirely. [Incremental Append] 3. Post-sync dbt transformation — let T+D write the sparse tombstone (NULLing columns), then use a dbt model to restore values from the raw/history table where

_dip_deleted_at IS NOT NULL

. 4. Switch to a CDC-capable source — if your API can be fronted by a CDC-enabled database, Airbyte's native CDC handling would manage deletions more gracefully. Unfortunately, the knowledge sources don't confirm whether Databricks Lakehouse v3.3.7 exposes any connector-level configuration to customize the MERGE UPDATE clause for partial updates. I'd recommend opening a GitHub issue or checking with Airbyte support for a connector-level feature request for partial-update semantics on soft deletes. 👍 0 button 👎 0 button ℹ️ Tip: Mention @kapa.ai in the thread for follow-ups or DM the app for 1-to-1 help.