Auto SCD API Tombstone Garbage Collection

[de01]

Are there any settings that can be used to influence the frequency at which the auto SCD API runs the tombstone garbage collection process in Spark Declarative Pipelines?  I've seen a couple community posts that referenced the following:

pipelines.applyChanges.tombstoneGCFrequencyInSeconds

pipelines.cdc.tombstoneGCFrequencyInSeconds

But these are not covered in any of the official Databricks docs am having trouble getting either of these to work at all.  When you sequence by a struct, the GC deletes become very inefficient from a data skipping perspective, so hoping there is a way to get these to run less frequently

[GabFernandes]

Hi @de01 ,

Short answer: Those two settings (pipelines.applyChanges.tombstoneGCFrequencyInSeconds and pipelines.cdc.tombstoneGCFrequencyInSeconds) are internal/undocumented configurations. They are not part of the public API surface and may have been removed, renamed, or gated behind specific runtime versions. There is currently no officially supported way to control tombstone GC frequency in Spark Declarative Pipelines.

---

What's happening under the hood

When you use AUTO CDC INTO (formerly APPLY CHANGES INTO), the pipeline maintains an internal state store of "tombstones" — markers for deleted keys. These ensure that late-arriving events for already-deleted records are correctly dropped (out-of-order event handling). Periodically, the pipeline runs a garbage collection pass that removes expired tombstones by issuing DELETE operations against the underlying Delta table.

Why SEQUENCE BY STRUCT(...) makes GC expensive

When your sequence column is a scalar (e.g., a single BIGINT or TIMESTAMP), Delta's file-level min/max statistics can efficiently skip files during the GC DELETE — only files potentially containing the tombstoned keys are rewritten.

With a STRUCT sequence (e.g., STRUCT(timestamp_col, id_col)), Delta's data skipping is far less effective because:

1. File-level statistics on nested struct fields are not as granular
2. The comparison semantics for structs (lexicographic, field-by-field) don't align well with the range-based pruning that min/max stats provide
3. The result: the GC DELETE effectively becomes a broad scan/rewrite, which is expensive and generates unnecessary I/O

What you can try

1. File a support ticket

This is the most reliable path. Databricks Support can confirm whether those configs still exist in your pipeline runtime version, provide the correct spelling/namespace, or offer an alternative. Internal configs change across runtime versions without notice.

2. Try both config namespaces in the pipeline settings JSON

If you haven't already, set them in the pipeline's configuration block (not Spark conf, not cluster conf):

{
  "configuration": {
    "pipelines.applyChanges.tombstoneGCFrequencyInSeconds": "86400",
    "pipelines.cdc.tombstoneGCFrequencyInSeconds": "86400"
  }
}

The fact that they don't error doesn't mean they're being honored — DLT/SDP silently ignores unknown keys. Check the event log (system.pipeline_events or the pipeline UI events tab) for GC-related entries to see if the interval actually changed.

3. Workaround: flatten the struct sequence

If possible, replace SEQUENCE BY STRUCT(timestamp_col, id_col) with a single monotonically increasing column that encodes the same ordering:

-- Example: pack timestamp + id into a single BIGINT
-- (timestamp as epoch_millis * 1_000_000 + id) guarantees the same ordering
SEQUENCE BY (UNIX_MILLIS(timestamp_col) * 1000000 + CAST(id_col AS BIGINT))

This gives you a scalar sequence that Delta can data-skip on efficiently, while preserving the same tie-breaking semantics. Trade-off: loses readability, but dramatically improves GC DELETE performance.

4. Workaround: increase the target file size

Fewer, larger files mean fewer files to scan/rewrite during GC. In your pipeline settings:

{
  "configuration": {
    "pipelines.applyChanges.tombstoneGCFrequencyInSeconds": "86400",
    "spark.databricks.delta.optimizeWrite.fileSize": "268435456"
  }
}

This won't reduce GC frequency, but it reduces the cost of each GC pass by minimizing the number of files touched.

5. Workaround: switch to SCD Type 2 if applicable

If your use case allows history tracking (SCD Type 2), tombstone management becomes less aggressive since deleted records are "closed" (end-dated) rather than physically purged from state. This doesn't eliminate GC entirely but may reduce its impact depending on your delete volume.

The underlying issue is less about GC frequency and more about GC efficiency. Even if you could reduce frequency to once a day, each run would still be expensive with a struct sequence. Flattening the sequence column addresses the root cause rather than just deferring it.

If my answer was helpful, please consider marking it as accepted solution!

[saisaranv]

Excellent one and I am able to sense it why need to avoid the struct in sequnceBy : I had used 2 columns on a table with 10B records with huge performance gap.

Thank you so much for the detailed explanation.

[de01 · Accepted solution]

I was able to get this figured out.  The setting that works is pipelines.applyChanges.tombstoneGCFrequencyInSeconds, and it seems like it can be set as either a pipeline configuration or in the spark conf of the individual streaming table(either in create_streaming_table or the table decorator).  The way you have to set it is kind of odd though, as you need to specify that the number you are providing is in seconds even though the property seems to suggest that it only accepts a numeric seconds value:

"pipelines.applyChanges.tombstoneGCFrequencyInSeconds": "86400 seconds"