Predictive Optimization for Streaming tables in Lakeflow pipelines

[Gilk]

On July 2025 https://www.databricks.com/blog/whats-new-lakeflow-declarative-pipelines-july-2025 predictive optimization was enabled for all UC managed Lakeflow pipelines. I was wondering if there is a possibility now to disable it for Streaming tables?

I see that it possible for delta tables with ALTER TABLE { ENABLE | DISABLE | INHERIT } PREDICTIVE OPTIMIZATION, but there is nothing in documentation for streaming tables, lakeflow pipeline properties.

[Ashwin_DSA]

Hi @Gilk,

Before the how, can I ask what is driving the wish to turn it off? For most pipeline tables predictive optimization is doing useful work, running OPTIMIZE, VACUUM, and ANALYZE on serverless compute so file sizes, storage, and statistics stay healthy without you scheduling maintenance jobs. If the concern is cost, a conflict with your writes, or time-travel retention, there is often a more targeted solution than switching predictive optimization off entirely. Happy to point at the right one if you can share what you are seeing.

To answer your question... there is no toggle to turn off predictive optimization on the streaming table object itself. ALTER TABLE is not allowed on streaming tables, and neither ALTER STREAMING TABLE nor CREATE OR REFRESH exposes a PREDICTIVE OPTIMIZATION clause, so the per-table syntax you found for Delta managed tables has no direct equivalent here.

What you can do is control it one level up. Streaming tables and materialized views created by a Lakeflow pipeline are Unity Catalog managed tables, and predictive optimization uses an inheritance model across account, catalog, schema, and table. Since the table level is not settable for a streaming table, disable it at the schema or catalog that contains it, and the table inherits that.

ALTER SCHEMA my_catalog.my_schema DISABLE PREDICTIVE OPTIMIZATION;

or more broadly at catalog level..

ALTER CATALOG my_catalog DISABLE PREDICTIVE OPTIMIZATION;

PRedictive Optimization docs do not call out pipeline-managed tables as a special case, so I would verify it actually took effect on your object rather than assume. Both of these show the setting and whether it is inherited.

DESCRIBE SCHEMA EXTENDED my_catalog.my_schema;
DESCRIBE TABLE EXTENDED my_catalog.my_schema.my_streaming_table;

You can also confirm whether predictive optimzation is still touching the table by querying the predictive optimization system table, system.storage.predictive_optimization_operations_history.

If you would rather keep predictive optimization on but shape its behaviour instead of turning it off:

• Control VACUUM retention with the delta.deletedFileRetentionDuration table property, set in your pipeline definition. Just remember to set it before predictive optimization is enabled, and VACUUM FULL still enforces a 7-day minimum even if you configure a shorter value.
• If you are using automatic liquid clustering, predictive optimization may select or evolve clustering keys. Specifying explicit clustering columns in your pipeline definition avoids that.
• If predictive optimization is causing concurrency conflicts with active streaming writes, that is worth raising a support ticket.

Hope that helps.

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.

[Gilk]

Hi @Ashwin_DSA ,

Thanks for your answer.

Unfortunately, this doesn’t work. Even when predictive optimization (PO) is disabled at the schema or catalog level, the pipeline creates streaming tables with PO enabled.

We would like to disable PO because, in our case, it provides no benefit and only adds costs. Running OPTIMIZE and VACUUM once a day is sufficient for our needs. However, PO runs OPTIMIZE, VACUUM, and
ANALYZE multiple times throughout the day, resulting in additional charges.

Another issue is compression. We write these tables using ZSTD compression, but PO rewrites the files using Snappy during optimization. In our case, this increases the storage footprint compared with ZSTD.

[Ashwin_DSA]

Hi @Gilk,

Thanks. I need to correct my earlier answer.

Having checked this internally, for streaming tables and materialized views managed by a Lakeflow pipeline, there is currently no supported way to disable predictive optimization. PO runs maintenance on these pipeline-published tables even when it is disabled at the account, catalog, or schema level, so the inheritance approach I suggested does not override the pipeline behaviour. The pipelines.autoOptimize.managed property does not help either, since the docs note it is not used for pipelines managed by predictive optimisation.

PO maintains pipeline tables through backing objects that a table-level switch would not fully cover, and the pipeline model guarantees these tables stay maintained rather than silently accumulating stale files. The trade-off is that maintenance runs at an adaptive cadence rather than a fixed daily cadence, which is positive for many pipelines but is exactly the wrong side of the deal when a single daily OPTIMIZE and VACUUM already meets your needs. A hard opt-out or a cadence control is a fair feature request to raise with your Databricks account team or via a support case.

The compression point is more actionable, though. The codec OPTIMIZE writes with is controlled by the delta.parquet.compression.codec table property, which it reads from the Delta log. If that property is not set on the table, OPTIMIZE defaults away from ZSTD, which lines up with what you are seeing. Your ZSTD is likely coming from a write-time setting that OPTIMIZE does not pick up. The fix is to set the codec as a table property in the pipeline definition so it survives pipeline updates and PO honors it on subsequent OPTIMIZE runs.

For a SQL pipeline, add TBLPROPERTIES ('delta.parquet.compression.codec' = 'zstd') to the streaming table definition. For Python, pass table_properties={"delta.parquet.compression.codec": "zstd"} on the table decorator.

Setting the property only affects future writes. To recompress existing files, OPTIMIZE table_name FULL recompresses when the codec changes, on Databricks Runtime 16.0 and above. One caveat, manual maintenance commands are often restricted on pipeline-managed streaming tables, so I would test OPTIMIZE FULL on a single table before relying on it. The table property route above is the safe path either way, since PO will apply the new codec on its next run.

Let me know how that goes. Happy to investigate further.